LOCK TABLE acquires a table-level lock, blocking until any conflicting locks are released. If NOWAIT is specified and the lock cannot be acquired immediately, the transaction is aborted with an error. The lock is held for the remainder of the current transaction; there is no UNLOCK TABLE command.
When you lock a view, all relations in the view definition query are also locked recursively with the same lock mode.
Synopsis
LOCK [ TABLE ] [ ONLY ] name [ * ] [, ...] [ IN lockmode MODE ] [ NOWAIT ]
where lockmode is one of:
ACCESS SHARE | ROW SHARE | ROW EXCLUSIVE | SHARE UPDATE EXCLUSIVE
| SHARE | SHARE ROW EXCLUSIVE | EXCLUSIVE | ACCESS EXCLUSIVEParameters
| Parameter | Description |
|---|---|
name | The name (optionally schema-qualified) of an existing table to lock. To lock multiple tables, separate them with commas — LOCK TABLE a, b; is equivalent to LOCK TABLE a; LOCK TABLE b;, locking tables one by one in the order specified. |
ONLY | Lock only the named table, not its descendant tables. Omit ONLY to lock the table and all its descendants. Append * after the table name to explicitly include descendants. |
lockmode | Specifies which locks this lock conflicts with. Defaults to ACCESS EXCLUSIVE if not specified — the most restrictive mode. |
NOWAIT | Fail immediately with an error if the lock cannot be acquired, instead of waiting for conflicting locks to be released. The transaction is aborted. |
Lock modes
LOCK TABLE supports eight lock modes with increasing restrictiveness. The mode names containing ROW are misnomers at the table level: they indicate the intention to acquire row-level locks within the locked table. ROW EXCLUSIVE is itself a shareable table lock.
The eight available lock modes are:
ACCESS SHAREROW SHAREROW EXCLUSIVESHARE UPDATE EXCLUSIVESHARESHARE ROW EXCLUSIVEEXCLUSIVEACCESS EXCLUSIVE(default, most restrictive)
All lock modes have identical semantics as far as LOCK TABLE is concerned, differing only in the rules about which modes conflict with which.
Usage notes
Privileges
To lock a table, you must have the privilege corresponding to the lock mode, or be the table owner or a superuser:
| Required privilege | Permitted lock modes |
|---|---|
SELECT | ACCESS SHARE |
INSERT | ROW EXCLUSIVE |
UPDATE, DELETE, or TRUNCATE | Any lock mode |
To lock a view, you must have the corresponding privilege on the view itself. By default, the view's owner must have the relevant privileges on the underlying base relations — you do not need permissions on the base relations directly. However, if the view has security_invoker set to true, you (not the view owner) must have the relevant privileges on the underlying base relations.
Transaction block requirement
LOCK TABLE must be used inside a transaction block. Outside a transaction block, the lock is released as soon as the statement completes, making it useless — PostgreSQL reports an error in this case. Use BEGIN and COMMIT (or ROLLBACK) to define a transaction block.
Choosing a lock mode by isolation level
The right lock mode depends on your transaction's isolation level:
`READ COMMITTED`: Acquire a SHARE lock before querying to keep data stable for the duration of the transaction. SHARE conflicts with the ROW EXCLUSIVE lock held by writers, so LOCK TABLE IN SHARE MODE blocks until any concurrent writers commit or roll back. Once the lock is granted, no uncommitted writes are outstanding, and none can begin until you release it.
`REPEATABLE READ` or `SERIALIZABLE`: Run LOCK TABLE before any SELECT or data modification statement. The transaction's view of data is frozen at its first SELECT or data modification; a LOCK TABLE acquired later still prevents concurrent writes, but does not retroactively ensure you have read the latest committed values.
Avoiding deadlocks
If a transaction will both read and modify the table, use SHARE ROW EXCLUSIVE instead of SHARE. SHARE mode allows two transactions to hold it simultaneously, but both will then be unable to acquire ROW EXCLUSIVE to perform their updates — which causes a deadlock. SHARE ROW EXCLUSIVE ensures only one such transaction runs at a time.
To prevent deadlocks in general:
Acquire locks on the same objects in the same order across all transactions.
If a single object requires multiple lock modes, always acquire the most restrictive mode first.
Note that a transaction's own locks never conflict with each other: a transaction holding SHARE can acquire ROW EXCLUSIVE, as long as no other transaction also holds SHARE.
Examples
Lock a table before inserting into a foreign key table
Acquire a SHARE lock on the primary key table before inserting rows that reference it. This ensures no concurrent transaction deletes or updates referenced rows during the operation.
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;Lock a table before a delete operation
Use SHARE ROW EXCLUSIVE before a delete that affects related tables. This prevents other transactions from concurrently modifying either table and avoids potential deadlocks.
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;