Lorixen.
EN

Transaction isolation levels: picking one for your app

Short answer: Isolation levels decide what one database transaction can see of others running at the same time. PostgreSQL's default, Read Committed, is fine for most work but doesn't stop every race condition, such as two checkouts both seeing one item left and both selling it. Fix those hot spots with an atomic update that checks the condition, a row lock with SELECT … FOR UPDATE, or the Serializable level with automatic retries. Choose per operation, not one level for the whole app.

The race condition you can't see in testing

Imagine a shop with one ticket left for a workshop. Two customers click "Buy" at almost the same moment. Each request runs something like:

  1. Read the remaining stock: 1.
  2. Check it's above zero: yes.
  3. Insert an order and set stock to 0.

If both requests read the stock before either writes, both see 1 and both succeed. You've sold two tickets for one seat. In testing, with one person clicking at a time, it never happens. Under real traffic, especially during a sale or a launch, it does.

What isolation levels are

The SQL standard defines four isolation levels, each preventing more kinds of interference between concurrent transactions. PostgreSQL's documentation on transaction isolation sets them out, with a table showing which anomalies each allows: dirty reads, non-repeatable reads, phantom reads and serialisation anomalies. It notes that in PostgreSQL, Read Uncommitted behaves like Read Committed, so there are effectively three levels:

  • Read Committed (the default). Each statement sees data committed before that statement started. Two statements in the same transaction can see different data if another transaction commits in between.
  • Repeatable Read. The transaction sees a consistent snapshot from its start. If it tries to update a row another transaction changed after that snapshot, it fails with an error and must be retried.
  • Serializable. The strictest level. PostgreSQL's documentation says it works like Repeatable Read but also monitors for conditions that could make a set of concurrent transactions behave inconsistently with running them one at a time, and aborts one if so.

The documentation is explicit for the stricter levels: applications must be prepared to retry transactions that fail with serialisation errors, and the retry should run the whole transaction from the beginning.

Option 1: make the check part of the write

For many race conditions, the simplest fix doesn't need a different isolation level. Combine the check and the update into one statement:

UPDATE workshop_seats
SET remaining = remaining - 1
WHERE workshop_id = 42 AND remaining > 0
RETURNING remaining;

If no row comes back, the seat was already taken; tell the customer. Because the database applies the condition and the change together, two concurrent requests can't both succeed when only one seat is left.

This pattern works for stock counts, credit balances, voucher redemptions and booking slots with a capacity.

Option 2: lock the row first

When the logic is too complex for one statement, lock the row you're about to base a decision on. PostgreSQL's SELECT documentation explains that FOR UPDATE locks the selected rows against concurrent updates:

BEGIN;
SELECT remaining FROM workshop_seats WHERE workshop_id = 42 FOR UPDATE;
-- check business rules in the app, then:
UPDATE workshop_seats SET remaining = remaining - 1 WHERE workshop_id = 42;
INSERT INTO orders ...;
COMMIT;

The second request waits at the SELECT until the first commits, then sees the updated value. Keep these transactions short, and lock rows in a consistent order, to avoid slowdowns and deadlocks.

For job queues, FOR UPDATE SKIP LOCKED lets several workers each grab a different unclaimed job without waiting on each other.

Option 3: Serializable with retries

Some rules span many rows, such as "no more than three bookings per staff member per day" or "total allocations can't exceed the budget". Locking every relevant row can be awkward. Running those transactions at Serializable lets the database detect conflicting combinations:

BEGIN ISOLATION LEVEL SERIALIZABLE;
-- read existing bookings, check the rule, insert
COMMIT;

If PostgreSQL detects a conflict, one transaction fails with a serialisation error. Your code catches it and reruns the whole transaction, a limited number of times. Many frameworks have helpers for this.

The trade-off is more retries under heavy contention. For most small business workloads, it's very manageable.

Constraints are your last line of defence

Wherever a rule can be expressed as a database constraint, add one:

  • A CHECK (remaining >= 0) on stock or seats.
  • A unique constraint so one customer can't redeem the same voucher twice.
  • An exclusion constraint to prevent overlapping bookings for the same room, where your database supports it.

Even if application logic has a gap, the database refuses the bad write.

Test concurrency on purpose

Race conditions hide in normal testing, so provoke them. Write a test that fires twenty purchase requests for the last item at the same moment and checks that exactly one succeeds. Run it in CI against a real database, not an in-memory substitute, because isolation behaviour differs.

Choosing per operation

Situation Usual fix
Decrement a counter with a floor Conditional UPDATE … WHERE … > 0
Read, decide in code, then write one row SELECT … FOR UPDATE
Rule across many rows Serializable plus retry, or a constraint
Workers claiming jobs FOR UPDATE SKIP LOCKED
Everything else Default Read Committed

A worked example

Picture a hypothetical Halifax escape-room business whose booking app occasionally double-booked the last slot of a night. The code read available capacity, checked it in the app and then inserted the booking. The developer changed the capacity update to a conditional UPDATE with a CHECK constraint on remaining places, and moved the "maximum bookings per group per week" rule into a Serializable transaction with up to three retries. Double bookings stopped, and nobody noticed any slowdown.

FAQ

Should I set Serializable for the whole app?

You can, if every transaction handles retries. Most apps get better results using it only where needed.

Does my ORM handle this?

ORMs usually let you set the isolation level and add row locks, but you still have to choose where.

Is MySQL the same?

The concepts are the same, but defaults and behaviour differ. MySQL's InnoDB defaults to Repeatable Read; check its documentation.

If your app sometimes oversells or double-books under load, show us the code path and we'll find the race.

Sources

WhatsAppBook a call