LOCK TABLE

Purpose

This statement locks one or more tables, partitions, or subpartitions in the specified schema.

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

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, ...)

Syntax

Examples

The following example creates a subpartitioned table and performs a partition lock operation on it.

1. Create a subpartitioned table test. The names of the subpartitions generated by SUBPARTITION TEMPLATE are the names of the partitions plus 's' and the subpartition template name. For example, the p0 partition has subpartitions named p0ssp0, p0ssp1, and p0ssp2.

   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. Apply an exclusive lock on the entire test table.

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

3. Apply an exclusive lock on the p1 partition of the test table.

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

4. Apply an exclusive lock on the p1ssp1 subpartition of the test table.

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

5. Apply an exclusive lock on multiple partitions of the test table.

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

6. Apply an exclusive lock on multiple subpartitions of the test table.

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

7. Apply an exclusive 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. Attempt to apply an exclusive lock on a non-existent partition of the test table. In the example, the p3 partition does not exist, and the p0 partition also fails to be locked. The entire statement will be rolled back.

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

9. Lock the test table in two client sessions. One session holds an exclusive lock, and the other requests a shared lock. When the LOCK TABLE statement does not specify the WAIT N or NOWAIT keyword, the timeout is determined by the minimum of the statement timeout and transaction timeout. In this example, the 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; # 10000 seconds
   Query OK, 0 rows affected
   obclient> SET ob_query_timeout = 1000000;   # 1 second
   Query OK, 0 rows affected
   
   # The requesting client will receive 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 in two client sessions. One session holds an exclusive lock, and the other requests a shared lock, using the NOWAIT keyword. When the LOCK TABLE statement specifies the NOWAIT keyword, a lock conflict will immediately result in an error message that includes "NOWAIT". This is different from when the WAIT N or NOWAIT keyword is not specified.

    # 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 in two client sessions. One session holds an exclusive lock, and the other requests a shared lock, using the WAIT N keyword. When the LOCK TABLE statement specifies the WAIT N keyword, a lock conflict will result in an error message that includes "timeout expired" after N seconds. This is different from when the WAIT N or NOWAIT keyword is not specified.

    # 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 requesting client will receive 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