Isolation Levels & Concurrency Interview Questions & Answers

Transaction isolation controls how and when the changes made by one transaction become visible to other concurrent transactions. Higher isolation prevents more anomalies but increases contention; lower isolation is faster but allows more concurrency bugs.

SQL defines four standard isolation levels ranked from weakest to strongest: READ UNCOMMITTED → READ COMMITTED → REPEATABLE READ → SERIALIZABLE.

-- Set isolation level for the current transaction (Postgres)
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- Or set a session default (MySQL)
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

Rule of thumb: most applications are fine with READ COMMITTED (the Postgres default). Upgrade to REPEATABLE READ or SERIALIZABLE only when your application logic requires a consistent snapshot across multiple reads in the same transaction.

The SQL standard defines three anomalies that can occur when transactions run concurrently:

1. Dirty read — reading uncommitted changes from another transaction. If that transaction rolls back, you read data that never existed.
2. Non-repeatable read — reading the same row twice in the same transaction and getting different values because another transaction committed an update between the two reads.
3. Phantom read — running the same range query twice and getting different sets of rows because another transaction inserted or deleted rows between the two reads.

-- Dirty read example (requires READ UNCOMMITTED)
Tx A: UPDATE products SET price = 999 WHERE id = 1  (not committed)
Tx B: SELECT price FROM products WHERE id = 1  → 999  (dirty!)
Tx A: ROLLBACK
-- Tx B acted on a price of 999 that never permanently existed.

Rule of thumb: map each anomaly to the isolation level that prevents it: READ COMMITTED prevents dirty reads; REPEATABLE READ also prevents non-repeatable reads; SERIALIZABLE also prevents phantoms.

READ UNCOMMITTED is the lowest isolation level. Transactions can read uncommitted ("dirty") changes from other transactions. This means you can read data that another transaction later rolls back — data that was never permanently committed.

-- SQL Server: allow dirty reads
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT * FROM orders WHERE status = 'pending';
-- May return rows that are being modified by another transaction
-- and might disappear if that transaction rolls back.

It is rarely appropriate. One accepted use case: very approximate counts or estimates on a large table where absolute accuracy is not required and locking overhead matters more than precision.

In Postgres, READ UNCOMMITTED is mapped to READ COMMITTED internally — Postgres's MVCC architecture never allows dirty reads regardless of the isolation level set.

Rule of thumb: never use READ UNCOMMITTED for any business-logic query. If you need a rough count on a large table, use pg_class.reltuples in Postgres instead.

READ COMMITTED is the default in Postgres and Oracle. Each statement within a transaction sees only rows that were committed before that statement started. This prevents dirty reads.

What can still go wrong:

• Non-repeatable reads — two SELECTs in the same transaction can return different values for the same row if another transaction commits between them.
• Phantom reads — a range query can return different row counts if another transaction inserts/deletes rows between queries.

-- Non-repeatable read under READ COMMITTED:
-- Tx A (READ COMMITTED):
BEGIN;
  SELECT balance FROM accounts WHERE id = 1;  -- → 500
  -- Tx B commits: UPDATE accounts SET balance = 0 WHERE id = 1;
  SELECT balance FROM accounts WHERE id = 1;  -- → 0 (different!)
COMMIT;

Rule of thumb: READ COMMITTED is correct for most OLTP workloads where each query is self-contained. If your transaction reads a value and then uses it in a later write, consider REPEATABLE READ to prevent the value from changing between the read and the write.

REPEATABLE READ ensures that if a transaction reads a row, it will see the same values for that row on every subsequent read within the same transaction — even if another transaction commits updates to that row in between. This prevents both dirty reads and non-repeatable reads.

In Postgres, REPEATABLE READ uses a snapshot taken at the start of the transaction, so the transaction sees a consistent view of all data as it was when it began. Postgres also prevents phantom reads under this level (stronger than the SQL standard requires).

In MySQL (InnoDB), REPEATABLE READ is the default and uses a consistent read snapshot for SELECTs, but phantom rows can still appear in locking reads (SELECT FOR UPDATE).

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
  SELECT balance FROM accounts WHERE id = 1;  -- → 500
  -- Another transaction commits: UPDATE accounts SET balance = 0 WHERE id = 1
  SELECT balance FROM accounts WHERE id = 1;  -- → still 500 (snapshot)
COMMIT;

Rule of thumb: use REPEATABLE READ when a transaction reads the same data multiple times and the business logic requires it to be consistent across those reads (e.g., computing a report in multiple steps).

SERIALIZABLE is the strongest isolation level. It guarantees that the result of concurrent transactions is equivalent to running them one after another in some serial order — as if there were no concurrency at all.

Postgres implements this via Serializable Snapshot Isolation (SSI), which tracks read/write dependencies and aborts transactions that would create a cycle (a non-serializable schedule). It avoids broad locking but can abort transactions that need to be retried.

-- Classic serialization anomaly (write skew) — prevented by SERIALIZABLE:
-- Two doctors both check "at least one doctor is on call" and both
-- decide to go off call — ending with zero doctors on call.

-- Under SERIALIZABLE, one of the two transactions is aborted and must retry.
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
  SELECT COUNT(*) FROM doctors WHERE on_call = TRUE;  -- → 2
  UPDATE doctors SET on_call = FALSE WHERE id = 42;
COMMIT; -- may fail with serialization failure → retry

Rule of thumb: use SERIALIZABLE for financial ledgers, inventory management, or any domain where write skew would produce incorrect results. Build retry logic for SQLSTATE 40001 (serialization failure) into the application.

MVCC (Multi-Version Concurrency Control) allows readers and writers to operate concurrently without blocking each other by keeping multiple versions of each row (old and new) in the storage engine.

• A reader sees the version of each row that was committed before its transaction (or query) started — it never waits for a writer.
• A writer creates a new row version alongside the old one. Other readers still see the old version until the new one is committed.

-- Timeline (Postgres MVCC):
t=1: INSERT INTO t VALUES (1, 'old')  -- row version v1
t=2: Tx A begins (snapshot = v1)
t=3: Tx B: UPDATE t SET val='new' WHERE id=1  -- creates v2
t=4: Tx B: COMMIT
t=5: Tx A: SELECT * FROM t  -- still sees v1 (its snapshot)
t=6: Tx A: COMMIT
t=7: VACUUM reclaims v1 (no transaction needs it anymore)

The downside of MVCC is dead row accumulation — old versions must be cleaned up by VACUUM in Postgres. A long-running transaction prevents VACUUM from reclaiming any versions created after its snapshot.

Rule of thumb: understand that Postgres reads never block writes and writes never block reads — this is MVCC in action. Long-running transactions are the enemy of MVCC health because they pin old row versions in storage.

Write skew is a concurrency anomaly where two transactions each read an overlapping set of rows, make a decision based on what they read, and then each write to a different row — producing a state that neither transaction would have allowed if it had run alone.

-- Invariant: at least one doctor must be on call at all times.
-- Both Tx A and Tx B read: 2 doctors on call → each decides to go off call.

-- Tx A:
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
  SELECT COUNT(*) FROM on_call WHERE shift_id = 1;  -- → 2, safe to go off
  UPDATE on_call SET doctor_id = NULL WHERE doctor_id = 101 AND shift_id = 1;
COMMIT;

-- Tx B (concurrent):
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
  SELECT COUNT(*) FROM on_call WHERE shift_id = 1;  -- → 2, safe to go off
  UPDATE on_call SET doctor_id = NULL WHERE doctor_id = 202 AND shift_id = 1;
COMMIT;

-- Result: 0 doctors on call — invariant violated.
-- Under SERIALIZABLE, one transaction is aborted and must retry.

REPEATABLE READ does NOT prevent write skew because each transaction writes to a different row. Only SERIALIZABLE (SSI) detects the rw-dependency cycle and prevents it.

Rule of thumb: write skew is subtle and hard to spot in code reviews. Audit any transaction that reads a set of rows and then writes based on an aggregate of that set — it is a write-skew candidate.

A lost update occurs when two transactions both read a value, compute a new value based on it, and then both write back — the second write overwrites the first writer's change, effectively losing it.

-- Both sessions read stock = 10
-- Session A: stock = 10 - 1 = 9  → UPDATE inventory SET stock = 9 ...
-- Session B: stock = 10 - 1 = 9  → UPDATE inventory SET stock = 9 ...
-- Result: stock = 9 instead of 8. One sale is lost.

-- Fix 1: atomic UPDATE (no read-then-write race)
UPDATE inventory SET stock = stock - 1 WHERE product_id = 42 AND stock > 0;

-- Fix 2: SELECT FOR UPDATE (pessimistic lock)
BEGIN;
  SELECT stock FROM inventory WHERE product_id = 42 FOR UPDATE;
  UPDATE inventory SET stock = stock - 1 WHERE product_id = 42;
COMMIT;

-- Fix 3: optimistic locking with a version column
UPDATE inventory SET stock = stock - 1, version = version + 1
WHERE product_id = 42 AND version = 7;
-- 0 rows affected → conflict → retry

Rule of thumb: the safest fix is an atomic UPDATE col = col - delta (the database computes the new value from the current one, no race window). Use SELECT FOR UPDATE when the read-compute-write logic is too complex to express in a single UPDATE.

A phantom read occurs when a transaction executes the same range query twice and gets different rows because another transaction inserted or deleted qualifying rows between the two reads.

-- Tx A (REPEATABLE READ in standard SQL, not Postgres):
BEGIN;
  SELECT COUNT(*) FROM bookings WHERE room_id = 5 AND date = '2026-07-01';
  -- → 0 (room is free)
  -- Tx B inserts a booking for room 5, date 2026-07-01 and commits
  SELECT COUNT(*) FROM bookings WHERE room_id = 5 AND date = '2026-07-01';
  -- → 1 (phantom row appeared!)
COMMIT;

• READ COMMITTED: phantoms possible.
• REPEATABLE READ (standard SQL): phantoms still possible for inserts; prevented for updates on existing rows. Postgres's MVCC snapshot prevents phantoms completely at this level.
• SERIALIZABLE: prevents all phantoms.

Rule of thumb: if your application logic checks "does row X exist before inserting it," use SERIALIZABLE or an explicit lock (SELECT FOR UPDATE / FOR SHARE) to prevent phantom inserts from racing with your check.

Gap locks and next-key locks are MySQL InnoDB mechanisms that prevent phantom reads under REPEATABLE READ by locking ranges of index space, not just existing rows.

• Gap lock: locks the gap between two index values — prevents inserts into that range by other transactions.
• Next-key lock: a gap lock plus the index record at the upper boundary. InnoDB uses next-key locks by default under REPEATABLE READ.

-- Under REPEATABLE READ in MySQL, this SELECT FOR UPDATE
-- locks not just the rows where age BETWEEN 20 AND 30,
-- but also the gaps so no new rows in that range can be inserted.
SELECT * FROM users WHERE age BETWEEN 20 AND 30 FOR UPDATE;

Gap locks can cause unexpected lock contention: inserting a value in a range scanned by another transaction will block even if the inserted row does not match the other transaction's WHERE clause exactly.

Rule of thumb: if you see unexpected INSERT waits in MySQL, check whether a concurrent transaction's range scan holds a gap lock covering your insert position. Upgrading to READ COMMITTED disables gap locks if you don't need phantom prevention.

Database	Default isolation level	MVCC?
Postgres	READ COMMITTED	Yes — reads never block writes
MySQL InnoDB	REPEATABLE READ	Yes — consistent read snapshots
SQL Server	READ COMMITTED	Optional (READ_COMMITTED_SNAPSHOT)
Oracle	READ COMMITTED	Yes

-- Check current isolation level
-- Postgres:
SHOW transaction_isolation;

-- MySQL:
SELECT @@transaction_isolation;

-- SQL Server:
SELECT transaction_isolation_level
FROM   sys.dm_exec_sessions
WHERE  session_id = @@SPID;

SQL Server has READ_COMMITTED_SNAPSHOT (RCSI) — an opt-in mode that gives READ COMMITTED MVCC-style behaviour (readers do not block writers), similar to Postgres's default.

Rule of thumb: know your database's default before assuming behaviour. Code written for Postgres (READ COMMITTED) may behave differently when ported to MySQL (REPEATABLE READ) and vice versa.

When the database aborts a SERIALIZABLE (or REPEATABLE READ in MySQL) transaction due to a conflict, it raises SQLSTATE 40001 (serialization failure). The correct response is to retry the entire transaction from the beginning — not just the failed statement.

# Python + psycopg2 example
import psycopg2
from psycopg2 import errors
import time

MAX_RETRIES = 5

def transfer(conn, from_id, to_id, amount):
    for attempt in range(MAX_RETRIES):
        try:
            with conn.cursor() as cur:
                conn.autocommit = False
                cur.execute("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE")
                cur.execute("UPDATE accounts SET balance = balance - %s WHERE id = %s",
                            (amount, from_id))
                cur.execute("UPDATE accounts SET balance = balance + %s WHERE id = %s",
                            (amount, to_id))
                conn.commit()
                return  # success
        except errors.SerializationFailure:
            conn.rollback()
            time.sleep(0.05 * (2 ** attempt))  # exponential back-off
    raise RuntimeError("Transaction failed after max retries")

Rule of thumb: serialization failures are expected and normal under SERIALIZABLE — design every transaction that uses this level with a retry loop and exponential back-off. Never surface the raw database error to the end user.

Snapshot isolation (SI) gives each transaction a consistent snapshot of the database as it was at transaction start. Reads always see committed data from that snapshot, and writes conflict only if two transactions write to the same row (first-committer-wins). This prevents dirty reads, non-repeatable reads, and most phantom reads.

The key difference from SERIALIZABLE: SI still allows write skew — two transactions can each read a set of rows and write to different rows based on that read, producing a state that neither transaction would have permitted alone (see write-skew question).

Isolation guarantees comparison:
┌─────────────────────┬──────┬──────┬─────────┬──────────────┐
│                     │ R.U. │ R.C. │ Rep.Rd. │ Serializable │
├─────────────────────┼──────┼──────┼─────────┼──────────────┤
│ Dirty read          │  ✗   │  ✓   │   ✓     │      ✓       │
│ Non-repeatable read │  ✗   │  ✗   │   ✓     │      ✓       │
│ Phantom read        │  ✗   │  ✗   │  ✓*     │      ✓       │
│ Write skew          │  ✗   │  ✗   │   ✗     │      ✓       │
└─────────────────────┴──────┴──────┴─────────┴──────────────┘
(* Postgres REPEATABLE READ prevents phantoms via MVCC snapshot)

Rule of thumb: "snapshot isolation" is what most databases actually implement when you ask for REPEATABLE READ. It is safe for the vast majority of workloads. Upgrade to SERIALIZABLE only when write skew is a real risk in your domain.

Databases use a hierarchy of locks with compatibility rules that determine which locks can be held simultaneously by different transactions.

Common lock modes (Postgres naming):

Mode	Abbr	Conflicts with
Access Share	AS	Access Exclusive only
Row Share	RS	Exclusive, Access Exclusive
Row Exclusive	RX	Share, Share Row Exclusive, Exclusive, Access Exclusive
Share	S	Row Exclusive, Share Row Exclusive, Exclusive, Access Exclusive
Exclusive	X	Everything except Access Share
Access Exclusive	AX	Everything

-- SELECT acquires Access Share (compatible with almost everything)
SELECT * FROM orders;

-- INSERT/UPDATE/DELETE acquire Row Exclusive
UPDATE orders SET status = 'shipped' WHERE id = 1;

-- ALTER TABLE requires Access Exclusive — blocks ALL other operations
ALTER TABLE orders ADD COLUMN notes TEXT;

-- Check current locks
SELECT relation::regclass, mode, granted
FROM   pg_locks
WHERE  relation IS NOT NULL;

Rule of thumb: ALTER TABLE takes an Access Exclusive lock and blocks every read and write on the table for its duration. On large tables, use CREATE INDEX CONCURRENTLY and multi-step migrations to minimise lock time.