Transactions and Concurrency — PHP 8: Modern PHP Development

Q: How do you prevent two requests from overselling the last item?

Use an atomic conditional update (`UPDATE ... SET stock = stock - 1 WHERE id = ? AND stock >= 1`) and check `rowCount()`, or lock the row with `SELECT ... FOR UPDATE` inside a transaction.

Wrap related writes in a transaction so they succeed or fail together. Keep transactions short and never call slow external services inside them. Handle concurrency with conditional updates (UPDATE ... WHERE stock >= :qty), optimistic locking (a version column), or pessimistic locking (SELECT ... FOR UPDATE). Retry on deadlocks/serialization failures.

<?php
function transferStock(PDO $pdo, int $productId, int $qty, int $orderId): void
{
    $pdo->beginTransaction();
    try {
        $stmt = $pdo->prepare('UPDATE products SET stock = stock - :qty WHERE id = :id AND stock >= :qty');
        $stmt->execute(['qty' => $qty, 'id' => $productId]);
        if ($stmt->rowCount() === 0) {
            throw new OutOfStock($productId);
        }
        $pdo->prepare('INSERT INTO order_items (order_id, product_id, qty) VALUES (?, ?, ?)')
            ->execute([$orderId, $productId, $qty]);
        $pdo->commit();
    } catch (Throwable $e) {
        $pdo->rollBack();
        throw $e;
    }
}

Common pitfalls

Check-then-update races (SELECT stock then UPDATE) without locks.
Long transactions holding locks while calling APIs or sending email.
Forgetting to roll back on exceptions.

Key points

Transactions make multi-step writes atomic
Conditional updates prevent overselling
Keep transactions short; retry deadlocks

Further reading: PHP manual: PDO transactions

Practice questions

How do you prevent two requests from overselling the last item?

Answer

Use an atomic conditional update (UPDATE ... SET stock = stock - 1 WHERE id = ? AND stock >= 1) and check rowCount(), or lock the row with SELECT ... FOR UPDATE inside a transaction.

Why shouldn't you send an email inside a database transaction?

Answer

It holds locks while waiting on a slow external service, and if the transaction rolls back the email has already been sent. Use an outbox table and send after commit.