Transactions, Isolation and Things That Overlap

1All or nothing, and what that does not promise

A transaction is a bracket around several statements that says: all of these, or none of them. That is a smaller promise than most people carry in their heads, and the gap is where the bugs live.

Move money between two accounts and you have two writes: one down, one up. Without a bracket around them, a crash between the two leaves the money nowhere. The bracket — BEGIN … COMMIT — makes the pair indivisible from the outside. Either both writes are durable or neither ever happened, and there is no observable moment in between.

That is atomicity, the A of ACID, and it is the part everybody knows. The other three are worth stating precisely because each is a different promise:

Notice the shape of the isolation row, because it is the whole chapter. Isolation is not a boolean. It is a dial, your database ships with it set somewhere in the middle, and where it is set determines which specific wrong answers your application can observe. A team that believes “we use transactions, so we are safe” has confused atomicity with isolation, and the bug that eventually finds them will not reproduce on a laptop.

One more thing that atomicity does not promise, and it catches people who are otherwise careful: a failing statement does not necessarily roll back the transaction. In most engines a constraint violation aborts that statement and leaves your transaction open, holding its locks, with earlier writes still pending. If your code catches the error and carries on to COMMIT, you commit a partial result that atomicity never protected you from, because from the database’s point of view you asked for exactly that. The habit worth building is that every error path in transactional code ends in an explicit ROLLBACK.

2The four anomalies

The literature names four things that can go wrong when transactions overlap. They are worth memorising, because every isolation level is defined as a list of which of them it forbids.

All four are the same underlying situation — two transactions touching overlapping data with their operations interleaved — distinguished by exactly what one of them sees or loses.

The last one deserves its own emphasis. A dirty read is loud — something downstream usually breaks in a way somebody notices. A lost update is silent. Both transactions succeeded, both returned 200, no error was logged anywhere, and the number is simply wrong. There is no artefact to search for afterwards, which is why lost updates are typically discovered by a customer doing arithmetic rather than by an engineer reading a log.

Step through both of the labs below. In each one you choose an isolation level first, then advance the interleaving one operation at a time and watch which anomalies fire and which are prevented.

The second lab is the one to sit with. Both transactions did exactly what their code said. Neither read stale data in the dirty sense — both read a committed value of 100. The failure is that the value each of them read stopped being true before its write landed, and nothing in the read-committed contract promises otherwise.

3The isolation levels, and what each actually stops

Four levels, defined by which anomalies they forbid. The useful move is to learn the table by its gaps rather than its guarantees.

* The asterisks matter. “Repeatable read” means different things in different engines: PostgreSQL’s implementation is snapshot isolation and does prevent phantoms, while MySQL’s prevents them for plain reads but not for locking ones. This is not pedantry — it is the reason a pattern that is safe on one engine is unsafe on another with the same level name, and it is why the 1995 Berenson paper that first catalogued this is still cited.

Two conclusions follow immediately, and both surprise people.

The first: your database almost certainly allows lost updates by default. Read committed is the default in most of the systems you will meet, and read committed does not stop a lost update. If your code reads a row, computes a new value in application memory, and writes it back, that sequence is unsafe as written, on the settings you are running right now, and it will be fine in every test you write because your tests do not overlap.

The second: raising the level is a real option and it is not free. Serializable does fix the whole table, and databases implement it by detecting conflicts and aborting one of the transactions involved. That means your application must be prepared to be told “no, run that again” — not as an exceptional case but as normal operation under load. Code that does not handle serialization failures with a retry does not get correctness from serializable; it gets a new error to page someone about.

4Losing an update, and the three ways to stop it

The read-modify-write is the single most common shape in application code and the single most common source of silent corruption. There are exactly three standard fixes and it is worth knowing all three, because they fail differently.

The unsafe shape is unmistakable once you look for it: a SELECT, some arithmetic in your language, and an UPDATE that writes an absolute value. Every balance = balance - x computed in Python or JavaScript rather than in SQL is an instance.

The three fixes, in the order you should reach for them:

The version column deserves the most attention, because it is the only one of the three that treats a conflict as information rather than as something to hide. When the update changes zero rows you know something specific: somebody else edited this row between your read and your write. That is exactly the moment to show a human what changed rather than to silently retry and overwrite them. Optimistic concurrency is not just a locking strategy — it is a product decision about who wins.

5Long transactions are an availability problem

A transaction holds resources until it ends. How long it is open is therefore not a performance detail — it is a limit on how many other things can happen.

An open transaction holds locks on every row it has written, and in an MVCC engine it also pins a snapshot, which prevents the cleanup of every row version created since it started. Both effects scale with duration. A transaction open for two milliseconds is invisible; the same transaction open for thirty seconds because it is waiting on an HTTP call blocks every writer that touches its rows for thirty seconds, and the queue that forms behind it is what your users experience as an outage.

The rule that follows is short and absolute: never hold a transaction open across a network call you do not control. Not a payment provider, not an email service, not a model API. The failure mode is specific and nasty — a provider that normally answers in 80 ms starts answering in 30 seconds, your transactions stop retiring, your connection pool fills, and the database begins refusing new connections. Nothing is wrong with the database. Nothing is wrong with your queries. Your application has simply attached its lock lifetimes to somebody else’s latency.

This is more likely, not less, in AI-heavy systems, because a model call is a network call with a latency distribution measured in seconds and a tail measured in tens of seconds. Code that wraps “write the draft, call the model, write the result” in one transaction is the same bug with a more fashionable dependency. The shape that works is: commit what you know, make the call outside any transaction, then open a second short transaction to record the outcome — which forces you to decide what the row means in between, and that decision is the design.

The arithmetic is just Little’s law: sustainable concurrency is pool size divided by hold time. It is worth memorising because it turns “keep transactions short” from advice into a number you can put in a design review.

6Build: a transfer that cannot leave money missing

Everything above assembles into one small pattern, and it is worth writing out in full because the exercises ask you to reproduce it from scratch.

The requirements for moving money between two accounts are: the total across all accounts never changes; no account ever goes negative; a repeated request moves the money once; and a request that cannot be satisfied changes nothing at all rather than half of something.

Four requirements, four mechanisms, one for each:

1. The total never changes → both writes inside one transaction, so a crash between them is not observable.
2. Never negative → a guard in the WHERE clause, so the check and the deduction are one statement, backed by a CHECK constraint so that a code path that forgets the guard fails loudly instead of quietly.
3. Repeats move it once → an idempotency key row inserted first, with a unique constraint, so the second attempt collides.
4. All or nothing → the credit is itself conditional on the debit having applied, so a refused debit cannot be followed by a successful credit.

Read step three carefully, because it is the part that is easy to get wrong and impossible to test by hand. changes() reports the number of rows altered by the most recent statement, so chaining conditions on it means each step only runs if its predecessor did. Get the order wrong and you have written a system that credits without debiting, which is a considerably more expensive bug than the one you were trying to fix.

One honest caveat about the environment. SQLite in your browser has a single connection, so nothing here can genuinely interleave — you cannot make a real lost update happen in this page. What you can do, and what the exercises are built around, is write the statement that would be safe if it could, and prove it by sending the exact write a stale request would send and watching it change zero rows. That is the same assertion you would write as an integration test against a real engine, and it is the one that catches the bug.

✓Checkpoint

▶Playground

Build your own interleaving. Change the operations, pick a level, and step through to see which anomalies the level lets past.

✓Exercise set

Twelve problems against a real SQLite database. Several of them prove safety the way an integration test would: after your write, the tests send the exact statement a stale or repeated request would send, and assert that it changes nothing. Your work is saved in this browser.