An inventory reservation can appear to work until two customers want the last item. Both read “one available”, both proceed to payment, and one discovers later that there was never enough stock for both orders.
The mistake is separating the availability check from the allocation. Let’s make those one database decision, then deal with payment and reservation expiry.
Allocate while checking availability
Don’t read quantity into JavaScript, compare it, then write a new quantity in a later statement. Another request can make the same decision between those steps.
Use a conditional update that succeeds only when enough stock remains. Check the number of changed rows. A zero-row result is an ordinary unavailable outcome, not a reason to retry until the condition becomes true.
The reservation row and stock change should commit together. If inserting the reservation fails, the stock deduction must roll back as well. Database transaction semantics, rather than a local application mutex, establish that relationship.
Give the reservation a stable identity
Use a unique reservation or checkout operation ID. If the customer retries the same checkout request, return the existing reservation after verifying that the item and quantity match.
Don’t accept the same ID with different quantities. That hides a change of intent inside what should be a retry. A changed cart needs a new proposal and an explicit reservation adjustment policy.
Keep reserved stock distinct from fulfilled stock. Your inventory model should make it possible to explain on-hand quantity, committed allocations and available-to-sell quantity without counting the same item twice.
Build the atomic update
This example uses SQLite’s actual transaction and conditional-update behaviour. It models two attempts to reserve one unit and then safely releases the successful reservation.
import assert from 'node:assert/strict';
import { DatabaseSync } from 'node:sqlite';
const db = new DatabaseSync(':memory:');
db.exec(`CREATE TABLE stock(sku TEXT PRIMARY KEY, available INTEGER CHECK(available>=0));
CREATE TABLE holds(id TEXT PRIMARY KEY,sku TEXT,quantity INTEGER,state TEXT);
INSERT INTO stock VALUES('v1',1);`);
function reserve(id, sku, quantity) {
if(!Number.isSafeInteger(quantity)||quantity<1) throw new Error('Invalid quantity');
db.exec('BEGIN IMMEDIATE');
try {
const old=db.prepare('SELECT * FROM holds WHERE id=?').get(id);
if(old) { if(old.sku!==sku||old.quantity!==quantity)throw new Error('Intent mismatch');db.exec('COMMIT');return old.state; }
const r=db.prepare('UPDATE stock SET available=available-? WHERE sku=? AND available>=?').run(quantity,sku,quantity);
if(!r.changes){db.exec('ROLLBACK');return 'unavailable';}
db.prepare("INSERT INTO holds VALUES(?,?,?,'held')").run(id,sku,quantity);
db.exec('COMMIT');return 'held';
}catch(e){db.exec('ROLLBACK');throw e;}
}
function release(id) {
db.exec('BEGIN IMMEDIATE');
try {
const h=db.prepare("UPDATE holds SET state='released' WHERE id=? AND state='held' RETURNING *").get(id);
if(h)db.prepare('UPDATE stock SET available=available+? WHERE sku=?').run(h.quantity,h.sku);
db.exec('COMMIT');
}catch(e){db.exec('ROLLBACK');throw e;}
}
assert.equal(reserve('a','v1',1),'held');
assert.equal(reserve('a','v1',1),'held');
assert.equal(reserve('b','v1',1),'unavailable');
release('a');release('a');
assert.equal(db.prepare('SELECT available FROM stock').get().available,1);
db.close();console.log('PASS: conditional allocation, retry identity and one-time release');
The second attempt is rejected because the first transaction already allocated the unit. Releasing the same reservation again doesn’t add another unit back.
These calls run in one local process. They test the transaction logic, not multi-host concurrency or lock behaviour under production load. Run competing connections against your chosen deployment database before relying on its capacity characteristics.
Expire reservations without racing payment
A reservation needs an expiry time, but expiration and payment confirmation can arrive together. Make their transitions conditional on the current reservation state.
For example, change held to expired and restore stock in one transaction. Payment handling can change held to committed. Only one transition should win. If payment arrives after expiry, route to an explicit policy: attempt a new allocation, offer an alternative or arrange a refund.
Don’t keep the stock row locked while the customer completes payment. That can take minutes and would block unrelated activity. Use durable reservation state and short transactions instead.
PostgreSQL documents how concurrent transactions interact at different isolation levels. Test the exact statements and isolation mode you deploy, including deadlock or serialization retries where applicable. Transaction isolation reference.
Keep quantity and currency out of model discretion
An AI shopping assistant can interpret “two pairs”, but the reservation service still validates a positive integer quantity, the chosen variant and the current quote. Use the source catalogue identity rather than the model’s product name.
The model should receive a structured result such as reserved, unavailable or requote. Customer-facing wording can then match what happened. “I found it” and “I’ve reserved it” are different claims.
If reservation creation succeeds but the response is lost, retrieve the original reservation by operation ID. Don’t generate a new checkout ID just because the first HTTP attempt timed out.
Record adjustments so you can reconcile
Keep reservation history or inventory ledger entries for allocation, commitment, release and correction. A mutable available quantity alone is difficult to investigate after an incident.
Reconcile reservations against order and payment states. Alert on held reservations that are far past expiry, paid orders without committed stock and stock quantities that violate your invariants.
Manual inventory changes need a policy too. Reducing on-hand stock below already committed allocations should create an exception to resolve, not silently cancel a customer’s paid order.
Test the race you actually fear
Send more checkout attempts than available units using separate database connections. Assert that total committed and held allocations never exceed the supported stock model. Then repeat requests with the same operation ID.
Race expiry against payment, crash between statements and retry release twice. Check both the reservation state and inventory quantity after recovery.
Start with the conditional stock update and a reservation row in one transaction. That closes the check-then-write gap before you add the more complicated payment lifecycle around it.
Run the example and adapt it
Save the code as 15-atomic-stock-reservations.mjs and run it with Node.js 24 or later:
node 15-atomic-stock-reservations.mjs
The code above includes assertions you can run locally. It tests the local decision logic with controlled inputs; it doesn’t connect to a live provider or establish production performance. Keep that boundary when adapting it to your application.
