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:

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:

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.

-- 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

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 :

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

-- 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:

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:

pg_multixact/members/  → arrays of (XID, mode) per MultiXact
pg_multixact/offsets/  → MultiXactId → offset into members file

-- 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:

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

-- 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.

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

-- 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.

-- 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:

-- 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

-- ═══ 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"

-- 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

-- ═══ 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.