Concurrency: Race Conditions, Locking, and Idempotency

Recognize race conditions caused by concurrent check-then-act operations, and prevent them using optimistic locking, pessimistic row locks, unique constraints, and idempotency keys.

A database is almost never talking to just one request at a time -- in production, dozens or thousands of requests can be reading and writing the same rows concurrently. Code that looks perfectly correct when you trace through it one request at a time can be subtly, dangerously broken the instant two requests run at nearly the same moment. This lesson works through the canonical race condition (two requests decrementing the same stock count), and the four main tools PHP developers use to make concurrent writes safe: optimistic locking, pessimistic locking, unique constraints, and idempotency keys.

Race Conditions: The Stock Decrement Problem

A race condition happens when the correctness of a piece of code depends on the precise timing or interleaving of operations from multiple concurrent processes, and that interleaving isn't controlled. The classic example: a products table has a stock column, and 'buy one unit' is implemented as read the current stock, check it's greater than zero, then write stock - 1 back. Each of those three steps is a separate database round trip, and nothing stops another request from running its own read-check-write in between yours.

Concretely: stock is 1. Request A reads stock = 1, sees it's > 0, and prepares to write stock = 0. Before A's write lands, Request B also reads stock = 1 (A hasn't written yet), also sees it's > 0, and also prepares to write stock = 0. Both requests believe they successfully bought the last unit. Both writes succeed. The final stock is 0 -- but two units were 'sold' from a stock of one. This is a textbook check-then-act race condition, and it's invisible in single-request testing because it requires genuine concurrency to trigger.

The root cause is always the same shape: a read, a decision based on that read, and a write based on that decision, with no guarantee nothing else changed the data between the read and the write. Fixing it means either making the whole read-check-write sequence atomic from the database's point of view, or detecting after the fact that the data changed underneath you and refusing to proceed.

• Race condition: correctness depends on timing/interleaving between concurrent operations. • Check-then-act pattern: read a value, decide based on it, write a new value -- unsafe under concurrency. • Classic example: two requests both read stock=1, both decide to decrement, both succeed -- stock goes negative in effect. • The fix is either atomicity (do it in one indivisible step) or detection (notice the data moved and reject the write).

Optimistic Locking

Optimistic locking assumes conflicts are rare, so it doesn't block anyone up front -- instead, it detects a conflict at write time and rejects the write if the data changed since it was read. The standard mechanism is a version integer column on the row. When you read a row, you also read its current version. When you write, your UPDATE includes WHERE id = ? AND version = ? (the version you originally read) and sets version = version + 1. If no other request modified the row in between, exactly one row matches and the update succeeds. If another request already updated it (bumping the version), zero rows match -- your UPDATE affects 0 rows, and your code detects that and can retry or report a conflict.

This turns the dangerous read-check-write sequence into read-check-conditional-write, where the 'conditional' part (the WHERE version = ? clause) is what the database checks atomically at write time, closing the window where another request could sneak in undetected. Optimistic locking is cheap (no locks held, no blocking) and works well when conflicts are genuinely uncommon, which is true for most rows in most applications most of the time.

The cost is that a losing request has to notice the failure (rowCount() === 0) and decide what to do -- retry the whole operation from a fresh read, or surface an error to the user ('someone else already modified this, please refresh'). Optimistic locking is a poor fit for high-contention rows where conflicts are the norm rather than the exception, since most attempts would just keep failing and retrying.

• Version column on the row; UPDATE includes WHERE ... AND version = <version you read>. • Successful update also bumps version = version + 1. • 0 rows affected means someone else updated it first -- detect via rowCount() and retry/report. • Cheap, non-blocking; best when conflicts are rare.

Pessimistic Locking and Row Locks

Pessimistic locking assumes conflicts are likely enough that it's worth blocking other transactions up front. SELECT ... FOR UPDATE inside a transaction acquires an exclusive row lock on the selected row(s): any other transaction that tries to SELECT ... FOR UPDATE (or modify) the same row has to wait until the first transaction commits or rolls back. This closes the race window completely -- there is never a moment where two transactions both believe they hold the 'current' value, because the second transaction simply can't read-for-update until the first is done.

The tradeoff is throughput: locked rows serialize access, so if many requests contend for the same row (a single wildly popular product's stock count, say), they queue up behind each other instead of running concurrently, and a slow transaction holding the lock delays every other transaction waiting on it. Pessimistic locking is the right tool when conflicts are frequent and correctness under contention matters more than raw throughput -- exactly the stock-decrement scenario, where 'oversold by two units' is a real business problem, not a cosmetic one.

A row lock is scoped to the transaction that acquired it and is released automatically when that transaction commits or rolls back -- which is why SELECT ... FOR UPDATE only makes sense inside an explicit transaction (beginTransaction() ... commit()), never as a standalone query. Held locks that never get released (a transaction that never commits due to a bug, or a connection that hangs) can cascade into other requests timing out waiting for the lock, so keeping the locked section of code as short as possible matters.

• SELECT ... FOR UPDATE inside a transaction locks the selected row(s) exclusively. • Other transactions trying to read-for-update or modify the same row block until the lock is released. • Lock is released on COMMIT or ROLLBACK -- always pair FOR UPDATE with an explicit transaction. • Best for high-contention rows where correctness matters more than maximum concurrency. • Keep the locked section short -- a slow transaction holding a lock delays every waiter behind it.

Unique Constraints and Idempotency

For some problems, the safest 'lock' isn't a row lock at all -- it's letting the database's own uniqueness guarantee do the work. If you need to prevent two concurrent requests from both creating a duplicate row (the same user signing up twice with the same email, a payment being recorded twice for the same order), a UNIQUE constraint on the relevant column(s) is often simpler and more reliable than trying to check-then-insert in application code. Two concurrent INSERTs racing against a unique index: the database itself guarantees only one succeeds and the other fails with a constraint-violation error, atomically, no matter how tightly the two INSERTs overlap in time. Your code just needs to catch that specific error and handle it (usually: treat it as 'already exists' rather than a hard failure).

Idempotency tackles a related but different problem: safely retrying an operation (like 'charge this card' or 'place this order') without accidentally performing it twice, when the retry happens because the client couldn't tell whether the first attempt actually succeeded (a network timeout, a dropped connection). The client generates a unique idempotency key (often a UUID) for the logical operation once, and sends that same key on every retry attempt. The server records which idempotency keys it has already processed (typically in a dedicated table with a UNIQUE constraint on the key) and, on seeing a key it's already handled, returns the original result instead of repeating the side effect.

Idempotency keys and unique constraints are close cousins: both lean on the database's uniqueness guarantee to make 'has this already happened?' an atomic question instead of a racy check-then-act one. The difference is what they protect: a unique constraint on email prevents duplicate *entities*; an idempotency key prevents duplicate *executions* of an operation that might otherwise run twice due to a retry.

• A UNIQUE constraint lets the database itself reject a duplicate atomically -- no application-level race window. • Catch the constraint-violation exception in application code and handle it as 'already exists', not a crash. • Idempotency key: a client-generated unique id sent with a request so retries don't repeat side effects. • Server stores processed idempotency keys (with a UNIQUE constraint) and returns the original result on a repeat. • Both patterns turn 'has this happened already?' into an atomic database check instead of a race-prone one.

Optimistic Locking Update Pattern

The WHERE version = :expected_version clause is what makes the check-and-write atomic. version = version + 1 must happen in the same statement as the data change, not a separate query.

UPDATE products
SET stock = stock - 1, version = version + 1
WHERE id = :id AND version = :expected_version;
-- Check affected row count after this runs:
--   1 row  -> success, no one else touched it since you read it
--   0 rows -> someone else updated it first; re-read and retry (or report conflict)

Code Examples

The read (SELECT), the decision (if ($stock <= 0)), and the write (UPDATE) are three independent round trips with no atomicity between them, so two concurrent calls can interleave and both believe they're the only one decrementing.

Common mistake: Treating 'SELECT then check then UPDATE' as a single logical operation when the database sees it as three completely separate statements that any other transaction can interleave with.

The WHERE version = ? clause makes the UPDATE conditional on nothing having changed since the read. If another request already decremented (and bumped the version), this UPDATE matches zero rows, rowCount() reports that, and the loop retries with a fresh read instead of silently overwriting a value it no longer knows to be current. Only one of two concurrent racers can win on any given attempt; the loser detects rowCount() === 0 and retries, so stock can never go negative from this race.

if ($update->rowCount() === 1) { return true; } A concurrent second call to this function against the same productId literally cannot execute its SELECT... FOR UPDATE until the first transaction commits or rolls back -- the two requests are serialized by the database itself, so there's never a moment where both hold a stale stock value. Every step (lock, read, decide, write) happens inside one transaction, so from any other transaction's point of view the whole sequence is effectively atomic.

SELECT stock FROM products WHERE id = ? FOR UPDATE The initial SELECT is just an optimization for the common case (avoids doing work on an obvious retry). The actual safety comes from the UNIQUE constraint on idempotency_key: even if two retries race and both pass the initial SELECT check, only one INSERT can succeed -- the other hits a constraint-violation error (SQLSTATE 23000), which the code catches and turns into the same 'already_processed' response instead of double-charging the card.

if ($e->getCode() === '23000') { /* someone else's INSERT already won the race */ }

Common mistake: Relying only on the initial SELECT-then-INSERT check without a UNIQUE constraint backing it -- that check-then-act pair is exactly as racy as the naive stock decrement, just with 'insert a duplicate payment' as the failure mode instead of 'oversell stock'.

<?php
declare(strict_types=1);

// UNSAFE: read, check, write as three separate steps -- classic check-then-act race.
function buyOneUnitUnsafe(PDO $db, int $productId): bool
{
    $stmt = $db->prepare('SELECT stock FROM products WHERE id = ?');
    $stmt->execute([$productId]);
    $stock = (int) $stmt->fetchColumn();

    if ($stock <= 0) {
        return false; // out of stock
    }

    // <-- ANOTHER REQUEST CAN RUN ITS OWN READ+CHECK RIGHT HERE, UNDETECTED -->

    $update = $db->prepare('UPDATE products SET stock = ? WHERE id = ?');
    $update->execute([$stock - 1, $productId]);
    return true;
}

// Two concurrent requests both calling buyOneUnitUnsafe() when stock is 1 can BOTH
// read stock=1, BOTH pass the check, and BOTH write stock=0 -- overselling by one unit.

<?php
declare(strict_types=1);

function buyOneUnitOptimistic(PDO $db, int $productId, int $maxRetries = 3): bool
{
    for ($attempt = 0; $attempt < $maxRetries; $attempt++) {
        $stmt = $db->prepare('SELECT stock, version FROM products WHERE id = ?');
        $stmt->execute([$productId]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);

        if ($row === false || (int) $row['stock'] <= 0) {
            return false; // out of stock
        }

        $update = $db->prepare(
            'UPDATE products
             SET stock = stock - 1, version = version + 1
             WHERE id = ? AND version = ?'
        );
        $update->execute([$productId, (int) $row['version']]);

        if ($update->rowCount() === 1) {
            return true; // no one else touched it since we read -- success
        }

        // rowCount() === 0: another request updated it between our read and write.
        // Loop and retry with a fresh read.
    }

    return false; // gave up after retries -- treat as contention failure
}

<?php
declare(strict_types=1);

function buyOneUnitPessimistic(PDO $db, int $productId): bool
{
    $db->beginTransaction();
    try {
        // Locks this row -- any other transaction doing SELECT ... FOR UPDATE
        // on the same row blocks here until we COMMIT or ROLLBACK.
        $stmt = $db->prepare(
            'SELECT stock FROM products WHERE id = ? FOR UPDATE'
        );
        $stmt->execute([$productId]);
        $stock = (int) $stmt->fetchColumn();

        if ($stock <= 0) {
            $db->rollBack();
            return false;
        }

        $update = $db->prepare('UPDATE products SET stock = stock - 1 WHERE id = ?');
        $update->execute([$productId]);

        $db->commit(); // releases the row lock
        return true;
    } catch (Throwable $e) {
        $db->rollBack();
        throw $e;
    }
}

<?php
declare(strict_types=1);

// Table: payments (id, idempotency_key UNIQUE, order_id, amount_cents, created_at)

function chargeCardIdempotently(PDO $db, string $idempotencyKey, int $orderId, int $amountCents): array
{
    // Check first for a fast, friendly response -- but this alone is NOT the guarantee.
    $existing = $db->prepare('SELECT id FROM payments WHERE idempotency_key = ?');
    $existing->execute([$idempotencyKey]);
    if ($row = $existing->fetch(PDO::FETCH_ASSOC)) {
        return ['status' => 'already_processed', 'payment_id' => (int) $row['id']];
    }

    try {
        // The real guarantee: the database rejects a second concurrent INSERT
        // with the same idempotency_key, atomically, via the UNIQUE constraint.
        $insert = $db->prepare(
            'INSERT INTO payments (idempotency_key, order_id, amount_cents, created_at)
             VALUES (?, ?, ?, NOW())'
        );
        $insert->execute([$idempotencyKey, $orderId, $amountCents]);

        // ... actually charge the card here, now that the row is safely recorded ...

        return ['status' => 'charged', 'payment_id' => (int) $db->lastInsertId()];
    } catch (PDOException $e) {
       
…

Same Race, Three Outcomes

Optimistic detects the conflict after the fact and retries; pessimistic prevents the conflict from ever occurring.

Naive (unsafe):        Optimistic:                  Pessimistic:
A: read stock=1        A: read stock=1,v=5           A: BEGIN; SELECT...FOR UPDATE (locks row)
B: read stock=1        B: read stock=1,v=5           B: BEGIN; SELECT...FOR UPDATE -> BLOCKS
A: write stock=0       A: UPDATE WHERE v=5 -> OK,v=6 A: UPDATE stock=0; COMMIT (lock released)
B: write stock=0       B: UPDATE WHERE v=5 -> 0 rows B: (unblocked) sees stock=0, correctly fails
RESULT: stock=0,       B retries: reads v=6,stock=0
  but SOLD TWICE       B correctly detects out-of-stock

Important Rules

Never trust a read-check-write sequence across separate queries to be safe under concurrency by default.

Optimistic locking: add a version column, condition every UPDATE on WHERE version = <read version>, check rowCount().

Pessimistic locking: SELECT ... FOR UPDATE must be inside an explicit transaction, and the transaction should be short.

Prefer a UNIQUE constraint over an application-level 'check if it exists, then insert' for preventing duplicates.

Generate idempotency keys client-side per logical operation, and store processed keys with a UNIQUE constraint server-side.

Always catch and specifically handle the constraint-violation error rather than treating it as a generic failure.

Why This Matters in Production

Overselling inventory, double-charging a customer, or creating duplicate accounts are the kinds of bugs that pass every single-request test and only appear under real traffic -- often first noticed by an angry customer or a finance team reconciling numbers that don't add up. Payment APIs like Stripe require an idempotency key on charge requests precisely because network retries are unavoidable at scale, and 'the client couldn't tell if the first attempt worked' is a routine occurrence, not an edge case. Choosing optimistic locking, pessimistic locking, or a unique constraint isn't about picking a favorite -- it's about matching the tool to how often conflicts actually happen and how bad it is if one slips through.

$stock = readStock($id); if ($stock > 0) { writeStock($id, $stock - 1); } // read, check, and write as three separate unguarded steps

UPDATE products SET stock = stock - 1 WHERE id = ? AND stock > 0; -- then check rowCount() === 1 to know whether the decrement actually happened

Folding the check into the UPDATE's WHERE clause makes the read-check-write effectively atomic from the database's perspective -- there's no longer a gap between 'we decided stock > 0' and 'we wrote the new value' for another transaction to sneak into.

$existing = $db->query("SELECT id FROM users WHERE email = '$email'"); if (!$existing->fetch()) { $db->exec("INSERT INTO users (email) VALUES ('$email')"); } // check-then-insert with no unique constraint

ALTER TABLE users ADD UNIQUE (email); -- then INSERT directly and catch the SQLSTATE 23000 constraint-violation error if it's already taken

Without a UNIQUE constraint, two concurrent signups with the same email can both pass the 'does it exist' check before either INSERT lands, creating two rows with the same email. A UNIQUE constraint makes the database itself the single source of truth, atomically.

[multiple-choice] In the naive stock-decrement race condition, why can two concurrent requests both successfully buy the last unit of stock? A. Because PHP's PDO driver has a bug that duplicates UPDATE statements B. Because the read, the stock > 0 check, and the write are three separate database round trips with no atomicity between them, so both requests can read the same 'stock=1' value before either one writes C. Because MySQL does not support transactions D. Because the products table is missing an index

B. Because the read, the stock > 0 check, and the write are three separate database round trips with no atomicity between them, so both requests can read the same 'stock=1' value before either one writes — Each step is an independent query; nothing prevents a second request's SELECT from happening before the first request's UPDATE lands, so both requests can make their decision based on the same, soon-to-be-stale, value.

[multiple-choice] In optimistic locking, what does an UPDATE affecting 0 rows tell you? A. The row was deleted and no longer exists in any case B. The database is down C. Another transaction already updated the row (and its version) since you read it -- you should re-read and retry, not assume success D. The version column type is incorrect

C. Another transaction already updated the row (and its version) since you read it -- you should re-read and retry, not assume success — Because the UPDATE's WHERE clause requires the version to still match what you read, 0 affected rows specifically means someone else's write already changed (and bumped) the version in between your read and your write.

[multiple-choice] Why must SELECT ... FOR UPDATE always run inside an explicit transaction? A. It's purely a stylistic convention with no functional effect B. Because the row lock it acquires is held until that transaction commits or rolls back -- outside a transaction there is nothing to release the lock at the right time C. Because MySQL requires all SELECT statements to run inside transactions D. Because it makes the query run faster

B. Because the row lock it acquires is held until that transaction commits or rolls back -- outside a transaction there is nothing to release the lock at the right time — The exclusive row lock from FOR UPDATE is tied to the enclosing transaction's lifetime; it's released on COMMIT or ROLLBACK, so running it outside an explicit transaction leaves no well-defined point at which the lock is released.

[multiple-choice] Why does an idempotency key need a UNIQUE constraint backing it, rather than just an initial SELECT-then-INSERT check in application code? A. It doesn't -- the SELECT check alone is already fully safe under concurrency B. A SELECT-then-INSERT check is itself a check-then-act race: two concurrent retries with the same key can both pass the SELECT before either INSERT lands, unless the database's UNIQUE constraint atomically rejects the second INSERT C. UNIQUE constraints make INSERT statements execute faster D. Idempotency keys are only useful for GET requests

B. A SELECT-then-INSERT check is itself a check-then-act race: two concurrent retries with the same key can both pass the SELECT before either INSERT lands, unless the database's UNIQUE constraint atomically rejects the second INSERT — Without a UNIQUE constraint, the SELECT-then-INSERT pattern has exactly the same race-condition shape as the stock decrement problem -- the UNIQUE constraint is what actually guarantees only one INSERT for a given key can ever succeed, no matter how the retries overlap in time.

A flash sale sells a single limited-edition item with only 1 unit in stock, and you expect very high concurrent demand (thousands of simultaneous purchase attempts in the first second). Would you reach for optimistic locking or pessimistic locking for the stock decrement, and why?

Pessimistic locking (SELECT ... FOR UPDATE) is the better fit here: with thousands of requests racing for a single unit, conflicts aren't rare, they're nearly guaranteed on every attempt after the first. Optimistic locking would mean almost every request fails its version check and has to retry, effectively becoming a busy-retry-loop under exactly the load where you want predictable, serialized handling instead -- which is what row locking provides directly.

Think about how often conflicts will actually occur in this specific scenario, not in general.