Transactions, Isolation Levels and Concurrency Control: Preventing Race Conditions

A concert has one seat left. Two fans press "book" within the same millisecond. Your code does what looks perfectly reasonable: read the seat, see it is free, mark it booked. Both requests read "free" before either writes "booked". Both fans get a confirmation email. One of them arrives at the venue to find someone in their seat.

Nothing crashed. No error was logged. Each request, on its own, was correct. The bug exists only in the interleaving of two correct requests. That is a race condition, and it is the most common way systems that look fine in testing go wrong in production. This article shows the common races, the isolation levels databases offer, and the techniques that prevent races inside one database.

• The common races: lost updates, check-then-act, write skew, and the read anomalies.
• What isolation levels mean, and how MVCC lets readers and writers coexist.
• Five techniques: atomic conditional updates, constraints, optimistic concurrency, pessimistic row locks, and serializable isolation with retries.
• Deadlocks: why they happen and how to avoid and handle them.
• Hot rows, long transactions, and the limits of single-database techniques.
• Worked examples: seat booking, likes, a wallet and a bank transfer.

Time  Request A                       Request B 1    read balance = 100 2                                    read balance = 100 3    write balance = 100 + 50 = 150 4                                    write balance = 100 - 30 = 70Result: 70. A's deposit of 50 is lost.

Each request read a value, computed a new one in application code, and wrote it back. The second write overwrote the first.

 1    A: is seat 14 free? yes 2    B: is seat 14 free? yes 3    A: book seat 14 4    B: book seat 14            -> double booking

The check and the action are separate steps, and the world changed in between. Username uniqueness checks, "is there stock?" checks and "has this payment been made?" checks all have this shape.

Two requests read the same data, each checks a rule, and each updates different rows, together breaking the rule. The classic example: a hospital requires at least one doctor on call. Two doctors are on call; each, at the same moment, checks "is someone else on call?" (yes), and takes themselves off. Neither update conflicts with the other row-wise, but now nobody is on call.

These are about what a transaction sees:

• Dirty read: seeing another transaction's uncommitted change, which might be rolled back.
• Non-repeatable read: reading the same row twice in one transaction and getting different values because another transaction committed in between.
• Phantom read: running the same query twice and getting a different set of rows because another transaction inserted or deleted matching rows.

Isolation (the I in ACID) controls how much concurrent transactions can see of each other. The SQL standard defines four levels; stronger levels prevent more anomalies but allow less concurrency or cause more retries.

PostgreSQL's documentation describes its behaviour precisely: Read Committed is the default; Read Uncommitted behaves like Read Committed; Repeatable Read in PostgreSQL also prevents phantom reads; Serializable additionally prevents serialization anomalies such as write skew, and may abort a transaction with a serialization failure that the application must retry (PostgreSQL: Transaction Isolation). Other databases differ in defaults and details (MySQL's InnoDB engine, for example, defaults to Repeatable Read), so always check the documentation of the database you are using.

The most important practical fact: the common default, Read Committed, does not prevent lost updates or check-then-act races when the read and the write are separate statements. You must use one of the techniques below.

Many databases, including PostgreSQL, use multiversion concurrency control (MVCC): a write creates a new version of a row rather than overwriting it in place, and each transaction reads a consistent snapshot of versions. As PostgreSQL's documentation puts it, reading never blocks writing and writing never blocks reading (PostgreSQL: MVCC introduction). That is why plain reads are fast under heavy write load. It is also why a snapshot read can be slightly behind reality, and why two writers still need a way to avoid stepping on each other.

Move the check into the write, so the database performs both as one operation:

-- Decrement stock only if some is left. Check how many rows changed.UPDATE products SET stock = stock - 1 WHERE product_id = 9 AND stock > 0;-- 1 row updated: you got one. 0 rows: sold out.-- Book a seat only if it is still free.UPDATE seats SET status = 'booked', booking_id = 501WHERE event_id = 7 AND seat_no = 'C14' AND status = 'free';

The database locks the row while it updates it, so two such statements on the same row run one after the other, and the second sees the first's result. No read-modify-write gap exists. This is the first tool to reach for: simple, fast, and correct at any isolation level for single-row changes.

Counters are the same idea: UPDATE photos SET like_count = like_count + 1 WHERE photo_id = ? never loses increments, whereas reading the count into the application and writing back the incremented value does.

Let the database reject the second, conflicting write:

CREATE TABLE bookings (    booking_id BIGINT PRIMARY KEY,    event_id   BIGINT NOT NULL,    seat_no    VARCHAR(5) NOT NULL,    user_id    BIGINT NOT NULL,    UNIQUE (event_id, seat_no)           -- a seat can be booked once);

Two concurrent inserts for the same seat: one succeeds, the other fails with a unique-violation error, which the application turns into "sorry, that seat was just taken". Constraints are race-proof by construction, and they protect against bugs in every code path, not just the one you remembered. Article 20 uses the same idea for idempotency keys.

Assume conflicts are rare. Read the row with a version number, do your work, and write back only if the version has not changed:

SELECT stock, version FROM products WHERE product_id = 9;     -- stock 1, version 7-- ... application logic ...UPDATE products SET stock = 0, version = 8WHERE product_id = 9 AND version = 7;                          -- 0 rows if someone else changed it

If zero rows changed, someone else won; re-read and retry (or give up). No locks are held while the application thinks, so it suits workflows where a user edits a record for minutes. HTTP has a matching mechanism: the server sends an ETag, the client sends If-Match with it on update, and the server responds 412 Precondition Failed if the resource changed (RFC 9110), as article 04 described.

Assume conflicts are likely. Lock the rows you are about to change, so others wait:

BEGIN;SELECT balance FROM accounts WHERE account_id = 1 FOR UPDATE;  -- others trying to lock row 1 now wait-- ... check rules in application code ...UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;COMMIT;                                                         -- lock released

SELECT ... FOR UPDATE takes a row-level lock that blocks other transactions from updating or locking the same row until commit (PostgreSQL: Explicit Locking). It is the right tool when the rule involves logic that is awkward to express in one statement, conflicts are frequent, and the critical section is short.

Never hold a database lock while calling an external service (a payment provider, an email API). If the call takes 20 seconds, everyone else waits 20 seconds.

Run the transaction at Serializable isolation, and let the database detect conflicting interleavings. It aborts one of them with a serialization error; the application retries the whole transaction. This catches subtle cases like write skew without you designing a lock for each rule. The cost is more aborted transactions under contention, and every transaction must be safe to retry.

Real races depend on timing, which makes them hard to demonstrate. This Java program scripts the exact interleaving instead: two clients both read before either writes. It compares an unprotected read-modify-write, a conditional update and an optimistic version check. The methods mimic what the SQL statements above do inside the database.

// File: RaceDemo.javapublic class RaceDemo {    static int stock;    static int version;    // Mimics: UPDATE items SET stock = stock - 1 WHERE id = ? AND stock > 0    static boolean conditionalDecrement() {        if (stock > 0) {            stock--;            return true;        }        return false;    }    // Mimics: UPDATE items SET stock = ?, version = version + 1 WHERE id = ? AND version = ?    static boolean optimisticWrite(int newStock, int expectedVersion) {        if (version != expectedVersion) {            return false;        }        stock = newStock;        version++;        return true;    }    public static void main(String[] args) {        // 1. Unprotected read-modify-write: both read before either writes.        stock = 1;        int sold = 0;        int seenByA = stock;        int seenByB = stock;        if (seenByA > 0) {            stock = seenByA - 1;            sold++;        }        if (seenByB > 0) {            stock = seenByB - 1;            sold++;        }        System.out.println("Unprotected: sold " + sold + " of 1, stock shows " + stock);        // 2. Conditional update: the check and the change are one atomic statement.        stock = 1;        sold = 0;        for (String client : new String[] {"A", "B"}) {            boolean ok = conditionalDecrement();            if (ok) {                sold++;            }            System.out.println("Conditional: client " + client + (ok ? " bought" : " updated 0 rows, sold out"));        }        System.out.println("Conditional: sold " + sold + " of 1, stock shows " + stock);        // 3. Optimistic: both read version 7; only the first write with version 7 succeeds.        stock = 1;        version = 7;        int aStock = stock, aVersion = version;        int bStock = stock, bVersion = version;        System.out.println("Optimistic:  A write " + (optimisticWrite(aStock - 1, aVersion) ? "succeeded" : "failed"));        System.out.println("Optimistic:  B write " + (optimisticWrite(bStock - 1, bVersion) ? "succeeded" : "failed, version changed"));        System.out.println("Optimistic:  B re-reads stock " + stock + " and reports sold out; version is now " + version);    }}

Unprotected: sold 2 of 1, stock shows 0Conditional: client A boughtConditional: client B updated 0 rows, sold outConditional: sold 1 of 1, stock shows 0Optimistic:  A write succeededOptimistic:  B write failed, version changedOptimistic:  B re-reads stock 0 and reports sold out; version is now 8

Look at the first line. Both clients read a stock of 1, both passed the check, and two units were sold from a stock of one. Yet the stock column shows 0, so the inventory record looks perfectly healthy. Lost updates are dangerous precisely because the data looks valid afterwards. The conditional update sells exactly one; the optimistic version detects the conflict and B backs off.

With locks, two transactions can wait for each other forever:

Transaction A: locks account 1 ... then wants account 2Transaction B: locks account 2 ... then wants account 1-> each waits for the other: deadlock

Databases detect deadlocks and abort one transaction (PostgreSQL's documentation describes this behaviour (PostgreSQL: Explicit Locking)). Your job:

• Lock in a consistent order. For a transfer, always lock the lower account id first. Then two transfers between the same accounts lock in the same order and cannot deadlock.
• Keep transactions short and touch as few rows as possible.
• Retry the aborted transaction (it must be safe to retry).

All of these techniques serialise access to one row. If one row is very hot (the stock count of a flash-sale product, a global counter, a celebrity's like count), every request queues on it. Throughput is limited by how fast that single row can be locked, changed and committed, often a few thousand per second at best on good hardware, and latency spikes as requests wait.

Designs for hot rows:

• Split the counter into N rows (stock_bucket 0 to 9) and pick one at random to decrement; sum them to read. Contention drops by about N times.
• Pre-allocate: hand out blocks of inventory to servers or regions in advance.
• Queue the requests for the hot item and process them in order with a single consumer (article 43's flash sale).
• Count asynchronously for things that need not be exact in real time (likes, views).

A transaction that stays open for seconds or minutes holds locks, blocks others, and in MVCC databases keeps old row versions alive (which hurts cleanup). Keep external calls, user think time and batch processing outside database transactions. For long user edits, use optimistic concurrency, not open transactions.

Requirements: no seat is ever booked twice, many users may try the same popular seats at once.

CREATE TABLE seats (    event_id   BIGINT NOT NULL,    seat_no    VARCHAR(5) NOT NULL,    status     VARCHAR(10) NOT NULL DEFAULT 'free',   -- free, held, booked    hold_until TIMESTAMPTZ,    holder_id  BIGINT,    PRIMARY KEY (event_id, seat_no));-- Hold a seat for 10 minutes, only if it is free or its hold has expired.UPDATE seatsSET status = 'held', holder_id = :user, hold_until = now() + interval '10 minutes'WHERE event_id = :event AND seat_no = :seat  AND (status = 'free' OR (status = 'held' AND hold_until < now()));

One conditional update per seat: exactly one user gets each hold. Confirming the booking after payment is another conditional update (WHERE status = 'held' AND holder_id = :user AND hold_until > now()). Article 38 builds the full system, including what happens when payment succeeds after the hold expired.

Wallet debit (one row): UPDATE wallets SET balance = balance - :amt WHERE user_id = :u AND balance >= :amt, then check one row changed. No lock needed beyond the update itself.

Transfer between two wallets (two rows, both must change):

BEGIN;-- Lock both rows in a consistent order (lower id first) to avoid deadlocks.SELECT user_id, balance FROM wallets WHERE user_id IN (:a, :b) ORDER BY user_id FOR UPDATE;UPDATE wallets SET balance = balance - :amt WHERE user_id = :from AND balance >= :amt;  -- check 1 rowUPDATE wallets SET balance = balance + :amt WHERE user_id = :to;INSERT INTO transfers (transfer_id, from_id, to_id, amount) VALUES (:id, :from, :to, :amt);COMMIT;

The transfers row has a primary key supplied by the client's idempotency key, so a retried request fails the insert instead of moving money twice (article 20). Real payment systems record money movements as an append-only ledger instead of mutating balances directly; article 39 explains why.

Every technique here relies on one database coordinating access to its own rows. When the data involved lives in different databases or services (stock in the inventory service, money in the payment service), there is no single place to lock or to run one transaction. Those cases need idempotency (article 20), sagas or the outbox pattern (article 21), and sometimes distributed locks with fencing (article 22). A useful design instinct is to keep data that must change together in one database whenever you can.

• Reading a value into application code, changing it and writing it back.
• Checking existence and then inserting, without a unique constraint.
• Assuming the default isolation level prevents lost updates.
• Holding transactions open across network calls or user think time.
• Locking rows in different orders in different code paths.
• Retrying aborted transactions whose side effects are not idempotent.

• When two users can act on the same thing (a seat, a coupon, stock, a username, a balance), say "this is a race" and name your protection: conditional update, unique constraint, version check or row lock.
• State the database's default isolation level and that you are not relying on it to prevent lost updates.
• For very hot items, say how you avoid a single-row bottleneck.
• If the data spans services, say that local transactions are not enough and point to sagas or idempotency.

balance = SELECT balance FROM wallets WHERE user_id = ?if balance >= amount:    UPDATE wallets SET balance = (balance - amount) WHERE user_id = ?    record purchaseelse:    reject

UPDATE wallets SET balance = balance - :amountWHERE user_id = :user AND balance >= :amount;-- if 1 row updated: record the purchase; if 0 rows: reject (insufficient balance)

• Next: Database Replication: Primary-Replica, Read Replicas, Lag and Failover, where copies of these rows live on other machines and can lag behind.
• Later: idempotency (article 20), distributed transactions (article 21), ticket booking (article 38) and payments (article 39) build directly on these techniques.