Cellular Anatomy of PostgreSQL: Locks, Latches & Concurrency Control¶
1. Introduction: The Biological Hierarchy of Database Concurrency¶
To understand database concurrency at a cellular level, one must reject the high-level illusion that "Postgres locks a row or a table." In the C source code (src/backend/storage/lmgr/), PostgreSQL manages concurrency through a strict, multi-tiered hierarchy of synchronization primitives:
[Level 1: Hardware/CPU Level] ----> Spinlocks (TAS / Hardware Atomic instructions)
|
v
[Level 2: Memory Block Level] ------> LWLocks (Lightweight Locks: Reader/Writer Semaphores)
|
v
[Level 3: SQL/Catalog Level] ------> Heavyweight Locks (Regular Locks: 8 Modes, Deadlock Detection)
|
v
[Level 4: Transaction Level] ------> Predicate Locks (SSI: Serializable Snapshot Isolation)
If a surgeon opens a human body, they see organs composed of tissues, tissues composed of cells, and cells governed by proteins. In PostgreSQL:
* An SQL Table Lock (e.g., AccessExclusiveLock) is an organ.
* Inside shared memory, that lock is composed of LOCK and PROCLOCK hash structs (tissues).
* Those structs are protected from concurrent memory corruption by an LWLock (cells).
* That LWLock is protected from CPU instruction racing by a Hardware Spinlock (s_lock_t, atomic test-and-set) (molecules/proteins).
2. Tier 1: Hardware Spinlocks (s_lock_t)¶
2.1 The Molecular Definition¶
Located in src/include/storage/s_lock.h.
A spinlock is an un-interruptible, atomic CPU primitive. It occupies a single byte of memory:
2.2 Execution Mechanics: Test-And-Set (TAS)¶
When a backend process needs to modify a shared memory pointer for just a few CPU cycles:
void
s_lock(volatile slock_t *lock, const char *file, int line, const char *func)
{
int spins = 0;
while (TAS(lock)) // Test-And-Set assembly instruction
{
/* Spin in a tight loop */
do_sub_cpu_pause(); // Emits CPU PAUSE instruction (x86_64)
if (++spins > 1000)
{
/* After 1000 spins, yield CPU time-slice to OS kernel */
pg_usleep(1000L);
spins = 0;
}
}
}
2.3 The Cellular Rule of Spinlocks:¶
- Never sleep while holding a spinlock.
- Never perform disk I/O while holding a spinlock.
- Never allocate memory while holding a spinlock.
- Spinlocks are held for nanoseconds (typically 10 to 50 CPU instructions), exclusively to increment an integer, swap a pointer, or update a flag.
3. Tier 2: Lightweight Locks (LWLock)¶
3.1 Anatomical Structure¶
Located in src/include/storage/lwlock.h.
LWLocks protect multi-byte data structures in shared memory (such as the hash tables for shared_buffers or the global transaction array ProcArray).
typedef struct LWLock
{
uint16 tranche; /* Identifies which subsystem owns this lock */
pg_atomic_uint32 state; /* 32-bit atomic state packing flags & counts */
proclist_head waiters; /* Doubly-linked list of sleeping PGPROC processes */
} LWLock;
3.2 State Bit-Field Dissection (32-bit Integer)¶
The state field is not a simple integer. It is a bit-packed atomic field:
[Bit 31] : LW_VAL_EXCLUSIVE (1 = Lock held exclusively, 0 = Shared or Free)
[Bit 30] : LW_FLAG_HAS_WAITERS (1 = Backends are queued up sleeping)
[Bits 0 .. 29] : LW_VAL_SHARED (Count of concurrent readers holding shared lock)
- Shared Mode (
LW_SHARED): Up to \(2^{29}\) concurrent backends can read simultaneously (e.g., reading a page inshared_buffers). - Exclusive Mode (
LW_EXCLUSIVE): Exactly one backend is granted access (e.g., modifying buffer headers). All other backends are suspended.
3.3 Sleep and Wakeup Mechanics (OS Futexes and Semaphores)¶
If an LWLock cannot be acquired immediately:
1. The backend atomically sets LW_FLAG_HAS_WAITERS = 1.
2. The backend enqueues its own PGPROC struct into the lock's waiters queue.
3. The backend puts itself to sleep using an OS semaphore or latch (WaitLatch()).
4. When the lock holder finishes, it calls LWLockRelease():
* It clears LW_VAL_EXCLUSIVE.
* It inspects LW_FLAG_HAS_WAITERS. If set, it traverses the waiters queue and issues a system wakeup call (SetLatch()) to the sleeping backends.
4. Tier 3: Heavyweight Locks (The SQL Locking Engine)¶
This is the system that implements table locks, row locks, page locks, and transaction IDs.
Located in src/backend/storage/lmgr/lock.c and src/include/storage/lock.h.
4.1 The Core Anatomical Structs¶
There are two primary structures that live in the shared memory hash tables:
1. The LOCK Struct (The Object Being Locked)¶
Represents the target resource (e.g., a table, a page, or a transaction ID):
typedef struct LOCK
{
/* Identification */
LOCKTAG tag; /* Unique 16-byte identifier of the resource */
/* Lock Grant Counters */
LOCKMASK grantMask; /* Bitmask of all lock modes currently granted */
LOCKMASK waitMask; /* Bitmask of lock modes currently waiting */
SHM_QUEUE procLocks; /* Head of linked list of PROCLOCK objects */
PROC_QUEUE waitProcs; /* FIFO queue of waiting PGPROC processes */
int requested[8]; /* Total requests per lock mode (0..7) */
int nRequested; /* Total number of requests across all modes */
int granted[8]; /* Total granted locks per mode (0..7) */
int nGranted; /* Total number of granted locks */
} LOCK;
2. The LOCKTAG (The 16-Byte DNA of a Lock Target)¶
typedef struct LOCKTAG
{
uint32 locktag_field1; /* Database OID */
uint32 locktag_field2; /* Relation (Table) OID */
uint32 locktag_field3; /* Block (Page) Number */
uint16 locktag_field4; /* Tuple Offset or Sub-ID */
uint8 locktag_type; /* LOCKTAG_RELATION, LOCKTAG_TUPLE, etc. */
uint8 locktag_lockmethodid; /* DEFAULT_LOCKMETHOD */
} LOCKTAG;
3. The PROCLOCK Struct (The Bond Between Process and Lock)¶
An M:N relationship exists between processes and locks. One process can hold multiple locks; one lock can be shared by multiple processes. The PROCLOCK struct represents this exact pairing:
typedef struct PROCLOCK
{
/* Identification */
PROCLOCKTAG tag; /* Points to (LOCK*, PGPROC*) */
/* Mask of held modes */
LOCKMASK holdMask; /* Modes this specific process holds */
LOCKMASK releaseMask; /* Modes to release at transaction commit */
/* Doubly linked list links */
SHM_QUEUE lockLink; /* Member of LOCK->procLocks list */
SHM_QUEUE procLink; /* Member of PGPROC->myProcLocks list */
} PROCLOCK;
+--------------------------------------------------------------------------------+
| SHARED MEMORY LOCK HASH |
| |
| [ LOCK Object: Table "orders" ] <=========================================+ |
| | | |
| +--> grantMask: [AccessShareLock | RowExclusiveLock] | |
| +--> waitMask: [AccessExclusiveLock] | |
| +--> procLocks (List) | |
| | | |
| v | |
| +-------------------+ +-------------------+ | |
| | PROCLOCK (Backend A)| | PROCLOCK (Backend B)| | |
| | Holds: AccessShare| | Holds: RowExclusive| | |
| +---------+---------+ +---------+---------+ | |
| | | | |
| v v | |
| [ PGPROC: Backend A ] [ PGPROC: Backend B ] | |
| | |
| [ waitProcs FIFO Queue ] | |
| | | |
| +--> [ PGPROC: Backend C (ALTER TABLE) - SLEEPING ON LATCH ] ======+ |
+--------------------------------------------------------------------------------+
5. The Cellular Physiology of Lock Acquisition (LockAcquireExtended)¶
When your SQL query runs SELECT * FROM orders;, what happens inside the engine?
Follow the execution path in src/backend/storage/lmgr/lock.c:
Client SQL: SELECT * FROM orders;
|
v
heap_open() -> LockRelationOid() -> LockAcquire() -> LockAcquireExtended()
Microscopic Step-by-Step Traversal:¶
Step 1: Compute Hash Values¶
The engine computes two 32-bit hashes:
1. LockHashPartition(locktag): Identifies which of the 16 shared memory lock table partitions holds the LOCK struct.
2. Identifies the corresponding LWLock protecting that partition (LockHashPartitionLock).
Step 2: Acquire the Partition LWLock¶
The backend acquires LWLockAcquire(partitionLock, LW_EXCLUSIVE).
* This ensures that no other process can insert or delete locks in this partition while we look up the table.
Step 3: Fast-Path Lock Check (The CPU Bypass Optimization)¶
Acquiring a full LOCK and PROCLOCK struct involves updating shared hash tables, which causes CPU cache line invalidation across CPU sockets.
To avoid this for simple reads:
* Postgres maintains a tiny array inside each backend's private PGPROC called fpLockBits (Fast Path Locks).
* If the lock mode is weak (e.g., AccessShareLock, RowShareLock, RowExclusiveLock) and the table does not currently have any conflicting locks:
* Postgres records the lock directly into the local PGPROC->fpLockBits array!
* It releases the partition LWLock immediately.
* Zero shared memory writes! Total execution time: < 80 nanoseconds.
Step 4: The Slow Path (Full Lock Hash Table Insertion)¶
If Fast-Path cannot be used (or the lock mode is strong, like AccessExclusiveLock):
1. Search LockMethodLockHash for the LOCKTAG. If not found, allocate a new LOCK from shared memory.
2. Search LockMethodProcHash for the PROCLOCKTAG. If not found, allocate a new PROCLOCK.
3. Check the Conflict Matrix:
lock->waitProcs is empty:
* Add requested_mode to lock->grantMask.
* Add requested_mode to proclock->holdMask.
* Increment lock->granted[requested_mode].
* Release partition LWLock. Return LOCKACQUIRE_OK.
6. The Cellular Pathology: The Lock Stampede & Domino Queue¶
Now let us dissect lines 218–251 of your notes under an electron microscope. Why does an ALTER TABLE kill an entire database in seconds?
6.1 The 8-Mode Conflict Matrix in C Code¶
In src/backend/storage/lmgr/lock.c, the conflict matrix is defined as an array of bitmasks:
static const LOCKMASK LockConflicts[] = {
0,
/* 1: AccessShareLock */
(1 << AccessExclusiveLock),
/* 2: RowShareLock */
(1 << ExclusiveLock) | (1 << AccessExclusiveLock),
/* 3: RowExclusiveLock */
(1 << ShareLock) | (1 << ShareRowExclusiveLock) |
(1 << ExclusiveLock) | (1 << AccessExclusiveLock),
/* 4: ShareUpdateExclusiveLock */
(1 << ShareUpdateExclusiveLock) | (1 << ShareLock) |
(1 << ShareRowExclusiveLock) | (1 << ExclusiveLock) |
(1 << AccessExclusiveLock),
/* 5: ShareLock */
(1 << RowExclusiveLock) | (1 << ShareUpdateExclusiveLock) |
(1 << ShareRowExclusiveLock) | (1 << ExclusiveLock) |
(1 << AccessExclusiveLock),
/* 6: ShareRowExclusiveLock */
(1 << RowExclusiveLock) | (1 << ShareUpdateExclusiveLock) |
(1 << ShareLock) | (1 << ShareRowExclusiveLock) |
(1 << ExclusiveLock) | (1 << AccessExclusiveLock),
/* 7: ExclusiveLock */
(1 << RowShareLock) | (1 << RowExclusiveLock) |
(1 << ShareUpdateExclusiveLock) | (1 << ShareLock) |
(1 << ShareRowExclusiveLock) | (1 << ExclusiveLock) |
(1 << AccessExclusiveLock),
/* 8: AccessExclusiveLock */
(1 << AccessShareLock) | (1 << RowShareLock) |
(1 << RowExclusiveLock) | (1 << ShareUpdateExclusiveLock) |
(1 << ShareLock) | (1 << ShareRowExclusiveLock) |
(1 << ExclusiveLock) | (1 << AccessExclusiveLock)
};
6.2 The Domino Mechanism in C Code (WaitOnLock)¶
When ALTER TABLE requests AccessExclusiveLock:
1. It calls LockCheckConflicts().
2. A long SELECT is running, so lock->grantMask contains (1 << AccessShareLock).
3. LockConflicts[AccessExclusiveLock] has bit 1 set. Conflict is detected!
4. The backend enters WaitOnLock():
* It sets lock->waitMask |= (1 << AccessExclusiveLock).
* It appends its PGPROC to the tail of lock->waitProcs.
* It sets its state to WAITING and puts itself to sleep on its latch.
Now, a split second later, a new SELECT arrives:
1. It requests AccessShareLock.
2. It checks conflicts: Does AccessShareLock conflict with lock->grantMask?
* No! AccessShareLock does not conflict with AccessShareLock.
3. BUT HERE IS THE CELLULAR TRAP (src/backend/storage/lmgr/lock.c lines 1240–1260):
/*
* If there are waiters ahead of us, we must wait behind them
* IF our lock mode conflicts with ANY waiter already in the queue!
*/
if ((lock->waitMask & LockConflicts[my_requested_mode]) != 0)
{
/* We conflict with a WAITING transaction! We cannot jump the queue! */
must_wait = true;
}
AccessShareLock conflicts with AccessExclusiveLock (which is present in lock->waitMask)!
5. The verdict: Even though the current active lock holders are other readers, this new SELECT is forbidden from executing because allowing it to run would starve the waiting ALTER TABLE indefinitely!
6. The new SELECT is forced into WaitOnLock() and put to sleep.
7. Every subsequent SELECT hits this exact same branch and is put to sleep.
8. Within seconds, hundreds of backend worker processes are sleeping on semaphores, connection limits are saturated, and the application collapses.
7. The Cellular Diagnosis: How to Inspect the Lock Organism Live¶
When this incident occurs in production, run this anatomical query to view the exact lock dependencies:
SELECT
COALESCE(blockingl.relation::regclass::text, blockingl.locktype) as locked_resource,
blockeda.pid AS blocked_pid,
blockeda.usename AS blocked_user,
blockedl.mode AS blocked_mode,
blockeda.query AS blocked_statement,
blockinga.pid AS blocking_pid,
blockinga.usename AS blocking_user,
blockingl.mode AS blocking_mode,
blockinga.query AS current_statement_in_blocking_process
FROM pg_catalog.pg_locks blockedl
JOIN pg_stat_activity blockeda ON blockeda.pid = blockedl.pid
JOIN pg_catalog.pg_locks blockingl ON(
blockingl.database = blockedl.database AND
blockingl.relation = blockedl.relation AND
blockingl.locktype = blockedl.locktype AND
blockingl.pid != blockedl.pid)
JOIN pg_stat_activity blockinga ON blockinga.pid = blockingl.pid
WHERE NOT blockedl.granted
ORDER BY blocked_pid;
8. Cellular Antidotes (Defensive Architecture)¶
lock_timeoutInjection: Never execute DDL without setting a microscopic lock timeout. If the table is not acquired in 2 seconds, abort the transaction and release the queue before dominoes form:idle_in_transaction_session_timeout: Kills applications that hold row locks or share locks and go idle.- Partitioned DDL: Execute schema changes partition-by-partition so lock acquisition is isolated to individual sub-tables rather than the entire relation tree.