To use LOCK TABLES, you need the global LOCK TABLES privilege and a SELECT privilege on the tables involved.
The main reasons for using LOCK TABLES are to emulate transactions or to get more speed when updating tables.
LOCK TABLES works as follows:
- Sorts all tables to be locked in an internally defined order (from the user's point of view, the order is undefined).
- If a table is locked with a read lock and a write lock, it will place the write lock before the read.
- Blocks one table at a time until the thread gets all the locks.
The policy ensures that tablelocking is free of deadlosks *. There are, however, other things you should be aware of with this scheme:
If you are using a LOW_PRIORITY write lock on a table, this simply means that MySQL will wait for this particular lock until there are no threads that want a read lock. When the thread has the write lock and is waiting to get the next table lock in the list of tables to be locked, all other threads will wait for the write lock to be released. If this becomes a serious problem with your application, you should convert some of your tables into transaction tables.
You can safely terminate a thread that is waiting for a table lock with KILL.
Note that you should not lock tables that you are using using INSERT DELAYED. This is because, in this case, INSERT is done by a separate thread.
Typically, you do not have to lock tables because all single UPDATE declarations are atomic, no other thread can interfere with some other currently running SQL statement. There are some cases where you would like to lock tables anyway:
If you perform many operations on multiple tables, it is much faster to lock the tables you will be using. The problem is that no other thread can update a table with read lock and no other thread can read a locked table for write.
The reason some things are faster under LOCK TABLES is that MySQL will not unload the key cache of locked tables until UNLOCK TABLES is called (normally the key cache is unloaded after each SQL statement). This increases the speed of insertion, update, and deletion in MyISAM tables.
If you are using a storage engine in MySQL that does not support transactions, you should use LOCK TABLES if you want to make sure that no other threads are between a SELECT and an UPDATE. The example shown here requires LOCK TABLES to run quickly:
mysql> LOCK TABLES trans READ, clientes WRITE;
mysql> SELECT SUM(valor) FROM trans WHERE id_do_cliente=algum_id;
mysql> UPDATE cliente SET valor_total=soma_das_declarações_anteriores
-> WHERE id_do_cliente=algum_id;
mysql> UNLOCK TABLES;
Without UNLOCK TABLES there is a chance of another thread inserting a new row in the trans table between the execution of the SELECT and UPDATE statements.
* Deadlock in the context of database (MS-SQL / MySQL), characterizes a situation in which a deadlock occurs and two or more processes are prevented from continuing their executions, that is, they are blocked.
Source: MySQL Certification Study Guide (translation: Acauan Fernandes)
This article is also published in 6.7.5. Syntax LOCK TABLES and UNLOCK TABLES
The Handbook is available online in a variety of formats and languages on the MySQL AB website ( link )