PostgreSQL implements Serializable Snapshot Isolation (SSI) using predicate locking and serialization graph testing to achieve true serializability without the performance penalties of traditional two-phase locking. The 40001 error (serialization_failure) occurs specifically during write skew or read-write conflicts where two transactions establish a rw-dependency cycle. For example, Transaction A reads rows satisfying a predicate (e.g., WHERE color = 'red'), Transaction B reads rows satisfying a non-overlapping predicate (e.g., WHERE color = 'blue'), then A updates rows to 'blue' while B updates rows to 'red'. Neither transaction blocks the other, but the result is non-serializable.
This pattern represents a dangerous structure in the serialization graph: two consecutive rw-antidependencies forming a potential cycle. PostgreSQL detects this and aborts one transaction to prevent anomalous states. The problem is subtle because the transactions might modify different physical rows, making the conflict invisible to row-locking mechanisms used in lower isolation levels.
The mandated solution requires the application to implement an optimistic retry loop. When catching SQL EXCEPTION '40001', the application must roll back the current transaction and retry the entire operation with exponential backoff. Unlike deadlocks, which typically resolve by retrying immediately, serialization failures under high contention benefit from jittered delays to prevent thundering herds.
-- Example of application retry logic in PL/pgSQL DO $$ DECLARE retries INT := 0; max_retries INT := 3; BEGIN WHILE retries < max_retries LOOP BEGIN SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE; PERFORM * FROM inventory WHERE category = 'electronics' AND count > 0; UPDATE inventory SET count = count - 1 WHERE item_id = 123; COMMIT; EXIT; EXCEPTION WHEN SQLSTATE '40001' THEN ROLLBACK; retries := retries + 1; PERFORM pg_sleep(power(2, retries) * 0.1); -- Exponential backoff END; END LOOP; END $$;
A concert ticket exchange platform allowed users to swap seat categories via check-then-act logic. Transaction A verified that VIP seats were available, then downgraded a held VIP seat to Standard. Simultaneously, Transaction B verified Standard availability and upgraded a Standard seat to VIP. Under READ COMMITTED, both transactions read availability as true, executed updates, and the system ended with negative inventory in both categories despite each transaction checking constraints.
Three solutions were architected. The first used explicit SELECT FOR UPDATE locking, but this failed when availability queries returned zero rows, acquiring no locks and leaving the system vulnerable to phantom inserts. The second approach implemented ADVISORY LOCKS using pg_try_advisory_lock() to serialize access to seat categories, which prevented conflicts but introduced complex lock ordering risks and reduced throughput by 40% due to serialization of all category checks.
The third solution adopted SERIALIZABLE isolation with an application-level retry loop. This was chosen because it guaranteed correctness without manual lock management, and the retry overhead was acceptable given the low frequency of simultaneous swaps relative to read operations. The implementation used a JDBC retry handler catching SQLException with SQLState 40001, waiting 100ms * 2^attempt, and re-executing the transaction. This eliminated overbooking incidents entirely, though p99 latency increased by 15ms during peak sales windows.
What is the precise difference between predicate locks in Serializable isolation and row locks in Repeatable Read?
Repeatable Read prevents non-repeatable reads by locking rows actually returned by a query, but it does not prevent phantom reads—new rows inserted by other transactions that would satisfy the query's WHERE clause. Serializable isolation uses predicate locks that lock the search range itself, preventing any insertion that would match the query predicate, even in rows that did not exist when the query executed. Candidates frequently conflate these, wrongly believing that Repeatable Read prevents phantom reads or that Serializable only locks existing rows.
How does the serialization graph testing algorithm determine which transaction to abort when a cycle is detected?
PostgreSQL uses a "first committer wins" strategy combined with dangerous structure detection. When a rw-conflict (read-write dependency) forms between concurrent transactions, the system tracks whether this edge completes a cycle in the serialization graph. The transaction that completes the cycle is aborted with SQLSTATE 40001. The choice is deterministic based on the graph structure rather than transaction age, favoring the abortion of transactions whose rollback is least expensive or latest in the detected cycle. Understanding that this is a preventive abort (preventing an invalid history) rather than a deadlock (waiting for locks) is essential for proper error handling.
Why might SELECT FOR UPDATE fail to prevent serialization failures in scenarios where Serializable isolation detects a conflict?
SELECT FOR UPDATE acquires ROW SHARE locks only on rows that exist at the moment of execution. In check-then-act patterns where the initial query returns zero rows (e.g., checking for zero available seats), FOR UPDATE acquires no locks whatsoever, allowing another transaction to insert the conflicting row. Serializable isolation detects this as a predicate conflict because the "zero rows" result constitutes a valid read set that was invalidated by the concurrent insert. Candidates often incorrectly assume FOR UPDATE provides comprehensive protection, not realizing it offers no defense against phantom inserts when the predicate initially matches nothing.