Isolation governs what one transaction can see of another transaction's uncommitted changes while both run concurrently. Without isolation, two transactions running at the same time can interfere with each other in several specific, named ways:
| Anomaly | What happens |
|---|---|
| Dirty read | Transaction A reads a row that transaction B has changed but not yet committed. If B rolls back, A read data that never really existed. |
| Non-repeatable read | Transaction A reads the same row twice, and gets different values, because B committed a change to that row in between A's two reads. |
| Phantom read | Transaction A re-runs the same filtered query twice, and gets a different set of rows, because B inserted or deleted a row matching that filter in between. |
Isolation isn't all-or-nothing — it's a spectrum of isolation levels, each one preventing more anomalies at the cost of more locking (and therefore less concurrency):
| Level | Prevents | Still allows |
|---|---|---|
| Read Uncommitted | Nothing | Dirty reads, non-repeatable reads, phantom reads |
| Read Committed | Dirty reads | Non-repeatable reads, phantom reads |
| Repeatable Read | Dirty reads, non-repeatable reads | Phantom reads |
| Serializable | All of the above | Behaves as if every transaction ran one at a time, in some order |
Most databases default to Read Committed as the practical middle ground — dirty reads are almost never acceptable, but full serializability's locking overhead is more than most workloads need. Picking a stronger isolation level than a given piece of code actually requires is a common, quiet source of production slowdowns under load: every extra lock held is another point of contention for concurrent transactions to wait on.
-- A transfer that must see a consistent snapshot of both balances,
-- immune to a concurrent transaction changing them mid-flight.
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION;
SELECT balance FROM accounts WHERE id = 'A'; -- read once
-- ... application logic decides the transfer is valid ...
UPDATE accounts SET balance = balance - 100 WHERE id = 'A';
UPDATE accounts SET balance = balance + 100 WHERE id = 'B';
COMMIT;
Two specific bugs worth knowing by name because they show up in interviews constantly:
SELECT ... FOR UPDATE), or optimistic concurrency (a version/rowversion column checked on write, so the second writer's UPDATE matches zero rows and the application knows to retry).