Transactions and isolation

Multi-version concurrency control keeps several versions of each row rather than updating in place. A reader sees the snapshot that existed when its transaction began, while writers create new versions alongside. Readers do not block writers and writers do not block readers, which is the property that makes a database usable under mixed load and is the reason MVCC won. An update is therefore an insert of a new version plus a mark on the old one, not an overwrite. That has consequences people meet later: an update costs roughly what an insert costs, updating one column of a wide row still writes the whole row in Postgres, and every index entry has to be maintained for the new version unless the update qualifies for the in-page optimisation.

The old versions accumulate and have to be reclaimed, which is what vacuum does. It can only remove a version once no transaction might still need it, so the oldest open transaction sets the horizon for the entire database. That is the mechanism behind the classic production incident: a connection left idle in transaction by a pool or a debugger, holding the horizon still while a busy table bloats and its queries slow down, with write volume completely normal.

Bloat is the visible symptom and the space is not returned to the operating system by ordinary vacuum, only made reusable, so a table that ballooned stays large until it is rewritten. Autovacuum is tuned per table for a reason: a hot table with a high update rate frequently needs it running more aggressively than the defaults, and the settings that matter are the scale factor and the cost limits.

What MVCC buys at the isolation level is snapshot isolation, and it is worth being precise that this is not serializable. A snapshot is consistent, so every read in a transaction agrees with every other, and two transactions can still each read a consistent snapshot, each check a rule that holds in it, and each write something that makes the rule false. That is write skew, and it is the subject of the next topic.

The practical habits follow directly. Keep transactions short, never hold one open across user interaction or an external call, monitor the age of the oldest transaction as a first-class metric, and treat idle in transaction as an alertable state rather than a curiosity in a connection list.

Write skew is the anomaly people are most confident their database prevents. Two transactions read an overlapping set of rows, each checks a rule that still holds in its own snapshot, and each writes a different row. Neither touches what the other wrote, so nothing conflicts, both commit, and the rule is now false.

The textbook case is on-call cover: two doctors each check that at least one other doctor remains on duty, each sees a snapshot where that is true, and each takes themselves off the rota. Both checks passed. Nobody is on call. No database error was raised at any point, because from each transaction's perspective nothing was wrong.

The shape to recognise is a rule about a set combined with a write to a member of that set. Reserving the last seat, keeping a balance above zero across several accounts, enforcing at most one active subscription, allocating unique meeting rooms, maintaining a minimum staffing level. Every one of those is a constraint that no single row can express, which is exactly why the database cannot enforce it for you.

There are three fixes and they trade differently. Serializable isolation detects the dangerous read-write pattern and aborts one transaction, which is correct and needs a retry loop. Materialising the conflict gives the rule a row of its own, a rota row or a counter, that every participant must lock, which turns an invisible predicate into a real write conflict the database can see. A predicate lock does the same explicitly where the engine supports it.

The cheapest fix is often to change the model rather than the isolation level. A unique constraint or an exclusion constraint expresses many of these rules directly, and a constraint is checked by the database on every path, including the script somebody runs by hand at midnight. Where the rule can be made into a constraint, that is a better answer than any transaction setting.

The reason this stays hidden is that it needs concurrency and a rule spanning rows, which no unit test has and every production system does. It will not appear in review, will not appear in staging, and will appear on the day two people click at the same moment, which is why recognising the shape is worth more than remembering the name.

Writing to your database and then publishing an event is two operations that can fail independently. Commit succeeds, the process dies before the publish, and the event is gone: the order exists and nothing downstream will ever hear about it. Publish first and you have the opposite problem, an event for an order that was never created.

The outbox pattern makes it one operation. The event is written to an outbox table in the same transaction as the data, so both commit or neither does. A separate relay reads that table and publishes, marking rows as sent.

Delivery is still at-least-once. The relay can publish and die before recording that it did, so the same event goes out twice, which is why consumers stay idempotent. What the outbox removes is the possibility of losing an event entirely, and losing one is far worse than seeing one twice.

The relay can poll the table or read the database's replication log directly. Polling every second is simple and adds a query and a second of latency; change data capture via the write-ahead log has no polling delay and no query load, at the cost of running Debezium or similar. Either way the outbox needs an index on unsent rows and a cleanup job, because a table nobody prunes becomes the slowest part of the write path.