Transaction Isolation Levels and Anomalies
The four SQL isolation levels, the anomalies each one allows (dirty read, non-repeatable read, phantom, lost update, write skew), and how PostgreSQL and MySQL actually behave.
Transaction isolation levels decide how much one transaction can see of others running at the same time. From weakest to strongest they are Read Uncommitted, Read Committed, Repeatable Read and Serializable, each removing more anomalies. The detail interviewers expect: real engines differ, and PostgreSQL's Repeatable Read is snapshot isolation, which still allows write skew.
Isolation levels decide how much one transaction can see of another running at the same time. From weakest to strongest the standard levels are Read Uncommitted, Read Committed, Repeatable Read and Serializable, and each stronger level removes more anomalies at a higher cost in throughput or retries.
- In the SQL standard, Read Uncommitted allows dirty reads, Read Committed prevents them, Repeatable Read also prevents non-repeatable reads, and Serializable also prevents phantoms.
- A phantom read occurs when the same query returns a different set of rows because another transaction inserted matching rows; preventing it needs range or predicate locks or a snapshot.
- A lost update happens when two transactions read the same value and both write a new one; fix it with SET n = n + 1, SELECT ... FOR UPDATE, or an optimistic version column.
- Write skew occurs when two transactions read overlapping data and write different rows that together break a rule; only Serializable isolation or explicit locks prevent it.
- PostgreSQL defaults to Read Committed and implements Repeatable Read as snapshot isolation, while MySQL InnoDB defaults to Repeatable Read.
- Serializable isolation does not run transactions one at a time; they can overlap, but the outcome must match some serial order, and aborted transactions must be retried.
01Why isolation is a dial and not a switch
If every transaction ran strictly one after another, there would be no concurrency problems and also no concurrency. Real systems run many at once and accept that some interleavings are visible to other transactions. Isolation levels name how much is allowed to leak.
The SQL standard defines the levels by which anomalies they permit. The anomalies are the thing to learn, so we go through them first.
02Dirty read and non-repeatable read
A dirty read is reading data another transaction has written but not committed. If that transaction then rolls back, you acted on data that never existed. Example: T1 sets a balance to 500 and has not committed. T2 reads 500 and sends a confirmation email. T1 rolls back. The email was wrong.
A non-repeatable read is when you read the same row twice in one transaction and get different values because another transaction committed a change in between. T1 reads a price of 100. T2 updates it to 120 and commits. T1 reads again and gets 120. If T1 was building a report that assumed a stable price, the report is inconsistent.
03Phantom read
A phantom is like a non-repeatable read, but for a set of rows rather than a single row. T1 runs select count(*) from orders where status = 'open' and gets 10. T2 inserts a new open order and commits. T1 runs the same query again and gets 11.
Locking the rows T1 read does not help, since the new row did not exist to lock. Preventing phantoms needs range or predicate locks, or a consistent snapshot.
04Lost update and write skew
Lost update: T1 and T2 both read a counter of 10, both compute 11 in application code and write it. The counter ends at 11, not 12. One increment was lost. Fixes: do the arithmetic in SQL (update t set n = n + 1), lock the row with select ... for update, or use an optimistic version column and retry if it changed.
Write skew is subtler. A hospital requires at least one doctor on call. Two doctors, Asha and Ravi, are both on call. Each opens a transaction, checks that at least two are on call (true), and takes themselves off. Both commit. Now nobody is on call. Neither transaction wrote the same row, so row-level conflict detection saw nothing. Only serializable isolation (or explicit locking of the checked rows) stops it.
05The four standard levels
In the SQL standard's definition:
Read Uncommitted: allows dirty reads, non-repeatable reads and phantoms.
Read Committed: no dirty reads. Still allows non-repeatable reads and phantoms.
Repeatable Read: no dirty or non-repeatable reads. The standard still allows phantoms.
Serializable: none of them. Results match some one-at-a-time order of the transactions.
Notice the standard does not mention lost update or write skew by name. Real databases differ on how they treat those, which is why the next section matters.
06What real databases do
PostgreSQL: the default is Read Committed. Asking for Read Uncommitted gives you Read Committed, because PostgreSQL never allows dirty reads. Repeatable Read is implemented as snapshot isolation, which also prevents phantoms (stronger than the standard) but still allows write skew. Serializable uses a technique called serializable snapshot isolation that detects dangerous patterns and aborts one transaction with a serialization failure, which your code must catch and retry.
MySQL with InnoDB: the default is Repeatable Read, using MVCC snapshots for plain reads and next-key locks for locking reads.
Oracle and SQL Server default to Read Committed. SQL Server can also use row versioning (snapshot isolation) if you enable it.
So the practical rules: know your database's default, and do not assume that "Repeatable Read" means the same thing on two systems. Check the docs. Under Serializable, plan for retries.
07Choosing a level
Read Committed is a sensible default for most applications. It is cheap and avoids the worst surprise, dirty reads. Many bugs at this level are lost updates in read-modify-write code, which you fix in the query or with a row lock, not by raising the isolation level everywhere.
Use Repeatable Read or Serializable for transactions that read a set of data and then make a decision based on it, like the on-call example, or a report that must see one consistent snapshot. Be ready to retry on failure.
Keep transactions short at every level. The longer one stays open, the more conflicts it causes.
Q: Which level prevents dirty reads?
A: Read Committed and above.
Q: What is MVCC?
A: Multi-version concurrency control. Writers create new row versions, so readers see a consistent older version without blocking.
Q: Does Serializable mean transactions run one at a time?
A: No. They can overlap, but the outcome must match some serial order.
08Common interview questions
Q: Which anomalies does each standard level prevent?
A: Read Uncommitted prevents none. Read Committed prevents dirty reads. Repeatable Read also prevents non-repeatable reads. Serializable also prevents phantoms. Real engines differ: PostgreSQL's Repeatable Read is snapshot isolation, which still allows write skew.
Q: What is a lost update and how do you prevent it?
A: Two transactions read the same value, both compute a new one, and the second write overwrites the first. Fix it with an atomic update (SET x = x + 1), SELECT ... FOR UPDATE, or optimistic version checks, rather than a higher isolation level everywhere.
Q: What is write skew?
A: Two transactions read overlapping data, each writes a different row based on what it read, and together they break a rule neither broke alone. Snapshot isolation allows it; Serializable prevents it.
Q: Why is Read Committed so often the default?
A: It avoids dirty reads at low cost and with little blocking. Higher levels add retries and lock or abort overhead, so you raise the level only for specific operations that need it.