Three Layers of Defense Against Overselling: Concurrency Control in a Seat Allocation System
Yuwei's inventory safety design: row locks, optimistic locking, and database constraints -- all three are essential.
Yuwei is a seat allocation SaaS for local tour operators. In this business, the one accident you absolutely cannot have is overselling: two seats left on a departure, two dispatchers place orders at the same time, both succeed – and the next day there are two extra people on the bus with no seats.
This post is about how Yuwei shuts that door completely at the database level. Conclusion up front: one layer of defense isn’t enough. You need three.
Layer one: row locks inside a transaction
The critical path for placing an order is “read inventory, check if there’s enough, deduct.” Any concurrency window between those three steps can cause a double sale. The most direct fix is to lock the departure’s inventory row inside a transaction with SELECT ... FOR UPDATE:
BEGIN;
SELECT remaining FROM trip_inventory WHERE trip_id = $1 FOR UPDATE;
-- app layer checks remaining >= party size
UPDATE trip_inventory SET remaining = remaining - $2 WHERE trip_id = $1;
COMMIT;Orders for the same departure are serialized, first come first served. Later transactions wait on the lock, and by the time they acquire it, they read the already-deducted number.
Layer two: optimistic locking
Row locks only protect code that goes through that path. As the system grows, there are always other write entry points – bulk imports, admin-side inventory edits, refund restocks. Add a version column to the inventory row and make every update carry a version check:
UPDATE trip_inventory
SET remaining = $new, version = version + 1
WHERE trip_id = $1 AND version = $expected;If the affected row count is 0, the data was concurrently modified – retry or raise an error, preventing writes from silently overwriting each other.
Layer three: a database CHECK constraint
The first two layers only work “if the code is written correctly.” Humans write bugs: some new endpoint forgets to take the lock, some refactor deletes the check. The last line of defense belongs as close to the data as possible:
ALTER TABLE trip_inventory ADD CONSTRAINT chk_remaining CHECK (remaining >= 0);Even if everything above fails, the UPDATE that would drive inventory negative gets rejected by the database itself. Better to show a user one error than to put one extra person on the bus.
Lessons
- Application-layer validation is user experience, not a correctness guarantee: a form hint saying “only 2 seats left” is nice, but it can’t stop concurrency.
- Correctness constraints should live on the data, not be scattered across every caller: when a new hire writes a SQL statement that bypasses the service layer, the CHECK constraint still catches it.
- Each layer owns its piece: row locks handle throughput and ordering on the main path, optimistic locking handles overwrites from side-channel writes, and CHECK handles the bug nobody saw coming.