Read Committed, the Workhorse

The everyday default

Read Committed makes one promise. Every read sees only data committed at the moment that read ran, so you never see a dirty read.

It is already your default, because it already is in Postgres, Oracle and SQL Server. Most production transactions in the world run here, and most were written by people who never chose it.

The unit is one statement

The unit matters, because that is where the remaining anomalies come from. Each individual statement gets its own snapshot, taken when that statement starts. The statement, not the transaction.

So two queries in one transaction get two snapshots. A commit landing between them changes your view halfway through, which is the non-repeatable read. An aggregate you computed early can disagree with rows you read later.

Lost updates are fully available here too. Read a value, compute the new one in your application, write it back, and you can overwrite somebody else's committed change without any error at all.

For most everyday work none of that matters. A request that loads a user, updates a row and commits inside 20 milliseconds has a tiny window and usually no invariant spanning several reads. That is why the default is defensible: cheap, no read locks, few aborts.

The skill here is spotting the patterns that need reinforcing. Turn read-modify-write cycles into a single update statement with the arithmetic in SQL. Or lock the row across the gap. Or use a version column that fails the write if the row moved underneath you. Send reports needing one consistent view across several queries up to Repeatable Read. Leave everything else on the workhorse.

// Read committed: you never see uncommitted work, and you can
// still see two different answers to the same question.
tx.begin();
int a = db.queryInt("SELECT stock FROM items WHERE id = 7");   // 1

//   ... meanwhile another transaction sells the last one and commits ...

int b = db.queryInt("SELECT stock FROM items WHERE id = 7");   // 0
// a != b, and nothing is broken. This is the level doing its job.

// Repeatable read would have returned 1 both times, because it
// answers from the snapshot taken when the transaction began.
tx.commit();

Worked example

A loyalty points service at an airline runs on Postgres at Read Committed. Two check-in kiosks process the same family booking concurrently, and both transactions run SELECT points FROM accounts WHERE id = 88, both see 1,000, both add 200 in Java, and both UPDATE points = 1200. One committed update, the 200 points from the other kiosk, is gone; the family should have 1,400. No error was raised anywhere, which is the signature of a lost update. Dana, the engineer on the ticket, replaces the pattern with UPDATE accounts SET points = points + 200 WHERE id = 88. The database now serializes the two increments on the row lock and the balance lands at 1,400 every time. Isolation level unchanged; the code just stopped leaving the arithmetic in a race window.