Optimistic vs. Pessimistic Locking in Applications

1. Why Concurrency Becomes a Problem in Databases

As soon as two transactions read, change, and write back the same record at the same time, a classic race condition emerges: the lost update. Transaction A reads a balance of 100, transaction B reads the same value shortly afterward, both calculate a new value independently, and both write it back. The transaction that writes last overwrites the change of the first one, without any error being raised. Optimistic Locking and Pessimistic Locking both prevent exactly this scenario, but with fundamentally different mechanisms.

Pessimistic Locking blocks the record already at read time, so that no second transaction can write to it concurrently. Optimistic Locking, on the other hand, allows parallel read access and only checks at write time whether the record was changed in the meantime. Both approaches correctly solve the lost update problem, but they differ massively in throughput, wait times, and implementation effort. The following sections show both locking strategies with concrete SQL and PHP code and provide clear decision criteria for the respective use case.

2. Pessimistic Locking with SELECT FOR UPDATE

Pessimistic Locking assumes that a conflict is likely and prevents it proactively through a lock. In MySQL with InnoDB, this is achieved with SELECT ... FOR UPDATE inside an explicit transaction. The statement sets an exclusive row lock on the rows read, which remains in place until COMMIT or ROLLBACK. Every other transaction that tries to read the same row with FOR UPDATE or write it directly is blocked until the first transaction releases the lock.

This form of Pessimistic Locking is particularly well suited to scenarios with a high probability of conflict, such as reserving limited inventory or posting payments to an account. The advantage lies in its simplicity: there is no race condition, because competing accesses are processed serially. The downside is the wait time, which adds up under heavy load, along with the risk of deadlocks when multiple transactions request locks in different orders.

-- Pessimistic Locking: exclusive row lock until COMMIT
START TRANSACTION;

SELECT id, quantity, reserved
FROM inventory
WHERE product_id = 4711
FOR UPDATE;
-- Row is locked for all other transactions now

-- Application logic checks availability
UPDATE inventory
SET reserved = reserved + 1
WHERE product_id = 4711
  AND (quantity - reserved) >= 1;

COMMIT;
-- Lock is released, waiting transactions can proceed

3. Optimistic Locking with a Version Column

Optimistic Locking forgoes database locks entirely during the read phase. Instead, each table gets an additional column, usually called version, which is incremented by one on every successful update. When writing, the UPDATE statement checks in the WHERE clause whether the version still matches the value captured at read time. If it does not match, another transaction has changed the record in the meantime, and the update affects zero rows, which the application recognizes as a conflict.

The central advantage of Optimistic Locking is the absence of a waiting state: reads block nothing, and write conflicts are only detected at the moment of commit, not upfront through a lock. This significantly increases throughput for applications with a low probability of conflict, such as editing user profiles or forms where two people rarely edit the same record at the same time. The downside: the application must handle the conflict case explicitly, usually through an error message or a retry mechanism.

-- Optimistic Locking: version column detects concurrent writes
CREATE TABLE product (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  version INT NOT NULL DEFAULT 1
) ENGINE=InnoDB;

-- Step 1: read current state including version
SELECT id, name, price, version FROM product WHERE id = 42;
-- Application caches version = 5

-- Step 2: write with version check in WHERE clause
UPDATE product
SET price = 29.90, version = version + 1
WHERE id = 42 AND version = 5;
-- affected rows = 0 means a concurrent write happened

4. Implementing Optimistic Locking in PHP with PDO

Implementing Optimistic Locking at the application layer follows a fixed pattern: load the record including its version, make the changes on the PHP object, then run a conditional update and check the number of affected rows. PDO provides this information via PDOStatement::rowCount(). If the value is zero even though the WHERE condition targets the correct ID, the version was no longer current, and the application must react instead of silently ignoring the error.

It is important that the version check and the update happen in a single atomic SQL statement, not as a separate SELECT followed by an unchecked UPDATE. Otherwise a race condition window reopens between the check and the write, rendering the entire Optimistic Locking scheme ineffective. The following class shows a clean implementation with explicit conflict handling.

<?php
declare(strict_types=1);

final class OptimisticLockException extends RuntimeException
{
}

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

    /**
     * Loads a product together with its current version number.
     */
    public function find(int $id): array
    {
        $stmt = $this->pdo->prepare(
            'SELECT id, name, price, version FROM product WHERE id = :id'
        );
        $stmt->execute(['id' => $id]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);

        if ($row === false) {
            throw new RuntimeException("Product {$id} not found");
        }

        return $row;
    }

    /**
     * Updates the price using optimistic locking.
     * Throws OptimisticLockException on a version conflict.
     */
    public function updatePrice(int $id, float $price, int $expectedVersion): void
    {
        $stmt = $this->pdo->prepare(
            'UPDATE product
             SET price = :price, version = version + 1
             WHERE id = :id AND version = :version'
        );
        $stmt->execute([
            'price' => $price,
            'id' => $id,
            'version' => $expectedVersion,
        ]);

        if ($stmt->rowCount() === 0) {
            throw new OptimisticLockException(
                "Product {$id} was modified concurrently, version {$expectedVersion} is stale"
            );
        }
    }
}

5. Detecting and Avoiding Deadlocks with Pessimistic Locking

A deadlock occurs under Pessimistic Locking when two transactions mutually wait for locks held by the other transaction. Transaction A locks row 1 and waits for row 2, while transaction B locks row 2 and waits for row 1. InnoDB detects this situation automatically through a wait for graph and aborts one of the two transactions with the error ERROR 1213: Deadlock found when trying to get lock. The application must catch this error and re-execute the affected transaction.

The most effective protection against deadlocks is a consistent lock order: if all transactions always lock rows in the same order, for example sorted by primary key, no cycle can form in the wait for graph. In addition, a short transaction duration reduces the overall probability of conflicts, because the time window for competing lock requests becomes smaller. The table information_schema.innodb_trx and the command SHOW ENGINE INNODB STATUS provide details about the most recently detected deadlock, including the statements involved.

6. Retry Strategies for Optimistic Locking Conflicts

A detected conflict under Optimistic Locking is not an error in the technical sense but a normal execution branch that the application must handle. The common strategy is a retry with a fresh reload of the current record: the application catches the OptimisticLockException, reloads the record with the new version, applies the business change to the current state, and attempts the update again. A limited number of attempts prevents infinite loops under persistently high contention on the same record.

For conflicts that cannot be resolved automatically, for example when two users have edited the same text independently, an automatic retry is risky from a business perspective. Here the application should report the conflict back to the user instead of silently discarding a change. Exponential backoff between retry attempts additionally reduces load on heavily contested records.

<?php
declare(strict_types=1);

/**
 * Retries an optimistic-locking update with exponential backoff.
 */
function updateWithRetry(ProductRepository $repo, int $id, float $newPrice, int $maxAttempts = 3): void
{
    $attempt = 0;

    while (true) {
        $attempt++;
        $product = $repo->find($id);

        try {
            $repo->updatePrice($id, $newPrice, (int) $product['version']);
            return;
        } catch (OptimisticLockException $e) {
            if ($attempt >= $maxAttempts) {
                throw $e;
            }
            usleep(50_000 * (2 ** $attempt)); // exponential backoff
        }
    }
}

7. Decision Criteria: Which Model for Which Case

The choice between Optimistic Locking and Pessimistic Locking depends heavily on the probability of conflict. With a low probability of conflict, meaning rarely two transactions edit the same record at the same time, Optimistic Locking is almost always the better choice, because it creates no unnecessary waiting state. Classic examples are CMS articles, user profiles, and form editing, where conflicts are the exception.

With a high probability of conflict, such as limited inventory during a sale event or account balances with many concurrent postings, Pessimistic Locking outweighs the advantages, because repeated retries under Optimistic Locking can themselves become a performance problem under heavy contention. A third criterion is transaction duration: Pessimistic Locking should only be used for short transactions, since long held locks restrict the concurrency of the entire system.

8. Practical Example: Inventory in an E-Commerce Application

A typical practical example for Pessimistic Locking is reserving inventory during a checkout process. Several customers could put the last available unit of an item into their cart at the same time. Without a lock, both transactions would check the available stock independently, both would find a sufficient quantity, and both would reserve successfully, even though only one unit exists. The following example combines SELECT ... FOR UPDATE with a condition in the subsequent UPDATE statement as an additional safeguard.

For the associated product description, which is edited concurrently far less often, Optimistic Locking with a version column is a better fit, because read access to the product catalog should not be blocked by write locks. This combination of both strategies within the same application is common: each table gets the locking model that matches its actual access pattern, instead of applying a single strategy across the entire database.

-- Pessimistic Locking for high-contention inventory reservation
START TRANSACTION;

SELECT quantity, reserved
FROM inventory
WHERE product_id = 4711
FOR UPDATE;

UPDATE inventory
SET reserved = reserved + 1
WHERE product_id = 4711
  AND (quantity - reserved) >= 1;

-- Check affected rows in the application; 0 means sold out
COMMIT;

9. Optimistic Locking and Pessimistic Locking Compared

Both locking strategies solve the same business problem with different trade-offs in throughput, latency, and implementation effort. The following table summarizes the key differences and helps with a quick assessment in a concrete project.

In practice, combining both strategies within one application is normal and reasonable. Critical, heavily contested resources such as inventory benefit from Pessimistic Locking, while most of the application, with a low probability of conflict, benefits from the better scalability of Optimistic Locking.

10. Summary

Optimistic Locking and Pessimistic Locking both solve the lost update problem for concurrent access to records, but differ fundamentally in when the conflict is detected. Pessimistic Locking with SELECT ... FOR UPDATE proactively prevents conflicts through locks and suits scenarios with a high probability of conflict, such as inventory reservations. Optimistic Locking with a version column only detects conflicts at write time and needs a retry strategy at the application layer, but offers significantly higher throughput under low contention.

The right decision is made per table and per use case, not globally for the entire database. Anyone who masters both strategies and combines them deliberately avoids both unnecessary wait times and silent data loss from overwritten changes. Consistent lock ordering and short transactions further reduce deadlock risk under Pessimistic Locking, while a limited number of retries with backoff prevent infinite loops under Optimistic Locking.

11. FAQ: Optimistic vs. Pessimistic Locking