In this lesson, you will learn how PostgreSQL moves beyond basic Snapshot Isolation to guarantee true serializability using Serializable Snapshot Isolation (SSI).
The Limits of Snapshot Isolation
Standard Snapshot Isolation (SI) allows a transaction to see a consistent "snapshot" of the database as it existed when the transaction began. While this prevents "dirty reads," it is vulnerable to write skew. Write skew happens when two transactions read overlapping data, make independent decisions based on that data, and then perform conflicting writes—resulting in a state that could never have occurred if the transactions had run one after another (serially).
Enter SSI (Serializable Snapshot Isolation)
PostgreSQL implements SSI to bridge this gap. Instead of blocking other transactions with aggressive locks (which would kill performance), PostgreSQL allows transactions to run concurrently, but it tracks dependencies between them.
The system monitors two specific types of "interesting" events:
- SIREAD locks: These are lightweight, predicate-level markers that record that a transaction has read a specific row (or a range of rows).
- Conflict detection: When a transaction tries to write to a row, the system checks if any other concurrent transaction has an
SIREADlock on that data.
If PostgreSQL detects a cycle of dependencies—a pattern of reads and writes that suggests the transactions are "conflicting" in a way that violates serializability—it will automatically abort one of the transactions with a 40001 serialization failure.
Example: The "Bank Balance" Problem
Imagine a rule that a bank account balance must never drop below zero.
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- Transaction A reads balance ($100)
SELECT balance FROM accounts WHERE id = 1;
-- Transaction B reads balance ($100)
SELECT balance FROM accounts WHERE id = 1;
-- Transaction A deducts $70 (New balance $30)
UPDATE accounts SET balance = 30 WHERE id = 1;
-- Transaction B deducts $60 (New balance $40)
UPDATE accounts SET balance = 40 WHERE id = 1;
COMMIT;
In standard Snapshot Isolation, both might succeed, resulting in a balance of 30 depending on the timing), even though $130 was withdrawn. Under Serializable MVCC, the database detects that Transaction B read the state Transaction A was modifying, identifies the conflict, and forces one transaction to fail so the application can retry with the correct data.
Key Takeaway: Serializable MVCC protects data integrity by tracking read/write dependencies and aborting transactions that threaten the serial order of operations.