Skip to main content

LOCK TABLES

Description​

This statement is used to lock tables in a 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 using the LOCK TABLES statement, you need to enable the following configuration item:

    ALTER SYSTEM SET enable_lock_priority = TRUE;

    For more information about this configuration item, see enable_lock_priority.

Limitations and Considerations​

  • After a table is locked, it is not recommended to perform the RENAME TABLE operation. Before performing the RENAME TABLE operation, it is recommended to obtain a write lock (WRITE) on the table.
  • After a table is locked in the same session, it can only be renamed using the RENAME TABLE operation.
  • Locking a table by its alias is the same as locking the table by its original name.
  • If a table is already locked in the current session, executing the LOCK TABLES statement again will lock multiple tables.
  • The START TRANSACTION statement does not unlock a table. You must use the UNLOCK TABLES statement or disconnect the session to release the lock.
  • The LOCK TABLES statement does not commit the transaction.
  • Locking a view (View) is not supported.
  • Only the original table is locked when a table involved in a trigger or foreign key constraint is locked. For example, if a child table is locked in a foreign key constraint, 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.

Privileges​

To execute the LOCK TABLES statement, the current user must have at least the LOCK TABLES privilege on the corresponding object. For more information about the privileges of seekdb, see seekdb Privilege Classification.

Syntax​

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

lock_type:
    READ [LOCAL]
    | [LOW_PRIORTY] WRITE

Syntax Description​

FieldDescription
LOCK {TABLE | TABLES}Specifies to lock one or more tables. LOCK TABLE is a synonym for LOCK TABLES.
table_nameSpecifies the name of the table to be locked.
[AS] aliasSpecifies an alias for the table.
lock_typeSpecifies the type of table lock. For more information about table lock types, see lock_type.

lock_type​

  • READ [LOCAL]:

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

  • [LOW_PRIORTY] WRITE:

    • WRITE: Specifies to lock the table for writing. 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 enable_lock_priority configuration item.

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

    +------+------+------+
    | col1 | col2 | col3 |
    +------+------+------+
    |    1 | A1   |  100 |
    +------+------+------+
    1 row in set (0.001 sec)

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

    +------+------+------+
    | col1 | col2 | col3 |
    +------+------+------+
    |    1 | A1   |  100 |
    +------+------+------+
    1 row in set (0.001 sec)

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

    Query OK, 1 row affected

References​

UNLOCK TABLES

Contents

Edit this page