Transactions and isolation

A transaction is a promise the database makes about a group of statements: all or nothing, and, once committed, permanent. What it promises about other transactions running at the same time is the isolation level, and that is where the engineering is. Full isolation costs throughput, so every engine offers weaker levels, each of which allows specific anomalies, and PostgreSQL and MySQL implement the same names differently. This lesson is ACID stated precisely, the anomalies by name, and what each engine actually does at each level.

#ACID, precisely

• Atomicity. All of the transaction's writes become visible, or none do. A crash mid-transaction rolls back. This is what makes a transfer between two accounts safe.
• Consistency. The transaction moves the database from one state that satisfies its constraints to another. The database enforces the constraints it knows (keys, CHECK, foreign keys); the application is responsible for the rest. Of the four, this one is mostly a promise you make.
• Isolation. Concurrent transactions do not see each other's intermediate states, to the degree the isolation level specifies.
• Durability. A committed transaction survives a crash. Implemented with a write-ahead log flushed to disk before commit returns; synchronous_commit = off in PostgreSQL and innodb_flush_log_at_trx_commit = 2 in MySQL trade this away for speed, and should be off by default in your head.

#The anomalies

Each is a specific way concurrent transactions can interfere, and the isolation levels are defined by which they forbid.

Lost update and write skew are the two that bite application code, and neither is prevented by the level most systems run at.

#The levels

This is the SQL standard's table, and it is the answer to the textbook question. The engines are the real answer.

#MVCC: how the engines do it

Neither engine makes readers wait for writers. Both use multi-version concurrency control: an update does not overwrite the row, it creates a new version, and each transaction sees the versions that were committed as of its snapshot. PostgreSQL keeps old versions in the table itself, stamped with the transaction ids that created and expired them, and VACUUM reclaims the dead ones; that is why an update-heavy table bloats without vacuum. InnoDB keeps old versions in undo logs and rebuilds a row's past on demand. Readers never block writers and writers never block readers; writers block writers, on the same row.

#What PostgreSQL does

• Default: read committed. Each statement takes a fresh snapshot. Two SELECTs in one transaction can differ. An UPDATE that finds its target row changed by a concurrent committed transaction re-reads the row and applies to the new version, which is why a plain UPDATE accounts SET balance = balance - 30 WHERE id = 1 is safe from lost updates and a read-then-write in application code is not.
• Read uncommitted behaves as read committed. There are no dirty reads in PostgreSQL at any level.
• Repeatable read takes one snapshot for the whole transaction, and it is stronger than the standard requires: phantoms cannot happen, because the snapshot is fixed. A write that conflicts with a concurrent committed write fails with could not serialize access due to concurrent update, and the transaction must be retried. Write skew is still possible.
• Serializable uses serializable snapshot isolation: it tracks read/write dependencies and aborts one transaction of a cycle with a serialization failure. It prevents write skew. The cost is retries, so a serializable transaction needs a retry loop around it, always.

#What MySQL (InnoDB) does

• Default: repeatable read, with two behaviours that trip people. A plain SELECT is a consistent read from the snapshot taken at the first read, and it does not see later commits. A locking read (SELECT ... FOR UPDATE, FOR SHARE) and any UPDATE or DELETE see the latest committed data and take locks, and InnoDB's next-key locks on the range they scan are what stop phantoms for locking reads. So within one transaction, a plain read and a locking read of the same rows can disagree, which is confusing until you know the rule.
• Read committed drops the gap locking, which reduces deadlocks under concurrent inserts and is why many high-write MySQL systems run at this level on purpose. Non-repeatable reads and phantoms then occur.
• Serializable turns every plain SELECT into a shared locking read. Genuine serializability at the cost of readers blocking writers.
• Write skew is possible at repeatable read, as in PostgreSQL.

#Preventing the two that matter

Lost update, three ways, from best to worst:

UPDATE accounts SET balance = balance - 30 WHERE id = 1 AND balance >= 30;
-- check the row count: 0 means insufficient funds, and nothing was lost

BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;   -- others wait here
UPDATE accounts SET balance = 70 WHERE id = 1;
COMMIT;

UPDATE accounts SET balance = 70, version = version + 1 WHERE id = 1 AND version = 12;
-- 0 rows updated means somebody else got there first: reload and retry

The Locking lesson covers the second and third in depth; JPA's @Version is the third.

Write skew needs either SERIALIZABLE with retries, or a lock on something that represents the invariant: SELECT ... FOR UPDATE on the parent row (the shift, the account, the inventory line) so that the two transactions serialise on it. The second is what most systems do, because it is explicit and cheap.

#In Spring

@Transactional opens a transaction at the default level of the connection, which is read committed on PostgreSQL and repeatable read on MySQL, and those defaults differ, so a service moved between them behaves differently. @Transactional(isolation = Isolation.SERIALIZABLE) sets it per method. A transaction is held for the whole method, including any HTTP call inside it, which is the shape that exhausts a connection pool; the Connection pooling lesson has that incident.

#Under the hood: versions, snapshots, and the write-ahead log

Every row in PostgreSQL carries two hidden columns, xmin (the transaction id that created this version) and xmax (the one that deleted or replaced it, or 0). A snapshot is a record of which transaction ids were committed when it was taken: the lowest id still running, the highest assigned, and the list of in-progress ids between them. A version is visible to a snapshot if its xmin is committed in that snapshot and its xmax is not. That test, run per row against the transaction's snapshot, is isolation: at read committed a new snapshot is taken per statement; at repeatable read and serializable one snapshot is taken at the first statement and reused, so the same version test gives the same answer all transaction long, and phantoms cannot appear because an inserted row's xmin is not in the snapshot. Commit itself is one bit flipped in the commit log (pg_xact); nothing in the rows changes, which is why commit is cheap and why a later reader has to consult that log, cached per page in "hint bits", to know a version's fate.

InnoDB gets the same effect the other way round: the row in the clustered index is always the latest version, and each row carries a roll pointer into the undo log, a chain of older images. A consistent read walks that chain back until it finds a version whose transaction id is visible to its read view. The undo log is also what ROLLBACK replays, and a long-running transaction keeps every undo record since its start alive, which is InnoDB's version of the bloat PostgreSQL sees from a stalled vacuum.

Durability is the write-ahead log: before a data page is modified, the change is appended to the log, and COMMIT returns only after the log record is fsynced to disk (synchronous_commit = on, innodb_flush_log_at_trx_commit = 1). Data pages themselves are written later, at checkpoints; after a crash the engine replays the log from the last checkpoint. That ordering is why commit latency is dominated by one disk flush, why a battery-backed or NVMe log device matters more than anything for a write-heavy database, and why group commit (batching several transactions' log records into one flush) is what lets thousands of commits per second happen on one disk.

#Walkthrough: the double refund

A refund endpoint checked the order's refunded amount, then inserted a refund if there was room. Two support agents clicked at the same moment.

@Transactional
public void refund(long orderId, long paise) {
    long already = refunds.sumFor(orderId);            // SELECT SUM(amount) ...
    if (already + paise > orders.total(orderId)) throw new OverRefundException();
    refunds.insert(orderId, paise);                    // INSERT ...
}

1. Both transactions ran at PostgreSQL's default, read committed. Both SELECT SUM saw the same committed total of 0. Both checks passed. Both inserted. The order was refunded twice its value.
2. Nothing in the isolation level prevented it: neither transaction updated a row the other updated, so there was no write conflict for the engine to notice. The invariant lived in application code, across a read and a write, which is write skew.
3. Repeatable read would not have helped: both snapshots still show 0, both inserts succeed, since inserts of new rows do not conflict. Only SERIALIZABLE sees the read-write dependency (each transaction read a set the other's insert would have changed) and aborts one with 40001, which the code did not retry.
4. The fix chosen was a lock on the thing that represents the invariant: SELECT ... FROM orders WHERE id = ? FOR UPDATE as the first statement. The second agent's transaction waits on the order row until the first commits, then its SUM sees the new refund and the check fails properly. A CHECK-like constraint would have been better still, but "sum of refunds ≤ total" spans rows and cannot be a constraint; a refunded_paise column on orders, updated atomically with UPDATE ... SET refunded = refunded + ? WHERE id = ? AND refunded + ? <= total, made it one.
5. The postmortem's rule: any check-then-write on data other transactions can change is either an atomic conditional UPDATE, a FOR UPDATE on a parent row, or SERIALIZABLE with a retry loop. @Transactional alone is none of those.

The transaction did what it promised: both sets of writes were atomic and durable. What it did not promise was that a read made inside it stayed true.

#Try it yourself

#Misconceptions

• "@Transactional makes a read-then-write safe." It makes the writes atomic. A read inside it can be stale by the time the write runs unless the level or a lock says otherwise.
• "Repeatable read prevents lost updates and write skew." It prevents non-repeatable reads and, in PostgreSQL, phantoms and concurrent-update conflicts; write skew across different rows survives it on both engines.
• "Serializable means locks everywhere." In PostgreSQL it is snapshot isolation plus dependency tracking; it blocks nothing and aborts instead, so it needs retries. In MySQL it does turn reads into locking reads.
• "Commit writes the data to the table." Commit flushes the log; the table pages are written later at a checkpoint. Durability is the log's fsync.
• "Readers and writers block each other." Under MVCC readers never block writers or vice versa; only writers on the same row wait.

#Going deeper

• PostgreSQL manual, chapter "Concurrency Control": "Transaction Isolation" (the per-level semantics) and "Serializable Snapshot Isolation".
• MySQL reference, "InnoDB Transaction Isolation Levels", "Consistent Nonlocking Reads" and "Locking Reads".
• Martin Kleppmann, Designing Data-Intensive Applications, chapter 7, for the anomalies and write skew with worked examples.
• Dan Ports and Kevin Grittner, "Serializable Snapshot Isolation in PostgreSQL" (VLDB 2012).
• PostgreSQL manual, "Write-Ahead Logging" and synchronous_commit; MySQL's innodb_flush_log_at_trx_commit reference.