MySQL Forums
Forum List  »  Newbie

Re: Table Row Locking
Posted by: Peter Brawley
Date: January 11, 2012 04:44PM

Before MySQL implemented transactions in INNODB tables, MySQL offered pseudo-transactions in the form of a LOCK/UNLOCK TABLES command. They are still available:

LOCK TABLES tableName [AS alias] {READ | [READ LOCAL] | [LOW_PRIORITY] WRITE} [, tbl_name {READ | [LOW_PRIORITY] WRITE} ...] ... UNLOCK TABLES

LOCK TABLES applies to the current thread, until another LOCK TABLES, an UNLOCK TABLES, or connection death. You must lock all tables and aliases that will be used. Since 4.0.3, the command requires LOCK TABLES privilege.

While a thread holds a READ lock on a table, that and other threads can only read from the table. While a thread holds a WRITE lock on a table, only that thread can read or write the table. READ LOCAL allows non-conflicting INSERT statements to execute while the lock is held. While a thread holds a READ lock, if another thread requests a WRITE lock, other READ lock requests will wait until the WRITE thread locked and unlocked.

LOW_PRIORITY WRITE allows read locks to other threads while the thread is waited on. Obviously if there are many users likely to issue read locks, LOW_PRIORITY_WRITE may leave the update waiting indefinitely.

For portablity, robustness and ease of maintenance, choose ANSI/ISO SQL commands over non-standard ones. In most cases, INNODB transaction management supplies much more flexible concurrency management.

A workaround could be to make all tables as InnoDB and maintain keyed copies of text in MyISAM tables for text-searching queries & reports.

Options: ReplyQuote


Subject
Written By
Posted
January 11, 2012 03:27PM
Re: Table Row Locking
January 11, 2012 04:44PM
January 11, 2012 05:08PM
January 13, 2012 09:53AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.