LOCK TABLE|V4.3.3| docs|Distributed Database

LOCK TABLE

Last Updated：2025-11-27 02:38:06 Updated

Purpose

You can use this statement to lock one or more tables, partitions, or subpartitions in specified mode.

A locked table remains locked until the transaction is committed, rolled back, or rolled back to a savepoint before the table is locked.

Note

You can lock only tables in your own schema, or you must have the LOCK ANY TABLE privilege.

Syntax

LOCK TABLE [ schema. ]table_name

  [ partition_extension_clause]
    [, [ schema. ] table_name
      [ partition_extension_clause ]
     ]...
  IN lock_mode MODE
  [ NOWAIT  
   | WAIT integer
  ] ;

partition_extension_clause:

    PARTITION (partition_name, ...)
  | SUBPARTITION (subpartition_name, ...)

Parameters

Parameter	Description
table_name	The name of the table to be locked.
partition_name	The name of the partition to be locked.
subpartition_name	The name of the subpartition to be locked.
lock_mode	The lock mode. OceanBase Database supports the following lock modes: `ROW SHARE`: allows concurrent access to a locked table, but prohibits users from locking the entire table for exclusive access. `ROW EXCLUSIVE`: functions in the same way as `ROW SHARE`, but prohibits users from locking the table in `SHARE` mode. A `ROW EXCLUSIVE` lock is automatically obtained in an `UPDATE`, `INSERT`, or `DELETE` operation. `SHARE`: allows concurrent queries, but prohibits users from updating the locked table. `SHARE ROW EXCLUSIVE`: allows you to view the entire table and other users to view the rows in the table, but prohibits other users from locking the table or updating the rows in `SHARE` mode. `EXCLUSIVE`: allows only queries to the locked table, but prohibits other operations to the table.
NOWAIT	This keyword instructs the database to immediately return the control to you if a lock conflict occurs when you attempt to lock a table, partition, or subpartition. In this case, the database returns a message indicating that the table, partition, or subpartition is locked by another user.
WAIT	This keyword instructs the database to wait for the release of the lock until timeout, which is specified by `integer` in seconds, if a lock conflict occurs. The value of `integer` is not limited.

Examples

The following examples show how to create a subpartitioned table and lock a partition.

Create a subpartitioned table named test. Subpartitions generated by SUBPARTITION TEMPLATE are named in the format of partition name + 's' + subpartition template name. For example, p0ssp0, p0ssp1, and p0ssp2 are subpartitions in the p0 partition.

CREATE TABLE TEST(col1 INT, col2 INT)
PARTITION BY RANGE (col1)
SUBPARTITION BY RANGE (col2)
  SUBPARTITION TEMPLATE
    (
    SUBPARTITION sp0 VALUES LESS THAN (3),
    SUBPARTITION sp1 VALUES LESS THAN (6),
    SUBPARTITION sp2 VALUES LESS THAN (9)
    )
(
  PARTITION p0 VALUES LESS THAN (100),
  PARTITION p1 VALUES LESS THAN (200),
  PARTITION p2 VALUES LESS THAN (300)
);

Lock the entire test table in EXCLUSIVE mode.

obclient> LOCK TABLE test IN EXCLUSIVE MODE;
Query OK, 0 rows affected

Lock the p1 partition of the test table in EXCLUSIVE mode.

obclient> LOCK TABLE test PARTITION (p1) IN EXCLUSIVE MODE;
Query OK, 0 rows affected

Lock the p1ssp1 subpartition of the test table in EXCLUSIVE mode.

obclient> LOCK TABLE test SUBPARTITION (p1ssp1) IN EXCLUSIVE MODE;
Query OK, 0 rows affected

Lock multiple partitions of the test table in EXCLUSIVE mode.

obclient> LOCK TABLE test PARTITION (p1, p2) IN EXCLUSIVE MODE;
Query OK, 0 rows affected

Lock multiple subpartitions of the test table in EXCLUSIVE mode.

obclient> LOCK TABLE test SUBPARTITION (p0ssp1, p1ssp2) IN EXCLUSIVE MODE;
Query OK, 0 rows affected

Lock multiple partitions and subpartitions of the test table in EXCLUSIVE mode.

obclient> LOCK TABLE test PARTITION (p1, p2), test SUBPARTITION (p0ssp0, p0ssp1) IN EXCLUSIVE MODE;
Query OK, 0 rows affected

Lock a nonexistent partition of the test table in EXCLUSIVE mode. In the following example, the p3 partition does not exist. The p0 partition will not be locked either, and the entire statement will be rolled back.
```
obclient> LOCK TABLE test PARTITION (p0, p3) IN EXCLUSIVE MODE;
OBE-02149: Specified partition does not exist
```

Lock the test table on two clients. On the lock holding client, lock the table in EXCLUSIVE mode. On the lock requesting client, lock the table in SHARE mode. If you do not specify the WAIT N or NOWAIT keyword in the LOCK TABLE statement, the timeout is the statement timeout or the transaction timeout, whichever is shorter. In this example, a lock conflict error will be reported after 1 second.

# conn1
obclient> START TRANSACTION;
Query OK, 0 rows affected
obclient> LOCK TABLE test IN EXCLUSIVE MODE;
Query OK, 0 rows affected

# conn2
obclient> START TRANSACTION;
Query OK, 0 rows affected
obclient> SET ob_trx_timeout = 10000000000; # 10000s
Query OK, 0 rows affected
obclient> SET ob_query_timeout = 1000000;   # 1s
Query OK, 0 rows affected

# The lock requesting client reports a lock conflict error after 1 second.
obclient> LOCK TABLE test IN SHARE MODE;
OBE-30006: resource busy; acquire with WAIT timeout expired

Lock the test table on two clients. On the lock holding client, lock the table in EXCLUSIVE mode. On the lock requesting client, lock the table in SHARE mode with the NOWAIT keyword specified. After the NOWAIT keyword is specified in the LOCK TABLE statement, an error is reported immediately when a table lock conflict occurs, and the error message contains NOWAIT. The error message differs from the one returned when the WAIT N or NOWAIT keyword is not specified in the statement.
```
# conn1
obclient> START TRANSACTION;
Query OK, 0 rows affected
obclient> LOCK TABLE test IN EXCLUSIVE MODE;
Query OK, 0 rows affected

# conn2
obclient> START TRANSACTION;
Query OK, 0 rows affected
obclient> LOCK TABLE test IN SHARE MODE NOWAIT;
OBE-00054: resource busy and acquire with NOWAIT specified or timeout expired
```
Lock the test table on two clients. On the lock holding client, lock the table in EXCLUSIVE mode. On the lock requesting client, lock the table in SHARE mode with the WAIT N keyword specified. After the WAIT N keyword is specified in the LOCK TABLE statement, an error is reported if a table lock conflict occurs and lasts for more than N seconds. The error message contains timeout expired. The error message differs from the one returned when the WAIT N or NOWAIT keyword is not specified in the statement.
```
# conn1
obclient> START TRANSACTION;
Query OK, 0 rows affected
obclient> LOCK TABLE test IN EXCLUSIVE MODE;
Query OK, 0 rows affected

# conn2
obclient> START TRANSACTION;
Query OK, 0 rows affected

# The lock requesting client reports a lock conflict error after 1 second.
obclient> LOCK TABLE test IN SHARE MODE WAIT 1;
OBE-00054: resource busy and acquire with NOWAIT specified or timeout expired
```