Transactions & Isolation Levels

ACID, precisely

• Atomicity — all of a transaction's writes apply, or none do. This is about abortability, not concurrency.
• Consistency — the database moves from one valid state to another. This one is largely the application's job: the database only enforces the constraints you declared. It's in the acronym mostly because ACID is a nicer word than AID.
• Isolation — concurrent transactions don't interfere. This is the interesting one, and it's a dial, not a boolean.
• Durability — committed data survives a crash. In practice: WAL plus fsync, and on a replicated system, plus how many replicas acknowledged.

The anomalies, in increasing subtlety

Dirty read — you read data another transaction wrote but hasn't committed. If it rolls back, you acted on data that never existed.

Dirty write — you overwrite data another uncommitted transaction wrote. Essentially every database prevents this by default with row locks.

Non-repeatable read — you read a row twice in one transaction and get different values, because someone committed in between.

Phantom read — you run the same query twice and get different rows, because someone inserted a row matching your predicate.

Lost update — two transactions read-modify-write the same value; one silently overwrites the other. The hook, in its simplest form. Fixed by atomic operations (SET balance = balance - 80), explicit locking (SELECT … FOR UPDATE), or compare-and-set.

Write skew — the subtle one. Two transactions read an overlapping set of rows, each makes a decision based on what it read, and each writes to a different row. No write conflicts, so nothing collides — but the combined result violates an invariant that held for each individually.

The isolation levels

¹ Snapshot isolation detects and aborts conflicting concurrent updates to the same row in Postgres; MySQL's Repeatable Read does not, and will silently lose the update unless you lock.

Two warnings about this table:

The names are not portable. The SQL standard defined levels in terms of anomalies prevented, not mechanisms, so vendors implement them differently. Postgres's "Repeatable Read" is snapshot isolation and prevents phantoms; MySQL InnoDB's "Repeatable Read" uses gap locks and behaves differently again; Oracle's "Serializable" is actually snapshot isolation. Always check what your database means.

Defaults vary and matter. Postgres and Oracle default to Read Committed. MySQL InnoDB defaults to Repeatable Read. SQL Server defaults to Read Committed. If you've only used one, your intuitions won't transfer.

MVCC: how snapshots work

Modern databases don't block readers with locks. They keep multiple versions of each row.

In Postgres each row carries xmin (the transaction that created it) and xmax (the one that deleted it). A transaction sees a row if xmin is committed and visible to its snapshot, and xmax is not. An UPDATE writes a new row version and marks the old one dead.

Consequences worth knowing:

• Readers never block writers, and writers never block readers. This is MVCC's payoff.
• Dead row versions accumulate and must be reclaimed by VACUUM. Neglect it and you get table bloat — the disk fills with rows nobody can see.
• Long-running transactions are expensive. An open transaction pins a snapshot, so vacuum cannot remove any version newer than it. One forgotten idle-in-transaction connection can bloat an entire database. This is a top-tier real-world Postgres incident.

Getting to serializable

Three mechanisms:

Actual serial execution — run transactions one at a time on a single thread. Sounds absurd; works well when transactions are short and data fits in memory (Redis, VoltDB).

Two-phase locking (2PL) — pessimistic. Acquire shared locks to read and exclusive locks to write, hold everything until commit. Correct, and it produces low concurrency plus deadlocks that the database resolves by killing a victim.

Serializable Snapshot Isolation (SSI) — optimistic. Run under snapshot isolation, track read/write dependencies, and abort transactions whose commit would break serializability. Excellent when conflicts are rare; wasteful when they're common, since work is discarded.

Postgres's SERIALIZABLE is SSI. It's genuinely serializable — and it means your application must handle serialization failures by retrying. Code that doesn't retry isn't using serializable isolation, it's just failing occasionally.

Explicit locking, and deadlocks

When you need to force ordering:

• SELECT … FOR UPDATE — lock rows you're about to modify. The standard lost-update fix.
• SELECT … FOR SHARE — lock against modification while you read.
• Advisory locks — application-defined locks, useful for "only one worker does this job."

Deadlocks happen when two transactions grab the same locks in opposite order. The database detects the cycle and kills one. The prevention is boring and effective: always acquire locks in a consistent order — sort the IDs before locking.

Also: SELECT FOR UPDATE in a loop over rows is a deadlock generator. Sort first.

Distributed transactions

Once data spans nodes, atomicity gets much harder.

Two-phase commit (2PC). A coordinator asks everyone to prepare; if all agree, it tells everyone to commit. It works, and it's avoided in modern designs because the coordinator is a single point of failure at the worst possible moment: if it dies after prepare, participants hold locks indefinitely, unable to commit or abort. That's a blocking protocol, and production engineers dislike it for good reason.

Sagas are the common alternative: break the transaction into local transactions, each with a compensating action to undo it. No global locks, no coordinator, and no isolation — intermediate states are visible, so "compensation" must be a business-level concept (issue a refund, not "un-charge"). Tier 3 covers these properly.

The pragmatic guidance: design so that one transaction touches one database. Choose your partition key so related data lives together. Most distributed transaction problems are avoided at the data-model stage rather than solved at runtime.

What to take away

• ACID's C is mostly the application's job; isolation is the part with real choices.
• The anomaly ladder: dirty read → non-repeatable read → phantom → lost update → write skew.
• Write skew survives snapshot isolation — decisions on rows you read, writes to rows you don't.
• Isolation level names are not portable and defaults differ. Check your engine.
• MVCC gives non-blocking reads at the price of vacuum, and long transactions cause bloat.
• Serializable requires retry logic in the application.
• Lock in a consistent order to avoid deadlocks; keep transactions inside one database.