LOCK TABLE

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 the savepoint before the table is locked.

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

Examples

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

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

   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)
   );
   

2. Set a mutex lock on the entire test table.

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

3. Set a mutex lock on the p1 partition of the test table.

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

4. Set a mutex lock on the subpartition p1ssp1 of the test table.

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

5. Set a mutex lock on multiple partitions of the test table.

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

6. Set a mutex lock on multiple subpartitions of the test table.

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

7. Set a mutex lock on multiple partitions and subpartitions of the test table.

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

8. Set a mutex lock on a non-existing partition of the test table. In the following example, the p3 partition does not exist. The operation does not lock the p0 partition. The entire statement is rolled back.

   obclient> LOCK TABLE test PARTITION (p0, p3) IN EXCLUSIVE MODE;
   ORA-02149: Specified partition does not exist
   

9. Lock the test table on two clients. On the lock holding client, set a mutex lock. On the lock requesting client, set a shared lock. If you do not set 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;
   ORA-30006: resource busy; acquire with WAIT timeout expired
   

10. Lock the test table on two clients. On the lock holding client, set a mutex lock. On the lock requesting client, set a shared lock and use the NOWAIT keyword. After the NOWAIT keyword is set 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 set in the statement.

     ```shell
     # 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;
     ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
     ```
    

11. Lock the test table on two clients. On the lock holding client, set a mutex lock. On the lock requesting client, set a shared lock and use the WAIT N keyword. After the WAIT N keyword is set 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 set in the statement.

     ```shell
     # 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;
     ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
     ```