Lock a table

Table locking is the most basic locking strategy. OceanBase Database allows you to lock a single table, multiple tables, or multiple partitions and subpartitions of a table at a time.

A database is a shared resource that can be simultaneously accessed by multiple users. When multiple users concurrently access the database, it is likely that the same data is operated by multiple transactions at the same time. Uncontrolled concurrent operations on the database may result in data inconsistency. Locking is key to the control of database concurrency. The locking mechanism is introduced to ensure resource security (or data integrity and consistency) by controlling concurrent data operations of multiple transactions.

After a table is locked, it remains in the locked state until you commit a transaction or roll back a transaction to a savepoint before the table is locked. In other words, the table lock is released after you commit or roll back a transaction. For more information about transaction control statements, see Transaction control statements.

Table locking modes

OceanBase Database supports the following locking modes:

• ROW SHARE: allows concurrent access to the locked table but prohibits other users from locking the table for exclusive access. This means that other users are not allowed to lock the table in EXCLUSIVE mode.
• ROW EXCLUSIVE: prohibits users from locking the table in SHARE or a higher mode (SHARE, ROW SHARE EXCLUSIVE, or EXCLUSIVE). When UPDATE, INSERT, DELETE, or SELECT FOR UPDATE operations are performed on a table, the table is automatically locked in ROW EXCLUSIVE mode.
• SHARE: allows concurrent queries but prohibits updates on the locked table. UPDATE, DELETE, INSERT, or SELECT FOR UPDATE operations on table rows are not allowed, and other users cannot lock the table in SHARE ROW EXCLUSIVE or EXCLUSIVE mode.
• SHARE ROW EXCLUSIVE: allows other users to view rows of the locked table but prohibits the users from performing the UPDATE, DELETE, or INSERT operation on table rows, using SELECT FOR UPDATE to query table rows, or locking the table in any mode other than ROW SHARE.
• EXCLUSIVE: allows other users to query the locked table but prohibits the users from performing any Data Manipulation Language (DML) operations on the table or locking the table in any mode.

The following table describes the conflicts between different locking modes.

Required privileges

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

Lock an entire table

The SQL syntax for locking an entire table is as follows:

LOCK TABLE [schema.]table_name[,[schema.]table_name ...] IN lockmode MODE [NOWAIT | WAIT integer];

where:

• table_name specifies the name of the table to be locked. Separate multiple tables with commas (,).

• lockmode specifies the table locking mode.

• NOWAIT | WAIT integer specifies the action to take if a lock conflict occurs.

  If NOWAIT is specified and a lock conflict occurs when you lock the target table, the system immediately returns the right of control to you and returns an error message.

  If WAIT integer is specified and a lock conflict occurs when you lock the target table, the system waits for the conflicting table lock to be released until the specified timeout period for statement execution expires. If the conflicting table lock is not released within the timeout period, the system returns an error message. The unit of the value of integer is seconds, and the value is not limited.

  Notice

  If WAIT integer is specified, the timeout period for statement execution is subject to the values of integer, ob_query_timeout, and ob_trx_timeout. The smallest value among the three is used as the actual timeout period for statement execution. For example, if WAIT 10 is specified and the default values are retained for ob_query_timeout and ob_trx_timeout, the timeout period for waiting for the release of a conflicting table lock is 1,000,000 us (1s).

  If neither NOWAIT nor WAIT integer is specified, the smaller value between ob_query_timeout and ob_trx_timeout is used as the timeout period for statement execution.

Example: Lock the tbl1 table and set the locking mode to EXCLUSIVE.

LOCK TABLE tbl1 IN EXCLUSIVE MODE NOWAIT;

In this example, after an EXCLUSIVE lock is set for the tbl1 table, other users can only query this table and cannot perform any DML operations or set other types of locks on this table.

Lock partitions of a table

The SQL syntax for locking partitions of a table is as follows:

LOCK TABLE
{
[ schema.]table_name
[ PARTITION '('partition_name ...')' | SUBPARTITION '(' subpartition_name ...')' ] ...
}
IN lockmode MODE
[ NOWAIT | WAIT integer] ;

where:

• table_name specifies the name of the partitioned table whose partitions are to be locked.

• partition_name specifies the name of the partition to be locked. You can lock multiple partitions at a time. To do so, separate the partition names with commas (,).

• subpartition_name specifies the name of the subpartition to be locked. You can lock multiple subpartitions at a time. To do so, separate the subpartition names with commas (,).

• lockmode specifies the table locking mode.

• NOWAIT | WAIT integer specifies the action to take if a lock conflict occurs.

  If NOWAIT is specified and a lock conflict occurs when you lock the target table, the system immediately returns the right of control to you and returns an error message.

  If WAIT integer is specified and a lock conflict occurs when you lock the target table, the system waits for the conflicting table lock to be released until the specified timeout period for statement execution expires. If the conflicting table lock is not released within the timeout period, the system returns an error message. The unit of the value of integer is seconds, and the value is not limited.

  Notice

  If WAIT integer is specified, the timeout period for statement execution is subject to the values of integer, ob_query_timeout, and ob_trx_timeout. The smallest value among the three is used as the actual timeout period for statement execution. For example, if WAIT 10 is specified and the default values are retained for ob_query_timeout and ob_trx_timeout, the timeout period for waiting for the release of a conflicting table lock is 1,000,000 us (1s).

  If neither NOWAIT nor WAIT integer is specified, the timeout period for statement execution is determined by ob_query_timeout or ob_trx_timeout, whichever is smaller.

Here provides an example. Assume that a template-based subpartitioned table named tbl2 exists in the current database. The statement for creating this table is as follows:

CREATE TABLE tbl2(col1 NUMBER, col2 NUMBER)
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)
);

According to the partition naming rules of a template-based subpartitioned table, the subpartitions are named p0ssp0, p0ssp1, and p0ssp2. For more information about partition naming rules, see Overview.

The following examples show how to perform specific locking operations on the table:

• Set an EXCLUSIVE lock for the p1 partition of the tbl2 table.

  LOCK TABLE tbl2 PARTITION (p1) IN EXCLUSIVE MODE NOWAIT;
  

• Set an EXCLUSIVE lock for the p1ssp1 subpartition of the tbl2 table.

  LOCK TABLE tbl2 SUBPARTITION (p1ssp1) IN EXCLUSIVE MODE WAIT 60;
  

• Set a SHARE lock for the p1 andp2 partitions and the p2ssp0 and p2ssp1 subpartitions of the tbl2 table.

  LOCK TABLE tbl2 PARTITION (p1, p2), tbl2 SUBPARTITION (p2ssp0, p2ssp1) IN SHARE MODE;
  

View table locks

You can query the GV$OB_LOCKS and V$OB_LOCKS views for the locks held and requested for each table of the current user.

SELECT * FROM GV$OB_LOCKS;

Sample query results are as follows:

+----------------+----------+-----------+----------+------+--------+------+-------+---------+-----------+-------+
| SVR_IP         | SVR_PORT | TENANT_ID | TRANS_ID | TYPE | ID1    | ID2  | LMODE | REQUEST | CTIME     | BLOCK |
+----------------+----------+-----------+----------+------+--------+------+-------+---------+-----------+-------+
| xx.xx.xx.xx    |     2882 |      1004 |  1344711 | TM   | 500003 | NULL | RX    | NONE    | 462723786 |     0 |
| xx.xx.xx.xx    |     2882 |      1004 |  1344711 | TM   | 500003 | NULL | RS    | NONE    |   6690958 |     0 |
| xx.xx.xx.xx    |     2882 |      1004 |  1344711 | TM   | 200005 | NULL | X     | NONE    | 462718854 |     0 |
| xx.xx.xx.xx    |     2882 |      1004 |  1344711 | TM   | 200006 | NULL | X     | NONE    | 462710583 |     0 |
| xx.xx.xx.xx    |     2882 |      1004 |  1344711 | TM   | 200007 | NULL | X     | NONE    | 462701111 |     0 |
| xx.xx.xx.xx    |     2882 |      1004 |  1344711 | TM   | 200005 | NULL | S     | NONE    |   6687863 |     0 |
| xx.xx.xx.xx    |     2882 |      1004 |  1344711 | TM   | 200006 | NULL | S     | NONE    |   6679696 |     0 |
| xx.xx.xx.xx    |     2882 |      1004 |  1344711 | TM   | 200007 | NULL | S     | NONE    |   6671395 |     0 |
| xx.xx.xx.xx    |     2882 |      1004 |  1344711 | TM   | 200008 | NULL | S     | NONE    |   6661725 |     0 |
| xx.xx.xx.xx    |     2882 |      1004 |  1344711 | TM   | 200009 | NULL | S     | NONE    |   6653562 |     0 |
| xx.xx.xx.xx    |     2882 |      1004 |  1344711 | TM   | 200010 | NULL | S     | NONE    |   6645387 |     0 |
+----------------+----------+-----------+----------+------+--------+------+-------+---------+-----------+-------+
11 rows in set

The following table describes the fields in the query results.

More information

For more information about the LOCK TABLES statement, see LOCK TABLES.