LOCK TABLES
    tbl_name [[AS] alias] lock_type
    [, tbl_name [[AS] alias] lock_type] ...

lock_type:
    READ [LOCAL]
  | [LOW_PRIORITY] WRITE
  | IN SHARE MODE [NOWAIT]
  | IN EXCLUSIVE MODE [NOWAIT]

UNLOCK TABLES

MySQL enables client sessions to acquire table locks explicitly for the purpose of cooperating with other sessions for access to tables, or to prevent other sessions from modifying tables during periods when a session requires exclusive access to them. A session can acquire or release locks only for itself. One session cannot acquire locks for another session or release locks held by another session.

LOCK TABLES explicitly acquires non-transactional or transactional table locks for the current client session.

Table locks can be acquired for base tables or views. You must have the LOCK TABLES privilege, and the SELECT privilege for each object to be locked. (For view locking, LOCK TABLES adds all base tables used in the view to the set of tables to be locked and locks them automatically.)

MySQL 6.0 supports non-transactional and transactional locks. These are intended for use in non-transactional or transactional context, respectively. Non-transactional locks may be used to emulate transactions or to get more speed when updating tables. This is explained in more detail later in this section.

UNLOCK TABLES explicitly releases non-transactional table locks held by the current session. Transactional table locks are released by ending the current transaction.

Another use for UNLOCK TABLES is to release the global read lock acquired with FLUSH TABLES WITH READ LOCK. (You can lock all tables in all databases with a read lock with the FLUSH TABLES WITH READ LOCK statement. See Section 12.5.7.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.)

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

As of MySQL 6.0.3, DROP TABLE is allowed only if you have acquired a WRITE lock with LOCK TABLES, or if you hold no locks, or if the table is a TEMPORARY table. (Previously, if other tables were locked, you could drop a table while holding a read lock or no lock for it, which could lead to deadlocks between sessions. The current stricter behavior means that some usage scenarios will fail when previously they did not.)

The following discussion applies only to non-TEMPORARY tables. LOCK TABLES is allowed (but ignored) for a TEMPORARY table. The table can be accessed freely by the session within which it was created, regardless of what other locking may be in effect. No lock is necessary because no other session can see the table.

Rules for Lock Acquisition

To acquire non-transactional or transactional table locks within the current session, use the LOCK TABLES statement.

Rules for acquisition of non-transactional locks. MySQL supports non-transactional read and write table locks. These can be acquired in non-transactional contexts (that is, when autocommit is enabled).

READ [LOCAL] lock:

[LOW_PRIORITY] WRITE lock:

A session that requires non-transactional locks must acquire all the locks that it needs in a single LOCK TABLES statement.

A session that holds non-transactional locks can access only the locked tables while the locks are held.

A session cannot hold non-transactional locks and use transactions at the same time. Acquisition of a non-transactional lock implicitly commits any active transaction for the current session, and beginning a transaction implicitly releases all locks held by the session. (Additional information about the interaction between table locking and transactions is given later in this section.)

The difference between READ and READ LOCAL is that READ LOCAL allows non-conflicting INSERT statements (concurrent inserts) to execute while the lock is held. However, READ LOCAL 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 session obtains a READ lock and then another session requests a WRITE lock, subsequent READ lock requests wait until the session that requested the WRITE lock has obtained the lock and released it. A request for a LOW_PRIORITY WRITE lock, by contrast, allows subsequent READ lock requests by other sessions to be satisfied first if they occur while the LOW_PRIORITY WRITE request is waiting. You should use LOW_PRIORITY WRITE locks only if you are sure that eventually there will be a time when no sessions have a READ lock. For InnoDB tables in transactional mode (autocommit = 0), a waiting LOW_PRIORITY WRITE lock acts like a regular WRITE lock and causes subsequent READ lock requests to wait.

LOCK TABLES works as follows for non-transactional locks:

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 other sessions that want a READ lock. When the session has gotten the WRITE lock and is waiting to get the lock for the next table in the lock table list, all other sessions 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.

Rules for acquisition of transactional locks. As of MySQL 6.0.3, MySQL supports transactional shared and exclusive table locks that do not commit transactions automatically. These locks apply only for transactional storage engines that support them and only during a transaction (that is, when autocommit is disabled).

Currently, only InnoDB supports transactional locks. For other transactional storage engines or for non-transactional storage engines, requests for transactional locks are converted to requests for non-transactional locks, as described later.

IN SHARE MODE [NOWAIT] lock:

IN EXCLUSIVE MODE [NOWAIT] lock:

By default, LOCK TABLES waits if all requested locks cannot be acquired immediately (for example, if the requests cannot be granted due to locks held by other sessions). For transactional locks, the NOWAIT modifier can be given. The intent of this modifier is that the lock request will fail with an error if the lock cannot be acquired immediately, but NOWAIT is not currently implemented by any storage engine.

A session that requires transactional locks need not acquire them all in a single LOCK TABLES statement. A session can acquire transactional locks sequentially with multiple LOCK TABLES statements, each one adding new locks to the current set of locks. It is even possible to acquire additional transactional locks on a table for which the session already holds transactional locks.

A session that holds transactional locks can access non-locked tables while the locks are held.

Transactional locks are not specific to reading or writing. Both operations are allowed to the holder of the lock, whether it is shared or exclusive, with some restrictions:

Transactional locks do not apply if a session is not in transactional context; that is, when autocommit mode is enabled because the session has not used START TRANSACTION or SET AUTOCOMMIT = 0. In this case, the lock is released as soon as the LOCK TABLES statement ends, which makes the statement almost a non-operation. The only difference is that the request blocks if it must wait for an existing lock to be released, but then the new lock is immediately released.

For LOCK TABLES statements that involve a mix of non-transactional and transactional locks, requests for transactional locks are converted to requests for non-transactional locks. This occurs because a session can hold multiple locks at a time, but cannot hold a mix of non-transactional and transactional locks:

For example, these operations are allowed because they request only non-transactional locks, or only transactional locks:

LOCK TABLES t1 READ, t2 WRITE, t3 READ;
LOCK TABLES t4 IN SHARE MODE, t5 IN EXCLUSIVE MODE, t6 IN SHARE MODE;

But these operations cannot be processed as requested because they attempt to acquire a mix of non-transactional and transactional locks:

LOCK TABLES t1 READ, t2 IN SHARE MODE;
LOCK TABLES t3 WRITE, t4 IN EXCLUSIVE MODE;

For the latter statements, the requests for transactional locks are converted to requests for non-transactional locks. Lock conversions may succeed or fail, as described later.

You cannot refer to a locked table multiple times in a single query using the same name. Use aliases instead, and obtain a separate lock for the table and each alias:

mysql> LOCK TABLE t WRITE, t AS t1 READ;
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;

The error occurs for the first INSERT because there are two references to the same name for a locked table. The second INSERT succeeds because the references to the table use different names.

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;

Rules for Lock Release

When the table locks held by a session are released, they are all released at the same time. A session can release its locks explicitly, or locks may be released implicitly under certain conditions.

Rules for lock release when a session holds non-transactional locks:

Rules for lock release when a session holds transactional locks:

If a client connection drops, the server releases table locks held by the client. If the client reconnects, the locks will no longer be in effect. In addition, if the client had an active transaction, the server rolls back the transaction upon disconnect, and if reconnect occurs, the new session begins with autocommit enabled. For this reason, clients may wish to disable auto-reconnect. With auto-reconnect in effect, the client is not notified if reconnect occurs but any table locks or current transaction will have been lost. With auto-reconnect disabled, if the connection drops, an error occurs for the next statement issued. The client can detect the error and take appropriate action such as reacquiring the locks or redoing the transaction. See Section 28.2.13, “Controlling Automatic Reconnect Behavior”.

Rules for Transactional Lock Conversion

Under some circumstances, a request for a transactional lock cannot be granted:

When a transactional lock cannot be granted for the preceding reasons, the request is converted to a request for a non-transactional lock. The conversion is handled as follows:

The following notes describe what happens when a session already holds one type of lock and then requests another lock:

Interaction of Table Locking and Transactions

LOCK TABLES and UNLOCK TABLES interact with the use of transactions as follows:

Other Table-Locking Notes

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

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.

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. 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.

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:

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.8.5.2, “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 11.11.4, “Miscellaneous Functions”.

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