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 immediately recognize the parameter. Therefore, you must query the value of the parameter from the GV$OB_PARAMETERS view to ensure that the parameter takes effect on all nodes in the tenant.

• If you use a proxy to connect to the cluster, 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;
  

  For more information about these parameters, see proxy_id, client_session_id_version, and enable_single_leader_node_routing.

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) for 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 its original name.
• If a table is locked in the current session, the LOCK TABLES statement locks multiple tables when it is executed 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 in a table involved in a trigger or foreign key. 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 types in MySQL-compatible mode.

Syntax

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

lock_type:
    READ [LOCAL]
    | [LOW_PRIORTY] WRITE

Syntax

lock_type

• READ [LOCAL]:

  • READ: Indicates that all sessions can only read the locked table and 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_PRIORTY] WRITE:

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

Examples

1. Create a sample table and insert test data.

   1. Create a table named test_tbl1.

      CREATE TABLE test_tbl1(col1 INT, col2 VARCHAR(25), col3 INT);
      

   2. Insert test data into the test_tbl1 table.

      INSERT INTO test_tbl1 VALUES(1, 'A1', 100);
      

2. Enable the tenant-level configuration item enable_lock_priority.

   ALTER SYSTEM SET enable_lock_priority = TRUE;
   

3. Execute the following statement to commit the transaction.

   COMMIT;
   

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

   LOCK TABLES test_tbl1 READ;
   

5. In session 1, execute the following statement to view the data in the test_tbl1 table.

   SELECT * FROM test_tbl1;
   

   The execution result is as follows:

   +------+------+------+
   | col1 | col2 | col3 |
   +------+------+------+
   |    1 | A1   |  100 |
   +------+------+------+
   1 row in set
   

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

   INSERT INTO test_tbl1 VALUES(2, 'A2', 200);
   

   The execution result is as follows:

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

7. In session 2, execute the following statement to view the data in the test_tbl1 table.

   SELECT * FROM test_tbl1;
   

   The execution result is as follows:

   +------+------+------+
   | col1 | col2 | col3 |
   +------+------+------+
   |    1 | A1   |  100 |
   +------+------+------+
   1 row in set
   

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

   INSERT INTO test_tbl1 VALUES(2, 'A2', 200);
   

   The execution 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.

   UNLOCK TABLES;
   

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

    INSERT INTO test_tbl1 VALUES(2, 'A2', 200);
    

    The execution result is as follows:

    Query OK, 1 row affected
    

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

    INSERT INTO test_tbl1 VALUES(3, 'A3', 300);
    

    The execution result is as follows:

    Query OK, 1 row affected
    

References

UNLOCK TABLES