Transactions and isolation

ACID, honestly

A transaction groups several reads and writes into one unit that either happens or does not. The four letters of ACID are usually recited as if they were four equal guarantees. They are not, and an interviewer who asks "what does ACID mean" is checking whether you know which letters the database gives you and which ones you supply.

• Atomicity means all-or-nothing on failure: if a transaction aborts or the server crashes halfway, none of its writes survive. It says nothing about concurrency. A better name is abortability.
• Consistency is the odd one out. It means your application's invariants hold (an account never goes below zero, every order has a customer). The database can enforce only the rules you declare as constraints; everything else is the application's job, and the other three letters are the tools for keeping it.
• Isolation is what concurrent transactions are allowed to see of each other. This is the letter with levels, the one the rest of the chapter is about, and the one most often weaker than people assume.
• Durability means a committed transaction survives a crash, which is a statement about when the log reaches disk. PostgreSQL's default synchronous_commit = on waits for the local WAL flush (and for the synchronous standbys' flush too, if any are configured). Setting it to off lets the server report success first: the manual says recent commits can be lost in a crash, for up to three times wal_writer_delay (200 ms by default, so about 600 ms), but the database stays consistent, as though those transactions had aborted cleanly. Turning fsync off is different and can corrupt the cluster. Surviving a lost machine is a replication question, covered in the replication chapter.

The word "transaction" also stretches. Redis MULTI/EXEC runs its queued commands one after another with nothing interleaved, but it has no rollback: a command that fails after EXEC does not stop the rest, and the docs say rollback was left out for simplicity and performance. It is isolation without abortability. A relational database is the opposite trade.

The anomalies, each as a story you can retell

Isolation levels are defined by which anomalies they allow, so learn the anomalies first. Everything below involves two concurrent transactions, T1 and T2.

Write skew is the one worth dwelling on, because every simple rule people carry ("a transaction sees a consistent snapshot", "the later write wins") fails to catch it. Two doctors are on call; the rule is that at least one must stay on call. Both feel unwell and click "go off call" within the same second.

Isolation levels, and why the names lie

The SQL standard names four levels (read uncommitted, read committed, repeatable read, serializable) and defines them by three anomalies: dirty reads, non-repeatable reads and phantoms. Real databases implemented the names in different ways, and the standard's definitions never mentioned lost update or write skew at all. The consequence is that the label on the level tells you less than the engine behind it. Kleppmann's Hermitage project exists for exactly this reason: it runs the same test transactions against each database to "nail down precisely what different database systems actually mean with their isolation levels".

PostgreSQL's own table says a Read Uncommitted request behaves as Read Committed, and that its Repeatable Read does not allow phantoms even though the standard would permit them. Its documentation calls Repeatable Read an implementation of snapshot isolation and says that level is not necessarily consistent with any serial execution.

Snapshot isolation, the level most databases actually run

Most systems implement the middle levels with multi-version concurrency control (MVCC). Writers never overwrite a row in place; they add a new version, and each transaction reads the versions that were committed when its snapshot was taken. Readers never block writers and writers never block readers, which is why this design won. In PostgreSQL's Repeatable Read the snapshot is taken at the first statement of the transaction and held to the end; Read Committed takes a fresh snapshot for each statement. InnoDB uses snapshots too, though Hermitage rates its repeatable read as weaker than PostgreSQL's: its consistent reads at REPEATABLE READ use the snapshot established by the first read, while at READ COMMITTED each consistent read sets and reads its own fresh snapshot.

Snapshot isolation catches write-write conflicts on the same row. In PostgreSQL, if a Repeatable Read transaction tries to update a row that a concurrent transaction changed and committed since its snapshot, it fails with could not serialize access due to concurrent update and the application must retry the whole transaction. That kills the classic lost update. InnoDB does not do this: Hermitage's results for MySQL REPEATABLE READ show lost update and write skew both still possible, so the same "repeatable read" label protects you in one database and not in the other. Neither engine can see write skew, because T1 and T2 wrote different rows.

Dry run: lost update, then write skew, under each rule

First a counter at 10 and two clients each adding 1. Then the doctors, with 2 on call and a rule that the count must stay at 1 or more.

Notice that the three fixes for write skew have different shapes. Declare the rule as a constraint if the database can express it; take locks on the rows you read so the second transaction waits; or run at a serializable level and retry on failure. Phantom-based variants of the same bug (a booking inserted into an empty range) cannot be fixed by locking rows that do not exist yet, which is why range locks, predicate locks or a materialised slot table exist.

Making it truly serializable: three techniques

Serializable means the outcome equals some one-at-a-time ordering of the transactions, as the consistency models chapter defined it. There are three ways to deliver it.

PostgreSQL's SSI shows the design trade clearly. Its predicate locks, shown as SIReadLock in pg_locks, do not block anything and so cannot cause deadlock; they only record what was read. When a dangerous structure appears, a transaction fails with SQLSTATE 40001 and the application must start over, so under SSI a retry loop is part of the contract, not an optional extra. The authors of the PostgreSQL implementation measured, in 2012 on PostgreSQL 9.1, a performance cost of less than 7% relative to snapshot isolation on their transaction-processing and web-application benchmarks (a simple microbenchmark showed more), and found it outperformed two-phase locking significantly on some workloads. Two sharp edges: a hot standby cannot run SERIALIZABLE transactions, and data read in a serializable transaction should not be trusted until that transaction commits.

Which to choose? Serial execution when the data fits one partition and every transaction is short; SSI when contention is moderate and you want readers to stay unblocked; 2PL or explicit row locks when contention is high and aborting wastes more than waiting does. Under heavy contention SSI wastes work on aborts, though PostgreSQL's design guarantees that an immediate retry will not fail again with the same conflict; under locks the transactions queue instead.

What the four systems actually default to

Two cautions. A default is a statement about the average application, not about yours, so a design that depends on an invariant across rows needs to name the level it relies on. And a name can hide an engine difference, as the lost-update row shows: "repeatable read" on two databases is two different products.

DynamoDB's transaction page is a good example of what distributed transactions cost. A transaction groups up to 100 items totalling at most 4 MB, performs two underlying reads or writes per item (one to prepare, one to commit), and a conflicting concurrent transaction is cancelled rather than waited for. Its own capacity example: one transaction a second writing three 500-byte items needs 3 × 2 = 6 write capacity units. A client token makes TransactWriteItems idempotent for 10 minutes after the request finishes. That prepare-then-commit doubling is the next topic in miniature.

Distributed transactions: two-phase commit and why it blocks

When one transaction spans two databases (or two shards), atomicity needs agreement: all must commit or none. Two-phase commit (2PC) achieves it with a coordinator. In phase one the coordinator asks every participant to prepare: each writes its changes and a promise to the log, keeps its locks, and votes yes or no. If every vote is yes, the coordinator durably records the decision to commit, and in phase two tells everyone. A single no, or a timeout, means abort.

The blocking is the point. Once a participant has voted yes it must be able to commit if told to, so it cannot abort alone; yet it cannot commit alone because another participant may have voted no. If the coordinator crashes after collecting votes, participants are in doubt and hold their locks until the coordinator recovers and answers. PostgreSQL's PREPARE TRANSACTION documentation says exactly this: the transaction continues to hold whatever locks it held, it is unwise to leave one prepared for long (it holds up VACUUM and can even force the database to shut down to prevent transaction ID wraparound), and the feature is for external transaction managers, not for applications.

Costs to quote. A remote participant holds its locks from the moment it votes yes until the commit message reaches it, which is about one coordinator-to-participant round trip. With an 80 ms round trip that is 80 ms, so a hot row in such a transaction can commit at most 1 ÷ 0.080 = 12.5 times a second, and that is before counting the log forces and the cross-region round trips of the statements themselves, so real figures are worse. That arithmetic is why cross-region 2PC on contended rows is a bad idea. Spanner shows the repair: it runs 2PC across Paxos groups, but each coordinator and participant is itself a replicated state machine, so no single machine's crash blocks the transaction, and most transactions touch only one Paxos group and skip 2PC entirely. The price is paid in latency, with the commit wait described in the time and ordering chapter.

Sagas and the outbox: when you cannot or will not hold locks

Across services that own separate databases, holding locks across network calls is rarely acceptable, and a payment provider or email system will not join your 2PC at all. The alternative is a saga: a sequence of local transactions, each committed on its own, coordinated so that a failure midway triggers compensating transactions that undo the earlier steps. Reserve inventory, charge the card, create the shipment. If the shipment step fails, issue a refund and release the inventory.

• Choreography: each service reacts to the previous step's event and publishes its own. No central owner, but the flow lives nowhere you can read.
• Orchestration: an orchestrator tells each participant which local transaction to run and what to do on failure. One place to read, test and observe the flow.
• A compensation is not a rollback. It is a new business action, and some effects cannot be undone: an email already sent, a parcel already shipped. Design the step order so the irreversible actions come last.
• Sagas have no isolation. Another transaction can see the intermediate state (stock reserved, payment not yet taken). Treat that state as a legitimate status in the data model, such as "pending", and handle it explicitly.

A saga is only as reliable as each step's ability to commit locally and publish the message that triggers the next step. Doing those two things separately is the dual-write problem: publish before commit, and a rollback leaves a message about something that never happened; commit then publish, and a crash in between loses the message. The transactional outbox removes the gap. The service writes its business change and an outbox row in the same local transaction, so they commit or fail together. A separate relay then reads the outbox, either by polling it or by tailing the database's transaction log, and publishes to the broker. Because the relay can crash after publishing and before marking the row done, delivery is at least once, and consumers must be idempotent. The message queues chapter covers idempotent consumers and delivery guarantees in detail.

Picking the right tool, in one table

Recognizing it in an unseen problem

• Signals: "two users at once", "transfer", "book the last seat", "reserve", "exactly one", "must not go negative", "across services". Each is a concurrency or atomicity question; name the anomaly before naming the fix.
• The naive design reads a value, checks it in application code, then writes. That read-compute-write gap is where lost updates and write skew live. Replace it with a conditional write, a lock or a serializable transaction.
• Ask which level, and in which database. "We'll use transactions" is incomplete; the answer that scores is "Postgres at read committed by default, so I'd add FOR UPDATE here" or "serializable, with a retry loop".
• Distributed means expensive. Quote the 2PC lock-hold time (about a round trip per hop, plus log forces) and the blocking-on-coordinator-failure property, then offer the alternative: co-locate the data, or a saga with compensation.
• Events plus database writes always raise the dual-write question. Volunteer the outbox before it is asked.
• The pitfall to avoid: assuming "repeatable read" or "serializable" means the same thing in every engine, or treating compensation as a true rollback.