Lock a table

Table locking is the most basic locking strategy. After a table is locked, only one session is allowed to perform read and write operations on the table at any time. You can use the LOCK TABLES statement to lock a table.

A database is a shared resource that can be simultaneously accessed by multiple users. When multiple users concurrently access the database, it is likely that the same data is operated by multiple transactions at the same time. Uncontrolled concurrent operations on the database may result in data inconsistency. Locking is key to the control of database concurrency. The locking mechanism is introduced to ensure resource security (or data integrity and consistency) by controlling concurrent data operations of multiple transactions.

Limitations

Prerequisites

You can lock only tables in your own schema, or you must have the LOCK TABLES privilege.

Examples

The following example sets 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 SELECT operation can be performed on the table and the table cannot be modified. If a transaction is operating the table when the lock is added, the LOCK TABLES statement 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 TABLES statement is executed. No operations, including the SELECT operation, can be performed on the table in other sessions.

For more information about the LOCK TABLES statement, see LOCK TABLES.