Re: Table Row Locking
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.
Subject
Written By
Posted
Re: Table Row Locking
January 11, 2012 04:44PM
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.