LOCK TABLES and UNLOCK TABLES

TiDB enables client sessions to acquire table locks for the purpose of cooperating with other sessions for access to tables, or to prevent other sessions from modifying tables. 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 acquires table locks for the current client session. If you have the LOCK TABLES and SELECT privileges for each object to be locked, you can acquire table locks for common tables.

UNLOCK TABLES explicitly releases any table locks held by the current session. LOCK TABLES implicitly releases all table locks held by the current session before acquiring new locks.

A table lock protects against reads or writes by other sessions. A session that holds a WRITE lock can perform table-level operations such as DROP TABLE or TRUNCATE TABLE.

Note：

The table locks feature is disabled by default.

• For TiDB Self-Hosted, to enable the table locks feature, you need to set enable-table-lock to true in the configuration files of all TiDB instances.
• For TiDB Cloud, to enable the table locks feature, you need to contact TiDB Cloud Support to set enable-table-lock to true.

Synopsis

LockTablesDef
         ::= 'LOCK' ( 'TABLES' | 'TABLE' ) TableName LockType ( ',' TableName LockType)*


UnlockTablesDef
         ::= 'UNLOCK' 'TABLES'

LockType
         ::= 'READ' ('LOCAL')?
           | 'WRITE' ('LOCAL')?

Acquire table locks

You can acquire table locks within the current session by using the LOCK TABLES statement. The following lock types are available:

READ lock:

• The session that holds this lock can read the table, but cannot write it.
• Multiple sessions can acquire a READ lock from the same table at the same time.
• Other sessions can read the table without explicitly acquiring a READ lock.

The READ LOCAL lock is only for syntax compatibility with MySQL and is not supported.

WRITE lock:

• The session that holds this lock can read and write the table.
• Only the session that holds this lock can access the table. No other sessions can access it until the lock is released.

WRITE LOCAL lock:

• The session that holds this lock can read and write the table.
• Only the session that holds this lock can access the table. Other sessions can read the table, but cannot write it.

If the lock that the LOCK TABLES statement needs is held by another session, the LOCK TABLES statement must wait, and an error is returned upon the execution of this statement, for example:

> LOCK TABLES t1 READ;
ERROR 8020 (HY000): Table 't1' was locked in WRITE by server: f4799bcb-cad7-4285-8a6d-23d3555173f1_session: 2199023255959

The preceding error message indicates that the session with ID 2199023255959 in TiDB f4799bcb-cad7-4285-8a6d-23d3555173f1 already holds a WRITE lock on table t1. Therefore, the current session cannot acquire a READ lock on table t1.

You cannot acquire the same table lock multiple times in a single LOCK TABLES statement.

> LOCK TABLES t WRITE, t READ;
ERROR 1066 (42000): Not unique table/alias: 't'

Release table locks

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

• A session can release its locks explicitly with UNLOCK TABLES.
• If a session issues a LOCK TABLES statement to acquire a lock while already holding locks, its existing locks are released implicitly before the new locks are acquired.

If the connection for a client session terminates, whether normally or abnormally, TiDB implicitly releases all table locks held by the session. If the client reconnects, the locks are no longer in effect. For this reason, it is not recommended to enable auto-reconnection on the client. If you enable auto-reconnection, the client is not notified when reconnection occurs, and all table locks or current transactions are lost. By contrast, with auto-reconnection disabled, if the connection drops, an error occurs when the next statement is issued. The client can detect the error and take appropriate action such as reacquiring the locks or redoing the transaction.

Table-locking restrictions and conditions

You can safely use KILL to terminate a session that holds a table lock.

You cannot acquire table locks on tables in the following databases:

• INFORMATION_SCHEMA
• PERFORMANCE_SCHEMA
• METRICS_SCHEMA
• mysql

MySQL compatibility

Table lock acquisition

• In TiDB, if session A has already held a table lock, an error is returned if session B attempts to write to the table. In MySQL, the write request of session B is blocked until session A releases the table lock, and requests for locking the table from other sessions are blocked until the current session releases the WRITE lock.
• In TiDB, if the lock that the LOCK TABLES statement needs is held by another session, the LOCK TABLES statement must wait, and an error is returned upon the execution of this statement. In MySQL, this statement is blocked until the lock is acquired.
• In TiDB, the LOCK TABLES statement is effective in the whole cluster. In MySQL, this statement is effective only in the current MySQL server, and is not compatible with the NDB cluster.

Table lock release

When a transaction is explicitly started in a TiDB session (for example, with the BEGIN statement), TiDB does not implicitly release the table locks held by the session; but MySQL does.