1. Which isolation anomaly occurs when a transaction reads the same row twice and gets different values because another transaction modified and committed the row in between?
Interview Questions
1 questions
Cheatsheet
Download-ready reference
Cheatsheet
ACID Atomicity, Consistency, Isolation, Durability.
Dirty Read Reading uncommitted changes (Read Uncommitted level).
Non-Repeatable Read Reading changed committed rows (Read Committed level).
Phantom Read Reading new inserted rows in a range (Repeatable Read level).
Isolation Levels:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Locking:
SELECT * FROM users WHERE id = 1 FOR UPDATE; -- Acquires write lock on read
To balance concurrency performance and correctness, SQL standards define four Isolation Levels, each defending against specific anomalies:
Isolation Level
Dirty Read
Non-repeatable Read
Phantom Read
Read Uncommitted
Allowed
Allowed
Allowed
Read Committed
Prevented
Allowed
Allowed
Repeatable Read
Prevented
Prevented
Allowed
Serializable
Read Committed (Default in Postgres/SQL Server)
This level prevents Dirty Reads. However, it still permits Non-repeatable Reads, where a transaction reads a row, another transaction modifies and commits it, and the first transaction reads it again, getting a different value:
BEGIN;
SELECT balance FROM accounts
WHERE id = 1; // Returns 5000
T1 reads the balance. It successfully reads the committed value of $5000.
Transaction 2
queued
// no operations this step
T2 has not started yet.
Database State
Database Balance
$5000
T1 starts and reads the balance. In READ COMMITTED level, T1 can only read committed data, preventing dirty reads.
Repeatable Read (Default in MySQL InnoDB)
This level prevents both Dirty and Non-repeatable Reads. However, it permits Phantom Reads, where a transaction queries a range of rows, another transaction inserts a new matching row and commits, and the first transaction re-runs the query, seeing a 'phantom' row:
Phantom Read Anomaly (Isolation: REPEATABLE READ)
step 1 / 3
Transaction 1
EXECUTING...
BEGIN;
SELECT COUNT(*) FROM users
WHERE active = true; // Returns 10
T1 queries active users and receives a count of 10 records.
Transaction 2
queued
// no operations this step
T2 is waiting.
Database State
Active Users count
10
T1 reads the active users range. REPEATABLE READ level locks existing individual records so they cannot change during T1's execution.
Serializable
The highest level. Transactions execute as if they ran sequentially (one after another), completely isolating database states. It utilizes range locks (or optimistic concurrency checks), which decreases concurrency throughput and can trigger serialization failures.
1. Assuming Read Committed prevents all concurrent anomalies#
Read Committed is the default in many databases, but it does not prevent Non-repeatable or Phantom reads. If you write calculation queries (like inventory updates) across multiple steps, concurrency can modify the records in between, leading to calculation drift.
When running in SERIALIZABLE mode, PostgreSQL will fail transactions with a 40001 serialization error if it detects dependency loops. Your backend application must be written to catch these errors and retry the entire transaction block.
Keep Transactions Small: Minimize the time write locks are held to avoid database blockages and deadlocks.
Use SELECT FOR UPDATE: Under Read Committed, execute SELECT ... FOR UPDATE to acquire write locks on read rows immediately, preventing concurrent updates from modifying them before commit.
Handle Deadlocks: Write transaction code blocks to execute writes in the same table order across your entire backend to prevent deadlocks (where T1 locks table A and waits for B, while T2 locks B and waits for A).