Transactions, MVCC and isolation levels

A transaction groups several reads and writes into one unit. Databases promise certain guarantees for that unit, but the guarantees depend on the isolation level. The default level in many databases does not stop a common bug: two requests read the same balance, both subtract from it, and both write back. A customer can withdraw more than the account holds, and no error is raised. Every statement ran correctly. The bug is in how the transactions interleaved.

This section explains what goes wrong, how databases prevent it, and what your code should do. It builds on concurrency and synchronization, since a database is a shared system with its own locks, and on the write-ahead log from storage engines.

The big idea

A transaction gives you four properties, called ACID. Two of them come from the log. One depends on your rules. One is the knob you turn.

• Atomicity and durability come from the write-ahead log and fsync. Crashes roll back unfinished work, and commits survive restarts.
• Consistency means your invariants hold. The database helps only through constraints you declare.
• Isolation decides how much of another transaction's work you can see. This is where the bugs live.

Concepts in this section

Read them in this order.

Cross-cutting mistakes

• Trusting the default isolation level. Know what your database and your ORM actually do. An ORM may wrap every request in a transaction.
• Doing read-modify-write in application code. This is the most common source of lost updates. Use one atomic statement, a row lock, or a version check.
• Keeping transactions open. Long transactions block vacuum and hold locks. Keep them short.
• Retrying without a plan. Serializable and repeatable read can abort work. Retries need a cap, backoff, and code that is safe to run again.

From one node to many

Everything here assumes one database process, where one log and one lock manager see every transaction. Once data is split across services or shards, no single component sees it all. Atomic commit across machines needs a protocol such as two-phase commit. Long business processes use sagas, which undo finished steps with compensating actions. Systems such as Spanner and CockroachDB provide serializable transactions across machines by combining these protocols with consensus. Continue with distributed transactions and consistency.

At work

• Code review. Flag balance = row.balance - x; save(row), and suggest an atomic UPDATE, FOR UPDATE or a version check. Flag check-then-insert, and suggest a unique constraint.
• Operations. Alert on long-running and idle-in-transaction sessions, on dead tuple counts, and on transaction ID age.
• Interviews. "Two users book the last seat at the same time. What happens?" Walk through the race, fix it with a unique constraint or a conditional update, then mention isolation levels and retries.

Key takeaways

• Atomicity and durability come from the log. Consistency comes from constraints. Isolation is the tunable part.
• Postgres defaults to read committed, and MySQL to repeatable read. Neither default stops lost updates in application read-modify-write code.
• MVCC gives each transaction a snapshot using row versions. Long transactions stop vacuum from cleaning up.
• Prefer atomic statements, unique and exclusion constraints, and FOR UPDATE. Use serializable only with retries.

Further learning

Start with the anomalies page, then the isolation levels page. If you want the hardest case first, the write skew example there is the one to work through.

Martin Kleppmann's Designing Data-Intensive Applications covers the material in depth. Jepsen's notes on consistency models place these levels next to linearizability.

Jepsen: Consistency ModelsConsistencyjepsen.io Designing Data-Intensive Applications (Kleppmann)Designing Data-Intensive Applications (DDIA) — an O’Reilly book by Martin Kleppmann (The Wild Boar Book)dataintensive.net