transactions & locking
Isolation Levels
Trade off concurrency anomalies vs throughput by choosing how much isolation each transaction gets.
Perfect isolation — every transaction behaving as if it ran completely alone — is expensive, because it requires serialising work that could otherwise overlap. Isolation levels let you dial back that guarantee in exchange for concurrency, accepting specific, well-defined anomalies in return for throughput. Choosing a level is choosing which anomalies your application can tolerate.
A shared spreadsheet with different view modes. "Read Committed" — you see changes as people save. "Repeatable Read" — you see a snapshot from when you opened the file. "Serializable" — like editing offline; the system reconciles at the end.
Key Concepts
1
The SQL standard defines four levels against three anomalies. READ UNCOMMITTED permits dirty reads — seeing another transaction's uncommitted, possibly-rolled-back changes — and is almost never appropriate. READ COMMITTED (the default in PostgreSQL and Oracle) prevents dirty reads but still allows non-repeatable reads: re-reading the same row within a transaction can return a different value if another transaction committed an update in between. REPEATABLE READ (MySQL/InnoDB's default) additionally guarantees that rows you have read won't change, but the standard still permits phantom reads — new rows appearing in a range you queried earlier. SERIALIZABLE forbids all three, making transactions behave as if executed one at a time, at the cost of more locking or more aborts. Many modern databases implement these with multi-version concurrency control (MVCC), where readers see a consistent snapshot and never block writers, rather than with read locks.
READ UNCOMMITTEDREAD COMMITTEDREPEATABLE READSERIALIZABLE
2
The practical framing interviewers look for is matching the level to the workload: READ COMMITTED is a sensible default for typical web applications; step up to REPEATABLE READ or SERIALIZABLE for financial or inventory logic where read-then-write decisions must be stable. Higher isolation reduces anomalies but increases lock contention and the chance of deadlocks or serialization failures that the application must retry. Knowing the three anomalies and which level eliminates each — dirty, non-repeatable, phantom — is the core fact to have ready.
READ COMMITTEDREPEATABLE READSERIALIZABLE