LOCK TABLES

Purpose

You can use this statement to lock a table in a session to prevent other sessions from performing concurrent operations on this table. To prevent all DML and DDL operations on a table, you can use this statement to lock the table.

Prerequisites

• You have enabled the tenant-level parameter enable_lock_priority:

  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 this parameter. You can query the GV$OB_PARAMETERS view for the value of this parameter on all nodes.

• You have completed the following settings for OceanBase Database Proxy (ODP), if you connect to the cluster through 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.

Limitations and considerations

• After a table lock is applied, we recommend that you do not execute the RENAME TABLE statement separately. To execute this statement, we recommend that you obtain a write lock first.
• After a table is locked in a session, you cannot read data from or write data to the locked table. You can only execute the RENAME TABLE statement on the table.
• Locking a table by its alias is equivalent to locking the table by its original name.
• If you execute the LOCK TABLES statement when the current session already holds a table lock, the current session will hold multiple table locks.
• The START TRANSACTION statement does not unlock a table. A table can be unlocked only by the UNLOCK TABLES statement or after the session is disconnected.
• LOCK TABLES does not proactively commit the transaction.
• You cannot lock a view.
• For a table with triggers or foreign keys, you can lock only the original table. In other words, the locking operation will not be cascaded. For example, for a table with a foreign key, if you lock the child table, the parent table will not be locked. If you lock the parent table, the child table will not be locked.
• If you execute the LOCK TABLES statement on a temporary table, the table will not be locked and no error is returned.

Required privileges

To execute the LOCK TABLES statement, you must have the LOCK TABLES privilege on the table. For more information about privileges in OceanBase Database, 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: allows all sessions, including the session that applies the lock, to read the locked table but not to modify it. The locked table can be read by multiple sessions at the same time.
  • READ LOCAL: equivalent to READ.

• [LOW_PRIORTY] WRITE:

  • WRITE: specifies to apply a write lock on a table. In the current version, a table with a write lock can still be read.
  • LOW_PRIORTY WRITE: equivalent to WRITE.

Examples

1. Create a sample table and insert test data into the table.

   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 parameter enable_lock_priority.

   ALTER SYSTEM SET enable_lock_priority = TRUE;
   

3. Execute the following statement to commit the transaction:

   COMMIT;
   

4. Execute the following statement in Session 1 to set a read lock for the test_tbl1 table:

   LOCK TABLES test_tbl1 READ;
   

5. Execute the following statement in Session 1 to view the data of 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. Execute the following statement in Session 1 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. Execute the following statement in Session 2 to view the data of 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. Execute the following statement in Session 2 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. Execute the following statement in Session 1 to release all table locks held by the current session:

   UNLOCK TABLES;
   

10. Execute the following statement in Session 1 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. Execute the following statement in Session 2 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