The Concert Ticket Disaster
Getting database isolation levels wrong can break your application in ways that have nothing to do with bugs.In 2014, a major ticketing platform accidentally sold 200 tickets for a 150-person venue. The problem was not a bug in the code - it was the wrong isolation level. Two hundred users hit 'Buy' within the same second. Each user's transaction checked if tickets remained, saw 'yes,' and purchased. By the time the database processed all the writes, it had sold 50 tickets that did not exist. Understanding transactions and isolation levels prevents exactly this kind of disaster.
What Is a Transaction?
A transaction is a group of operations that must succeed or fail as a unit. The database guarantees that partial failures cannot leave your data in a broken state. Transactions are governed by four guarantees known as ACID.
-- Bank transfer: debit + credit must be atomic
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 500 WHERE id = 'alice';
-- Server crashes here? BOTH lines are rolled back.
-- Alice keeps her $500. No money vanishes.
UPDATE accounts SET balance = balance + 500 WHERE id = 'bob';
COMMIT;
-- Only after COMMIT do both changes become permanent.
-- What happens on crash:
-- Before COMMIT: crash --> entire transaction rolled back (atomicity)
-- After COMMIT: crash --> data survives on disk (durability)
-- This is guaranteed by the Write-Ahead Log (WAL)The database guarantees that either:
every operation succeeds (COMMIT)
or every operation is undone (ROLLBACK)
Concurrency Anomalies Visualized
When multiple transactions run simultaneously without proper isolation, four types of anomalies can occur:
1. DIRTY READ (reading uncommitted data)
Transaction A Transaction B
---------------------- ----------------------
UPDATE balance = 0
WHERE id = 'alice'
SELECT balance
FROM accounts
WHERE id = 'alice'
--> reads $0 (WRONG!)
ROLLBACK (undo!)
(balance is still $500) (but B already used $0)
2. NON-REPEATABLE READ (data changes between reads)
Transaction A Transaction B
---------------------- ----------------------
SELECT price FROM products
WHERE id = 7
--> reads $29.99
UPDATE price = $39.99
WHERE id = 7
COMMIT
SELECT price FROM products
WHERE id = 7
--> reads $39.99 (different!!)
3. PHANTOM READ (new rows appear between queries)
Transaction A Transaction B
---------------------- ----------------------
SELECT COUNT(*) FROM orders
WHERE total > 100
--> returns 5
INSERT INTO orders
(total) VALUES (250)
COMMIT
SELECT COUNT(*) FROM orders
WHERE total > 100
--> returns 6 (phantom row!) The Four Isolation Levels
SQL defines four isolation levels, each preventing more anomalies at the cost of more coordination:
Isolation Level Dirty Non-Repeatable Phantom Performance
Read Read Read
----------------- ------ -------------- ------- -----------
Read Uncommitted YES YES YES Fastest
Read Committed no YES YES Fast (PG default)
Repeatable Read no no YES Moderate (MySQL default)
Serializable no no no Slowest
Key insight: each level adds more protection but more locking.
PostgreSQL default: Read Committed (good for most apps)
MySQL InnoDB default: Repeatable Read (slightly stricter)
For financial operations: use Serializable selectivelySetting Isolation in Practice
-- PostgreSQL: set per-transaction (not globally!)
-- Use the minimum level needed for each operation.
-- Normal read (default: Read Committed)
SELECT * FROM products WHERE id = 42;
-- Financial transfer (needs Serializable)
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT balance FROM accounts WHERE id = 'alice' FOR UPDATE;
-- FOR UPDATE locks the row - nobody else can modify it
UPDATE accounts SET balance = balance - 500 WHERE id = 'alice';
UPDATE accounts SET balance = balance + 500 WHERE id = 'bob';
COMMIT;
-- Inventory check (needs at least Repeatable Read)
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT quantity FROM inventory WHERE product_id = 7;
-- Guaranteed: this value won't change during our transaction
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 7;
COMMIT;Optimistic vs Pessimistic Locking
PESSIMISTIC LOCKING (lock first, then work):
BEGIN;
SELECT * FROM inventory WHERE id = 7 FOR UPDATE;
-- Row is now LOCKED. Other transactions wait.
-- Do work...
UPDATE inventory SET quantity = quantity - 1 WHERE id = 7;
COMMIT;
-- Lock released.
Pros: Simple, guarantees no conflicts
Cons: Transactions wait (deadlock risk), lower throughput
Use when: Conflicts are frequent (ticket sales, inventory)
OPTIMISTIC LOCKING (work first, check at end):
-- Add a version column to the table
SELECT quantity, version FROM inventory WHERE id = 7;
-- quantity = 10, version = 3
-- Do work... then try to update:
UPDATE inventory
SET quantity = 9, version = 4
WHERE id = 7 AND version = 3;
-- If version changed, 0 rows updated --> RETRY
-- If version matches, 1 row updated --> SUCCESS
Pros: No locks, high concurrency
Cons: Retries on conflict, wasted work
Use when: Conflicts are rare (user profile edits)Deadlocks: When Locks Go Wrong
A deadlock happens when two transactions each hold a lock the other needs:
Transaction A Transaction B
----------- -----------
LOCK row 1 LOCK row 2
wait for row 2... wait for row 1...
(DEADLOCK! Neither can proceed)
Solution: the database detects the deadlock and kills
one transaction (rolling it back). The other proceeds.
Prevention: always lock rows in the same order!
Good: lock user_id 1, then user_id 2 (both transactions)
Bad: A locks 1 then 2, B locks 2 then 1 (deadlock risk)Interview Tip
When discussing concurrent access in interviews, say: 'I would use Read Committed as my default isolation level because it prevents dirty reads without excessive locking. For operations where correctness is critical - like inventory deduction or financial transfers - I would upgrade to Serializable for just those transactions, using SELECT FOR UPDATE to lock the specific rows involved. I would also implement optimistic locking with version columns for scenarios where conflicts are rare, like user profile updates, to keep throughput high.'
Key Takeaway
Transactions group operations into atomic units. Isolation levels control how much concurrent transactions can see each other's work. Higher isolation prevents more anomalies but reduces throughput. Choose the lowest level that maintains correctness for each operation, and prefer optimistic locking when conflicts are rare.