Part 2 of 7 · 2 min read
Read committed
The basic level: no dirty reads and no dirty writes.
Think of two clerks editing the same paper form. If one clerk reads the other's half-written notes, or scribbles over them before they are final, the form ends in a mess. A basic rule helps: read only finished work, and do not write over unfinished work.
The book "Designing Data-Intensive Applications" says the most basic level is read committed, with two guarantees.
Read committed
Read committed guarantees that a transaction reads only committed data (no dirty reads) and overwrites only committed data (no dirty writes).
Example: user 1 sets x to 3 but has not committed. User 2 still reads the old value, 2, until user 1 commits.
No dirty reads. A dirty read is seeing another transaction's uncommitted write. Reasons to prevent it: a transaction that changes several objects would otherwise be seen half done (the unread-email counter of module 1), and if a transaction aborts, other transactions would have read data that never existed.
No dirty writes. A dirty write is overwriting an uncommitted value. The book's example: Alice and Bob both try to buy the same used car. A purchase needs two writes: the listing is marked with the buyer, and an invoice goes to the buyer. With dirty writes, Bob wins the listing and Alice wins the invoice, so the car is Bob's but the bill goes to Alice. Read committed stops this by making the second write wait.
How it is built:
- Dirty writes are prevented with row-level locks. A transaction that wants to change an object takes a lock and holds it until commit or abort. Another writer waits.
- Dirty reads: using the same lock for readers would make read-only queries wait for any long write transaction, which hurts response times. So most databases keep both the old committed value and the new uncommitted value. Other transactions read the old one until the commit.
Read committed is very popular. It is the default in Oracle 11g, PostgreSQL, SQL Server 2012, MemSQL and many others.
A worked example. Account A has 500 and B has 500. Transaction T1 moves 100 from A to B. At the time T2 reads, T1 has changed A to 400 and not yet B.
- With dirty reads allowed: T2 sees A = 400 and B = 500, a total of 900. The 100 seems lost.
- With read committed: T2 sees the old A = 500 and B = 500 until T1 commits. Total 1,000.
Read committed does not prevent the counter race of module 1. Two increments from 42: both read 42, both write 43. The second write comes after the first commit, so it is not a dirty write, yet one increment is lost. Section 3 deals with that.
Check yourself:
- What is the difference between a dirty read and a dirty write?
- Why do most databases not use read locks to prevent dirty reads?