Transaction isolation levels and their tradeoffs

WHAT THIS TESTS Whether you understand isolation as a tunable tradeoff between protecting against concurrency anomalies and maximizing throughput.

WHY IT EXISTS When many transactions run concurrently, they can interfere, producing anomalies like dirty reads (seeing uncommitted data), non-repeatable reads (a row changing between two reads in one transaction), and phantom reads (new rows appearing for a repeated range query). Isolation levels let you choose which of these are tolerable.

A GOOD ANSWER COVERS The SQL standard defines Read Uncommitted, Read Committed, Repeatable Read, and Serializable, in increasing strictness. Read Committed, a common default, guarantees you never read uncommitted data but permits non-repeatable and phantom reads; it allows high concurrency because it holds minimal read locks or uses snapshots. Serializable is the strongest: the system guarantees the outcome equals some serial order of the transactions, preventing all those anomalies, but it does so by holding more locks or aborting transactions that would violate serializability, which reduces concurrency and can raise retry rates. Choosing a level is choosing how much consistency you need versus how much concurrent throughput you are willing to sacrifice. Picking the wrong level either corrupts logic, too weak, or throttles the system, too strong.

COMMON WRONG ANSWERS Thinking Serializable has no cost, believing higher isolation always means slower in every engine without nuance, or ignoring that the default varies by database.

LIKELY FOLLOW-UPS Which anomalies each level allows; how MVCC implements snapshot isolation; handling serialization-failure retries; why some apps need Serializable only for specific transactions.

ONE CONCRETE EXAMPLE A banking transfer that reads a balance, checks a limit, and debits must run at Serializable (or use explicit locking), or two concurrent transfers could each read the same balance and both succeed, overdrawing the account. A product-listing read, by contrast, runs fine at Read Committed, trading perfect repeatability for far higher concurrency.