Skip to main content

LOCK

Locks a table.

Syntax​

LOCK [TABLE] [ONLY] name [ * ] [, ...] [IN <lock_mode> MODE] [NOWAIT]
where <lock_mode> can be one of:
    ACCESS SHARE | ROW SHARE | ROW EXCLUSIVE 
    | SHARE UPDATE EXCLUSIVE | SHARE | SHARE ROW EXCLUSIVE 
    | EXCLUSIVE | ACCESS EXCLUSIVE

Description​

Relyt does not provide UNLOCK TABLE. All locks in a transaction are automatically released when the transaction ends.

Relyt adopts the least restrictive lock mode for each statement that references tables and automatically acquires a lock. You can use LOCK TABLE to achieve more restrictive locking.

Parameters​

  • <name>

    The name of the table. If ONLY is specified in your statement, only this table is locked. Otherwise, this table and its descendant tables are locked. Alternatively, you can specify * after the table name to explicitly specify to lock the descendant tables. You can specify the names of multiple tables. In this case, these tables will be locked one by one in their specified order.

  • <lock_mode>

    The lock mode that specifies which locks this lock conflicts with. Possible values include:

    • ACCESS SHARE: conflicts with the ACCESS EXCLUSIVE lock mode. The SELECT command acquires a lock of this mode on referenced tables. In general, any query that only reads a table and does not modify it will acquire this lock mode.

    • ROW SHARE: conflicts with the EXCLUSIVE and ACCESS EXCLUSIVE lock modes. The SELECT ... FOR SHARE command automatically acquires a lock of this mode on the target tables, in addition to ACCESS SHARE locks on any other tables that are referenced but not selected FOR SHARE.

    • ROW EXCLUSIVE: conflicts with the SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE lock modes. The commands INSERT and COPY automatically acquire this lock mode on the target table, in addition to ACCESS SHARE locks on any other referenced tables. For more information, see Usage notes.

    • SHARE UPDATE EXCLUSIVE: conflicts with the SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE lock modes. This lock mode protects a table against concurrent schema changes and VACUUM runs. VACUUM (without FULL) on heap tables and ANALYZE acquire locks of this mode.

    • SHARE: conflicts with the ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE lock modes. This mode protects a table against concurrent data changes. The CREATE INDEX command automatically acquires a lock of this mode.

    • SHARE ROW EXCLUSIVE: conflicts with the ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE lock modes. This mode protects a table against concurrent data changes, and is self-exclusive so that only one session can hold it at a time.

    • EXCLUSIVE: conflicts with the ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE lock modes. This mode allows only concurrent ACCESS SHARE locks, only reads from the table can proceed in parallel with a transaction holding this lock mode. This lock mode is automatically acquired for UPDATE, SELECT ... FOR UPDATE, and DELETE in Relyt. For more information, see Usage notes.

    • ACCESS EXCLUSIVE: conflicts with locks of all modes. This lock mode ensures that the holder is the only transaction that is using the table. This is the default lock mode. This lock mode is acquired automatically by the ALTER TABLE, DROP TABLE, TRUNCATE, REINDEX, CLUSTER, and VACUUM FULL commands. This is the default lock mode for LOCK TABLE statements that do not specify a mode explicitly. This lock is also briefly acquired by VACUUM (without FULL) on append-optimized tables during processing.

  • NOWAIT

    Specifies that LOCK TABLE should not wait for any conflicting locks to be released: if the specified locks cannot be acquired immediately without waiting, the transaction is canceled.

Usage notes​

LOCK TABLE ... IN ACCESS SHARE MODE requires SELECT privileges on the target table. All other forms of LOCK require table-level UPDATE, DELETE, or TRUNCATE privileges.

LOCK TABLE is useless outside of a transaction block: the lock will be held only to the completion of the LOCK statement. Therefore, Relyt reports an error if LOCK is used outside of a transaction block. Use BEGIN and END to define a transaction block.

LOCK TABLE only deals with table-level locks, and so the mode names involving ROW are all misnomers. These mode names are generally read as indicating the intention of the user to acquire row-level locks within the locked table. Also, ROW EXCLUSIVE mode is a shareable table lock. Keep in mind that all the lock modes have identical semantics so far as LOCK TABLE is concerned, differing only in the rules about which modes conflict with which.

Examples​

Obtain a SHARE lock on the films table when going to perform inserts into the films_user_comments table:

BEGIN WORK;
LOCK TABLE films IN SHARE MODE;
SELECT id FROM films
    WHERE name = 'Star Wars: Episode I - The Phantom Menace';
-- Do ROLLBACK if record was not returned
INSERT INTO films_user_comments VALUES
    (_id_, 'GREAT! I was waiting for it for so long!');
COMMIT WORK;

Take a SHARE ROW EXCLUSIVE lock on a table when performing a delete operation:

BEGIN WORK;
LOCK TABLE films IN SHARE ROW EXCLUSIVE MODE;
DELETE FROM films_user_comments WHERE id IN
    (SELECT id FROM films WHERE rating < 5);
DELETE FROM films WHERE rating < 5;
COMMIT WORK;

SQL standard compatibility​

The SQL standard does not support LOCK.

The lock modes provided by Relyt, except for ACCESS SHARE, ACCESS EXCLUSIVE, and SHARE UPDATE EXCLUSIVE and the LOCK TABLE syntax are compatible with those of LOCK TABLE in Oracle.