You should not lock any tables that you are
INSERT DELAYED. An
INSERT DELAYED in this case
results in an error because the insert must be handled by a
separate thread, not by the session which holds the lock.
Normally, you do not need to lock tables, because all single
UPDATE statements are atomic; no
other session can interfere with any other currently executing
SQL statement. However, there are a few cases when locking
tables may provide an advantage:
If you are going to run many operations on a set of
MyISAMtables, it is much faster to lock the tables you are going to use. Locking
MyISAMtables speeds up inserting, updating, or deleting on them because MySQL does not flush the key cache for the locked tables until
UNLOCK TABLESis called. Normally, the key cache is flushed after each SQL statement.
The downside to locking the tables is that no session can update a
READ-locked table (including the one holding the lock) and no session can access a
WRITE-locked table other than the one holding the lock.
If you are using tables for a nontransactional storage engine, you must use
LOCK TABLESif you want to ensure that no other session modifies the tables between a
UPDATE. The example shown here requires
LOCK TABLESto execute safely:
LOCK TABLES trans READ, customer WRITE; SELECT SUM(value) FROM trans WHERE customer_id=
some_id; UPDATE customer SET total_value=
some_id; UNLOCK TABLES;
You can avoid using
in many cases by using relative updates (
See Section 126.96.36.199, “Transactions and Atomic Operations”.
You can also avoid locking tables in some cases by using the
user-level advisory lock functions
RELEASE_LOCK(). These locks are
saved in a hash table in the server and implemented with
pthread_mutex_unlock() for high speed. See
Section 12.15, “Miscellaneous Functions”.
See Section 8.11.1, “Internal Locking Methods”, for more information on locking policy.