Two requests, both succeeded, and the total is wrong

Two support agents apply a credit to the same account within the same second. Both requests return 200. Both appear in the audit log. The balance moved by one credit instead of two. Nothing errored; the log shows two committed transactions that each did what the code says.

The code reads the balance, adds the credit in the application, writes the total back. At your database's default isolation level, that is allowed to lose one of them.

Predict

Read the row, compute in the application, write it back. Which databases let the second write silently overwrite the first at their out-of-the-box isolation level?

Choose one answer

None of the mainstream ones. Preventing this is what transactions are for, and both transactions were committed.Only the ones running read committed. Repeatable read exists precisely to stop a value changing underneath you.Postgres at its default, MySQL at its default, and SQL Server at its default. Only Postgres turns it into an error if you raise one level.Only those without multi-version concurrency control. Snapshot-based engines give each transaction a consistent view, which is enough.

Lock in answer

Two anomalies that look like one bug

Lost update. Two transactions read the same row, both compute from what they read, both write. One result survives. This is the case above, and it needs no exotic schema: a counter, a balance, a JSON document read and rewritten, a tag list fetched and saved, an inventory count.

Write skew. Two transactions read a set of rows, each checks a condition over that set, each finds the condition satisfied, and each writes a different row. Nothing is overwritten and the invariant is still broken. The textbook case is on-call: a rule says at least one doctor must remain on shift, two doctors each check that the other is on shift, and both successfully book themselves off. There is no single row for the two transactions to fight over, which is why row locks do not help and why this one survives even snapshot isolation.

The distinction matters because it decides which fixes can work. Lost update is a conflict on a row and can be fixed by making the row the point of contention. Write skew is a conflict on a predicate, and the only fixes are to materialise it into something lockable or to make the database detect it.

What the defaults actually are

Postgres defaults to read committed, where each statement sees a snapshot taken at the start of that statement. Lost update is possible across two statements. Raising a transaction to repeatable read gives it one snapshot for its whole duration, and Postgres implements first-updater-wins: the second transaction to touch a row updated since its snapshot is aborted with a serialisation failure rather than allowed to overwrite. So repeatable read does fix lost update in Postgres, at the price that every write path now needs a retry loop. Write skew survives repeatable read and is caught only by serializable, which uses predicate-level tracking and will abort transactions that have no row in common.

MySQL InnoDB defaults to repeatable read, and that level does not mean what the Postgres name implies. Plain reads come from a consistent snapshot, but an UPDATE, DELETE or SELECT ... FOR UPDATE performs a current read: it sees the latest committed version, not your snapshot, and it waits for any lock on it rather than aborting. The practical consequence is that InnoDB at its default allows the lost update in the opening scenario and gives you no serialisation error to retry on. What InnoDB's repeatable read does buy, which Postgres repeatable read does not, is gap locking on locking reads, which blocks the phantom insert that produces one family of write skew.

SQL Server defaults to read committed implemented with locks, which means readers block writers and writers block readers, and lost update is possible across a read and a later write. The database option READ_COMMITTED_SNAPSHOT switches that to a non-blocking snapshot version, and the two products ship it differently: an on-premises SQL Server instance has it off by default, while a database created in Azure SQL Database has it on. The same application, the same isolation level by name, two different concurrency behaviours depending on where it runs — and the migration from one to the other is the moment teams discover which one their code assumed.

What would you do

A Postgres primary at its default isolation level. An accounts table with a balance column, and a service that reads the balance, applies business rules in the application, and writes the new total. Peak is about nine hundred write transactions a minute, almost all against distinct accounts.

The business rules include a call to a fraud service that takes a few hundred milliseconds. There are eleven call sites that write a balance. One engineer has two weeks.

Optimising for

Two concurrent requests crediting the same account can never produce a balance other than the sum of the credits that returned success, with no lock held across a network call to another service, and with a failure path a reviewer can point at in the diff.

You cannot know

• How often two writes to the same account genuinely collide, which nobody has instrumented
• Whether the eleven call sites all go through one data-access layer or have drifted apart
• Whether any of the business rules need the pre-credit balance rather than the post-credit one
• How the client behaves on a 409 or a 500, and whether it retries automatically

Choose an option

Set the whole service to SERIALIZABLE and add a retry wrapper around every transaction.Take SELECT ... FOR UPDATE on the account row at the top of the transaction, keeping everything else as it is.Move the arithmetic into the database: one UPDATE accounts SET balance = balance + $1 WHERE id = $2, with a CHECK constraint forbidding a negative balance, and do the fraud call before the transaction opens.Take a per-account distributed lock in Redis around the whole operation, with a timeout.

Check

Your booking system must never double-book a room for overlapping dates. Two requests arrive for overlapping ranges on an empty room. What stops them?

Choose one answer

A SELECT FOR UPDATE over the room's existing bookings before inserting.Repeatable read, so each transaction sees a stable view of the bookings table.A Postgres exclusion constraint on the room and the date range, which makes the overlap unrepresentable.Serializable isolation, which is the only level that catches write skew.

Check answer

When none of this applies

Everything above is about concurrency, so it all goes quiet when there is no concurrency. If every write to a given row goes through one single-threaded consumer — one partition of a log consumed in order, one worker, one queue with a concurrency of one — then there is no interleaving to protect against, and the isolation level stops mattering for that path. Some systems are built this way on purpose, and it is a real answer rather than a dodge.

It is also a narrow answer, and the boundary is worth naming before you rely on it. Serialising all writes through one consumer caps your write throughput at what one consumer can do, and recovers from a stalled consumer at the speed of the backlog. It also tends to be true of one table rather than of a system: the moment a second code path writes that row directly, in a migration, an admin tool or a backfill script, every guarantee you were relying on is gone and nothing in the schema records that it ever existed.

Which is the argument for putting the invariant in the database even when your architecture makes it unnecessary. Constraints survive the next architecture.

›Reading your own default rather than believing this pageOptional

Three queries, and they take less time than the argument.

In Postgres, SHOW default_transaction_isolation; tells you the server default, and SELECT current_setting('transaction_isolation'); inside a transaction tells you what the connection actually got — these differ more often than you would expect, because pooling middleware, ORM configuration and framework defaults all set it. In MySQL, SELECT @@global.transaction_isolation, @@session.transaction_isolation; gives you both. In SQL Server, SELECT is_read_committed_snapshot_on FROM sys.databases WHERE name = DB_NAME(); answers the question this lesson flagged as the one that varies by hosting.

Then check the layer above. A framework that opens a transaction per request, a pooler in transaction mode, and a read-replica router each have their own opinion, and the level your code runs at is the one that survives all three.

Worth remembering

• Atomicity is not isolation: two correct atomic transactions can produce a wrong total.
• Lost update is a conflict on a row; write skew is a conflict on a predicate.
• Postgres defaults to read committed, where read-modify-write can lose an update.
• Postgres repeatable read aborts the second updater, so it needs retry loops everywhere.
• Write skew survives snapshot isolation and is caught only by serializable.
• MySQL InnoDB defaults to repeatable read, and its writes do a current read, not a snapshot read.
• InnoDB allows the lost update at its default and raises no serialisation error to retry on.
• SQL Server ships READ_COMMITTED_SNAPSHOT off; Azure SQL Database ships it on.
• The usual fix is an atomic UPDATE with a delta, not a stronger isolation level.
• An exclusion constraint makes an overlap unrepresentable and cannot be forgotten at a call site.