The concurrency problem
Databases handle many concurrent connections. Without isolation controls, two transactions reading and writing the same rows at the same time can interfere in subtle ways — one transaction sees a value that another is about to roll back, or a count changes between two reads in the same transaction.
SQL defines four isolation levels, each preventing a different class of anomaly.
The anomalies each level prevents
| Anomaly | What it is |
|---|---|
| Dirty read | Reading another transaction's uncommitted changes |
| Non-repeatable read | Re-reading a row and getting a different value because another transaction committed an update between reads |
| Phantom read | Re-running a query and getting different rows because another transaction committed an INSERT or DELETE |
| Lost update | Two transactions both read a value, both modify it, and one overwrites the other's change |
The four isolation levels
| Level | Dirty read | Non-repeatable read | Phantom read |
|---|---|---|---|
READ UNCOMMITTED | Possible | Possible | Possible |
READ COMMITTED | Prevented | Possible | Possible |
REPEATABLE READ | Prevented | Prevented | Possible* |
SERIALIZABLE | Prevented | Prevented | Prevented |
*Postgres's REPEATABLE READ also prevents phantom reads via MVCC.
-- Set isolation level for the current transaction (Postgres / SQL Server)
BEGIN ISOLATION LEVEL READ COMMITTED;
-- or
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- MySQL
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
READ COMMITTED — the practical default
Postgres and SQL Server default to READ COMMITTED. Each statement inside the
transaction sees only rows committed before that statement started.
-- Transaction A: checking inventory before placing an order
BEGIN;
SELECT stock_qty FROM product_inventory WHERE product_id = 42;
-- Returns 5
-- Meanwhile, Transaction B commits: stock_qty = 3 (someone else bought 2 units)
SELECT stock_qty FROM product_inventory WHERE product_id = 42;
-- Returns 3 — different value in the same transaction (non-repeatable read)
COMMIT;
This is acceptable for most operations but dangerous for "read-then-write" patterns where both reads must see the same value.
REPEATABLE READ — stable snapshots
Each transaction works from a snapshot taken at its first read. Subsequent reads of the same row return the same value, regardless of what other transactions commit.
-- Transaction A: generating a financial report
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT SUM(total_amount) FROM orders WHERE created_at::DATE = '2026-06-20';
-- Returns 12,450.00
-- Transaction B commits new orders during this time
SELECT COUNT(*) FROM orders WHERE created_at::DATE = '2026-06-20';
-- Still reflects the same snapshot — consistent report
COMMIT;
MySQL's default isolation level is REPEATABLE READ.
SERIALIZABLE — full correctness, higher cost
Transactions execute as if they ran one at a time (serially). The database detects any read/write conflicts and aborts one of the transactions — the application must retry.
-- Booking system: prevent double-booking a conference room
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT COUNT(*) FROM bookings
WHERE room_id = 7
AND start_time < '2026-06-25 15:00'
AND end_time > '2026-06-25 14:00';
-- Returns 0 — room appears free
-- Concurrent transaction B runs the same check and also gets 0
-- Both insert a booking:
INSERT INTO bookings (room_id, start_time, end_time, user_id)
VALUES (7, '2026-06-25 14:00', '2026-06-25 15:00', 44);
COMMIT;
-- One of the two transactions gets:
-- ERROR: could not serialize access due to concurrent update
-- The application retries the failed transaction
MVCC — how Postgres implements isolation
Postgres uses Multi-Version Concurrency Control (MVCC). Instead of locking
rows for reads, every write creates a new row version with transaction ID
timestamps (xmin, xmax). Readers get a consistent snapshot by ignoring row
versions committed after their snapshot began.
This means:
- Readers never block writers (no read locks).
- Writers never block readers (old row versions remain readable).
- Only writer-writer conflicts require locks.
The cost: old row versions accumulate (called dead tuples) until VACUUM
cleans them up. Tables with heavy UPDATE traffic need regular vacuuming.
SELECT FOR UPDATE — explicit row locking
When a "read then write" pattern needs protection against concurrent updates, lock the row at read time:
BEGIN;
-- Lock the inventory row so no other transaction can update it until we commit
SELECT stock_qty
FROM product_inventory
WHERE product_id = 42
FOR UPDATE; -- acquires an exclusive row lock
-- Now safely decrement
UPDATE product_inventory
SET stock_qty = stock_qty - 2
WHERE product_id = 42;
COMMIT; -- lock released
FOR UPDATE SKIP LOCKED is useful for job queues — workers pick up tasks
without waiting for each other:
-- Worker picks the next unclaimed job, skipping any locked by other workers
SELECT id, payload FROM job_queue
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;
Recap
Default to READ COMMITTED for most OLTP work. Upgrade to REPEATABLE READ
for long-running reports or batch jobs where consistent snapshots matter. Use
SERIALIZABLE only for logic that requires complete correctness (inventory,
seat booking, financial ledgers) — and always implement retry logic, because
serialization failures are expected and normal. Use SELECT FOR UPDATE for
explicit row locks in read-then-write patterns. Understand MVCC: readers and
writers do not block each other in Postgres, but dead tuples accumulate and must
be cleaned up by VACUUM.