Isolation levels in practice: the anomalies your default setting allows

01What each level prevents

Isolation levels are defined by the anomalies they prevent. Higher levels prevent more and cost more in contention or retries, which is the trade you are making whether or not you make it consciously.

02The lost update that read-modify-write produces

Read a balance, subtract an amount in application code, write it back. Two concurrent transactions both read the same starting value, both compute from it, and the second write silently overwrites the first. Nothing errors; money simply disappears from the arithmetic.

At Read Committed this is entirely possible and it is the most common concurrency bug in application code. The fix is either to make the update atomic in SQL - compute the new value from the current one in a single statement - or to lock the row explicitly while you work on it.

03Write skew is the one people have not heard of

Two transactions each read a set of rows, each check a constraint that currently holds, and each write different rows in a way that together violates the constraint. Neither wrote what the other read, so nothing conflicts - and the invariant is broken anyway.

The classic example is an on-call rota requiring at least one person available. Two people check that someone else is on duty, both mark themselves unavailable, and nobody is. Repeatable Read permits this; only Serializable prevents it.

04Serializable and the retry requirement

Serializable gives you the guarantee that concurrent transactions produce the same result as some sequential order - which is what most developers assume they already have. The cost is that transactions can be aborted with a serialization failure, and your application must retry them.

That retry logic is mandatory, not optional. Adopting Serializable without it converts a rare data-integrity bug into a visible error rate, which is arguably worse because it happens under exactly the load conditions where you least want new failures.

• Serializable requires application-level retry on serialization failure.
• Keep serializable transactions short - long ones abort more often.
• Retry with backoff, and cap attempts.
• Do not mix long analytical reads into serializable write transactions.

05Explicit locking as the targeted alternative

You do not have to raise isolation globally. SELECT FOR UPDATE locks the specific rows you are about to modify, which prevents lost updates precisely where they matter while leaving everything else at the default.

The trade is contention and deadlock risk. Lock rows in a consistent order across your codebase - deadlocks come from two transactions taking the same locks in opposite sequences, and a convention about ordering eliminates most of them.

06Choosing deliberately

Read Committed with targeted locking covers most application workloads well, and it is the pragmatic default. Move to Serializable for operations where an invariant spans multiple rows and correctness genuinely matters more than throughput - financial postings, inventory allocation, scheduling constraints.

The mistake is not choosing at all, then discovering under production concurrency that the guarantee you assumed was never there. Write down which operations need which level, and make it part of the design rather than a default nobody examined.

• Default is fine for most reads and simple writes.
• Use explicit locks for read-modify-write on specific rows.
• Use Serializable where an invariant spans rows you did not write.
• Document which operations rely on which guarantee.