LOCK TABLES syntax

LOCK TABLES table_name [AS alias] READ|WRITE [, table_name READ|WRITE]
...
UNLOCK TABLES

Locks tables for this thread. If a thread has a READ lock on a table, the thread (and all other threads) can only read from the table. If a thread has a WRITE lock one a table, then only this thread can READ and WRITE on the table. All threads waits until they get all locks (no timeouts).

When one uses LOCK TABLES one must lock all tables one is going to use! This policy ensures that table locking is deadlock free.

LOCK TABLES trans READ, customer AS c WRITE
SELECT SUM(value) FROM trans WHERE customer_id= #some_id#;
UPDATE customer SET total_value=#value_from_last_statement# WHERE
customer_id=#some_id#
UNLOCK TABLES

All tables are automatically unlocked when one issues another LOCK TABLES or if the connection to the server is closed.

Normally you don't have to lock tables. There is a couple of cases when you would like to lock tables anyway:

• If you are going to run many operations on a bunch of tables, its much faster to lock the tables you are going to use. The downside is of course that no other thread can update a READ locked table and no other thread can read a WRITE locked table.
• As MySQL doesn't support a transaction environment, you must use lock tables if you want to ensure that no other thread comes between a read and a update. For example the previous example requires LOCK TABLES to be safe! If one didn't use LOCK TABLES there is a change that someone inserts a new 'trans' row between the SELECT and UPDATE statements.
• By using incremental updates (UPDATE customer set value=value+new_value) or the LAST_INSERT_ID() function you can avoid using LOCK TABLES in many cases.

You can also solve some cases by using user level locks: GET_LOCK() and RELEASE_LOCK(). These locks are saved in a hash table in the server and implemented with pthread_mutex for high speed. See section Miscellaneous functions.

Go to the first, previous, next, last section, table of contents.