What does the READ COMMITTED transaction isolation level guarantee, and which concurrency anomalies can still occur under it?
answer
- No dirty reads — and nothing more
- Committed as of statement start, not transaction start
- Non-repeatable + phantom + read skew still legal
- Lost update is the application's problem
- Default level in most engines
basics
~20 sIt guarantees one thing: you never read data written by a transaction that has not committed — no dirty reads. Everything weaker still happens: non-repeatable reads, phantoms, read skew across statements, and lost updates in read-modify-write code.
solid answer
~50 sREAD COMMITTED forbids **dirty reads**: every value a statement returns was committed at the time that statement started. It also forbids dirty writes — you cannot overwrite another transaction's uncommitted row; your write blocks until that transaction ends. That is the whole guarantee. Within one transaction, reads are **not stable**: re-running the same query can return a changed value (non-repeatable read) or a different set of rows (phantom), because each statement sees a fresh view of committed data. Two queries against related tables can therefore observe mutually inconsistent states (read skew) — for example a transfer counted in the destination table but not yet in the source read. It also gives you no protection for read-modify-write logic in the application: two transactions can both read balance 100, both write 90, and one update is silently lost. So READ COMMITTED buys cheap, low-blocking reads, and leaves every multi-statement invariant to you — atomic single statements, explicit row locks, version columns, or a higher isolation level.
go deeper
Be able to state the guarantee (no dirty reads) and name at least non-repeatable read and phantom read as still possible.
Explain the per-statement view that produces the guarantee, and give a concrete read-skew or lost-update scenario from application code.
Discuss the practical mitigations — single-statement writes, explicit row locks, version columns, declarative constraints — and when you would escalate the level instead.
Frame it as the throughput/complexity default: cheap non-blocking reads with invariant enforcement pushed to schema constraints and targeted locking, escalating isolation only on the few paths that genuinely need it.
## What isolation levels actually specify When many transactions run concurrently, the engine may interleave their reads and writes in ways that produce results no serial (strictly one-at-a-time) execution could produce. The SQL standard does not describe *how* an engine prevents that; it describes which **anomalies** each named level is forbidden to exhibit. The classic anomalies are dirty read, non-repeatable read, and phantom read, on top of dirty write, which every level forbids. ## The one guarantee READ COMMITTED forbids exactly one anomaly: the **dirty read**. A dirty read is observing a row version written by a transaction that has not yet committed — data that may be rolled back and thus never have logically existed. Under READ COMMITTED, every value your statement returns was committed by somebody at the instant your statement began. It also forbids the **dirty write**: you cannot replace a row version that an uncommitted transaction has already modified. Your write blocks until the other transaction commits or rolls back. That rule is universal across levels, but it matters here because it is the only place READ COMMITTED makes you wait. ## How engines deliver it Two implementation families: - **MVCC engines** keep multiple versions of each row. Each *statement* takes a fresh snapshot of committed state when it starts, and reads the newest version that was committed before that instant. Readers never block writers and writers never block readers. - **Lock-based engines** take a shared read lock on each row as they read it and release it immediately after, instead of holding it to end-of-transaction. Holding it only for the read is enough to keep you off uncommitted data, and not enough to keep the data stable afterwards. Both give you "committed data as of now". Neither gives you "the same data as a moment ago". ## What is still legal, with concrete shapes - **Non-repeatable read** — you `SELECT price FROM item WHERE id=7` and get 100; another transaction updates it to 120 and commits; you re-run the same query in the same transaction and get 120. - **Phantom read** — you count orders `WHERE status='NEW'` and get 5; a concurrent insert commits; you count again and get 6. - **Read skew (inconsistent multi-statement view)** — you read account A (balance 100), a transfer of 50 from A to B commits, then you read account B. You see the money in B and also still in A, so your computed total is wrong. No single statement lied; the pair of statements straddled a commit. - **Lost update** — two transactions each read `qty = 10`, each compute `10 - 1` in application code, each write 9. One decrement vanishes. Nothing in READ COMMITTED forbids this, and in most engines nothing even reports an error. - **Write skew** — two transactions each check "at least one doctor is still on call", each see two, each take themselves off call. Both checks were true when made; the combined result violates the invariant. ## Why anyone chooses it Because it is cheap and it rarely blocks. Reads take no lasting locks and produce no serialization failures, so throughput is high and application code does not need retry loops for isolation conflicts. The exposure is real but bounded: any *single* statement is internally consistent, so a single aggregate query, a single multi-row `UPDATE ... WHERE`, or a single `INSERT ... SELECT` still sees one coherent state. This is why READ COMMITTED is the shipped default in most relational engines. ## Living with it The practical discipline is: **push each invariant into one statement, or lock explicitly, or move up a level.** - Express read-modify-write as a single statement (`SET qty = qty - 1 WHERE qty > 0`) so the engine, not your process, does the arithmetic on the current row. - When you must round-trip through the application, take an explicit row lock on read (`SELECT ... FOR UPDATE`) or use an optimistic version column and re-check the version in the `WHERE` of the update, retrying on zero rows affected. - Rely on declarative constraints — unique keys, foreign keys, check constraints — which are enforced by the engine at write time regardless of isolation level, and therefore catch races that your reads missed. - Escalate the isolation level only where a genuine multi-statement invariant exists, and accept the retry/blocking cost there rather than globally. ## The mental model to keep READ COMMITTED means *"nothing I read was a lie at the moment I read it."* It does not mean *"the world stood still while my transaction ran."* Every bug people blame on READ COMMITTED comes from assuming the second sentence.
- If READ COMMITTED allows non-repeatable reads, is a single aggregate query such as SUM over a whole table still trustworthy?Yes, within itself. A single statement runs against one consistent view of committed data, so the SUM reflects a state the database really was in at that statement's start. What you cannot do is compare that SUM to a second query's result and assume both describe the same instant — the second statement gets its own view.
- Does READ COMMITTED prevent lost updates?No. Two transactions can each read a value, compute a new one in application code, and write it back, with the second write silently overwriting the first; both writes were of committed data, so nothing in the level is violated. You prevent it with a single self-referencing UPDATE, an explicit SELECT ... FOR UPDATE, or an optimistic version check that fails the second writer.
- How is READ COMMITTED different from just taking a snapshot at transaction start?A transaction-start snapshot would freeze the whole transaction's view, making reads repeatable and hiding all concurrent commits. READ COMMITTED deliberately re-snapshots per statement, so it sees newer commits as the transaction proceeds — that freshness is the point, and the instability is its direct cost.
It is like reading a live news site rather than a printed newspaper: nothing you read is a rumour, every headline was real when it was published — but refresh and the front page has changed, so two facts you noted a minute apart may never have been true at the same moment.
saying these in an interview costs you the question
- Saying READ COMMITTED gives a consistent view for the whole transaction — the view is per statement
- Claiming it prevents lost updates or write skew
- Confusing 'no dirty reads' with 'no anomalies'
- Assuming readers block writers or writers block readers under it in an MVCC engine
- Believing two queries in the same READ COMMITTED transaction are guaranteed mutually consistent