PostgreSQL takes locks automatically for every statement. Readers do not block writers and writers do not block readers, because each statement reads from a snapshot (MVCC). Locks matter in three situations: two transactions change the same row, a schema change needs a table to itself, or your application asks for a lock explicitly.
To see who is blocked and by whom right now:
SELECT a.pid,
pg_blocking_pids(a.pid) AS blocked_by,
now() - a.query_start AS waiting_for,
left(a.query, 60) AS query
FROM pg_stat_activity a
WHERE cardinality(pg_blocking_pids(a.pid)) > 0;
The rest of this page explains what those sessions are waiting on. The examples use one table:
CREATE TABLE accounts (
id int PRIMARY KEY,
owner text NOT NULL,
balance numeric(12,2) NOT NULL
);
INSERT INTO accounts VALUES (1, 'alice', 500), (2, 'bob', 300);
Table-level locks
Every statement that touches a table takes a table-level lock in one of eight modes. The names are historical and misleading: ROW EXCLUSIVE is a lock on the table, not on rows. What distinguishes the modes is which other modes they conflict with.
| Mode | Taken by | Blocks (in short) |
|---|---|---|
ACCESS SHARE | SELECT | Only ACCESS EXCLUSIVE |
ROW SHARE | SELECT ... FOR UPDATE / FOR SHARE | EXCLUSIVE, ACCESS EXCLUSIVE |
ROW EXCLUSIVE | INSERT, UPDATE, DELETE, MERGE | SHARE and stronger |
SHARE UPDATE EXCLUSIVE | VACUUM (not FULL), ANALYZE, CREATE INDEX CONCURRENTLY, some ALTER TABLE forms | Itself, SHARE and stronger |
SHARE | CREATE INDEX (not concurrent) | Writes and VACUUM, but not other SHARE locks |
SHARE ROW EXCLUSIVE | CREATE TRIGGER, some ALTER TABLE forms | Writes, VACUUM, SHARE and itself |
EXCLUSIVE | REFRESH MATERIALIZED VIEW CONCURRENTLY | Everything except plain SELECT |
ACCESS EXCLUSIVE | DROP TABLE, TRUNCATE, VACUUM FULL, CLUSTER, REINDEX (not concurrent), most ALTER TABLE forms | Everything, including SELECT |
The practical summary: ordinary reads and writes coexist with each other and with VACUUM. A plain CREATE INDEX blocks writes for its whole duration. Most schema changes block everything, usually for a moment, but see the lock queue problem below.
A lock is held until the transaction ends. There is no UNLOCK TABLE command in PostgreSQL; COMMIT or ROLLBACK releases everything.
LOCK TABLE
You can take a table lock yourself, inside a transaction:
BEGIN;
LOCK TABLE accounts IN SHARE ROW EXCLUSIVE MODE;
-- work that must not run concurrently with other writers
COMMIT;
If you leave out the mode, LOCK TABLE takes ACCESS EXCLUSIVE, the strongest one. You rarely need this statement; row locks or a stricter isolation level usually solve the same problem with less blocking.
Row-level locks
UPDATE and DELETE lock the rows they change. A second transaction that tries to change the same row waits until the first commits or rolls back. Row locks never block a plain SELECT.
To lock rows before you change them, add a locking clause to SELECT:
BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
-- decide what to do based on the balance
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;
Without FOR UPDATE, two sessions could both read 500, both decide the withdrawal is allowed, and both write.
There are four row lock strengths, from strongest to weakest:
| Clause | Also taken by | Conflicts with |
|---|---|---|
FOR UPDATE | DELETE, and UPDATE that changes a key column | All four |
FOR NO KEY UPDATE | Other UPDATEs | All except FOR KEY SHARE |
FOR SHARE | FOR UPDATE, FOR NO KEY UPDATE | |
FOR KEY SHARE | Foreign key checks on the referenced row | FOR UPDATE only |
The two "key" variants exist so that inserting a child row, which takes FOR KEY SHARE on its parent, does not block an ordinary update of the parent's non-key columns.
PostgreSQL records a row lock in the row itself, not in a memory table. There is therefore no limit on how many rows a transaction can lock and no lock escalation: a million row locks never turn into a table lock.
Not waiting: NOWAIT and SKIP LOCKED
By default a locking statement waits indefinitely. Two options change that:
-- Fail immediately if any selected row is locked
SELECT * FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;
ERROR: could not obtain lock on row in relation "accounts"
-- Skip locked rows and return the others
SELECT id FROM accounts ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED;
SKIP LOCKED is the standard way to build a job queue on a table: each worker claims the next row nobody else has claimed.
Deadlocks
A deadlock is two transactions each waiting for a lock the other holds:
-- Session A -- Session B
BEGIN; BEGIN;
UPDATE accounts SET balance = balance - 10
WHERE id = 1;
UPDATE accounts SET balance = balance - 10
WHERE id = 2;
UPDATE accounts SET balance = balance + 10
WHERE id = 2; -- waits for B
UPDATE accounts SET balance = balance + 10
WHERE id = 1; -- waits for A
After a session has waited for deadlock_timeout (one second by default), PostgreSQL checks for a cycle and aborts one of the transactions:
ERROR: deadlock detected
DETAIL: Process 4121 waits for ShareLock on transaction 1207; blocked by process 4098.
Process 4098 waits for ShareLock on transaction 1208; blocked by process 4121.
HINT: See server log for query details.
The other transaction continues. The error has SQLSTATE 40P01; the application should roll back and retry the whole transaction.
To prevent deadlocks, make every transaction lock rows in the same order (for a transfer, always the lower account id first), take the strongest lock you will need at the start instead of upgrading later, and keep transactions short.
Advisory locks
Advisory locks are locks on a number you choose. PostgreSQL attaches no meaning to them and they do not block access to any table. They only conflict with other requests for the same number, which makes them a simple mutex for application logic such as "only one instance of this nightly job may run".
-- Returns true if we got the lock, false if another session holds it
SELECT pg_try_advisory_lock(42);
-- ... do the work ...
SELECT pg_advisory_unlock(42);
| Function | Scope | Released |
|---|---|---|
pg_advisory_lock(key) | Session; waits | By pg_advisory_unlock(key) or disconnect |
pg_try_advisory_lock(key) | Session; returns at once | Same |
pg_advisory_xact_lock(key) | Transaction; waits | Automatically at commit or rollback |
pg_try_advisory_xact_lock(key) | Transaction; returns at once | Automatically at commit or rollback |
The key is one bigint or a pair of integers. Session-level advisory locks survive ROLLBACK and are tied to the database connection, so behind a connection pooler in transaction mode they can be "held" by a connection your code no longer has. Use the transaction-level functions there.
Inspecting and resolving blocking
pg_locks lists every lock held or awaited; granted = false marks a waiter.
SELECT l.pid, l.locktype, l.mode, l.granted, l.relation::regclass AS relation
FROM pg_locks l
WHERE l.relation = 'accounts'::regclass;
Once you know the blocking pid from the first query on this page, look at what it is doing in pg_stat_activity. A session in state idle in transaction is the classic culprit: an application opened a transaction, changed something and never committed. You can stop it:
SELECT pg_cancel_backend(4098); -- cancel the running statement
SELECT pg_terminate_backend(4098); -- end the session and roll back its transaction
Setting log_lock_waits = on writes a log entry whenever a session waits longer than deadlock_timeout, which gives you a record of blocking after the fact.
Common mistakes
Schema changes without a timeout. An ALTER TABLE that needs ACCESS EXCLUSIVE has to wait for every open transaction that has touched the table, even a read. While it waits, every new query on that table queues up behind it. One forgotten open transaction plus one "instant" ALTER TABLE can stall an application. Always set a limit and retry on failure:
SET lock_timeout = '5s';
ALTER TABLE accounts ADD COLUMN note text;
If the lock is not obtained in time you get ERROR: canceling statement due to lock timeout and nothing else was held up for long.
Building indexes on a busy table with plain CREATE INDEX. It blocks writes until it finishes. Use CREATE INDEX CONCURRENTLY, which takes longer and cannot run inside a transaction block, but does not block writes.
Idle transactions. Set idle_in_transaction_session_timeout so the server ends sessions that sit in an open transaction.
Locking many tables in one transaction. Table locks are tracked in shared memory. A transaction that touches thousands of tables or partitions can fail with ERROR: out of shared memory and a hint to increase max_locks_per_transaction.
Expecting locks to prevent all anomalies. Under the default READ COMMITTED level, a waiting UPDATE re-reads the row after the blocker commits and then proceeds. If your logic needs a consistent view across statements, lock explicitly with FOR UPDATE or use REPEATABLE READ or SERIALIZABLE and retry on serialization failures.
Further reading
- Explicit Locking (lock modes and full conflict tables)
- The pg_locks view
- Lock management settings
- Advisory lock functions
Get the weekly commit
New database deep dives every week.
