Choose an isolation level
Transactions on one database read and write the same rows at the same time, and some business rule must stay true: a balance never lost, a room never overbooked. The isolation level decides which interleavings of those transactions the database allows. But a stronger level is not the only fix, and often not the cheapest. What is the cheapest protection that keeps your rule true?
You will end with the cheapest protection that keeps your rule true: a rewritten operation, a constraint, or an isolation level, with what it costs.
The questions go from cheapest to most expensive answer: stop at the first one that fits.
Decide
Can the operation be rewritten so the order of updates does not matter?
SET balance = balance - 100 instead of reading the balance, computing in code and writing it back; appending a change instead of overwriting; an upsert that gives the same result if repeated.
Why
Two updates that commute give the same result in either order, and the engine applies each to the latest value. Nothing can be lost, so the isolation level stops mattering. It is free, and available far more often than people check.
Does the rule fit on a single row?
balance >= 0 as a CHECK constraint, or 'one account per email' as a UNIQUE index.
Why
The engine enforces constraints on one row at every isolation level. It is the cheapest correctness available.
Does a transaction check a condition over several rows, then write a row that changes that condition?
Check that a room's bookings stay under capacity, then insert a booking; check that at least one doctor is on call, then take yourself off call.
YesSerializable
NoGo to 4
Why
This shape is write skew. Under snapshot isolation two such transactions each read a snapshot where the condition holds, write different rows, and both commit, breaking the rule. Snapshot isolation only checks for overlapping writes, and these writes do not overlap, so it cannot see the problem.
Does application code read a value, compute in code, then write it back?
SELECT the balance, add the bonus in Java, UPDATE the balance.
Why
At read committed, another transaction can change the row between your read and your write, and your write silently overwrites it: a lost update. The risk grows with the time between read and write, and application code with network round trips holds that window open about 1,000 times longer than a single SQL statement.
Where you can land
Rewrite the operation so it commutes
Use it when the update can be expressed relative to the current value, or as an append.
Works at any isolation level. What it costs: it exists only where the operation has that algebra.
A single-row constraint
Use it when the rule is about one row.
CHECK constraints and UNIQUE indexes are enforced at every level. What it costs: the rule must fit in one row.
Read committed
Use it when no transaction reads, computes in code and writes back, and no rule spans several rows.
Each statement sees data committed when it started. Cheap, and the default in many engines.
Before you build it
Keep read-modify-write inside the engine
If someone later adds code that reads a value and writes it back, read committed starts losing updates. Write such logic as one SQL statement where you can: it shrinks the window by three to four orders of magnitude.
Read committed with a lock or version check, or snapshot isolation
Use it when application code reads, computes and writes back, but no rule spans several rows.
Three ways to stop the lost update, in rising cost:
- One statement: move the computation into the
UPDATEitself, if you can. - Version check: write only if the row's version is unchanged (
… WHERE version = 7), and retry if zero rows changed. Costs a retry loop, which someone must own. - Lock the row:
SELECT … FOR UPDATEholds the row until commit. Costs blocking, and a risk of deadlock.
Snapshot isolation also prevents lost updates, by aborting the second writer.
Serializable
Use it when a transaction checks a condition over several rows and then writes into that set.
The engine guarantees the result matches some one-at-a-time order of the transactions.
What it costs: with serializable snapshot isolation (SSI), false aborts and memory for tracking reads; with locking, blocking, which lengthens transactions and raises conflict further.
The alternative, and why to be wary of it: stay on snapshot isolation and materialize the conflict, making every transaction that touches the condition also update one shared row (for example, the room's row), so the engine sees the clash.
Prefer serializable over targeted fixes
The real argument is not performance. Proving snapshot isolation safe means analysing which transactions can combine into an anomaly, and that analysis does not compose: a transaction added next quarter can combine with one written today, and nothing will flag it. Serializable's abort rate is a number you can measure; the fragility of manual analysis is a risk you cannot.