LOCK TABLES

Purpose

You can use this statement to lock a table within a session to prevent other sessions from performing concurrent operations on the same table. When it is necessary to stop all DML and DDL operations on a table, this statement can be used to lock the table.

Prerequisites

• Before you use the LOCK TABLES statement, you need to enable the following tenant-level parameter:

  ALTER SYSTEM SET enable_lock_priority = TRUE;
  

  For more information about the tenant-level parameter, see enable_lock_priority.

  Notice

  After you enable the enable_lock_priority parameter for a tenant, the parameter value is not immediately recognized by all nodes. Therefore, you must query the value of the enable_lock_priority parameter on all nodes using the GV$OB_PARAMETERS view to ensure that the parameter is enabled on all machines in the tenant.

• If you use a proxy to connect to the cluster, you also need to 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 the proxy parameters, see proxy_id, client_session_id_version and enable_single_leader_node_routing.

Limitations and considerations

• After enabling table locks, it is recommended not to perform RENAME TABLE operations independently. Instead, acquire write locks (WRITE) before executing RENAME TABLE operations.
• Once a session locks a table, read and write operations on the locked tables are not supported. Only RENAME TABLE operations are allowed.
• Locking a table by its alias name is equivalent to locking the table by its original name.
• If the current session holds a table lock, executing the LOCK TABLES statement again will add locks to additional tables.
• START TRANSACTION does not release table locks. The locks can only be released using the UNLOCK TABLES statement or by disconnecting the session.
• The LOCK TABLES statement does not automatically commit transactions.
• Views cannot be locked.
• For tables involved in foreign key relationships, only the explicitly locked table is affected. For example, if a child table is locked, the parent table is not locked, and vice versa.
• If the LOCK TABLES statement is executed on 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 OceanBase Database privileges, see Privilege types in MySQL mode.

Syntax

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

lock_type:
    READ [LOCAL]
    | [LOW_PRIORTY] WRITE

Parameters

lock_type

• READ [LOCAL]:

  • READ: All sessions can read the locked table but cannot modify it, including the session that applies the lock. Multiple sessions can obtain a read lock on the same table at the same time.
  • READ LOCAL: The same as READ.

• [LOW_PRIORITY] WRITE:

  • WRITE: The same as READ.
  • LOW_PRIORITY WRITE: The same as WRITE.

Examples

1. Create sample tables 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 test_tbl1.

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

2. Enable the tenant-level parameter 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 obtain a read lock on the test_tbl1 table.

   LOCK TABLES test_tbl1 READ;
   

5. In Session 1, execute the following statement to query data from the test_tbl1 table.

   SELECT * FROM test_tbl1;
   

   The return 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 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 test_tbl1 table.

   SELECT * FROM test_tbl1;
   

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

   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 return 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 return result is as follows:

    Query OK, 1 row affected
    

References

UNLOCK TABLES