Exploring race conditions, conditional updates, and row-level locking by building a ticket reservation API with Node.js, Prisma, and PostgreSQL.
0 likes
I wanted to understand what actually happens when two requests try to reserve the last available slots in an event. Not just how to prevent it, but what PostgreSQL does when those requests arrive at almost the same time.
So I built a small ticket reservation API with Node.js, Prisma and PostgreSQL to experiment with different approaches.
Here's the scenario.
Total capacity: 100
Already reserved: 90
Request A wants: 7
Request B wants: 7
Only one of these requests should succeed. There are just 10 slots left, and each request needs 7.
But without the right checks in place, both requests can succeed.
The problem with checking before updating
Let's say Request A and Request B arrive at almost the same time.
Both requests read the current capacity from the database. Both see that 90 slots have been reserved, meaning there are 10 slots left.
Since each request needs only 7, both pass the availability check.
This is where things become interesting.
Request A proceeds to make its reservation. But Request B has no knowledge of that decision yet. It already read the previous value and has made its own decision based on it.
If both requests continue without any concurrency control, the application can accept 14 reservations when only 10 slots are available.
We're now overbooked by 4.
The problem is not that either request performed an incorrect calculation. Each request made a decision using information that was valid when it read it. The problem is that the information may no longer be valid by the time the update happens.
This is a race condition, specifically a time-of-check-to-time-of-use (TOCTOU) problem.
Here's the implementation I used to demonstrate it:
const event = await prisma.eventCapacity.findUnique({
where: { id }
});
if (!event || event.remainingCapacity < quantity) {
throw new Error("Insufficient capacity");
}
const updated = await prisma.eventCapacity.update({
where: { id },
data: {
remainingCapacity: event.remainingCapacity - quantity
}
});The read, availability check and update are separate operations. Another request can get in between them.
There's also a lost-update problem here. Both requests can calculate a new value from the same old value and write that value back, effectively overwriting one another's work.
Using a transaction and row locking
One way to handle this is to make the reservation process wait its turn.
PostgreSQL provides row-level locking through SELECT ... FOR UPDATE. It allows a transaction to lock a particular row so that other transactions attempting conflicting updates or locks on that row have to wait.
I implemented it using a Prisma interactive transaction:
const event = await prisma.$transaction(async (tx) => {
const [event] = await tx.$queryRaw`
SELECT remaining_capacity
FROM event_capacities
WHERE id = ${id}
FOR UPDATE
`;
if (!event || event.remaining_capacity < quantity) {
throw new Error("Insufficient capacity");
}
return tx.eventCapacity.update({
where: { id },
data: {
remainingCapacity: {
decrement: quantity
}
}
});The sequence is straightforward:
Start a transaction.
Lock the event's capacity row.
Read the available capacity.
Check whether the requested quantity can be reserved.
Decrement the capacity.
Commit the transaction.
If Request A gets the lock first, Request B has to wait until A commits or rolls back.
Once A commits, B can proceed and read the updated capacity. It sees 3 remaining slots, not the original 10, so its request for 7 is rejected.
This protects the reservation from concurrent requests.
There is a trade-off, though.
The lock is held until the transaction finishes. While a request waits for another transaction holding the same row lock, its database connection remains occupied. Under heavy contention, many waiting requests can consume available connections from the pool.
That doesn't mean row locking is inherently bad. It means transaction duration and contention matter. You don't want to hold a lock while performing unrelated work, making external API calls or waiting on a slow network request.
Updating capacity directly in the database
Another approach is to avoid the separate read and update altogether.
Instead of reading the capacity into the application, checking it there and then sending another query to update it, we can express the condition and the update in a single SQL statement.
UPDATE event_capacities
SET remaining_capacity = remaining_capacity - 7
WHERE id = 1
AND remaining_capacity >= 7
RETURNING *;This is the conditional update approach.
The database checks whether enough capacity remains and performs the decrement as part of the same statement.
If two requests arrive together:
The first request updates 10 to 3.
The second request waits for the conflicting update.
PostgreSQL rechecks the WHERE condition against the updated row.
Since 3 is less than 7, the second request updates no rows.
Only one reservation succeeds.
This is important because PostgreSQL's default READ COMMITTED isolation level does not simply let the second update proceed using an outdated version of the row. When it encounters a concurrent update, it waits and re-evaluates the search condition against the updated row version.
I also experimented with Prisma's atomic decrement operation:
const updated = await prisma.eventCapacity.update({
where: { id },
data: {
remainingCapacity: {
decrement: quantity
}
}
});One thing to be careful about here: an atomic decrement by itself does not guarantee that capacity cannot become negative.
For that, I added a database constraint:
ALTER TABLE event_capacities
ADD CONSTRAINT chk_positive_capacity
CHECK (remaining_capacity >= 0);The constraint gives the database responsibility for enforcing that invariant, even if another part of the application attempts an invalid update.
The conditional SQL update and the database constraint provide complementary protection: the conditional update handles insufficient capacity as a normal rejected reservation, while the constraint protects the data from invalid values regardless of which application path attempts the write.
So which approach should you use?
I don't think one approach should automatically replace the other.
A conditional update is a good fit when the business rule can be expressed directly in the update statement. It is simple, avoids an extra read, and reduces the amount of application-level coordination required.
Row locking is useful when the decision involves reading and checking data before performing several related operations. You can lock the relevant row, make the decision and apply the changes within one transaction.
For example, if reserving a ticket also requires inserting a reservation record, both operations should succeed or fail together.
That is where transaction atomicity becomes important.
await prisma.$transaction(async (tx) => {
// Check and update capacity.
// Insert the reservation record.
// Both operations commit together.
});If the reservation insert fails, the capacity update must also roll back.
A transaction is not just about making requests wait. It ensures that a group of related database operations is treated as one unit.
There is no need to introduce explicit locking everywhere. Sometimes a conditional update and a database constraint are enough. Other workflows need transactions and more deliberate concurrency control.
The important thing is understanding what the operation needs to guarantee before choosing the implementation.
What I took away
The biggest lesson for me was that checking a value before updating it doesn't guarantee that the value will still be valid when the update happens.
Concurrency is not something we can solve by hoping requests arrive one after another. Two requests can execute independently, and our database operations need to account for that.
I also came away with a better appreciation for the different responsibilities involved:
Atomic updates prevent application-level read-modify-write races.
Database constraints protect important data invariants.
Transactions make related database operations succeed or fail together.
Row-level locks help coordinate workflows that require a read, decision and subsequent write.
I'm still interested in exploring how these approaches behave under higher contention, especially when a large number of requests compete for a small amount of available capacity.
That's something I want to test further rather than assume from a small local experiment.
Found this useful? Pass it on.