Transactions and isolation levels

conceptThe concept

READ COMMITTED takes a new snapshot for every statement. It never shows uncommitted data, but two SELECTs in one transaction can see different results. If an UPDATE finds a row changed by a concurrent committed transaction, it re-checks the new version and continues.

REPEATABLE READ takes one snapshot at the first statement and keeps it, so the transaction sees a stable view, with no phantoms either (stronger than the SQL standard requires). If it tries to update a row that a concurrent transaction changed, it fails with could not serialize access due to concurrent update.

SERIALIZABLE adds Serializable Snapshot Isolation: it tracks read/write dependencies and aborts one transaction when the outcome could differ from some serial order, preventing anomalies such as write skew. Applications must retry transactions that fail with SQLSTATE 40001.

That is write skew: each transaction checked the rule on a snapshot and changed a different row. Only SERIALIZABLE, or explicit locking (SELECT ... FOR UPDATE), prevents it.

whyWhy it matters

Concurrency bugs pass every test and appear in production under load: double-booked seats, overdrawn balances, counters that lose increments. Knowing the guarantees tells you when the default is enough and when you need locks or SERIALIZABLE plus retries.

Use caseReal-world scenario

Transferring money between accounts uses READ COMMITTED with UPDATE ... SET balance = balance - $1, which is safe because the update re-reads the current value. An on-call scheduling rule across rows uses SERIALIZABLE with a retry loop.

Try itExamples

Run and edit these in the interactive lesson.

SHOW default_transaction_isolation;
BEGIN ISOLATION LEVEL REPEATABLE READ;
SHOW transaction_isolation;
SELECT pg_current_snapshot();
SELECT count(*) FROM orders WHERE status = 'pending';
ROLLBACK;

Two sessions both read stock 10 and both write 9: one sale is lost. Let the database do the arithmetic, or lock the row first.

BEGIN;
-- unsafe pattern: SELECT stock, compute in the app, UPDATE ... SET stock = 9
-- safe pattern 1: atomic update with a guard
UPDATE products SET stock = stock - 1 WHERE id = 3 AND stock >= 1 RETURNING stock;
-- safe pattern 2: lock the row, then decide
SELECT stock FROM products WHERE id = 3 FOR UPDATE;
ROLLBACK;

BEGIN;
UPDATE products SET price = price + 1 WHERE id = 5;
SAVEPOINT before_risky;
DELETE FROM reviews WHERE product_id = 5;
SELECT count(*) AS reviews_inside_savepoint FROM reviews WHERE product_id = 5;
ROLLBACK TO SAVEPOINT before_risky;
SELECT count(*) AS reviews_after_rollback_to FROM reviews WHERE product_id = 5;
ROLLBACK;

A PL/pgSQL EXCEPTION block runs as a subtransaction: the failed statement is undone and the outer transaction continues.

BEGIN;
DO $$
BEGIN
  INSERT INTO customers (name, email, city, country, created_at)
  VALUES ('Dup', (SELECT email FROM customers WHERE id = 1), 'X', 'UK', now());
EXCEPTION WHEN unique_violation THEN
  RAISE NOTICE 'duplicate email ignored: %', SQLERRM;
END $$;
SELECT 'transaction still usable' AS status;
ROLLBACK;

internalsUnder the hood

PostgreSQL never allows dirty reads; READ UNCOMMITTED behaves like READ COMMITTED. SERIALIZABLE uses predicate locks (SIReadLock in pg_locks) that never block, only detect dangerous structures. The playground has a single session, so the timelines above describe two sessions you would open in separate psql windows.

Avoid / preferDo this, not that

SELECT stock FROM products WHERE id = 7;   -- app computes stock - 1
UPDATE products SET stock = 41 WHERE id = 7;

UPDATE products SET stock = stock - 1
WHERE id = 7 AND stock >= 1
RETURNING stock;

SET default_transaction_isolation = 'serializable';
-- no retry logic anywhere

-- serializable plus a retry loop on SQLSTATE 40001

PitfallsCommon mistakes

• Reading a value, computing in the application, then writing it back under READ COMMITTED. Use an atomic UPDATE, SELECT ... FOR UPDATE, or a higher isolation level.
• Using SERIALIZABLE without a retry loop. Serialization failures are expected, not bugs.
• Catching exceptions in a PL/pgSQL loop for every row. Each EXCEPTION block is a subtransaction, and too many of them cause subtransaction overflow and performance problems.

LimitsWhen not to use it

Avoid SERIALIZABLE for long batch jobs that touch much of the database; they conflict with everything and retry endlessly. Use explicit locks or design the batch to be idempotent and chunked.

SecuritySecurity notes

Race conditions are security bugs: double-spending a gift card or redeeming a one-time code twice. Enforce such rules with atomic conditional updates or unique constraints, never with check-then-act in the application.

ChallengeTry it yourself

BEGIN;
UPDATE products SET stock = stock - 2
WHERE id = 7 AND stock >= 2
RETURNING id, stock;
ROLLBACK;

InterviewQuestions you should be able to answer

QuizCheck your understanding

4 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.

Go deeperOfficial documentation

• Transaction isolation
• SAVEPOINT