Unsupported versions: 7.0 / 6.5 / 6.4
This documentation is for an unsupported version of PostgreSQL.
You may want to view the same page for the current version, or one of the other supported versions listed above instead.

LOCK

Name

LOCK — Explicit lock of a table inside a transaction
LOCK [ TABLE ] table

Inputs

table

The name of an existing table to lock.

Outputs

DELETE 0

Message returned on a successful lock. LOCK is implemented as a DELETE FROM table which is guaranteed to not delete any rows.

ERROR table: Table does not exist.

Message returned if table does not exist.

Description

LOCK locks in exclusive mode a table inside a transaction. The classic use for this is the case where you want to select some data, then update it inside a transaction. If you don't explicit lock a table using LOCK statement, it will be implicit locked only at the first UPDATE, INSERT, or DELETE operation. If you don't exclusive lock the table before the select, some other user may also read the selected data, and try and do their own update, causing a deadlock while you both wait for the other to release the select-induced shared lock so you can get an exclusive lock to do the update.

Another example of deadlock is where one user locks one table, and another user locks a second table. While both keep their existing locks, the first user tries to lock the second user's table, and the second user tries to lock the first user's table. Both users deadlock waiting for the tables to become available. The only solution to this is for both users to lock tables in the same order, so user's lock acquisitions and requests to not form a deadlock.

Note: Postgres does detect deadlocks and will rollback transactions to resolve the deadlock. Usually, at least one of the deadlocked transactions will complete successfully.

Notes

LOCK is a Postgres language extension.

LOCK works only inside transactions.

Bug: If the locked table is dropped then it will be automatically unlocked even if a transaction is still in progress.

Usage

--Explicit locking to prevent deadlock:
--
BEGIN WORK;
    LOCK films;
    SELECT * FROM films;
    UPDATE films SET len = INTERVAL '100 minute'
        WHERE len = INTERVAL '117 minute';
COMMIT WORK;

Compatibility

SQL92

There is no LOCK TABLE in SQL92, which instead uses SET TRANSACTION to specify concurrency level on transactions.