SQL Transaction Isolation and Observable Anomalies

Isolation levels describe what one transaction can observe of other transactions running at the same time. The SQL standard defines them by which anomalies they forbid, and textbooks list those in a table. Interviews go one level deeper: can you describe an anomaly with a concrete example, say which level prevents it in the database you actually use, and explain what the application must do in exchange.

That last part matters because PostgreSQL’s implementation differs from the textbook table in useful ways. Every example below ran on PostgreSQL 16.15 with two psql sessions started a fraction of a second apart; the waiting transaction slept for one second between its steps to make the overlap reliable.

The levels PostgreSQL actually has

You can ask for four levels: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ and SERIALIZABLE. PostgreSQL implements three. Read Uncommitted behaves exactly like Read Committed, because MVCC means a transaction never needs to read another’s uncommitted row versions, so dirty reads never happen in PostgreSQL at any level.

The default is Read Committed. You set the level per transaction:

BEGIN ISOLATION LEVEL REPEATABLE READ;
-- ...
COMMIT;

or per session with SET default_transaction_isolation = 'serializable'.

Read Committed: each statement sees the latest commits

Session A reads an account balance twice, one second apart. In between, session B sets the balance to 40 and inserts a second account, committing both.

-- session A
BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT balance AS first_read  FROM accounts WHERE id = 1;   -- 100
SELECT pg_sleep(1);                                          -- B commits here
SELECT balance AS second_read FROM accounts WHERE id = 1;   -- 40
SELECT count(*) AS rows_seen  FROM accounts;                -- 2
COMMIT;

Within one transaction, the same query returned 100 and then 40 (a non-repeatable read), and a count picked up a row that didn’t exist when the transaction started (a phantom). Read Committed takes a fresh snapshot at the start of each statement. Each individual statement is consistent; the transaction as a whole is not.

For a lot of web requests that’s fine: one statement per decision, short transactions. It becomes a bug when code reads in one statement and acts on that read in a later one. A report that sums revenue in one query and lists line items in a second can show a total that doesn’t match its own lines.

Repeatable Read: one snapshot for the whole transaction

The same experiment at Repeatable Read:

 first_read  | 100
 second_read | 100
 rows_seen   | 1

The transaction’s snapshot is taken at its first statement, and every later statement uses it. Session B’s change and its new row are invisible until A finishes. Note that PostgreSQL’s Repeatable Read prevents phantoms too, which the SQL standard allows at this level. That’s a frequent interview gotcha: the textbook table says phantoms are possible at Repeatable Read, and in PostgreSQL they aren’t.

Now the price. What happens if a Repeatable Read transaction tries to update a row someone else changed after its snapshot?

-- session A
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1;            -- 100
SELECT pg_sleep(1);                                    -- B: UPDATE ... balance - 50, commits
UPDATE accounts SET balance = balance - 30 WHERE id = 1;

ERROR:  could not serialize access due to concurrent update

A’s snapshot says the balance is 100, but the real current value is 50. Applying - 30 to either would be wrong in some way, so PostgreSQL refuses and aborts A (SQLSTATE 40001). The final balance is 50: B’s change stands, and A must retry from the beginning. At Read Committed, the same UPDATE would have waited for B’s row lock, re-read the row and applied - 30 to 50, leaving 20. That re-read is why single-statement updates are safe at Read Committed; the row locks guide shows it in detail.

Write skew: the anomaly that slips past snapshots

The classic example: a hospital requires at least one doctor on call per shift. Alice and Bob are both on call for 1 November and both want to go off call. Each transaction checks the rule, then updates its own row:

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) AS on_call_now
FROM on_call
WHERE shift = '2026-11-01' AND on_call;     -- 2, so it's safe to leave
SELECT pg_sleep(1);
UPDATE on_call SET on_call = false WHERE doctor = 'alice';   -- bob's session updates 'bob'
COMMIT;

Both sessions saw a count of 2, both committed, and the table ended up:

 doctor | on_call
--------+---------
 alice  | f
 bob    | f

No one is on call. Neither transaction modified a row the other modified, so there was no write conflict for Repeatable Read to catch. Each transaction was correct against its snapshot; together they broke the rule. This is write skew, and it’s the anomaly that separates snapshot isolation from true serializability.

The same two scripts at Serializable:

ERROR:  could not serialize access due to read/write dependencies among transactions
DETAIL:  Reason code: Canceled on identification as a pivot, during write.
HINT:  The transaction might succeed if retried.

 doctor | on_call
--------+---------
 alice  | f
 bob    | t

Alice’s transaction committed; Bob’s was aborted. When Bob retries, his count returns 1 and the application refuses to take him off call. PostgreSQL’s Serializable Snapshot Isolation tracks which rows each transaction read (with predicate locks, visible in pg_locks as SIReadLock) and aborts one transaction when the pattern of reads and writes couldn’t have happened in any serial order.

What higher isolation costs

Retries are mandatory. At Repeatable Read and Serializable, 40001 errors are a normal outcome under contention, not a bug. The application must retry the whole transaction, including the reads, because the decision was based on data that’s now out of date. Retrying only the failing statement is wrong. Most teams write a small wrapper: run the transaction function, catch 40001 (and 40P01 for deadlocks), back off briefly, try again up to a few times. The deadlock retry guide shows that shape.

Serializable has overhead. Predicate tracking uses memory and CPU, and you’ll see some false-positive aborts. It works best with short transactions; declaring read-only transactions as READ ONLY helps PostgreSQL avoid unnecessary aborts. Long-running Serializable transactions increase the abort rate for everyone.

Long snapshots hold back cleanup. A Repeatable Read report that runs for an hour keeps its snapshot open, and vacuum can’t remove row versions it might still need. See MVCC and vacuum.

Choosing a level in practice

• Read Committed for most OLTP code, combined with the right tool for each invariant: atomic UPDATE ... SET x = x - 1 WHERE x >= 1, SELECT ... FOR UPDATE when code must decide, and constraints (UNIQUE, CHECK, exclusion) for rules the database can enforce directly.
• Repeatable Read for reports, exports and backups that need a consistent view across several queries. pg_dump uses a repeatable-read-style snapshot for exactly this reason.
• Serializable when correctness depends on rules spanning several rows that aren’t easy to lock or constrain, like the on-call rule, booking limits or balance rules across accounts, and you can afford retries. It’s often simpler and safer than hand-placed locks, because you don’t need to identify every row that should have been locked.

The on-call rule also has a Read Committed fix: lock the rows you read (SELECT ... FROM on_call WHERE shift = ... FOR UPDATE), so the second transaction waits and then sees the first one’s change. That works because the rule’s rows exist to be locked. A rule like “no more than five bookings per day” is harder, because the row that would break it doesn’t exist yet; that’s where Serializable or a counter row with a lock earns its keep.

Interview practice: the gift card double spend

A gift card has 50.00 left. The redemption endpoint runs at Repeatable Read: it reads the card’s balance and the sum of pending holds from a separate holds table, checks that the purchase fits, and inserts a new hold. Two purchases of 40.00 arrive at the same moment and both succeed. Why didn’t Repeatable Read prevent this, and what would you change?

Answer

Both transactions read the same snapshot: balance 50, holds 0. Each inserted a new row into holds. They didn’t update the same row, so there was no write conflict for Repeatable Read to detect. It’s write skew: a rule across rows (balance minus all holds must stay non-negative) checked against a snapshot that each transaction made stale for the other.

There are three reasonable fixes. Run the transaction at Serializable and retry on 40001: PostgreSQL will see that each transaction read the holds rows the other was inserting into and abort one, and the retry will see 40.00 already held. Or lock the gift card row with SELECT ... FROM gift_cards WHERE id = $1 FOR UPDATE before reading holds, so all redemptions for one card serialise on that lock. That works at Read Committed, since the second transaction re-runs its reads after the wait. Or keep a held_total column on the card and do the check in one atomic statement, UPDATE gift_cards SET held_total = held_total + 40 WHERE id = $1 AND balance - held_total >= 40, inserting the hold only if it updated a row. I’d pick the row lock or the atomic update for a hot path like checkout, and add a retry loop regardless, since any of them can hit deadlocks or serialization failures under load.

Keep going

• SQL and PostgreSQL interview questions
• SQL and PostgreSQL MCQs
• PostgreSQL row locks and concurrent updates
• SQL constraints as concurrent safety nets
• Official docs: Transaction isolation