LOCK

LOCK — Lock a table

Synopsis

LOCK [ TABLE ] [ ONLY ] name [ * ] [, ...] [ IN lockmode MODE ] [ NOWAIT ]

where lockmode can be one of:

ACCESS SHARE | ROW SHARE | ROW EXCLUSIVE | SHARE UPDATE EXCLUSIVE | SHARE | SHARE ROW EXCLUSIVE | EXCLUSIVE | ACCESS EXCLUSIVE

Description

LOCK TABLE acquires a table-level lock, waiting if necessary for any conflicting locks to be released. If NOWAIT is specified, LOCK TABLE does not wait to acquire the desired lock: if it cannot be acquired immediately, the command is aborted and an error is raised. Once acquired, the lock is held for the remainder of the current transaction (there is no UNLOCK TABLE command; locks are always released at transaction end).

When a view is locked, all relations appearing in the view's defining query are also recursively locked using the same lock mode.

When automatically acquiring locks for commands that reference tables, Halo always uses the least restrictive lock mode possible.

LOCK TABLE is provided for cases where a more restrictive lock is desired. 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 for the duration of the transaction. To achieve this, you must acquire a SHARE lock mode on the table before querying it. This will prevent concurrent data modifications and ensure that subsequent reads of the table will see a stable view of committed data, because the SHARE lock mode conflicts with the ROW EXCLUSIVE lock required by writers, and your LOCK TABLE name IN SHARE MODE statement will wait until any concurrent holders of ROW EXCLUSIVE mode locks commit or roll back. Therefore, once the lock is obtained, there are no unresolved uncommitted writes. Furthermore, no one else can begin before the lock is released.

To achieve a similar effect in a transaction running at the REPEATABLE READ or SERIALIZABLE isolation level, you must execute the LOCK TABLE statement before executing any SELECT or data modification statements. The data view of a REPEATABLE READ or SERIALIZABLE transaction is frozen at the start of its first SELECT or data modification statement.

A LOCK TABLE later in the transaction will still prevent concurrent writes — but it will not ensure that what the transaction reads corresponds to the latest committed values.

If such a transaction is about to modify data in a table, it should use the SHARE ROW EXCLUSIVE lock mode instead of the SHARE mode. This ensures that only one such transaction runs at a time. Without this mode, deadlocks can occur: two transactions might both request SHARE mode, and neither can obtain ROW EXCLUSIVE mode to actually perform their updates (note that locks held by a single transaction do not conflict, so a transaction can acquire ROW EXCLUSIVE mode while holding SHARE mode — but not if someone else holds SHARE mode). To avoid deadlocks, ensure that all transactions acquire locks on the same objects in the same order, and if multiple lock modes are involved on a single object, transactions should always acquire the most restrictive mode first.

Parameters

name

The name of an existing table to lock (optionally schema-qualified). If ONLY is specified before the table name, only that table is locked. If ONLY is not specified, the table and all its descendants (if any) are locked. Optionally, specify * after the table name to explicitly indicate that descendants should be included.

The command LOCK TABLE a, b; is equivalent to LOCK TABLE a; LOCK TABLE b;. The tables are locked one by one in the order specified in LOCK TABLE.

lockmode

The lock mode specifies which locks conflict with this lock.

If no lock mode is specified, the most restrictive mode ACCESS EXCLUSIVE is used.

NOWAIT

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

Notes

LOCK TABLE ... IN ACCESS SHARE MODE requires SELECT privilege on the target table. LOCK TABLE ... IN ROW EXCLUSIVE MODE requires INSERT, UPDATE, DELETE, or TRUNCATE privilege on the target table. All other forms of LOCK require UPDATE, DELETE, or TRUNCATE privilege on the table.

The user performing the lock on a view must have the corresponding privileges on the view itself. Additionally, the view's owner must have the relevant privileges on the underlying base relations, but the user performing the lock does not need any permissions on the underlying base relations.

LOCK TABLE has no use outside a transaction block: the lock would only be held until the statement completes. Therefore, if LOCK is used outside a transaction block, Halo will report an error. Use BEGIN and COMMIT (or ROLLBACK) to define a transaction block.

LOCK TABLE only deals with table-level locks, so mode names involving ROW are somewhat misleading here. These mode names should generally be interpreted as indicating the user's intention to acquire row-level locks in the locked table. Also, the ROW EXCLUSIVE mode is a shareable table lock. Remember that for LOCK TABLE, all lock modes have the same semantics; only the conflict rules for the modes differ. For information on how to acquire a true row-level lock, see The Locking Clause in the SELECT documentation.

Examples

-- Acquire a SHARE lock on the primary key table before performing an insert into a foreign key table:

BEGIN WORK;

LOCK TABLE films IN SHARE MODE;

SELECT id FROM films

WHERE name = 'Star Wars: Episode I - The Phantom Menace';

-- If no record is returned, do ROLLBACK

INSERT INTO films_user_comments VALUES

(_id_, 'GREAT! I was waiting for it for so long!');

COMMIT WORK;

-- Acquire a SHARE ROW EXCLUSIVE lock on the primary key table before 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;