Introduction
PostgreSQL uses MVCC (Multi-Version Concurrency Control) for concurrency control: reads never block writes, and writes never block reads.
Its locking system has 8 table-level lock modes and 4 row-level lock modes, and the conflict tables in the documentation tell you exactly which lock modes conflict with which.
In practice, though, once you actually operate PostgreSQL, locks end up conflicting in places you never expected. Queries take far longer than anticipated, and in the worst case you end up with an outage.
This article walks through five of these counterintuitive locking behaviors.
Environment
- Version: PostgreSQL 18
- Transaction isolation level: READ COMMITTED (the default)
1. Once an ACCESS EXCLUSIVE request is queued, subsequent queries get blocked in a chain
The first one: an ALTER TABLE that should finish instantly can bring your entire service to a halt.
Suppose one session is running a long SELECT on table t, and another session runs the following ALTER TABLE:
Session 1
SELECT pg_sleep(600) FROM t LIMIT 1; -- a long-running SELECT
Session 2
ALTER TABLE t ADD COLUMN name text;
Since Session 1 holds an ACCESS SHARE lock on table t, the ACCESS EXCLUSIVE lock that Session 2's ALTER TABLE requires is forced to wait. So far, this is expected behavior.
But PostgreSQL's lock waiting works like a FIFO queue. While the ACCESS EXCLUSIVE lock is waiting, any SELECT issued against table t afterward gets stuck behind it — even though that SELECT does not conflict with Session 1's currently running SELECT at all.
SOCIAL SHARE CARD GENERATOR