LOCK TABLE

Purpose

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

Locked tables remain 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 applies a lock to its partitions.

1. Create a subpartitioned table named 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. Apply an exclusive lock to the entire test table.

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

3. Apply an exclusive lock to 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 to 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 to 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 to 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 to 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 to a non-existent partition p3 of the test table. In this example, p3 does not exist, and p0 will also fail to be locked, resulting in a rollback of the entire statement.

   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 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;
   OBE-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 result in an immediate error, with the error message indicating "NOWAIT". This differs from when the WAIT N or NOWAIT keywords are 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;
    OBE-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 a wait of N seconds. If the lock is not released after N seconds, an error will be reported, with the error message indicating "timeout expired". This differs from when the WAIT N or NOWAIT keywords are 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;
    OBE-00054: resource busy and acquire with NOWAIT specified or timeout expired