Two customers click the last available seat at nearly the same moment. Preventing overbooking requires the database to decide which transaction wins, not two separate reads that both say yes.
Reproduce the Race Safely
Use a disposable table in a test database with one available seat. In two query windows, begin transactions and read the same inventory value before either window writes. Both sessions can see one seat. If each then inserts a booking based on that earlier read, both can succeed unless a constraint or locking design stops them. The demo is about interleaving, so run the reads first, pause, then run the writes. I ask teams to do this once with two windows. The race becomes much easier to understand than a paragraph about isolation levels.
CREATE TABLE dbo.SeatInventory
(
EventID int NOT NULL PRIMARY KEY,
SeatsLeft int NOT NULL CONSTRAINT CK_SeatInventory_Nonnegative CHECK (SeatsLeft >= 0)
);
INSERT dbo.SeatInventory(EventID, SeatsLeft) VALUES (1, 1);
SELECT SeatsLeft FROM dbo.SeatInventory WHERE EventID = 1;Let One Statement Choose the Winner
The simplest fix is a conditional UPDATE that decrements only when SeatsLeft is positive. SQL Server takes the required write locks while executing that statement. Check @@ROWCOUNT immediately. One row means the reservation succeeded; zero means no seat was available. Run the block a second time and it stops with error 50010, because the only seat is already gone. Put the inventory change and booking insert in the same transaction so a later insert failure rolls the decrement back. I prefer this pattern when inventory is represented by a counter. It is compact, testable, and avoids a separate stale read.
BEGIN TRANSACTION;
UPDATE dbo.SeatInventory
SET SeatsLeft = SeatsLeft - 1
WHERE EventID = 1 AND SeatsLeft > 0;
IF @@ROWCOUNT = 0
BEGIN
ROLLBACK TRANSACTION;
THROW 50010, 'No seat is available.', 1;
END;
COMMIT TRANSACTION;Lock the Read When Logic Needs It
Some booking rules require several checks before the write. In that case, read the inventory row with UPDLOCK and HOLDLOCK inside a short transaction. UPDLOCK reserves the intent to update, while HOLDLOCK keeps serializable semantics for the read. Use a predicate supported by an appropriate index so the lock scope is narrow. Keep user interaction outside the transaction. A transaction waiting for a person to choose a meal is a fine way to reserve the entire restaurant by accident.
I check the lock behavior with two windows, not by reading the query text and hoping. Under read committed snapshot isolation, an ordinary read sees the last committed version without waiting; the explicit locking pattern changes that behavior for this critical decision.
BEGIN TRANSACTION;
SELECT SeatsLeft
FROM dbo.SeatInventory WITH (UPDLOCK, HOLDLOCK)
WHERE EventID = 1;
-- Validate the booking rule, then update and insert before COMMIT.
ROLLBACK TRANSACTION;Preventing Overbooking With Constraints
A CHECK constraint on SeatsLeft >= 0 is a final guard against an oversold counter. It does not replace the conditional update, because a failed write still needs a clear response and transaction handling. If every seat has a distinct identifier, a unique constraint on EventID and SeatID can prevent two bookings for the same seat. Design the constraint around the true business invariant. A count constraint cannot prove that no two customers share one numbered seat.
Do not rely on an application-side check alone. Applications scale across processes, and a read from one process is stale as soon as another commits. The database is the shared point that can enforce the rule.
Preventing Overbooking Across Both Sessions
Run simultaneous attempts against one remaining seat. Verify one success, one clear failure, one inventory decrement, and one booking row. Repeat after a forced insert failure to confirm the decrement rolls back. Test a retry with the same operation ID so a duplicate request does not book twice. Network retries are common; a booking API needs an idempotency rule as well as concurrency control. I also check deadlock behavior under load and make the caller retry only the whole transaction when appropriate.
What does the losing customer see? Return a clear "sold out" response rather than a raw constraint error. The constraint protects the data, while the procedure gives the user a sensible outcome.
Separate Capacity From Individual Seats
The counter pattern works when any remaining seat is interchangeable. Numbered seats require a row-level uniqueness rule on the actual seat identifier. A theater can have one seat left in its counter while two requests ask for the same seat. I represent each seat or reservation with a unique key and enforce one active booking per seat through a constraint or carefully designed filtered unique index. A counter can still summarize availability, but it cannot be the sole source of truth for assigned seats.
For general inventory, a conditional decrement protects the quantity. For one specific resource, a unique reservation key protects the identity. I choose the invariant before writing the transaction. Otherwise, the code can be race-free for the wrong thing. The two-window test should target the same seat, not merely the same event.
Handle Cancellation and Retry
A booking request can time out after the database commits but before the application receives the response. If the caller retries, a second conditional decrement can book twice unless the operation has an idempotency key. Store a unique request ID with the booking and return the existing outcome when that ID is seen again. Keep the uniqueness check and inventory change in one transaction. I test both "two different customers race" and "one customer repeats the same request." They are different concurrency problems.
What should happen when payment fails after a seat is reserved? Decide whether the reservation expires or is explicitly released. Use a reliable state transition and an audit trail. Do not simply increment the counter in an error handler without checking whether the booking was ever committed. Compensation must be as carefully guarded as the original decrement, or the recovery path can create seats that never existed.
Preventing Overbooking Without Long Locks
Put price lookup, email sending, and payment gateway calls outside the lock-held part where possible. Decide the order of payment authorization and inventory reservation with the business owner, including release of a reservation that expires. An atomic SQL update solves the last-seat counter race; it does not design the full commerce workflow. Document timeout and compensation rules.
I watch for blocking on the inventory key after deployment. A hot event can make one row the natural serialization point. That serialization is correct, but preventing overbooking on a hot event still needs a throughput test. If contention grows, introduce a bounded reservation queue or seat-specific rows rather than weakening the invariant.
Related reading on this blog: UPDLOCK and READPAST for Queue Tables and SQL Server Deadlock: Build One With Your Own Hands.
A seat check is not a reservation, it is only a momentary observation.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
