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

LOCK TABLES locks base tables (but not views) for the current thread. If any of the tables are locked by other threads, it blocks until all locks can be acquired.

UNLOCK TABLES explicitly releases any locks held by the current thread. All tables that are locked by the current thread are implicitly unlocked when the thread issues another LOCK TABLES, or when the connection to the server is closed. UNLOCK TABLES is also used after acquiring a global read lock with FLUSH TABLES WITH READ LOCK to release that lock. (You can lock all tables in all databases with read locks with the FLUSH TABLES WITH READ LOCK statement. See Section 13.5.5.2, “FLUSH Syntax”. This is a very convenient way to get backups if you have a filesystem such as Veritas that can take snapshots in time.)

To use LOCK TABLES, you must have the LOCK TABLES privilege and the SELECT privilege for the involved tables.

The main reasons to use LOCK TABLES are to emulate transactions or to get more speed when updating tables. This is explained in more detail later.

A table lock protects only against inappropriate reads or writes by other clients. The client holding the lock, even a read lock, can perform table-level operations such as DROP TABLE. Truncate operations are not transaction-safe, so an error occurs if the client attempts one during an active transaction or while holding a table lock.

Note the following regarding the use of LOCK TABLES with transactional tables:

When you use LOCK TABLES, you must lock all tables that you are going to use in your statements. Because LOCK TABLES will not lock views, if the operation that you are performing uses any views, you must also lock all of the base tables on which those views depend. While the locks obtained with a LOCK TABLES statement are in effect, you cannot access any tables that were not locked by the statement. Also, you cannot use a locked table multiple times in a single query. Use aliases instead, in which case you must obtain a lock for each alias separately.

mysql> LOCK TABLE t WRITE, t AS t1 WRITE;
mysql> INSERT INTO t SELECT * FROM t;
ERROR 1100: Table 't' was not locked with LOCK TABLES
mysql> INSERT INTO t SELECT * FROM t AS t1;

If your statements refer to a table by means of an alias, you must lock the table using that same alias. It does not work to lock the table without specifying the alias:

mysql> LOCK TABLE t READ;
mysql> SELECT * FROM t AS myalias;
ERROR 1100: Table 'myalias' was not locked with LOCK TABLES

Conversely, if you lock a table using an alias, you must refer to it in your statements using that alias:

mysql> LOCK TABLE t AS myalias READ;
mysql> SELECT * FROM t;
ERROR 1100: Table 't' was not locked with LOCK TABLES
mysql> SELECT * FROM t AS myalias;

If a thread obtains a READ lock on a table, that thread (and all other threads) can only read from the table. If a thread obtains a WRITE lock on a table, only the thread holding the lock can write to the table. Other threads are blocked from reading or writing the table until the lock has been released.

The difference between READ LOCAL and READ is that READ LOCAL allows non-conflicting INSERT statements (concurrent inserts) to execute while the lock is held. However, this cannot be used if you are going to manipulate the database using processes external to the server while you hold the lock. For InnoDB tables, READ LOCAL is the same as READ.

WRITE locks normally have higher priority than READ locks to ensure that updates are processed as soon as possible. This means that if one thread obtains a READ lock and then another thread requests a WRITE lock, subsequent READ lock requests wait until the WRITE thread has gotten the lock and released it. You can use LOW_PRIORITY WRITE locks to allow other threads to obtain READ locks before the thread that is waiting for the WRITE lock. You should use LOW_PRIORITY WRITE locks only if you are sure that eventually there will be a time when no threads have a READ lock. (Exception: For InnoDB tables in transactional mode (autocommit = 0), a LOW_PRIORITY WRITE lock acts like a regular WRITE lock and precedes waiting READ locks.)

LOCK TABLES works as follows:

This policy ensures that table locking is deadlock free. There are, however, other things you need to be aware of about this policy: If you are using a LOW_PRIORITY WRITE lock for a table, it means only that MySQL waits for this particular lock until there are no threads that want a READ lock. When the thread has gotten the WRITE lock and is waiting to get the lock for the next table in the lock table list, all other threads wait for the WRITE lock to be released. If this becomes a serious problem with your application, you should consider converting some of your tables to transaction-safe tables.

For some operations, system tables in the mysql database must be accessed. For example, the HELP statement requires the contents of the server-side help tables, and CONVERT_TZ() might need to read the time zone tables. Before MySQL 5.1.17, to perform such operations while a LOCK TABLES statement is in effect, you must also lock the requisite system tables explicitly or a lock error occurs. As of 5.1.17, the server implicitly locks the system tables for reading as necessary so that you need not lock them explicitly. These tables are treated as just described:

mysql.help_category
mysql.help_keyword
mysql.help_relation
mysql.help_topic
mysql.proc
mysql.time_zone
mysql.time_zone_leap_second
mysql.time_zone_name
mysql.time_zone_transition
mysql.time_zone_transition_type

If you want to explicitly place a WRITE lock on any of those tables with a LOCK TABLES statement, the table must be the only one locked; no other table can be locked with the same statement.

You can safely use KILL to terminate a thread that is waiting for a table lock. See Section 13.5.5.3, “KILL Syntax”.

Note that you should not lock any tables that you are using with INSERT DELAYED because in that case the INSERT is performed by a separate thread.

Normally, you do not need to lock tables, because all single UPDATE statements are atomic; no other thread can interfere with any other currently executing SQL statement. However, there are a few cases when locking tables may provide an advantage:

You can avoid using LOCK TABLES in many cases by using relative updates (UPDATE customer SET value=value+new_value) or the LAST_INSERT_ID() function. See Section 1.9.5.3, “Transactions and Atomic Operations”.

You can also avoid locking tables in some cases by using the user-level advisory lock functions GET_LOCK() and RELEASE_LOCK(). These locks are saved in a hash table in the server and implemented with pthread_mutex_lock() and pthread_mutex_unlock() for high speed. See Section 12.11.4, “Miscellaneous Functions”.

See Section 7.3.1, “Internal Locking Methods”, for more information on locking policy.

Note: If you use ALTER TABLE on a locked table, it may become unlocked. See Section B.1.7.1, “Problems with ALTER TABLE”.