LOCK TABLES

Purpose

This statement is used to lock a table in the current session to prevent other sessions from performing concurrent operations on the same table. You can use this statement to stop all DML and DDL operations on a table.

Prerequisites

• Before you execute the LOCK TABLES statement, you must enable the following tenant-level parameter:

  ALTER SYSTEM SET enable_lock_priority = TRUE;
  

  For more information about this parameter, see enable_lock_priority.

  Notice

  After you enable the enable_lock_priority parameter for a tenant, not all nodes can immediately detect the parameter. Therefore, you must query the GV$OB_PARAMETERS view to check the value of this parameter on all nodes to ensure that it takes effect on all nodes of the tenant.

• If you connect to the cluster through a proxy, you must also enable the following proxy parameters:

  ALTER PROXYCONFIG SET proxy_id = 1;
  ALTER PROXYCONFIG SET client_session_id_version = 2;
  ALTER PROXYCONFIG SET enable_single_leader_node_routing = false;
  

Limitations and considerations

• After a table is locked, we recommend that you do not execute the RENAME TABLE statement. Before you execute the RENAME TABLE statement, we recommend that you obtain a write lock (WRITE) on the table.
• After a table is locked in the current session, you cannot lock the table for read/write operations. You can only execute the RENAME TABLE statement.
• Locking a table by using an alias (alias) is the same as locking the table by using the original name.
• If a table is locked in the current session, you can lock multiple tables by executing the LOCK TABLES statement again.
• The START TRANSACTION statement does not unlock a table. You must execute the UNLOCK TABLES statement or disconnect the session to release the lock.
• The LOCK TABLES statement does not commit a transaction.
• You cannot lock a view (View).
• Only the original table is locked when a table involved in a trigger or foreign key is locked. For example, if you lock a child table involved in a foreign key, the parent table is not locked. If you lock the parent table, the child table is not locked.
• If the LOCK TABLES statement locks a temporary table, no lock is applied and no error is returned.

Privilege requirements

To execute the LOCK TABLES statement, the current user must have the LOCK TABLES privilege on the corresponding object. For more information about the privileges of OceanBase Database, see Privilege classification in MySQL mode.

Syntax

LOCK {TABLE | TABLES}
    tbl_name [[AS] alias] lock_type
    [, tbl_name [[AS] alias] lock_type ...]

lock_type:
    READ [LOCAL]
    | [LOW_PRIORITY] WRITE

Syntax description

lock_type

• READ [LOCAL]:

  • READ: Indicates that all sessions can only read from the locked table but cannot modify it (including the session that locked the table). Multiple sessions can simultaneously perform read locking operations.
  • READ LOCAL: Behaves the same as READ.

• [LOW_PRIORITY] WRITE:

  • WRITE: Indicates that the table is locked for write operations. In the current version, WRITE does not affect read operations.
  • LOW_PRIORITY WRITE: Behaves the same as WRITE.

Examples

1. Create a sample table and insert test data.

   1. Create a table named orders.

      obclient> CREATE TABLE orders(order_id INT, customer_name VARCHAR(50), amount DECIMAL(10,2));
      

   2. Insert test data into the orders table.

      obclient> INSERT INTO orders VALUES(1, 'Alice', 1500.00);
      

2. Enable the tenant-level configuration item enable_lock_priority.

   obclient> ALTER SYSTEM SET enable_lock_priority = TRUE;
   

3. Execute the following statement to commit the transaction.

   obclient> COMMIT;
   

4. In session 1, execute the following statement to set a read lock on the orders table.

   obclient> LOCK TABLES orders READ;
   

5. In session 1, execute the following statement to query data from the orders table.

   obclient> SELECT * FROM orders;
   

   The query result is as follows:

   +----------+---------------+---------+
   | order_id | customer_name | amount  |
   +----------+---------------+---------+
   |        1 | Alice         | 1500.00 |
   +----------+---------------+---------+
   1 row in set
   

6. In session 1, execute the following statement to insert a row of data into the orders table.

   obclient> INSERT INTO orders VALUES(2, 'Bob', 2000.00);
   

   The return result is as follows:

   ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
   

7. In session 2, execute the following statement to query data from the orders table.

   obclient> SELECT * FROM orders;
   

   The query result is as follows:

   +----------+---------------+---------+
   | order_id | customer_name | amount  |
   +----------+---------------+---------+
   |        1 | Alice         | 1500.00 |
   +----------+---------------+---------+
   1 row in set
   

8. In session 2, execute the following statement to insert a row of data into the orders table.

   obclient> INSERT INTO orders VALUES(2, 'Bob', 2000.00);
   

   The return result is as follows:

   ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
   

9. In session 1, execute the following statement to release all table locks held by the current session.

   obclient> UNLOCK TABLES;
   

10. In session 1, execute the following statement to insert a row of data into the orders table.

    obclient> INSERT INTO orders VALUES(2, 'Bob', 2000.00);
    

11. In session 2, execute the following statement to insert a row of data into the orders table.

    obclient> INSERT INTO orders VALUES(3, 'Charlie', 2500.00);
    

References

UNLOCK TABLES