LOCK

Description

LOCK TABLE obtains a table-level lock.

An M-compatible database always tries to select the lock mode with minimum constraints when automatically requesting a lock for a statement referenced by a table. Use LOCK if users need a more strict lock mode. For example, suppose an application runs a transaction at the Read Committed isolation level and needs to ensure that data in a table remains stable in the duration of the transaction. To achieve this, you could obtain READ lock mode over the table before the query. This will prevent concurrent data changes and ensure subsequent reads of the table see a stable view of committed data. It is because the READ lock mode conflicts with the ROW EXCLUSIVE mode required by writers, the statement will wait until any concurrent holders of ROW EXCLUSIVE mode locks commit or roll back. Therefore, once you obtain the lock, there are no uncommitted writes outstanding; furthermore none can begin until you release the lock.

Precautions

• LOCK TABLE is useless outside a transaction block: the lock would remain held only to the completion of the statement. If LOCK TABLE is out of any transaction block, an error is reported.
• If no lock mode is specified, then WRITE, the most restrictive mode, is used.
• There is no UNLOCK TABLE statement. Locks are always released at transaction end. Currently, M-compatible databases do not support the UNLOCK TABLE command.
• LOCK TABLE processes only table-level locks. For details about the rules for checking whether locks conflict with each other, see Table 1.
• If the xc_maintenance_mode parameter is not enabled, an error is reported when a WRITE lock is applied for a system catalog.
• Only the redistribution tool can use the automatic CANCEL service API.

Syntax

LOCK [ TABLE | TABLES ] [ONLY]
   tbl_name [lock_type]
    [, tbl_name [lock_type] ] ...
    [NOWAIT]
    [CANCELABLE];
 
lock_type: {
    READ
  | WRITE
  | IN ACCESS SHARE MODE
  | IN EXCLUSIVE MODE
}     

Parameters

LOCK parameters are as follows:

• table_name

  Specifies the name (optionally schema-qualified) of an existing table to lock.

  Tables are locked one-by-one in the order specified in the LOCK TABLE statement.

  Value range: an existing table name

• ONLY

  If ONLY is specified, only that table is locked. If it is not specified, the table and all its sub-tables are locked.

• READ

  Allows concurrent queries of a table but does not allow modification of the table.

  Acquired by CREATE INDEX (without CONCURRENTLY).

• WRITE

  Guarantees that the holder is the only transaction accessing the table in any way.

  The ALTER TABLE, DROP TABLE, TRUNCATE and REINDEX statements automatically request this lock.

  This is also the default lock mode for LOCK TABLE statements that do not specify a mode explicitly.

• ACCESS SHARE

  Allows only read operations on a table. In general, any SQL statements that only read a table and do not modify it will acquire this lock mode. The SELECT statement acquires an ACCESS SHARE lock on referenced tables.

• EXCLUSIVE

  Allows concurrent queries of the target table but does not allow any other operations.

  This mode allows only concurrent ACCESS SHARE locks; that is, only reads from the table can proceed in parallel with a transaction holding this lock mode.

  No SQL statements automatically acquire this lock mode on user tables. However, it will be acquired on some system catalogs in case of some operations.

• ROW SHARE | ROW EXCLUSIVE | SHARE UPDATE EXCLUSIVE | SHARE ROW EXCLUSIVE

  In M-compatible databases, LOCK TABLE cannot be used to obtain the locks of these modes.

• NOWAIT

  Specifies that LOCK TABLE does not wait for any conflicting locks to be released. If the lock cannot be obtained immediately, the command exits and an error message is displayed.

  If a table-level lock is obtained without specifying NOWAIT and other mutex locks exist, the system waits for other locks to be released.

• CANCELABLE

  Allows the waiting thread to send CANCEL signals to the holding threads and waiting threads.

  Only the redistribution tool can use this parameter. An error message is displayed when the parameter is used by users.

Examples

-- Create a schema.
m_db=# CREATE SCHEMA tpcds;

-- Create the tpcds.reason table.
m_db=# CREATE TABLE tpcds.reason
(
r_reason_sk         INTEGER      NOT NULL,
r_reason_id         CHAR(16)     NOT NULL,
r_reason_desc       INTEGER
);

-- Insert multiple records into the table.
m_db=# INSERT INTO tpcds.reason VALUES (1, 'AAAAAAAABAAAAAAA', '18'),(5, 'AAAAAAAACAAAAAAA', '362'),(7, 'AAAAAAAADAAAAAAA', '585');

m_db=# START TRANSACTION;

m_db=# LOCK TABLE tpcds.reason WRITE;

m_db=# COMMIT;

-- Drop the table.
m_db=# DROP TABLE tpcds.reason;

-- Drop the schema.
m_db=# DROP SCHEMA tpcds;