Getting writes right

Reads can be a little stale and nobody dies. Writes are where the money moves, and they fail in a distinctive way: not by being slow but by being interleaved — two requests, each correct alone, landing on the same rows at the same time. This stage is the machinery that makes concurrent writes tell the truth.

What a transaction actually promises

Atomicity: all of the group’s writes commit or none do — implemented with the WAL you met in Stage 3. Durability: committed means fsync’d. Consistency here means your constraints hold. The load-bearing letter is Isolation: the rules for what concurrent transactions may see of each other — and it comes in degrees, each permitting named anomalies:

Most engines default to read committed or snapshot, implemented with MVCC: writers create new row versions instead of overwriting, readers see the version current at their snapshot — readers never block writers, which is why modern databases feel so concurrent. The classic snapshot escape is write skew: two doctors check the on-call roster (two listed), each removes themselves in parallel; both snapshots were valid, neither wrote what the other read, both commit, the roster is empty. The reads were the constraint, and snapshots do not guard reads.

Choosing your weapon per invariant

• Let the database enforce it where possible: unique and check constraints, foreign keys — they hold at every isolation level and under every retry.
• Pessimistic: SELECT … FOR UPDATE locks the rows the invariant depends on. Right when conflicts are common; brings lock waits and deadlocks (the engine detects and kills one — your code retries).
• Optimistic: version each row; UPDATE … WHERE version = n; zero rows updated means someone beat you — retry. Right when conflicts are rare; it is idempotency’s cousin and pairs perfectly with retries from the Distributed Systems pathway.
• Serializable for the one transaction that deserves it — per-transaction, not global; pay the aborts where correctness is priceless.

When the write spans systems

All of the above is one database’s promise. “Commit the order, then charge the card, then publish the event” crosses three systems, and no isolation level covers the trio. Two-phase commit exists (prepare everywhere, then commit everywhere) but blocks the world when the coordinator dies mid-flight — the Distributed Systems pathway explained why that is disqualifying on hot paths. The workable pattern is the saga: a sequence of local transactions, each with a compensating action (refund, release, cancel) run if a later step fails. You trade isolation for availability — intermediate states are visible, so design them to be honest (“payment pending”), make every step idempotent, and let a driver (often the outbox/stream machinery of the next stage) push each saga to completion or compensation. Never half-finished, only visibly-in-progress or cleanly undone.