5 PostgreSQL locking behaviors that trip people up

Summary

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 Enter fullscreen mode Exit fullscreen mode Session 2 ALTER TABLE t ADD COLUMN name text; Enter fullscreen mode Exit fullscreen mode 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. Besides a long-running SELECT, the same thing happens with a session that ran a SELECT inside a BEGIN transaction and then left it open without COMMIT/ROLLBACK (a so-called idle in transaction session). An ACCESS SHARE lock is held until the transaction ends, so even if the SELECT itself finished in an instant, simply forgetting to close the transaction will keep...