Duplicate reservations in PostgreSQL: transactions and locks to prevent it
Published on 2026-10-26 by frfrand
Avoid double booking and race conditions with SQL transactions
Only one spot remains for a tour. Two users click “Reserve” almost at the same time. If your backend first performs a SELECT and then an UPDATE without protection, both can see availability and sell the same spot.
The idea is to solve a data or deployment problem that appears in real applications. We will look at the concept without fluff, an implementation example, and what to check before taking it to production.
What Transactions and Duplicate Reservations Do
Transactions group operations with ACID properties and allow coordinating concurrent access. A row lock FOR UPDATE, an atomic conditional update, or a uniqueness constraint can resolve different models. What is correct depends on the structure of quotas and payments.
It is not enough to repeat a recipe if you do not understand who reads the data, from which process, and with what permissions. In an application with reservations, invoices, or users, a race condition or a poorly designed access policy can be more serious than a slow API.
A Concrete Example
BEGIN;
SELECT plazas_disponibles
FROM horarios
WHERE id = 42
FOR UPDATE;
-- Si plazas_disponibles >= 1, descontar y crear reserva
UPDATE horarios
SET plazas_disponibles = plazas_disponibles - 1
WHERE id = 42 AND plazas_disponibles >= 1;
-- Comprobar ROW_COUNT / RETURNING antes de confirmar
COMMIT;The names, tables, and keys in this example are illustrative. Try it first in a test or staging environment and adapt permissions, indexes, and versions. When there are write operations, do not run sample queries on customer data without reviewing what they modify.
Step by Step
- Define the business rule: maximum slots, pending reservations, expiration, and payments.
- Store availability and reservations with constraints that prevent impossible states.
- Use a short transaction that locks or conditionally updates the resource.
- Check the result of the UPDATE; if no slots remain, respond with a clear business error.
- Test two concurrent requests and verify that only one consumes the last slot.
At the end, don't settle for "the SQL didn't return an error." Verify the actual effect from the API and with concurrency or permission tests as appropriate. In databases, there are errors that only appear when two users do something at the same time.
Example work project
On the tours website, client A and B choose the last slot. With the schedule row locked in a transaction, the second waits and upon resuming sees the availability already updated.
That scenario helps distinguish performance from consistency. Speeding up a query is useless if it allows creating two reservations for a single slot, nor does a huge database fix the backend allowing querying another client's data.
The typical error
The example is a simplified schema; do not make external payments while keeping a transaction open. Separate provisional reservation and confirmation via webhook using idempotency.
It's also a good idea to have a recovery plan. Before touching schemas, users, or policies, save a verifiable copy or reversible migration and check that the application can keep running during the change.
How would I solve this with ArduMaker?
A Django or FastAPI backend on ArduMaker can use PostgreSQL for this logic. Integrity is designed in the database and application, not obtained automatically by the VPS provider.
ArduMaker allows you to deploy services from Git and work with databases and processes within the VPS. The panel does not replace your application's logic: transactions, permissions, and validations must be properly designed in the code and database.
Three checks before publishing
Integrity: if a request is repeated or fails halfway through, do the data end up in a valid state?
Security: can an authenticated user access only their own information, and does the API use a database identity with minimal permissions?
Recovery: do you know how to restore the data and revert the compatible code version if an update goes wrong?
If you can answer those three questions with tests, your backend is much better prepared than when you only checked if the endpoint returned 200.
Resources to learn more
To wrap up: choose the simplest solution that allows keeping data correct, secure, and recoverable. Performance comes after measuring.