Concurrency Belongs at the Write, Not at the Read

Mostafa Dhouib·11 min read

Concurrency Belongs at the Write, Not at the Read

The short answer. Two requests arrive at the same moment against a balance of two days, each asking for two days. Both read the balance, both see two available, both decide there is enough, both write. The balance is now minus two. This is not a race that requires unlucky timing on an unusual database. It is the default behaviour of read committed isolation, which is what most systems run, and snapshot isolation does not prevent it either when the two transactions touch different rows. Validating availability at read time cannot fix it, because the read is a view of the past by the time the write happens. The constraint has to be enforced at the write, by a uniqueness constraint, a conditional update, explicit row locking, or serializable isolation with a retry.

The code looks correct. Fetch the balance, compare it to the requested amount, return an error if there is not enough, otherwise record the request. Every reviewer reads that and approves it.

It is wrong, and no amount of reading the function will reveal it, because the defect is not in the function. It is in the assumption that nothing else is running.

What actually happens

Two transactions, both doing the obvious thing.

-- transaction A                      -- transaction B
BEGIN                                 BEGIN
SELECT allocated - used - pending     SELECT allocated - used - pending
  FROM balances WHERE emp = 7           FROM balances WHERE emp = 7
-- returns 2                          -- returns 2
-- 2 >= 2, proceed                    -- 2 >= 2, proceed
INSERT INTO entries (...) VALUES      INSERT INTO entries (...) VALUES
  (7, 'pending', 2)                     (7, 'pending', 2)
COMMIT                                COMMIT

Four days of pending leave against an allocation of two. Neither transaction did anything invalid by its own lights. Each read a true value, applied the rule correctly, and committed.

The gap is the interval between the read and the write. In that interval the premise of the decision stopped being true, and nothing told either transaction so. The check did not fail. It was answered honestly and then went stale.

The isolation levels, accurately

Precision matters here, because the common advice to use transactions does not address this at all. Both transactions above were in transactions.

Read committed. Each statement sees a snapshot taken at the moment that statement begins. It prevents dirty reads, meaning you never see another transaction's uncommitted work. It prevents nothing else. Two statements in the same transaction can return different results. A value you read can be changed by somebody else before you write. This is the default in Postgres and in several other engines, which means it is what most applications are running whether or not anyone chose it.

Repeatable read. The transaction sees one snapshot for its whole duration. It prevents non-repeatable reads and, in Postgres, phantom reads as well, because Postgres implements this level as snapshot isolation rather than as the standard's lock-based definition. It also makes a transaction fail rather than silently lose an update when two transactions update the same row. That last part is the important qualifier, because it only applies when the two transactions write the same row. In the example above they insert different rows. Neither sees the other, neither conflicts, both commit. The anomaly has a name in the literature, write skew, and snapshot isolation is specifically known not to prevent it.

MySQL's InnoDB defaults to repeatable read and has the same hole for the same reason. A plain select is a consistent read against the transaction's snapshot and takes no locks, so two sessions can both read the old balance.

Serializable. In Postgres this is serializable snapshot isolation, which tracks read and write dependencies between concurrent transactions and aborts one when the interleaving could not have arisen from running them one after another. It does catch write skew. The price is that transactions now fail with a serialisation error, SQLSTATE 40001, under contention, and the application must catch that and retry the whole transaction. Code that does not retry has traded a silent wrong balance for a visible intermittent error, which is better but is not finished.

So the summary is short. Read committed does not help. Repeatable read does not help for inserts against a shared constraint. Serializable helps and obliges you to write retry logic.

Move the decision to the write

The reliable approaches all have one property in common. The check and the write become a single atomic operation at the storage layer, so there is no interval for the premise to go stale in.

Conditional update. Put the predicate into the update statement and let the database evaluate it under the row lock it is already taking.

UPDATE balances
   SET pending = pending + 2
 WHERE emp = 7
   AND allocated - used - pending >= 2

If this affects zero rows, there was not enough, and the application returns the insufficient-balance error. One statement, one lock, no window. The second of two concurrent requests will see the first one's committed effect because an update re-reads the row under lock even in read committed. This is the smallest change that works and it is usually the right first move.

Explicit locking. Take the row lock before reading, so the two transactions serialise against each other.

SELECT allocated - used - pending
  FROM balances
 WHERE emp = 7
   FOR UPDATE

The second transaction blocks until the first commits, then reads the updated value and correctly refuses. This works with an entry-based ledger too, where there is no balance row to lock, as long as there is an account row to lock instead. It costs you a serialisation point per account, which for a per-employee balance is fine and for a single shared pool is a bottleneck.

A constraint the database enforces. If a snapshot row holds the derived figures, a check constraint on it refuses to persist a negative balance regardless of which code path got it wrong. This is the only option that also protects you from the next feature somebody adds in two years without reading this code.

A uniqueness constraint for the duplicate-intent case. Double submission is the same bug with a different shape. A manager taps approve twice and the transition runs twice. A unique index on the transition, for example on the pair of request identifier and target state, makes the second attempt fail on insert rather than succeed. The constraint is the mechanism. Checking whether the transition already happened, then performing it, is the same stale-read pattern in new clothing.

Serializable with retry. Correct and general, and the one to reach for when the invariant spans several rows in ways a single conditional update cannot express. Budget for the retry loop and for the contention.

These compose. In practice I use a conditional update or a lock on the hot path and a check constraint as the backstop, since the backstop protects the invariant from future code rather than from this code.

The test that reproduces it

A concurrency bug that cannot be reproduced on demand will be argued about instead of fixed. The test is not difficult, and it belongs in the suite permanently rather than being run once during the investigation.

The structure is two real database connections, not two threads sharing one, with a barrier between the read and the write.

Set the balance to two days. On connection one, begin and read the balance. On connection two, begin and read the balance. Then, and only then, let connection one write and commit, then connection two. Assert that exactly one succeeded and that the final balance is zero rather than negative.

Two details decide whether the test is worth having.

The barrier has to be explicit. A sleep makes the test slow and flaky, and the interleaving you want is a specific one rather than a random one. Use a synchronisation primitive the test controls, so the ordering is deterministic and the test fails every time on broken code rather than one run in twenty.

The connections have to be separate sessions at the same isolation level the application uses. A test that runs at serializable against an application that runs at read committed proves nothing about production. If the application retries on serialisation failure, the test should exercise the retry as well, because an unretried 40001 reaching a user is still a defect.

Run the same test against each remedy. The conditional update version passes. The read-then-write version fails. That difference, visible in the suite, is what stops the pattern coming back.

The honest limit

Everything above is about a single database. The moment the invariant spans two systems, a balance in one service and a commitment in another, none of these mechanisms apply, because there is no shared lock and no shared constraint. That problem needs a different treatment, and the honest version of it accepts temporary inconsistency with a reconciliation process that detects and repairs it rather than pretending a distributed transaction will hold.

The remedies also have costs that are easy to understate. Row locking serialises traffic per account and queues under load if the hot account is shared. Serializable isolation produces aborts that scale with contention, and a retry loop around a transaction that sends an email or calls a payment provider is a new bug rather than a fix, because the side effect does not roll back.

And a caveat on the levels themselves. The names are standard but the behaviour behind them is not uniform across engines. Postgres implements repeatable read as snapshot isolation, MySQL's version of the same name behaves differently again, and some engines offer a level called snapshot isolation alongside both. Read your engine's documentation for the level you actually run rather than trusting the name, because the anomaly that matters here sits exactly in the gap between the standard's wording and each implementation's behaviour.

FAQ

How can two requests both pass a balance check and still overdraw the balance? Each transaction reads the balance, finds it sufficient, and writes, and there is an interval between the read and the write in which the other transaction commits. Under read committed each statement sees a fresh snapshot, so the read was true when it happened and stale by the time the write landed. Neither transaction did anything invalid, and the balance ends up negative.

Does repeatable read isolation prevent this? Not in the general case. It gives the transaction a single consistent snapshot and it does make two transactions updating the same row conflict. When each transaction inserts its own row instead, as a ledger of entries does, there is no shared row to conflict on, neither sees the other, and both commit. That anomaly is write skew, and snapshot isolation is known not to prevent it.

What does serializable isolation cost? Correctness at the cost of aborts. Postgres tracks dependencies between concurrent transactions and aborts one when the interleaving could not have come from running them sequentially, raising SQLSTATE 40001. The application has to catch that and retry the entire transaction. Without a retry loop you have replaced a silent wrong balance with an intermittent user-visible error, and a retry around a transaction containing an external side effect introduces a different bug.

What is the smallest change that actually fixes it? Move the predicate into the update statement. An update that increments pending only where the derived available figure is still sufficient is evaluated by the database under the row lock it already takes, so there is no window. Zero rows affected means insufficient balance. A single statement replaces a read, a decision and a write.

How do you stop a double submission from applying twice? With a uniqueness constraint rather than a check. A unique index on the transition, such as the request identifier paired with the target state, makes the second attempt fail at insert time. Querying whether the transition has already occurred and then performing it is the same stale-read pattern that caused the original problem.

What does a good reproduction test look like? Two separate database connections at the isolation level the application really uses, with an explicit synchronisation barrier rather than a sleep. Both read the balance, then one writes and commits, then the other writes and commits. Assert that exactly one succeeded and that the final balance is not negative. Deterministic ordering is what makes it fail every time on broken code instead of occasionally.