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
.
Warning:
To enable table locks, you need to set
enable-table-lock
totrue
in the configuration files of all TiDB instances.
Synopsis
- LockTablesDef
- UnlockTablesDef
- LockType
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, theLOCK 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.