--- title: "PostgreSQL Internals - Module 8: Concurrency & Locking" description: "PostgreSQL locking deep dive: lock hierarchy, row-level locks in xmax, MultiXactId, deadlock detection, advisory locks, SKIP LOCKED queue, and pg_locks queries." author: "Samba Siva" published: 2026-05-25T15:26:28.683Z updated: 2026-11-09T08:48:30.241Z tags: - "PostgreSQL" - "Technical" - "PGInternals" url: "https://sambasivareddy.in/blog/postgresql-internals-module-8-concurrency-locking" --- # PostgreSQL Internals - Module 8: Concurrency & Locking In the previous blog, we explored VACUUM and how it keeps our PostgreSQL system clean by reclaiming dead tuple space, how autovacuum works under the hood, and what properties need to be configured to keep it running effectively. In this blog, we are going into **Module 8: Concurrency & Locking** - the mechanism PostgreSQL uses to coordinate simultaneous access to shared data without corruption. * * * ## The Lock Hierarchy - Two Levels PostgreSQL has two distinct locking systems that work together: - Table-Level locks - Row-Level Locks * * * ## Part 1: Table-level locks (relation locks) Stored in shared memory in the **lock table**. Eight lock modes, ordered by restrictiveness: 1. **AccessShareLock** - `SELECT` 2. **RowShareLock** - `SELECT FOR UPDATE` / `FOR SHARE` 3. **RowExclusiveLock** - `INSERT` , `DELETE` , `UPDATE` 4. **ShareUpdateExclusiveLock** \- `VACUUM` , `ANALYZE` , `CREATE INDEX CONCURRENTLY` 5. **ShareLock** \- `CREATE INDEX` (non-concurrent) 6. **ShareRowExclusiveLock** \- `CREATE TRIGGER` , some `ALTER TABLE` 7. **ExclusiveLock** \- `REFRESH MATERIALIZED VIEW CONCURRENTLY` 8. **AccessExclusiveLock** \- `DROP` , `TRUNCATE` , `VACUUM FULL` , `ALTER TABLE` , `LOCK TABLE` , most DDLs. **The conflict matrix - what blocks what** ``` AS RS RE SUE S SRE E AE AccessShareLock (AS) . . . . . . . X RowShareLock (RS) . . . . . . X X RowExclusiveLock (RE) . . . . X X X X ShareUpdateExclusiveLock(SUE) . . . X X X X X ShareLock (S) . . X X . X X X ShareRowExclusiveLock(SRE) . . X X X X X X ExclusiveLock (E) . X X X X X X X AccessExclusiveLock (AE) X X X X X X X X X = conflict (one blocks the other) . = compatible (both can proceed) ``` **Key observations from the matrix:** - `SELECT` never blocks `SELECT` - AS vs AS = Compatible → Reads never block reads - `SELECT` blocks `DDL` : - AS vs AE = Conflict → Alter Table waits for all SELECTs to finish - Writes never block reads `MVCC` : - RE vs AS = compatible → INSERT/UPDATE/DELETE never blocks SELECT - VACUUM never blocks normal DML - SUE vs AS/RS/RE = compatible → autovacuum runs alongside production traffic - The most dangerous: `ALTER TABLE` acquires AE - It must wait for ALL existing locks to clear AND new queries queue behind it. * * * ## Part 2: How Table Locks are Acquired and Stored ### The lock table in shared memory PostgreSQL maintains a **lock table** in shared memory - a hash table keyed by `(database OID, relation OID)` . Each entry contains: ```c typedef struct LOCK { LOCKTAG tag; /* unique identifier for the lockable object */ LOCKMASK grantMask; /* bitmask of lock modes currently granted */ LOCKMASK waitMask; /* bitmask of lock modes being waited for */ SHM_QUEUE procLocks; /* list of PROCLOCK objects for this lock */ PROC_QUEUE waitProcs; /* list of waiting backends */ int requested[MAX_LOCKMODES]; /* count of each mode requested */ int granted[MAX_LOCKMODES]; /* count of each mode granted */ int nRequested; /* total requested locks */ int nGranted; /* total granted locks */ } LOCK; ``` Each backend also has a **PROCLOCK** entry linking it to a lock: ```c typedef struct PROCLOCK { PROCLOCKTAG tag; /* (LOCK *, PGPROC *) pair */ PGPROC *groupLeader; /* lock group leader */ LOCKMASK holdMask; /* bitmask of lock modes held */ LOCKMASK releaseMask; /* bitmask of lock modes to release */ SHM_QUEUE lockLink; /* list of PROCLOCKs for same LOCK */ SHM_QUEUE procLink; /* list of PROCLOCKs for same PGPROC */ } PROCLOCK; ``` ### Lock escalation does not exist Unlike some other databases, PostgreSQL does **not** escalate row locks to table locks. A transaction that updates 10 million rows holds 10 million rows holds 10 million row-level locks (via `xmax` , not the lock table) and still only holds one `RowExclusiveLock` on the table. There is no automatic promotion to a table-level write lock. ### Fast-path locking For the common case of `AccessShareLock` and `RowExclusiveLock` on regular tables, PostgreSQL uses a **fast-path** optimization - it stores up to 16 weak locks per backend in the `PGPROC` struct directly, bypassing the shared lock table entirely. Only when a conflicting lock appears does it fall back to the main lock table. ```sql -- See all current locks: SELECT pid, locktype, relation::regclass, mode, granted FROM pg_locks WHERE relation IS NOT NULL ORDER BY relation, pid; ``` * * * ## Part 3: Row-Level Locks - No Lock Table Entry Row-level locks are fundamentally different from table-level locks. They are **not stored in the lock table**. Instead they are encoded directly in the tuple header - specifically in `xmax` and `t_infomask`. ### How a row lock is stored ```sql SELECT * FROM orders WHERE id = 42 FOR UPDATE; ``` What happens on the heap page: ``` Before FOR UPDATE: tuple: xmin=1000, xmax=0, t_infomask=0x0900 ↑ XMIN_COMMITTED | XMAX_INVALID After FOR UPDATE (transaction 5001): tuple: xmin=1000, xmax=5001, t_infomask=0x0440 ↑ XMAX_EXCL_LOCK | XMAX_LOCK_ONLY ``` The key flags in `t_infomask` : ```sql HEAP_XMAX_LOCK_ONLY 0x0080 — xmax is a lock, not a delete HEAP_XMAX_EXCL_LOCK 0x0040 — exclusive lock (FOR UPDATE) HEAP_XMAX_KEYSHR_LOCK 0x0010 — key-share lock (FOR KEY SHARE) ``` When the locking transaction commits or rolls back, the `xmax` is left in place. The next transaction to visit the tuple checks whether `xmax` is still active (via CLOG) - if the locking transaction is done, the lock is considered released. This is why row locks have zero cleanup cost - they disappear when the transaction ends. ### Lock modes for row-level operation - `FOR KEY SHARE` - weakest. Prevents key updates and deletes. Used by foreign key checks on referenced rows. - `FOR SHARE` - prevents updates and deletes. - `FOR NO KEY UPDATE` - like FOR `UPDATE` but allows key-share lockers. Used by `UPDATE` on non-key columns. - `FOR UPDATE` - strongest. Prevents all concurrent modification. Used by `UPDATE` and `DELETE`. **Conflict matrix for row locks**: ``` KEY SHARE SHARE NO KEY UPDATE UPDATE FOR KEY SHARE . . . X FOR SHARE . . X X FOR NO KEY UPDATE . X X X FOR UPDATE X X X X ``` ### Example ```sql -- Demonstrate row lock: -- Terminal 1: BEGIN; SELECT * FROM orders WHERE id = 42 FOR UPDATE; -- Row is now locked -- Terminal 2: SELECT * FROM orders WHERE id = 42 FOR UPDATE; -- Blocks! Waiting for Terminal 1 to commit/rollback -- See the wait: SELECT pid, locktype, relation::regclass, page, tuple, mode, granted FROM pg_locks WHERE NOT granted; ``` * * * ## Part 4: MultiXactId - When Multiple Transactions Lock One Row What happens when two transactions both hold a `FOR SHARE` lock on the same row at the same time? They are compatible, so both should be able to lock it. But the tuple's `xmax` field is only 32 bits - it can only store one XID. PostgreSQL solves this with **MultiXactId**: ```markdown Normal row lock: xmax = 5001 (single locker XID) t_infomask: XMAX_KEYSHR_LOCK | XMAX_LOCK_ONLY Multiple shared lockers: xmax = 123 ← this is a MultiXactId, not a real XID! t_infomask: XMAX_IS_MULTI | XMAX_KEYSHR_LOCK | XMAX_LOCK_ONLY ``` The `MultiXactId` is a pointer into the **multixact** subsystem - files in `$PGDATA/pg_multixact/` that store arrays of (XID, lock\_mode) pairs: ```c pg_multixact/members/ → arrays of (XID, mode) per MultiXact pg_multixact/offsets/ → MultiXactId → offset into members file ``` ```sql -- MultiXact occupies just as much space as XID — 32 bits -- It also has a wraparound problem: vacuum_multixact_freeze_max_age -- Monitor it like XID age: SELECT datname, age(datfrozenxid) AS xid_age, mxid_age(datminmxid) AS mxid_age FROM pg_database ORDER BY mxid_age DESC; ``` * * * ## Part 5: Deadlock Detection A deadlock occurs when two or more transactions are each waiting for a lock held by the other: ``` T1 holds lock on row A, waits for lock on row B T2 holds lock on row B, waits for lock on row A → neither can proceed → deadlock ``` ### How PostgreSQL detects deadlocks PostgreSQL does **not** use a timeout to detect deadlocks. It uses a **wait-for graph** algorithm: ```xml 1. When T1 waits for a lock, it sleeps for deadlock_timeout (default 1s) — this is the "optimistic" wait: most locks clear within 1s 2. After deadlock_timeout elapses, T1 calls DeadLockCheck(): a. Build a directed graph: T1 → T2 means "T1 is waiting for T2" b. Walk the graph using DFS looking for cycles c. If cycle found: pick one transaction to abort (the "victim") — PostgreSQL picks the transaction that would be cheapest to restart — usually the one that has done the least work 3. The victim receives: ERROR: deadlock detected DETAIL: Process 1234 waits for ShareLock on transaction 5001 Process 5678 waits for ShareLock on transaction 1234 HINT: See server log for query details. ``` ### Example ```sql -- Demonstrate deadlock: -- Terminal 1: BEGIN; UPDATE orders SET status='x' WHERE id=1; -- (now pause) -- Terminal 2: BEGIN; UPDATE orders SET status='y' WHERE id=2; UPDATE orders SET status='y' WHERE id=1; -- waits for T1 -- Terminal 1: UPDATE orders SET status='x' WHERE id=2; -- waits for T2 → deadlock! -- After deadlock_timeout (1s), one transaction gets: -- ERROR: deadlock detected -- Monitor deadlocks: SELECT deadlocks FROM pg_stat_database WHERE datname = current_database(); ``` * * * ## Part 6: Lock Queuing - The Hidden Traffic Jam Understanding lock queuing is critical for production systems. Locks are not just granted or denied - they queue. ```xml Timeline: t=0 T1 acquires AccessShareLock (SELECT) — granted immediately t=1 T2 acquires AccessShareLock (SELECT) — granted immediately t=2 T3 requests AccessExclusiveLock (ALTER TABLE) — BLOCKED by T1, T2 T3 enters the wait queue for AE lock t=3 T4 requests AccessShareLock (SELECT) — BLOCKED by T3! Even though AS is compatible with AS, T4 must wait because T3 is already in the queue. This prevents T3 from starving. t=4 T1 commits — T3 still waiting (T2 still holds AS) t=5 T2 commits — T3 granted AE lock T3 is now running ALTER TABLE t=6 T4 finally gets AS lock (after T3 completes) ``` **This is the ALTER TABLE traffic jam.** A single `ALTER TABLE` on a busy table: 1. Waits for all existing connections to release locks 2. Blocks ALL new queries behind it (even simple SELECTs) 3. Can cascade into hundreds of waiting connections ### Safe ALTER TABLE pattern ```sql -- Instead of running ALTER TABLE alone (dangerous on busy tables): ALTER TABLE orders ADD COLUMN notes text; -- Use lock_timeout + retry loop: SET lock_timeout = '2s'; BEGIN; ALTER TABLE orders ADD COLUMN notes text; COMMIT; -- If it times out, retry during a quieter period -- Even safer: check for blocking sessions first: SELECT count(*) FROM pg_stat_activity WHERE query NOT LIKE '%pg_stat_activity%' AND state != 'idle' AND pid != pg_backend_pid(); -- Only run ALTER TABLE when this is near zero ``` * * * ## Part 7: Advisory Locks Advisory locks are application-level locks with no automatic association to database objects. PostgreSQL provides the locking mechanism; your application decides what it means. ```sql -- Session-level advisory lock (held until released or session ends): SELECT pg_advisory_lock(12345); -- ... do work ... SELECT pg_advisory_unlock(12345); -- Transaction-level advisory lock (released at COMMIT/ROLLBACK): BEGIN; SELECT pg_advisory_xact_lock(12345); -- ... do work ... COMMIT; -- lock released automatically -- Try-lock (non-blocking): SELECT pg_try_advisory_lock(12345); -- Returns TRUE if lock acquired, FALSE if already held -- Lock with two 32-bit integers (more namespace): SELECT pg_advisory_lock(hashtext('job_processor'), job_id); ``` ### SKIP LOCKED - the queue pattern `SKIP LOCKED` is not an advisory lock - it is a row-level lock modifier. It tells PostgreSQL: **"if you encounter a row that is already locked, skip it instead of waiting."** This is the foundation of efficient work queues: ```sql -- Multiple workers can safely dequeue without contention: -- Worker 1: SELECT * FROM jobs WHERE status='pending' ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED; -- Locks job_id=1 -- Worker 2 (simultaneously): SELECT * FROM jobs WHERE status='pending' ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED; -- Sees job_id=1 is locked → skips to job_id=2 -- No blocking, no waiting ``` * * * ## Part 8: Lock Monitoring in Production ```sql -- ═══ 1. See everything currently locked ═══ SELECT pid, locktype, CASE locktype WHEN 'relation' THEN relation::regclass::text WHEN 'tuple' THEN relation::regclass::text || ':' || page::text || ',' || tuple::text WHEN 'transactionid' THEN transactionid::text ELSE locktype END AS object, mode, granted, waitstart FROM pg_locks ORDER BY granted, waitstart NULLS LAST; -- ═══ 2. Find blocked queries and their blockers ═══ SELECT blocked.pid AS blocked_pid, blocked.usename AS blocked_user, now() - blocked.query_start AS wait_time, blocked.query AS blocked_query, blocking.pid AS blocking_pid, now() - blocking.query_start AS blocking_duration, blocking.query AS blocking_query FROM pg_stat_activity blocked JOIN pg_stat_activity blocking ON blocking.pid = ANY(pg_blocking_pids(blocked.pid)) WHERE cardinality(pg_blocking_pids(blocked.pid)) > 0 ORDER BY wait_time DESC; -- ═══ 3. Identify long-running transactions (lock holders) ═══ SELECT pid, usename, now() - xact_start AS tx_duration, now() - query_start AS query_duration, state, left(query, 100) AS query FROM pg_stat_activity WHERE xact_start IS NOT NULL AND state != 'idle' ORDER BY xact_start ASC LIMIT 10; -- ═══ 4. Count lock waits per table (operational health) ═══ SELECT relation::regclass AS table, mode, count(*) FILTER (WHERE granted) AS granted, count(*) FILTER (WHERE NOT granted) AS waiting FROM pg_locks WHERE relation IS NOT NULL GROUP BY relation, mode HAVING count(*) FILTER (WHERE NOT granted) > 0 ORDER BY waiting DESC; -- ═══ 5. Kill a blocking session (use carefully!) ═══ -- Soft kill (waits for current query to finish): SELECT pg_cancel_backend(pid); -- Hard kill (terminates session immediately): SELECT pg_terminate_backend(pid); -- Kill all idle-in-transaction sessions > 10 minutes: SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state = 'idle in transaction' AND now() - state_change > interval '10 minutes'; ``` * * * ## Part 9: Lock Timeout and Statement Timeout - `lock_timeout` - Prevent queries from waiting forever for locks. - If a lock cannot be acquired within specified time (say `5s`), it throws **"ERROR: canceling statement due to lock timeout"** - `statement_timeout` - Prevent long-running queries from holding locks. - If a query runs longer than specified time (say `30s`), it throws **"ERROR: canceling statement due to statement timeout"** - `idle_in_transaction_session_timeout` - Prevent idle transaction from holding locks. - If a transaction is open but idle for the given time (say `5min`), it throws **"ERROR: termination connection due to idle-in-transaction timeout"** ```sql -- Best practice: set all three in postgresql.conf SET lock_timeout = '30s' SET statement_timeout = '60s' SET idle_in_transaction_session_timeout = '5min' -- Per-session override for maintenance work: SET LOCAL lock_timeout = '0'; -- no timeout for this transaction SET LOCAL statement_timeout = '0'; -- no timeout for this transaction ``` * * * ## Part 10: Hands-On Lab ```sql -- ═══ 1. Observe lock acquisition ═══ -- See your own locks: BEGIN; SELECT * FROM orders LIMIT 1; SELECT pid, locktype, relation::regclass, mode, granted FROM pg_locks WHERE pid = pg_backend_pid(); -- Should see AccessShareLock on orders -- Also see ExclusiveLock on your transaction ID COMMIT; -- ═══ 2. Demonstrate lock conflict ═══ -- Terminal 1: BEGIN; LOCK TABLE orders IN SHARE MODE; SELECT pg_sleep(30); -- hold the lock -- Terminal 2: SET lock_timeout = '3s'; BEGIN; INSERT INTO orders VALUES (...); -- blocked by SHARE lock -- After 3s: ERROR: canceling statement due to lock timeout -- ═══ 3. Observe row-level locking in tuple ═══ CREATE TABLE lock_demo (id int, val text); INSERT INTO lock_demo VALUES (1, 'hello'); BEGIN; SELECT * FROM lock_demo WHERE id = 1 FOR UPDATE; -- Inspect the tuple while locked: SELECT lp, t_xmin, t_xmax, t_infomask FROM heap_page_items(get_raw_page('lock_demo', 0)); -- t_xmax = your transaction XID (lock recorded in tuple!) -- t_infomask has XMAX_EXCL_LOCK | XMAX_LOCK_ONLY bits set COMMIT; -- After commit, check again: SELECT lp, t_xmin, t_xmax, t_infomask FROM heap_page_items(get_raw_page('lock_demo', 0)); -- t_xmax still shows the XID — but CLOG says committed -- Next accessor checks CLOG → sees lock released -- ═══ 4. Reproduce and observe a deadlock ═══ -- Terminal 1: BEGIN; UPDATE lock_demo SET val='T1' WHERE id=1; -- Terminal 2: BEGIN; UPDATE lock_demo SET val='T2' WHERE id=2; UPDATE lock_demo SET val='T2' WHERE id=1; -- waits -- Terminal 1: UPDATE lock_demo SET val='T1' WHERE id=2; -- deadlock! -- One terminal will get: ERROR: deadlock detected -- Check deadlock counter: SELECT deadlocks FROM pg_stat_database WHERE datname = current_database(); -- ═══ 5. SKIP LOCKED queue pattern ═══ CREATE TABLE work_queue ( id bigserial primary key, payload text, status text DEFAULT 'pending' ); INSERT INTO work_queue(payload) SELECT 'job_' || g FROM generate_series(1,100) g; -- Simulate two workers claiming jobs simultaneously: -- Worker 1: BEGIN; SELECT id, payload FROM work_queue WHERE status = 'pending' ORDER BY id LIMIT 5 FOR UPDATE SKIP LOCKED; -- Claims jobs 1-5 -- Worker 2 (simultaneously): BEGIN; SELECT id, payload FROM work_queue WHERE status = 'pending' ORDER BY id LIMIT 5 FOR UPDATE SKIP LOCKED; -- Claims jobs 6-10 (skipped 1-5 which are locked) -- No blocking! -- ═══ 6. Advisory lock for distributed job ═══ -- Only one session can run this job: SELECT CASE WHEN pg_try_advisory_lock(12345) THEN 'Got the lock — running job' ELSE 'Another worker has the lock — skipping' END; -- See advisory locks in pg_locks: SELECT pid, locktype, classid, objid, mode, granted FROM pg_locks WHERE locktype = 'advisory'; SELECT pg_advisory_unlock(12345); ``` * * * ## Conclusion In this module, we explored how PostgreSQL manages concurrent access at every level — from the 8-mode table lock hierarchy and its conflict matrix, to row-level locks encoded directly in the tuple's `xmax` field with zero lock table overhead, to `MultiXactId` for shared row locks, deadlock detection via wait-for graph cycle analysis, the hidden lock queuing problem that turns a single `ALTER TABLE` into a traffic jam, and advisory locks with `SKIP LOCKED` for contention-free work queues. **What's Next → Module 9: Replication** You now understand how PostgreSQL coordinates concurrent writes safely. Module 9 goes into how those writes are streamed to replicas - physical streaming replication at the WAL level, logical replication and the decoding pipeline, replication slots and their wraparound risk, and synchronous vs asynchronous commit.