Optimistic and Pessimistic Locking in PHP Database Access

1. What problem locking strategies solve

As soon as several processes want to read and modify the same database row at the same time, a classic concurrency problem arises: two users load the same record, both make changes, and whoever saves last silently overwrites the other one's change. Without a locking strategy, nobody notices this data loss, because from the database's point of view every single UPDATE statement succeeded.

Optimistic locking and pessimistic locking are the two established answers to this problem, with fundamentally different philosophies. Optimistic locking assumes conflicts are rare and only checks at save time whether another change has happened in the meantime. Pessimistic locking assumes the opposite and blocks a row already at read time, so no other process can change it in the meantime.

The choice between optimistic locking and pessimistic locking is not an academic question, it has a direct impact on throughput and user experience. Overly aggressive pessimistic locking unnecessarily reduces concurrency and creates waiting times, while overly careless optimistic locking in systems with a high conflict rate forces constant retries.

2. Optimistic locking: version columns as the core principle

The core principle of optimistic locking is an additional column, usually called version, that is incremented on every UPDATE. When loading a record, the application remembers the current version number. When saving, the UPDATE runs with a WHERE condition that checks both the id and the originally read version. If the version has changed in the meantime through another UPDATE, the own statement affects zero rows, which the application recognizes as a conflict.

This mechanism does not require a permanently held database lock. Any amount of time can pass between reading and writing, for example while a user fills out a form, without blocking other processes. Optimistic locking is therefore particularly well suited for web applications, where several seconds or minutes can pass between displaying and saving a form.

3. Implementing optimistic locking in PHP

The concrete implementation of optimistic locking in PHP combines a version column in the database with an UPDATE statement that checks exactly this version. A domain object carries its loaded version as a property, and the repository's save method returns whether the UPDATE actually affected a row. If it affects no row, another process has changed the record in the meantime, and the application must handle the conflict instead of silently ignoring it.

What matters with optimistic locking is actually evaluating the return value of rowCount(). A common mistake is to run the UPDATE and assume success without checking whether any row was actually changed. This check is the core of the entire mechanism.

<?php

declare(strict_types=1);

final class OptimisticLockException extends RuntimeException
{
}

final class ProductRepository
{
    public function __construct(private readonly PDO $pdo)
    {
    }

    public function findById(int $id): ?Product
    {
        $statement = $this->pdo->prepare(
            'SELECT id, name, stock, version FROM products WHERE id = ?'
        );
        $statement->execute([$id]);
        $row = $statement->fetch(PDO::FETCH_ASSOC);

        if ($row === false) {
            return null;
        }

        return new Product((int) $row['id'], (string) $row['name'], (int) $row['stock'], (int) $row['version']);
    }

    /** @throws OptimisticLockException When another process changed the row concurrently. */
    public function save(Product $product): void
    {
        $statement = $this->pdo->prepare(
            'UPDATE products SET name = ?, stock = ?, version = version + 1
             WHERE id = ? AND version = ?'
        );
        $statement->execute([$product->name, $product->stock, $product->id, $product->version]);

        if ($statement->rowCount() === 0) {
            throw new OptimisticLockException(
                "Product {$product->id} was modified concurrently, reload and retry"
            );
        }
    }
}

final class Product
{
    public function __construct(
        public readonly int $id,
        public readonly string $name,
        public readonly int $stock,
        public readonly int $version,
    ) {
    }
}

4. Pessimistic locking: SELECT FOR UPDATE in detail

Pessimistic locking takes the opposite approach: instead of only detecting conflicts at save time, a row is locked against other transactions already at read time. In MySQL and PostgreSQL this happens through SELECT ... FOR UPDATE inside an explicit transaction. Every other transaction that tries to read or modify the same row, also with FOR UPDATE, waits until the holding transaction commits or rolls back.

This lock is particularly valuable for operations that absolutely must stay consistent and where a conflict cannot simply be resolved with another attempt, for example reserving stock or processing payments. Pessimistic locking prevents from the outset that two processes sell the same last available unit of stock at the same time.

5. Pessimistic locking in PHP with PDO transactions

In PHP, pessimistic locking is implemented by starting a transaction, loading the row with SELECT ... FOR UPDATE, running the business logic and then committing the transaction. As long as the transaction stays open, the database holds the lock on the affected row. It is important to keep this transaction as short as possible, because every extra millisecond inside the lock extends the wait time for competing processes.

A common mistake with pessimistic locking is running additional, slow operations such as external HTTP calls or sending emails inside the open transaction. Such operations strictly belong outside the transaction, right after the commit, so the database lock is not held longer than necessary.

<?php

declare(strict_types=1);

final class InventoryService
{
    public function __construct(private readonly PDO $pdo)
    {
    }

    /** @throws RuntimeException When stock is insufficient. */
    public function reserveStock(int $productId, int $quantity): void
    {
        $this->pdo->beginTransaction();

        try {
            // Row is locked for the duration of this transaction
            $statement = $this->pdo->prepare(
                'SELECT stock FROM products WHERE id = ? FOR UPDATE'
            );
            $statement->execute([$productId]);
            $currentStock = (int) $statement->fetchColumn();

            if ($currentStock < $quantity) {
                throw new RuntimeException("Insufficient stock for product {$productId}");
            }

            $update = $this->pdo->prepare(
                'UPDATE products SET stock = stock - ? WHERE id = ?'
            );
            $update->execute([$quantity, $productId]);

            $this->pdo->commit();
        } catch (Throwable $e) {
            $this->pdo->rollBack();
            throw $e;
        }

        // Slow operations happen AFTER commit, outside the lock
        // $this->notifier->sendStockAlert($productId);
    }
}

6. Detecting and systematically avoiding deadlocks

A deadlock arises when two transactions try to lock the same rows in a different order: transaction A holds a lock on row 1 and waits for row 2, while transaction B holds row 2 and waits for row 1. Both transactions wait for each other indefinitely, until the database detects the deadlock and aborts one of the two transactions with an error. Pessimistic locking increases the risk of such deadlocks, because it actively holds locks, while optimistic locking structurally avoids this risk.

The most effective countermeasure against deadlocks with pessimistic locking is a consistent lock ordering: if a transaction has to lock several rows, for example during a transfer between two accounts, all transactions in the system should always lock these rows in the same order, for instance sorted by primary key. Additionally, PHP code should react to a deadlock error from the database with an automatic, bounded retry, since deadlocks in practice can never be fully avoided.

7. Conflict handling: retry strategies for users

With optimistic locking, a detected conflict is not an error case in the classic sense, but a normal, expected branch in the flow. The application should reload the affected record, check the user's changes for conflicts, and either merge them automatically or present the conflicting values to the user for a manual decision. Silently overwriting after a detected conflict would defeat the entire purpose of optimistic locking.

For technical conflicts, such as deadlocks with pessimistic locking, an automatic retry with a short, randomized wait time between attempts is appropriate, in order to prevent multiple retrying processes from colliding with each other again. A fixed upper bound on retry attempts prevents a permanently blocked process from indefinitely burdening the application.

8. Mixed strategies in real world applications

Real world applications rarely rely exclusively on one of the two strategies. A typical pattern is optimistic locking for most forms and edit flows, combined with targeted pessimistic locking for a handful of particularly conflict prone operations such as stock reservation or payment processing. This combination uses the advantages of both approaches without broadly accepting the downsides of either.

Another variant is to implement optimistic locking as the default case and only switch to pessimistic locking for the affected operation once repeatedly observed conflicts show up in production data. This data driven approach prevents premature pessimization of areas that rarely produce conflicts in practice.

9. Optimistic versus pessimistic locking compared

The decision between optimistic locking and pessimistic locking depends on the expected conflict rate, the criticality of the operation and the acceptable wait time.

Optimistic locking is the better default for most applications, because it maximizes concurrency and requires no permanent locks. Pessimistic locking, however, remains indispensable for operations where a conflict detected after the fact is unacceptable, such as preventing double bookings against an account with limited balance.

10. Summary

Optimistic locking and pessimistic locking solve the same fundamental problem, concurrent database access without silent data loss, through different means. A version column with a checked UPDATE is enough for most forms and edit flows, while SELECT ... FOR UPDATE inside short, targeted transactions remains necessary for critical, conflict prone operations such as stock reservation.

In both cases, thoughtful conflict handling matters: with optimistic locking the return value of the UPDATE must actually be checked, with pessimistic locking the lock duration must be minimized and a retry mechanism against deadlocks must be in place. Anyone who understands both strategies can combine them deliberately instead of applying a single solution across the entire application uniformly.

11. FAQ: Optimistic and Pessimistic Locking