How to Prevent Double Bookings in PostgreSQL with Transactions and Row-Level Locks

Most developers write their initial booking logic as an imperative two-step sequence in their API route: first, query the database to verify that no overlapping reservation exists for the requested resource; second, if the query returns zero rows, execute an INSERT statement.

Under concurrent execution, this architecture produces the classic Time-of-Check to Time-of-Use (TOCTOU) race condition. Imagine User A and User B requesting the same room for the same time slot simultaneously. Thread A issues a SELECT query and finds no conflicts. Before Thread A can issue its INSERT, Thread B executes its own SELECT query—which also returns zero rows, because Thread A has not committed anything yet.

Both threads proceed with their inserts. Under PostgreSQL's default READ COMMITTED transaction isolation level, neither transaction blocks the other. Both transactions commit successfully, creating an embarrassing and expensive double booking. The flaw is architectural: checking in application memory cannot guarantee consistency across concurrent stateless API replicas.

A common misconception among backend engineers is that wrapping the check-and-insert logic inside a standard BEGIN ... COMMIT transaction block solves concurrency issues. It does not.

In PostgreSQL's default READ COMMITTED mode, each query inside a transaction sees a snapshot of data committed before that specific query began, not when the transaction started. Wrapping statements in BEGIN and COMMIT does not acquire any locks on non-existent rows.

Switching to REPEATABLE READ also fails to prevent double bookings into empty time ranges. While REPEATABLE READ prevents non-repeatable reads on existing rows, concurrent transactions can still insert new rows into empty spaces without conflicting—the classic 'Phantom Insert' scenario. While full SERIALIZABLE isolation does detect read/write dependencies using Serializable Snapshot Isolation (SSI) and aborts conflicting transactions with error 40001 (serialization_failure), it requires complex application-level retry loops with exponential backoff and degrades under heavy write contention.

When resources are modeled as discrete, pre-allocated inventory units—such as specific airline seats (12A, 12B), numbered hotel rooms, or pre-seeded 30-minute calendar slots in a schedule table—pessimistic row-level locking provides a high-performance solution.

Using SELECT ... FOR UPDATE within a transaction instructs PostgreSQL to acquire an exclusive lock on the selected row. Any concurrent transaction attempting to read that row FOR UPDATE, or attempting to UPDATE or DELETE it, is forced to pause and wait until the first transaction commits or rolls back.

To prevent API threads from hanging indefinitely behind congested locks, production systems leverage the NOWAIT clause: SELECT id FROM slots WHERE id = $1 FOR UPDATE NOWAIT. If another transaction holds the lock, PostgreSQL aborts immediately with error code 55P03 (lock_not_available), allowing your API to return an immediate HTTP 409 Conflict without holding database connection pool slots hostage.

Pessimistic row locking breaks down when reservations represent continuous, arbitrary time windows rather than discrete slots. For example, in a car rental, conference room, or equipment booking application, a customer might reserve a resource from 14:15 to 16:45 on Tuesday. You cannot execute SELECT ... FOR UPDATE on a booking row that does not yet exist.

Standard SQL UNIQUE constraints fail here because they only evaluate exact equality (=); they cannot detect overlapping ranges. PostgreSQL solves this with a world-class feature: Exclusion Constraints using GiST (Generalized Search Tree) indexes and range types.

By enabling the btree_gist extension, you can create a constraint combining scalar equality with range overlap. Using the tstzrange (timestamp with time zone range) data type and the overlap operator (&&), the database kernel enforces that no two active rows for the same resource can have overlapping time intervals. This guarantee holds true across all isolation levels, under any level of concurrency, with zero race condition windows.

In high-read, low-write scenarios where reservation contention is rare, acquiring pessimistic row locks or holding database locks during external third-party operations (such as processing a Stripe payment modal) wastes connection pool resources.

Optimistic Concurrency Control (OCC) assumes conflicts are infrequent. The database table includes a version integer column. When the application reads the record, it records the current version number. When the user submits their reservation, the mutation executes as an atomic conditional update: UPDATE inventory SET reserved_count = reserved_count + 1, version = version + 1 WHERE id = $1 AND version = $expected_version AND reserved_count < capacity.

If a concurrent transaction committed a change in the interim, the version will not match, and PostgreSQL updates zero rows. The application detects the zero-row return and prompts the user or retries gracefully without ever deadlocking or corrupting inventory state.

When building production booking APIs in Node.js or TypeScript (using pg, Prisma, or Drizzle), you must properly format time ranges and handle PostgreSQL constraint violations.

Time intervals should always be formatted using half-open range notation: [start, end). The bracket '[' means inclusive of the start second, while the parenthesis ')' means exclusive of the end second. This standard allows back-to-back reservations (e.g. 14:00 to 15:00 and 15:00 to 16:00) to coexist seamlessly without colliding at 15:00:00.

When a concurrent collision occurs, PostgreSQL rejects the conflicting transaction and returns SQL state code 23P01 (exclusion_violation). Your API must intercept this specific error code and return a clean HTTP 409 Conflict status with a structured error payload, rather than letting an unhandled 500 Internal Server Error escape to the client.

Double bookings are a symptom of attempting to solve database-tier concurrency problems in stateless application servers. By offloading resource exclusivity to PostgreSQL's native GiST exclusion constraints and row-level locking primitives, your architecture remains bulletproof under any volume of concurrent traffic.