Postgres isolation levels don’t just dictate how transactions see each other’s data; they fundamentally change the guarantees you get about the consistency of your own transaction’s view of the database.
Let’s watch this unfold. We’ll use two client sessions, client1 and client2, interacting with a simple accounts table.
-- Setup (run by client1)
CREATE TABLE accounts (id INT PRIMARY KEY, balance DECIMAL);
INSERT INTO accounts (id, balance) VALUES (1, 100.00);
Now, let’s set client2 to READ COMMITTED and client1 to SERIALIZABLE.
Scenario 1: READ COMMITTED (client2)
-- client2 session (READ COMMITTED)
BEGIN;
SELECT balance FROM accounts WHERE id = 1;
-- Output: 100.00
-- At this point, client1 does this:
-- BEGIN;
-- UPDATE accounts SET balance = balance - 10.00 WHERE id = 1;
-- COMMIT;
-- client2 continues
SELECT balance FROM accounts WHERE id = 1;
-- Output: 90.00
COMMIT;
In READ COMMITTED, client2 saw the update from client1 immediately. This is because each statement within a transaction sees a snapshot of the database as it was when that specific statement began. Changes committed by other transactions before a statement starts are visible; changes committed after a statement starts are not. This is the default in Postgres.
Scenario 2: REPEATABLE READ (client2)
Let’s reset the table and try REPEATABLE READ for client2.
-- Reset (run by client1)
TRUNCATE accounts;
INSERT INTO accounts (id, balance) VALUES (1, 100.00);
-- client2 session (REPEATABLE READ)
BEGIN;
SELECT balance FROM accounts WHERE id = 1;
-- Output: 100.00
-- At this point, client1 does this:
-- BEGIN;
-- UPDATE accounts SET balance = balance - 10.00 WHERE id = 1;
-- COMMIT;
-- client2 continues
SELECT balance FROM accounts WHERE id = 1;
-- Output: 100.00
COMMIT;
With REPEATABLE READ, client2 didn’t see client1’s update. Here, each transaction sees a snapshot of the database as it was when the transaction began. All statements within the transaction see this same snapshot, even if other transactions commit changes in between. This prevents non-repeatable reads.
Scenario 3: SERIALIZABLE (client2)
Now, the most stringent level. Reset again.
-- Reset (run by client1)
TRUNCATE accounts;
INSERT INTO accounts (id, balance) VALUES (1, 100.00);
-- client1 session (SERIALIZABLE)
BEGIN;
UPDATE accounts SET balance = balance - 10.00 WHERE id = 1;
-- client2 session (SERIALIZABLE)
BEGIN;
SELECT balance FROM accounts WHERE id = 1;
-- Output: 100.00
-- client1 continues
COMMIT;
-- client2 continues
SELECT balance FROM accounts WHERE id = 1;
-- Output: 100.00
COMMIT;
Wait, that looks like REPEATABLE READ. But SERIALIZABLE is different. It prevents all anomalies, including phantom reads and write skews. Postgres enforces this by detecting if a transaction’s outcome could have been achieved by running its operations serially. If it detects a potential anomaly, it aborts one of the transactions with a serialization_failure error.
Let’s see a true SERIALIZABLE anomaly.
-- Reset (run by client1)
TRUNCATE accounts;
INSERT INTO accounts (id, balance) VALUES (1, 100.00);
-- client1 session (SERIALIZABLE)
BEGIN;
UPDATE accounts SET balance = balance - 10.00 WHERE id = 1;
-- client2 session (SERIALIZABLE)
BEGIN;
-- client1 commits first
COMMIT;
-- client2 now reads
SELECT balance FROM accounts WHERE id = 1;
-- Output: 90.00
-- client2 then tries to update
UPDATE accounts SET balance = balance + 5.00 WHERE id = 1;
-- COMMIT; -- This will likely fail with a serialization_failure
Here, client2’s UPDATE would fail because its initial read (which happened before client1 committed) was 100.00. But by the time client2 tries to UPDATE, the balance is 90.00. If client2 were to commit its UPDATE (adding 5.00), the final balance would be 95.00. However, if the transactions had run serially, client1 would have run, then client2. client1 would set it to 90.00, and then client2 would add 5.00 to that, resulting in 95.00. This specific scenario is a write skew. Postgres detects that the serial execution order client1 then client2 would produce a different result than client2 then client1 (if client2’s read happened after client1’s commit), and aborts client2.
The key takeaway is that SERIALIZABLE provides the strongest guarantee, but at the cost of potential transaction aborts. READ COMMITTED is the least strict, offering performance but allowing more anomalies. REPEATABLE READ falls in the middle.
Postgres uses a combination of Multi-Version Concurrency Control (MVCC) and predicate locking (for SERIALIZABLE) to achieve these isolation levels. For SERIALIZABLE, Postgres doesn’t just lock rows; it infers locks on the predicates (the WHERE clauses) of statements. If a transaction’s predicate could have returned different rows based on changes committed by another transaction, a serialization_failure is raised.
The default_transaction_isolation setting in postgresql.conf or ALTER SYSTEM controls the default level for new transactions. You can also set it per-session with SET SESSION TRANSACTION ISOLATION LEVEL ...;.
When working with SERIALIZABLE, robust retry logic is essential. Applications must be prepared to re-execute transactions that fail with serialization_failure.
The next frontier for understanding transaction isolation is exploring how to correctly implement retry logic for SERIALIZABLE transactions.