Table locking is the most basic locking strategy. It locks an entire table, allowing only one session to perform read or write operations on that table at any given time. You can use the LOCK TABLES statement to lock a table.
A database is a shared resource used by multiple users. When multiple users access data concurrently, multiple transactions may access the same data simultaneously. If concurrent operations are not controlled, incorrect data may be read and stored, compromising database consistency. Locking is a very important technique for implementing database concurrency control. The lock mechanism was introduced to ensure resource security (i.e., data integrity and consistency). It uses this mechanism to enable concurrent data access and data consistency among transactions.
Prerequisites
The table to be locked must be within the user's own Schema, or the user must have the LOCK TABLES privilege.
Example operation
Set a read lock on the test1 table and a write lock on the test2 table.
obclient> LOCK TABLES test1 as t1 READ LOCAL, test2 as t2 LOW_PRIORITY WRITE;
Query OK, 0 row affected
- If a read-only lock is added on a table, only the
SELECToperation can be performed on the table and the table cannot be modified. If a transaction is operating the table when the lock is added, theLOCK TABLESstatement hangs until the transaction ends. You can add a read-only lock on a table in multiple sessions at the same time. - If a read/write lock is added on a table, the table can be modified and queried in the session in which the
LOCK TABLESstatement is executed. No operations, including theSELECToperation, can be performed on the table in other sessions.
For more information about the LOCK TABLES statement, see LOCK TABLES.