The Balance Was Enough — Both Tellers Just Read the Same Number

In one line

If the balance check and the balance deduction are split into two statements, another request slips in between and the same balance is used as the basis to approve twice. The fix is to push the check into the update statement and to judge approval not by "the value I read" but by "the number of rows actually changed."

Why this was needed

One of the sentences most often written in incident reports is "the balance went negative." When you open the code, it usually looks like this. First it reads the balance with SELECT balance, then the Python or Java side judges with if balance >= amount, and then it runs UPDATE account SET balance = balance - amount. In a test environment where only one request comes in at a time, it is fine forever.

The problem is the moment requests overlap on the same account. If a payday automatic transfer and the customer's app withdrawal arrive within the same 100 milliseconds, both do the SELECT first, and both judge that the balance is sufficient. After that, the two UPDATEs go out in turn. Both were approved, both were written in the ledger, and the balance goes negative. The interesting point here is that the ledger and the balance do not diverge from each other. This is because both deductions were reflected properly. So a closing check that looks only at "ledger total == balance" lets this incident pass quietly.

This kind of defect lingers for a long time because it is hard to reproduce. The suggestion "try putting load on it" comes up often, but load is probability. With bad luck it does not occur even after an hour of running, and even if it occurs once by luck, there is no guarantee it will occur again on the next run. If reproduction is probabilistic, you can only talk about whether you fixed it in terms of probability too.

How it works

First, how to make reproduction deterministic. Instead of increasing load, you line up the overlapping point. If you put in one barrier so that two requests proceed in the order "both write only after both have read," they always read the same balance in every round. With forty requests, it takes under a second and gives the same result every run. Only when reproduction is deterministic does the claim that you fixed it have a basis.

The fix is one statement.

UPDATE account SET balance = balance - :amt
 WHERE acct_id = :acct AND balance >= :amt;

If the row this statement changed is 1, approve; if 0, reject. The number of changed rows is read with changes() in SQLite, and with the cursor's rowcount in Python's sqlite3 module. What matters here is that we do not use the balance we read earlier for the judgment. The value read is used only for records and explanation, and the database decides approval.

A daily cumulative limit attaches in the same shape. If you keep the cumulative amount in a separate cell, the limit lies from the moment that cell and the ledger diverge, so you put in the condition a subquery that counts that day's withdrawals from the ledger.

UPDATE account SET balance = balance - :amt
 WHERE acct_id = :acct AND balance >= :amt
   AND (SELECT COALESCE(-SUM(amount),0) FROM ledger
         WHERE acct_id = :acct AND biz_date = :d AND amount < 0) + :amt <= daily_limit;

Next come transactions and locks. SQLite's BEGIN documentation stipulates that many read transactions can be open at the same time but only one write transaction can be open at a time. And it spells out one pitfall. If you start with the default BEGIN DEFERRED and do a SELECT first, a read transaction is opened, and the write statement after that tries to promote the read to a write, and if another connection is already modifying the database, the promotion is impossible and it fails with SQLITE_BUSY. BEGIN IMMEDIATE opens a write transaction from the start and eliminates this promotion itself. So a transaction that "reads and then writes" is safest opened as IMMEDIATE from the start.

Waiting is handled by PRAGMA busy_timeout. When a lock is held, instead of failing immediately, it sleeps for the specified milliseconds and tries again. The default is 0, so it waits for nothing. How locks rise and fall by stage is written out stage by stage in the file locking and concurrency document, and which error codes come out in which situations is organized by the result code document.

The last is retry. You apply retries only to failures that are fine to try again. Lock contention clears with time, so trying again is fine, while insufficient balance or an exceeded limit gives the same answer no matter how many times you try again, so you must not apply retries. Unbounded retry enlarges an outage. You write down the limit, the wait interval, and which failures it applies to as policy, and leave the number of attempts in the record.

What it looks like in the field

First, the misunderstanding that "we wrapped it in a transaction so it is fine." A transaction gives atomicity; it does not stop another request from changing the same row in between. The isolation level and locks do that job, and putting the condition inside the update statement is the cheapest way to get the same effect.

Second, the case where retry enlarges an incident. A team put unbounded retry on every database error, and the moment one lock got long, waiting requests piled up and the number of connections hit the limit. Retry only postpones failure; it does not reduce it. If the limit and interval are not in the policy, that retry is a bomb.

Third, the case of having only one check item. A closing check that looks only at whether the ledger total and the balance match lets this incident pass. Only if you set up negative balance and limit exceedance separately will it be caught. Put several checks side by side, and leave both what was looked at and the observed values.

What really matters in practice

• The judgment is made by the update statement, not by the application. The value read is used only for explanation and records.
• Make reproduction by overlap, not by load. A probabilistic reproduction cannot serve as evidence that you fixed it.
• Open a transaction that reads and then writes with BEGIN IMMEDIATE. A promotion does not wait for you.
• Retry only on lock contention, and with a limit. Never apply it to a business judgment.
• Put several invariants side by side in checks. If you look at only one, there will be incidents that pass.

What you will do in the next lab

You build a snapshot of six accounts yourself and make a negative balance in a reproducible shape through the read-modify-write path. Then you block the same overlap with a conditional update, and experience write locks and busy_timeout firsthand. You put the daily cumulative limit inside the same statement, attach a bounded retry, and then check the six accounts against three invariants and close with an evidence report.