The capacity check that let all nine through
on this page
Nine writers hit the same limited slot in a stress run, all fired at once, and all nine got on. The capacity check ran for every one of them, and so did the lock.
The function does one job: take a place on something with a fixed limit. In outline:
def book(db, slot_id, user_id):
check_room(db, slot_id) # wrong: counts and decides before the lock
db.execute("BEGIN IMMEDIATE") # lock for writing from here
db.execute("INSERT INTO booking (slot_id, user_id) VALUES (?, ?)", (slot_id, user_id))
db.execute("COMMIT")
return "booked"
check_room counts what’s already booked, compares it to the limit and refuses if the slot is full. It sat above the lock on purpose. There was a comment saying the rules ran up front, ahead of the writes, and I get why. Up front is exactly right for a rule that only looks at the input, because a malformed number is malformed whenever you look at it. Room on the slot isn’t that kind of rule. It depends on what everyone else is doing at that moment.
The comment wasn’t even wrong. After the fix the check still runs before any write. Before any write and before the lock just turned out to be different places.
The check runs on every call. The lock exists too, and it’s the right kind: BEGIN IMMEDIATE takes SQLite’s write lock the moment it runs, instead of on the first write the way a deferred transaction does, so two writers can’t both get halfway through and collide. It still did its job with the wrong order. It serialised the inserts, cleanly, for nine writers who’d each already decided there was room.
Say the slot starts empty. Writer one counts zero. Writer two counts zero. All nine do, because none of them has written yet and the count isn’t inside anything that’d make them wait for each other. Each one passes the whole check before it reaches the lock. Then they queue at the lock and insert one by one, and the lock never asks whether the decision is still true. Each decision was correct against the state it was made on. That state is stale for everyone but the first.
And no test that runs one writer at a time can show it. One writer at a time sees the previous write every time, so a solo test passes, a click-through passes, and the function reads as correct. Nothing in the test suite raced two writers through this check until the fix came with a test that did.
To be fair to the check, it wasn’t useless. A slot that’s already full still refuses everyone, because the count’s right when nobody’s mid-write. What it can’t do is share out the last places between writers who overlap.
A capacity check that runs before the write lock can’t see anyone else in the queue. Every writer who counts before the first commit gets the same number, and BEGIN IMMEDIATE only protects a read that happens inside it.
The fix moved that one call below the lock. The rest of the function stayed as it was: the same BEGIN IMMEDIATE and the same insert, with the whole check now running while the lock is held, so the number it counts can’t go stale before the write.
The test that came with it releases every writer from a barrier at the same instant, holds the gap between count and write open for 0.15 seconds so a wrong order fails every run, fails if any writer ends in a lock error, and requires everyone who didn’t get a place to be refused cleanly.
The numbers come from a separate run, and all I’ve got for it is the commit message, because that harness never made it into the repository. On a copy of the database, with the server in its production shape, the old order overbooked in 57 of 60 rounds. The new order, 0 of 60.
Those are stress numbers. Every writer was aimed at the same moment, so 57 of 60 says the order was wrong, not how often it would’ve bitten under real traffic, and I’m not claiming it ever did. A lock inside one Python process only covers that process. The database’s lock covers every writer, wherever it runs, which is why it’s the right one.
Here’s the same mistake rebuilt from nothing: standard library only, a throwaway SQLite file in WAL mode, eight threads, a limit of 3, a hundred rounds. The two functions differ in one thing, which line BEGIN IMMEDIATE is on. The right order:
def lock_then_count(db, user, window):
"""Right order: the count happens while holding the lock."""
db.execute("BEGIN IMMEDIATE")
taken = count(db)
time.sleep(window)
if taken >= LIMIT:
db.execute("ROLLBACK")
return False
db.execute("INSERT INTO booking (slot, user) VALUES (1, ?)", (user,))
db.execute("COMMIT")
return True
The wrong order is the same function with the count and the sleep moved above BEGIN IMMEDIATE. Both are in capacity_race.py. window is an optional sleep between the count and the write. I ran it at 0 and at 0.02 seconds to check the delay wasn’t manufacturing the result. The harness runs the wrong order once with the writers one after another, then both orders with all eight released from a barrier together:
python 3.12.3, sqlite 3.45.1, 8 writers, limit 3, 100 rounds
count_then_lock one at a time overbooked rounds: 0 of 100
count_then_lock window=0 overbooked rounds: 100 of 100 worst: 8 on a limit of 3 lock errors: 0
lock_then_count window=0 overbooked rounds: 0 of 100 worst: 3 on a limit of 3 lock errors: 0
count_then_lock window=0.02 overbooked rounds: 100 of 100 worst: 8 on a limit of 3 lock errors: 0
lock_then_count window=0.02 overbooked rounds: 0 of 100 worst: 3 on a limit of 3 lock errors: 0
Same output on three runs. The first result is the control: the wrong order, alone, never overbooks, which is why a one-user test can’t find this. The second is the bug, with no delay needed: all eight on a slot that holds three, and zero lock errors, because the lock did its job on writers who’d already decided. Put the count under the lock and it’s three every time.
Don’t read the demo’s 100 of 100 against the app’s 57 of 60. Different harness, different shape, and the demo is threads on one SQLite file on local disk. It shows the mechanism and no more.
Go through anything of yours that reads, decides and then writes, and ask who’s holding the lock when it reads. If nobody is, don’t argue with it. Put a barrier in front of it, release more writers than there are places, and fail the test on one extra row. A one-user test will never tell you.