Prevent overselling: test two reservations for the last item
Test two competing reservations for the last item with a conditional database update, a transaction and an executed SQLite race fixture.
A stock counter showing one available item does not prove that two simultaneous checkouts cannot both buy it. The useful test is a race: let two independent database connections attempt the last reservation, then inspect both outcomes and the committed records.
This guide builds a small SQLite fixture around that invariant. It is a synthetic storage test, not a load test or a claim that a live Omnistu marketplace has passed concurrency testing.
Put the availability condition inside the write
A fragile flow reads stock, decides in application code that one unit remains, then writes a new balance. Two clients can make that decision from the same old value. A transaction around unrelated statements does not automatically make an earlier application decision safe.
The fixture instead uses a conditional update:
UPDATE stock
SET available = available - 1
WHERE sku = ? AND available >= 1;
For this one-unit operation, an affected-row count of one means the reservation acquired stock. Zero means it did not. The application must check that result before recording a successful reservation. It must not return success merely because SQL execution raised no exception.
The test table also declares available INTEGER NOT NULL CHECK(available >= 0). That is a useful last-line constraint, not a substitute for the conditional update or correct user-facing conflict handling.
Keep the reservation and decrement together
Once stock is acquired, insert the reservation record in the same database transaction. If that insert fails, the decrement must roll back. Otherwise inventory can disappear without a corresponding order.
Here is the worker used by the accompanying fixture:
def reserve(path, gate, request_id):
with closing(sqlite3.connect(path, timeout=10)) as db:
gate.wait()
with db:
changed = db.execute(
"UPDATE stock SET available=available-1 "
"WHERE sku=? AND available>=1", ("sample",)
).rowcount
if changed:
db.execute("INSERT INTO reservations VALUES (?)", (request_id,))
return changed == 1
The script imports closing from contextlib. Each worker creates and explicitly closes its own connection. The barrier makes both workers ready before either attempts the write; it does not require an impossible guarantee that both processors execute an instruction at precisely the same instant. Python's connection context manager commits on normal exit and rolls back on an exception. In Python 3.10 it does not close the connection, so long-lived application code should explicitly manage closure as well.
SQLite permits one simultaneous writer. That matters when interpreting the result: this fixture exercises SQLite's write behavior and this SQL operation, not the lock semantics of another database.
Assert the committed invariant, not a preferred winner
The fixture creates a fresh temporary database with one stock row and no reservations. Two thread-pool tasks submit distinct request IDs. It does not care which task wins. It requires:
- Exactly one worker returns success.
- Exactly one worker returns refusal.
- The stored quantity is zero.
- There is exactly one reservation row.
The script ran 20 independent races on Python 3.10.12 and SQLite 3.37.2. Every round satisfied those assertions. The databases were temporary; the test made no HTTP requests, sent no messages and charged no money.
Repeated success is useful regression evidence, but 20 races are not proof against every schedule. The conditional write and transaction boundary are the design controls. The test provides a concrete way to catch changes that violate them.
What this example deliberately does not solve
The fixture reserves a single SKU and one unit. A real basket might require several items, warehouse allocation, quantities greater than one, payment authorization and a reservation expiry policy. Keep multi-item allocation all-or-nothing according to the product contract, and decide how conflicting operations acquire resources.
The request ID column is unique, but this example does not implement a complete idempotency API. Retrying an accepted request should recover the original result rather than report a new purchase or silently reserve another unit. Store and compare the operation's intended parameters under a defined idempotency contract. A duplicate key with different contents needs a conflict policy.
Database busy or timeout errors are not the same as an out-of-stock result. This test lets such errors fail the run. Production code needs a bounded retry or failure response appropriate to its database, with logging that does not expose customer data.
Add failure and API-level tests before launch
Force the reservation insert to fail and assert that stock returns to one. Try a second purchase after the winning commit and require a refusal. Test an absent SKU separately if the API distinguishes missing products from unavailable stock.
Then run the equivalent invariant against the actual production database engine in a controlled test environment. Launch two independent API requests, inspect their documented responses and read the committed order and inventory records. Browser button disabling can improve the interface, but it cannot replace the server-side rule.
Payment behavior needs separate test-mode cases: a failed authorization, duplicate payment notice, expired hold and delayed confirmation must not create an extra sale. Do not exercise those checks with real charges merely to validate a stock counter.
The acceptance question is simple: when two customers compete for the last unit, can the team demonstrate one accepted reservation and a truthful refusal, with matching database evidence?
Sources and method
- SQLite transaction documentation: simultaneous readers, the single-writer boundary and busy behavior.
- SQLite UPDATE documentation: conditional update semantics.
- Python 3.10 sqlite3 documentation: affected-row counts, connection timeout and transaction context management.
Written with AI assistance. The included two-connection fixture was executed locally. No production system, customer data, benchmark or traffic claim is implied.