LOCK TABLE

Purpose

This statement is used to lock one or more tables, partitions, or subpartitions in the specified mode.

Locked tables remain locked until the transaction is committed, rolled back, or rolled back to a savepoint before the lock was placed.

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 demonstrates how to create a subpartitioned table and lock its partitions.

1. Create a subpartitioned table test. The subpartition names generated by SUBPARTITION TEMPLATE are formed by appending 's' and the subpartition template name to the partition 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. Lock the entire test table with an exclusive lock.

   obclient> LOCK TABLE test IN EXCLUSIVE MODE;
   

3. Lock the p1 partition of the test table with an exclusive lock.

   obclient> LOCK TABLE test PARTITION (p1) IN EXCLUSIVE MODE;
   

4. Lock the p1ssp1 subpartition of the test table with an exclusive lock.

   obclient> LOCK TABLE test SUBPARTITION (p1ssp1) IN EXCLUSIVE MODE;
   

5. Lock multiple partitions of the test table with exclusive locks.

   obclient> LOCK TABLE test PARTITION (p1, p2) IN EXCLUSIVE MODE;
   

6. Lock multiple subpartitions of the test table with exclusive locks.

   obclient> LOCK TABLE test SUBPARTITION (p0ssp1, p1ssp2) IN EXCLUSIVE MODE;
   

7. Lock multiple partitions and subpartitions of the test table with exclusive locks.

   obclient> LOCK TABLE test PARTITION (p1, p2), test SUBPARTITION (p0ssp0, p0ssp1) IN EXCLUSIVE MODE;
   

8. Attempt to lock a non-existent partition p3 of the test table. In this example, p3 does not exist, and p0 will also fail to be locked, causing the entire statement to roll back.

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

9. Lock the test table in two client sessions. One client holds an exclusive lock, while 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;
   obclient> LOCK TABLE test IN EXCLUSIVE MODE;
   
   # conn2
   obclient> START TRANSACTION;
   obclient> SET ob_trx_timeout = 10000000000; # 10000 seconds
   obclient> SET ob_query_timeout = 1000000;   # 1 second
   
   # The requesting client will receive a lock conflict error after 1 second
   obclient> LOCK TABLE test IN SHARE MODE;
   OBE-30006: resource busy; acquire with WAIT timeout expired
   

10. Lock the test table in two client sessions. One client holds an exclusive lock, while the other requests a shared lock, and the NOWAIT keyword is used. When the LOCK TABLE statement specifies the NOWAIT keyword, if a table lock conflict occurs, an error is immediately returned, and the error message will include "NOWAIT". This error message is different from when the WAIT N or NOWAIT keyword is not specified.

    # conn1
    obclient> START TRANSACTION;
    obclient> LOCK TABLE test IN EXCLUSIVE MODE;
    
    # conn2
    obclient> START TRANSACTION;
    obclient> LOCK TABLE test IN SHARE MODE NOWAIT;
    OBE-00054: resource busy and acquire with NOWAIT specified or timeout expired
    

11. Lock the test table in two client sessions. One client holds an exclusive lock, while the other requests a shared lock, and the WAIT N keyword is used. When the LOCK TABLE statement specifies the WAIT N keyword, if a table lock conflict occurs, the system will wait for N seconds. If the lock is not released within N seconds, an error will be returned, and the error message will include "timeout expired". This error message is different from when the WAIT N or NOWAIT keyword is not specified.

    # conn1
    obclient> START TRANSACTION;
    obclient> LOCK TABLE test IN EXCLUSIVE MODE;
    
    # conn2
    obclient> START TRANSACTION;
    
    # The requesting client will receive 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