Concurrency patterns that prevent races

Most concurrency bugs in application code can be avoided with five habits. None of them requires you to run every transaction at the strictest isolation level. Each one moves the check into the database, where it happens atomically.

1. Let the database do the read-modify-write

Put the condition and the change in one statement:

UPDATE accounts
SET balance = balance - 80
WHERE id = 1 AND balance >= 80;
-- rows affected: 1 means success, 0 means insufficient funds

The statement takes a row lock, and the condition is checked against the newest committed row after any wait. So even at read committed, two withdrawals cannot both succeed on a $100 balance. The first leaves $20, and the second matches zero rows. The same goes for views = views + 1. Avoid the pattern SELECT, subtract in code, then UPDATE.

2. Use constraints as concurrency control

A unique constraint cannot be raced. If two inserts use the same key, one wins and the other fails with unique_violation. Checking "does it exist?" and then inserting is not safe, because both checks can pass.

-- One ticket per seat per show, however the requests interleave
ALTER TABLE tickets ADD CONSTRAINT one_per_seat UNIQUE (show_id, seat_id);

-- No overlapping bookings for the same room (Postgres exclusion constraint)
CREATE EXTENSION IF NOT EXISTS btree_gist;
ALTER TABLE bookings ADD CONSTRAINT no_overlap
  EXCLUDE USING gist (room_id WITH =, during WITH &&);

The exclusion constraint fixes the room-booking phantom from the anomalies page. No row exists to lock beforehand, but the constraint checks every new booking against existing time ranges. A unique constraint on an idempotency key also makes retried API requests safe. See idempotency and ticket booking.

3. Lock what you read when you decide on it

For write skew where the rows already exist, such as the on-call doctors, lock them while you count:

SELECT id FROM doctors WHERE on_call FOR UPDATE;
-- the second transaction waits for the first to finish, then re-reads the rows

At read committed, the waiting transaction then sees the new count. At repeatable read, the same case fails with a serialization error, so you retry.

Where no rows exist yet, materialize the conflict. Create a row that can be locked, such as one row per room per time slot, and lock that row before you decide.

4. Or use serializable, and retry

Under SERIALIZABLE, Postgres detects the doctors' write skew and aborts one transaction with SQLSTATE 40001. Some aborts are false positives, so you must retry. This sketch shows the retry loop. It needs a connection and a work function that does the database calls.

import random, time
import psycopg
from psycopg import errors

def run_serializable(conn: psycopg.Connection, work, attempts: int = 5):
    conn.isolation_level = psycopg.IsolationLevel.SERIALIZABLE
    for attempt in range(attempts):
        try:
            with conn.transaction():
                return work(conn)
        except (errors.SerializationFailure, errors.DeadlockDetected):
            time.sleep(random.uniform(0, 0.01 * 2 ** attempt))  # jittered backoff
    raise RuntimeError("transaction failed after retries")

The whole transaction must be safe to run again. After an abort, work runs from the beginning, so anything it did outside the database runs twice.

5. Keep transactions short

Do not make network calls, wait for user input or leave a BEGIN open in a REPL inside a transaction. Short transactions hold locks briefly, conflict less, and let vacuum clean up. A payment call inside a transaction also makes a retry charge the customer twice.

Common mistakes

• Check, then insert. "If no user has this email, insert one" needs a unique constraint, or two requests can both insert.
• Retrying without limits or backoff. Under heavy contention, immediate retries keep colliding. Cap the attempts and add jitter.
• Side effects inside a retried transaction. Sending an email or charging a card inside work repeats it on every retry.
• Reading, subtracting in code, then writing. This is the lost-update pattern. Use one atomic UPDATE.

Check yourself

1. Why does UPDATE accounts SET balance = balance - 80 WHERE id = 1 AND balance >= 80 avoid a lost update at read committed? The change and the check happen in one statement under a row lock. After waiting, Postgres checks the condition against the newest committed row, so the second withdrawal sees the first one's result.
2. Why must the body of a serializable transaction be safe to run twice? After a 40001 abort, the retry loop runs the whole function again, so any email or payment inside it happens again too.

Key takeaways

• Put the check and the change in one atomic statement, such as UPDATE ... WHERE balance >= x.
• Let unique and exclusion constraints decide races. Do not check first and insert after.
• Lock the rows you decide on with FOR UPDATE, or lock a materialized row when none exist.
• With serializable isolation, retry with a cap and jitter, and keep side effects out of the transaction.

Further learning

Martin Kleppmann's Designing Data-Intensive Applications covers these trade-offs in depth, including write skew and serializable retries.

Next, return to the hub, transactions, MVCC and isolation, for how these ideas extend across many machines.