Concurrent Transactions and Isolation
Isolation is the "I" in ACID and addresses the question: what happens when multiple transactions execute at the same time? Ideally, each transaction should behave as if it were the only one running — its intermediate states should be invisible to others, and the final result should be equivalent to some serial (one-at-a-time) execution. This ideal is called serializability.
Why Isolation is Hard
In practice, achieving full serializability is expensive. It requires either locking data for the duration of each transaction (reducing concurrency) or detecting conflicts and aborting transactions that violate serial ordering (wasting work). Most databases therefore offer weaker isolation levels that trade correctness for performance. Understanding the anomalies that arise under weak isolation is essential for writing correct applications.
Anomaly: Dirty Read
A dirty read occurs when Transaction B reads data that Transaction A has written but not yet committed. If A later rolls back, B has acted on data that never officially existed. This is the most dangerous anomaly because it violates the atomicity boundary — B can see A's partial, uncommitted state. Dirty reads are only possible at the weakest isolation level (READ UNCOMMITTED).
Anomaly: Non-Repeatable Read
A non-repeatable read occurs when Transaction B reads the same row twice and gets different values because Transaction A committed a modification between the two reads. The data B read the first time is no longer there. This anomaly is allowed under READ COMMITTED but prevented by REPEATABLE READ and higher levels.
Anomaly: Phantom Read
A phantom read occurs when Transaction B executes the same range query twice and gets different sets of rows because Transaction A inserted or deleted rows matching the query's predicate between the two executions. Unlike a non-repeatable read (which affects existing rows), a phantom involves new rows appearing (or existing rows disappearing) from a result set.
Anomaly: Write Skew
Write skew is a subtle anomaly that occurs when two transactions read an overlapping set of data, make disjoint updates based on what they read, and the combined result violates a constraint that neither transaction violated individually. For example, two doctors check that at least one doctor is on-call, each sees two doctors on-call, and each removes themselves — leaving zero on-call. Write skew is not prevented by snapshot isolation; only full serializability catches it.
The Cost of Full Isolation
Full serializability can be implemented via two-phase locking (2PL), which acquires locks on all accessed data and holds them until commit, or via serializable snapshot isolation (SSI), which detects dangerous patterns of read-write dependencies. Both approaches reduce throughput compared to weaker levels. This is why understanding your application's tolerance for anomalies is critical — you should choose the weakest level that still guarantees correctness for your workload.
Isolation Anomalies in Action
Here are concrete scenarios showing each anomaly:
Dirty Read:
T1: UPDATE accounts SET balance = 0 WHERE id = 1; -- was 1000
T2: SELECT balance FROM accounts WHERE id = 1; -- reads 0 (uncommitted!)
T1: ROLLBACK; -- balance is back to 1000
T2: -- acted on balance=0, which never truly existed
Non-Repeatable Read:
T1: SELECT balance FROM accounts WHERE id = 1; -- reads 1000
T2: UPDATE accounts SET balance = 500 WHERE id = 1;
T2: COMMIT;
T1: SELECT balance FROM accounts WHERE id = 1; -- reads 500 (different!)
Phantom Read:
T1: SELECT COUNT(*) FROM employees WHERE dept = 'eng'; -- returns 5
T2: INSERT INTO employees (name, dept) VALUES ('Alice', 'eng');
T2: COMMIT;
T1: SELECT COUNT(*) FROM employees WHERE dept = 'eng'; -- returns 6 (phantom!)
Write Skew:
-- Constraint: at least 1 doctor must be on-call
-- Currently: Dr. A and Dr. B are both on-call
T1: SELECT COUNT(*) FROM doctors WHERE on_call = true; -- returns 2
T2: SELECT COUNT(*) FROM doctors WHERE on_call = true; -- returns 2
T1: UPDATE doctors SET on_call = false WHERE name = 'A'; -- still 1 on-call (B)
T2: UPDATE doctors SET on_call = false WHERE name = 'B'; -- still 1 on-call (A)
T1: COMMIT;
T2: COMMIT;
-- Result: 0 doctors on-call! Constraint violated.
Each anomaly reveals progressively more subtle ways concurrent transactions can interfere. Only serializability prevents all of them.
Market Order: Write Skew in the Wild
A classic real-world scenario where isolation matters: limit order books and inventory.
Scenario: A trading platform has 10 shares of AAPL left. Two users simultaneously click "Buy 10 shares" — each sees "10 available" and submits an order. Under snapshot isolation (PostgreSQL REPEATABLE READ), both transactions read inventory = 10, both pass the check inventory >= 10, both execute UPDATE inventory SET qty = qty - 10, and both commit. Result: -10 shares (oversold). The constraint "never sell more than you have" is violated.
-- Table: inventory(symbol, qty)
-- AAPL has 10 shares
-- User A (T1): -- User B (T2):
BEGIN; BEGIN;
SELECT qty FROM inventory SELECT qty FROM inventory
WHERE symbol = 'AAPL'; WHERE symbol = 'AAPL';
-- sees 10 (snapshot) -- sees 10 (snapshot)
UPDATE inventory SET qty = 0 UPDATE inventory SET qty = 0
WHERE symbol = 'AAPL'; WHERE symbol = 'AAPL';
-- qty = 10 - 10 = 0 -- qty = 10 - 10 = 0 (lost update!)
COMMIT; COMMIT;
-- Oversold: both sold 10, only 10 existed
Why this is write skew: Both transactions read the same row (overlapping read), made disjoint updates (each decremented "their" view of qty), and the combined result violates the invariant qty >= 0. Snapshot isolation does not detect this — each transaction's snapshot showed 10, so each believed the sale was valid.
Fix: Use SELECT ... FOR UPDATE to lock the row, or run at SERIALIZABLE so the database detects the read-write dependency and aborts one transaction.