Locking and Tables
PostgreSQL provides various lock modes to control concurrent access to data in tables. Some of these lock modes are acquired by PostgreSQL automatically before statement execution, while others are provided to be used by applications. All lock modes acquired in a transaction are held for the duration of the transaction.
Table-Level Locks
- AccessShareLock
A read-lock mode acquired automatically on tables being queried.
Conflicts with AccessExclusiveLock only.
- RowShareLock
Acquired by SELECT FOR UPDATE and LOCK TABLE for IN ROW SHARE MODE statements.
Conflicts with ExclusiveLock and AccessExclusiveLock modes.
- RowExclusiveLock
Acquired by UPDATE, DELETE, INSERT and LOCK TABLE for IN ROW EXCLUSIVE MODE statements.
Conflicts with ShareLock, ShareRowExclusiveLock, ExclusiveLock and AccessExclusiveLock modes.
- ShareUpdateExclusiveLock
Acquired by VACUUM (without FULL) and LOCK TABLE for IN SHARE UPDATE EXCLUSIVE MODE statements.
Conflicts with ShareUpdateExclusiveLock, ShareLock, ShareRowExclusiveLock, ExclusiveLock and AccessExclusiveLock modes.
- ShareLock
Acquired by CREATE INDEX and LOCK TABLE for IN SHARE MODE statements.
Conflicts with RowExclusiveLock, ShareUpdateExclusiveLock, ShareRowExclusiveLock, ExclusiveLock and AccessExclusiveLock modes.
- ShareRowExclusiveLock
Acquired by LOCK TABLE for IN SHARE ROW EXCLUSIVE MODE statements.
Conflicts with RowExclusiveLock, ShareUpdateExclusiveLock, ShareLock, ShareRowExclusiveLock, ExclusiveLock and AccessExclusiveLock modes.
- ExclusiveLock
Acquired by LOCK TABLE for IN EXCLUSIVE MODE statements.
Conflicts with RowShareLock, RowExclusiveLock, ShareUpdateExclusiveLock, ShareLock, ShareRowExclusiveLock, ExclusiveLock and AccessExclusiveLock modes.
- AccessExclusiveLock
Acquired by ALTER TABLE, DROP TABLE, VACUUM and LOCK TABLE statements.
Conflicts with all modes (AccessShareLock, RowShareLock, RowExclusiveLock, ShareUpdateExclusiveLock, ShareLock, ShareRowExclusiveLock, ExclusiveLock and AccessExclusiveLock).
 | Only AccessExclusiveLock blocks SELECT (without FOR UPDATE) statements. |
Row-Level Locks
Row-level locks are acquired when rows are being updated (or deleted or marked for update). Row-level locks do not affect data querying. They block writers to the same row only.
PostgreSQL does not remember any information about modified rows in memory and so has no limit to the number of rows locked at one time. However, locking a row may cause a disk write; thus, for example, SELECT FOR UPDATE will modify selected rows to mark them and so will result in disk writes.
In addition to table and row locks, short-term share/exclusive locks are used to control read/write access to table pages in the shared buffer pool. These locks are released immediately after a tuple is fetched or updated. Application writers normally need not be concerned with page-level locks, but we mention them for completeness.
Deadlocks
Use of explicit locking can cause deadlocks, wherein two (or more) transactions each hold locks that the other wants. For example, if transaction 1 acquires exclusive lock on table A and then tries to acquire exclusive lock on table B, while transaction 2 has already exclusive-locked table B and now wants exclusive lock on table A, then neither one can proceed. PostgreSQL automatically detects deadlock situations and resolves them by aborting one of the transactions involved, allowing the other(s) to complete. (Exactly which transaction will be aborted is difficult to predict, and should not be relied on.)
The best defense against deadlocks is generally to avoid them by being certain that all applications using a database acquire locks on multiple objects in a consistent order. You should also ensure that the first lock acquired on an object in a transaction is the highest mode that will be needed for that object. If it is not feasible to verify this in advance, then deadlocks may be handled on-the-fly by retrying transactions that are aborted due to deadlock.
So long as no deadlock situation is detected, a transaction seeking either a table-level or row-level lock will wait indefinitely for conflicting locks to be released. This means it is a bad idea for applications to hold transactions open for long periods of time (for example, while waiting for user input).