Purpose
This statement is used to lock a table in a session, preventing other sessions from performing concurrent operations on the same table. It can be used to lock a table when all DML and DDL operations on that table need to be stopped.
Prerequisites
Before using the
LOCK TABLESstatement, you must enable the following tenant-level parameter:ALTER SYSTEM SET enable_lock_priority = TRUE;For more information about the tenant-level parameter, see enable_lock_priority.
Notice
After you enable the
enable_lock_priorityparameter for a tenant, not all nodes immediately recognize the change. Therefore, you must query the value of this parameter on all nodes using theGV$OB_PARAMETERSview to ensure the configuration takes effect on all servers in the tenant.If you use a proxy to connect to the cluster, you must also enable the following proxy configuration:
ALTER PROXYCONFIG SET proxy_id = 1; ALTER PROXYCONFIG SET client_session_id_version = 2; ALTER PROXYCONFIG SET enable_single_leader_node_routing = false;For more information about the proxy configuration, see proxy_id, client_session_id_version and enable_single_leader_node_routing.
Limitations and considerations
- After acquiring a table lock, it is not recommended to perform a
RENAME TABLEoperation alone. It is suggested to obtain a write lock (WRITE) before renaming the table. - After locking a table in the same session, you cannot read from or write to the locked table; only the
RENAME TABLEoperation is allowed. - Locking on a table alias (
alias) is the same as locking on the original table name. - When the current session holds a table lock, executing
LOCK TABLESagain will acquire multiple table locks. - The
START TRANSACTIONstatement does not release the lock. The lock must be released by using theUNLOCK TABLESstatement or after the session is disconnected. - The
LOCK TABLESstatement does not commit a transaction. - Locking on views is not supported.
- Locks are acquired only on the tables involved in triggers or foreign keys. For example, if you lock a child table in a foreign key table, the parent table is not locked; if you lock the parent table, the child table is not locked.
- If the
LOCK TABLESstatement operates on a temporary table, no lock is acquired and no error is returned.
Privilege requirements
To execute the LOCK TABLES statement, the current user must have at least the LOCK TABLES privilege on the corresponding object. For more information about OceanBase AI Database privileges, see Permission classification.
Syntax
LOCK {TABLE | TABLES}
tbl_name [[AS] alias] lock_type
[, tbl_name [[AS] alias] lock_type ...]
lock_type:
READ [LOCAL]
| [LOW_PRIORITY] WRITE
Syntax
Field |
Description |
|---|---|
| LOCK {TABLE \ | TABLES} | Indicates a lock operation on one or more tables. LOCK TABLE is a synonym for LOCK TABLES. |
| table_name | Specifies the name of the table to lock. |
| [AS] alias | Specifies an alias for a table. |
| lock_type | Specifies the type of table lock. For more information about table lock types, see lock_type. |
lock_type
READ [LOCAL]:READ: Indicates that all sessions can only read the locked table and cannot modify it (including the session holding the lock). Multiple sessions can perform read locks simultaneously.READ LOCAL: Same asREAD.
[LOW_PRIORITY] WRITE:WRITE: Indicates a write lock operation on the table. In the current version,WRITEdoes not affect reads.LOW_PRIORITY WRITE: Same asWRITE.
Examples
Create a sample table and insert test data.
Create the
orderstable.obclient> CREATE TABLE orders(order_id INT, customer_name VARCHAR(50), amount DECIMAL(10,2));Insert test data into the
orderstable.obclient> INSERT INTO orders VALUES(1, 'Alice', 1500.00);
Enable the tenant-level parameter
enable_lock_priority.obclient> ALTER SYSTEM SET enable_lock_priority = TRUE;Execute the following statement to commit the transaction.
obclient> COMMIT;In session 1, execute the following statement to set a read lock on the
orderstable.obclient> LOCK TABLES orders READ;In session 1, execute the following statement to view the data in the
orderstable.obclient> SELECT * FROM orders;The query result is as follows:
+----------+---------------+---------+ | order_id | customer_name | amount | +----------+---------------+---------+ | 1 | Alice | 1500.00 | +----------+---------------+---------+ 1 row in setIn session 1, execute the following statement to insert a row of data into the
orderstable.obclient> INSERT INTO orders VALUES(2, 'Bob', 2000.00);The return result is as follows:
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionIn session 2, execute the following statement to view the data in the
orderstable.obclient> SELECT * FROM orders;The query result is as follows:
+----------+---------------+---------+ | order_id | customer_name | amount | +----------+---------------+---------+ | 1 | Alice | 1500.00 | +----------+---------------+---------+ 1 row in setIn session 2, execute the following statement to insert a row of data into the
orderstable.obclient> INSERT INTO orders VALUES(2, 'Bob', 2000.00);The return result is as follows:
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionExecute the following statement in session 1 to release all table locks held by the current session.
obclient> UNLOCK TABLES;In session 1, execute the following statement to insert a row of data into the
orderstable.obclient> INSERT INTO orders VALUES(2, 'Bob', 2000.00);In session 2, execute the following statement to insert a row of data into the
orderstable.obclient> INSERT INTO orders VALUES(3, 'Charlie', 2500.00);