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.

Prerequisites

• You have enabled the following tenant-level parameter:

ALTER SYSTEM SET enable_lock_priority = TRUE;

For more information about this parameter, see enable_lock_priority.

• You have completed the following settings for OceanBase Database Proxy (ODP), if you connect to the cluster by using ODP:

  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 preceding ODP parameters, see proxy_id, client_session_id_version, and enable_single_leader_node_routing.

Example

Set a read lock on the test1 table and a write lock on the test2 table.

LOCK TABLES test1 as t1 READ LOCAL, test2 as t2 LOW_PRIORITY WRITE;

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