30-Second Glance
- Read committed = no dirty reads, no dirty writes; the default in PostgreSQL, Oracle, SQL Server — the weakest level you should ever run.
- Readers see only committed data; writers hold row-level exclusive locks until commit. Statement-level snapshots in Postgres, locking reads in InnoDB.
- Still allows non-repeatable reads, read skew, lost updates, and write skew — it is a floor, not a correctness story.
- Costs ~1 row lock per written row held to commit; readers never block writers (MVCC) in modern engines.
Guarantees and Mechanism
Read committed provides exactly two promises:
- No dirty reads — a transaction never sees uncommitted data from another transaction. Implemented via MVCC (Postgres keeps old row versions; a statement snapshot exposes only rows committed before the statement began) or via short read locks (older engines; modern InnoDB uses MVCC snapshots for plain SELECTs too).
- No dirty writes — two transactions never concurrently modify the same row; the second writer blocks on the row's exclusive lock until the first commits or aborts. Without this, interleaved
SET balance = balance - 100style writes could permanently interleave and corrupt even a single row.
Implementation shape: writers take a row-level exclusive lock at write time and hold it to commit/abort (this "hold to commit" is already half of Two-Phase Locking). Readers in MVCC engines take no locks for plain SELECTs — they read from the snapshot — so read/write throughput decouples nicely. SELECT ... FOR UPDATE opts a read into locking when you need it.
What Still Goes Wrong
Read committed re-reads fresh committed state per statement, so anything spanning two statements can observe change:
| Anomaly | Example | Why read committed allows it |
|---|---|---|
| Non-repeatable read | SELECT balance → 500, again → 300 (concurrent commit in between) |
Each statement takes a new snapshot |
| Read skew | See A's new balance but B's old balance after a transfer | Two reads straddle a concurrent commit |
| Lost update | Two read → +100 → write sessions; one increment vanishes |
Neither writer saw the other's uncommitted write (correctly), then overwrote it |
| Write skew | On-call rota: two doctors each see the other on call, both go off call | Predicates, not rows, conflict — row locks don't cover it (see Write Skew and Phantom Writes) |
Lost update deserves emphasis because juniors assume row locks prevent it — they don't. The lock serializes the writes but both transactions already read the old value; second committer silently overwrites the first. Fixes: atomic UPDATE ... SET x = x + 1, SELECT FOR UPDATE, optimistic version check (WHERE version = :v), or a stronger level (Snapshot Isolation detects it; serializable prevents it).
Concrete Numbers
| Quantity | Typical value | Why it matters |
|---|---|---|
| Default level | Postgres, Oracle, SQL Server = read committed; MySQL InnoDB = repeatable read | Know each engine's default cold — interviewers ask |
| Lock held | Row-X per written row until commit | Long transactions = lock pile-ups + deadlock risk |
| Reader blocking | None for plain SELECTs (MVCC) | Reads scale independently of writes at this level |
| Snapshot scope (Postgres) | Per statement | The precise reason re-reads can differ |
| Deadlock detection | ~1 s poll (InnoDB innodb_deadlock_detect) |
Cycle → youngest victim aborted with error 1213 |
Trade-offs
| Approach | Pros | Cons | Use when |
|---|---|---|---|
| Read committed | Max concurrency; no read locks; simple mental model; sane default | Non-repeatable reads, skew, lost update, write skew all possible | Bulk analytics on live DB, append-mostly reads, any workload where per-statement freshness beats repeatability |
Read committed + SELECT FOR UPDATE / atomic updates |
Closes lost update on hot rows cheaply | Manual — every critical read must opt in; deadlocks if lock order varies | Counters, balances, seat holds at moderate contention |
| Snapshot isolation (repeatable read) | Consistent snapshot per txn; auto lost-update detection | Write skew/phantoms remain; first-committer-wins aborts to handle | Default for correctness-sensitive OLTP — see Snapshot Isolation |
| Serializable | No anomalies | Throughput/abort cost | Money movements, scheduling — see Serializable Snapshot Isolation |
Interview One-Liners
- Q: What does read committed actually guarantee? A: Only: never read uncommitted data, never overwrite uncommitted data. Everything else (re-read stability, skew, lost update) is unprotected.
- Q: Why do two SELECTs in one txn return different rows? A: Snapshots are per-statement at this level, so a concurrent commit landing between them is visible to the second read.
- Q: Don't row locks prevent lost update? A: No — they serialize writes but both txns already read the stale value; the second write silently overwrites the first. Use atomic UPDATE, FOR UPDATE, or version checks.
- Q: Do plain SELECTs block writes at read committed? A: Not in MVCC engines (Postgres, InnoDB): readers use snapshots, writers use row locks — they don't contend.
- Q: When is read committed the right choice? A: Read-mostly/analytic queries on a live DB and low-stakes OLTP where per-statement freshness is fine and you handle hot-row updates explicitly.