Optimistic vs Pessimistic Locking: Picking the Right Concurrency Model

When two users try to update the same row at the same time, one of them is about to overwrite the other's change. The bug is the kind that hides for months — most workloads have low concurrency, the race appears only when traffic spikes or two admins click "save" within a second.

Optimistic and pessimistic locking are the two answers. They have very different runtime profiles and very different user experiences. Picking between them is one of those decisions that compounds: changing later is a real refactor.

Pessimistic Locking

A transaction takes a database-level lock on the rows it intends to update. Other transactions that try to read or modify those rows wait until the first transaction commits or rolls back.

DB::transaction(function () use ($accountId, $amount) {
    $account = DB::table('accounts')
        ->where('id', $accountId)
        ->lockForUpdate()
        ->first();

    $newBalance = $account->balance - $amount;
    if ($newBalance < 0) {
        throw new InsufficientFundsException();
    }

    DB::table('accounts')
        ->where('id', $accountId)
        ->update(['balance' => $newBalance]);
});

The lockForUpdate() acquires a row-level write lock. A second transaction calling the same code waits for the first to commit, then runs against the post-update state. Lost updates are impossible.

The cost is contention. Long-held locks block other workers. If the transaction inside the lock makes external calls, all callers wait on the slowest external call.

Optimistic Locking

The transaction does not lock anything. Instead, it reads the row, does its work, and on commit checks whether the row has changed since it was read. If it has, the commit fails and the application retries or escalates to the user.

The check usually happens through a version column:

CREATE TABLE accounts (
    id BIGINT PRIMARY KEY,
    balance INTEGER NOT NULL,
    version INTEGER NOT NULL DEFAULT 0
);

$account = DB::table('accounts')->where('id', $accountId)->first();
$newBalance = $account->balance - $amount;

$updated = DB::table('accounts')
    ->where('id', $accountId)
    ->where('version', $account->version)
    ->update([
        'balance' => $newBalance,
        'version' => $account->version + 1,
    ]);

if ($updated === 0) {
    throw new ConcurrentModificationException();
}

The WHERE version = ? clause makes the update conditional. If another transaction has updated the row since the read, version no longer matches and the update affects zero rows. The application detects the conflict and decides what to do.

Optimistic locking is non-blocking. Two transactions reading the same row both proceed. The conflict is detected at commit time, and only one wins. The cost is the conflict-handling logic: retry, escalate to the user, merge.

When to Use Each

Pessimistic locking wins when:

• Conflicts are likely (high-concurrency updates to the same rows)
• The work inside the transaction is short and predictable
• Conflict handling on the application side would be complex
• Strong consistency is required (banking, inventory)

Optimistic locking wins when:

• Conflicts are rare (most updates touch different rows)
• The transaction is long-running or contains external calls
• Blocking other transactions for the duration would be unacceptable
• The application can sensibly handle a conflict (retry, ask the user)

Most web application workloads favor optimistic locking. Conflicts are rare, transactions are short, and a "someone else edited this, refresh and try again" UX is acceptable. Most banking and inventory systems use pessimistic locking on the hot path — the cost of a conflict is too high to handle in application code.

The Read-Modify-Write Trap

Both patterns address the same underlying bug, often called the read-modify-write race. Naive code reads a row, computes a new value, and writes it back without checking whether the row changed between read and write.

// Buggy
$account = Account::find($id);
$account->balance -= 100;
$account->save();

If two requests run this concurrently, both reads see the same balance, both subtract 100, both write the same new balance. One subtraction is lost.

The fix is either:

• lockForUpdate() on the read (pessimistic)
• A version check on the write (optimistic)
• A pure update statement: UPDATE accounts SET balance = balance - 100 WHERE id = ?

The last form — letting the database compute the new value — sidesteps the entire issue for simple operations. It is the right answer for counters and balances when the new value is a pure function of the old value plus an increment.

User-Facing Conflict UX

Optimistic locking forces a UX decision. When the conflict happens, what does the user see?

Refresh and retry. "This record was changed by someone else. Refresh and try again." Simple, useful for transactional operations.

Conflict diff. "This record was changed. Here is what is different. Pick a merge." Better UX, much more work. Common in collaborative editors and document workflows.

Last write wins (with a notification). "Your changes were saved, but you may have overwritten someone else's changes." Acceptable for low-value data, dangerous for anything important.

The right choice depends on the cost of the lost change. Picking refresh-and-retry by default is usually fine; upgrade to conflict diffs for high-value fields like contracts and prices.

Database-Specific Notes

PostgreSQL. SELECT ... FOR UPDATE for row locks. SELECT ... FOR UPDATE SKIP LOCKED is invaluable for queue-like workloads — workers can grab the next available row without waiting on others.

MySQL/InnoDB. Same SELECT ... FOR UPDATE syntax. Lock granularity depends on indexes; locking a row through a non-indexed column can escalate to gap locks or table locks.

SQLite. Database-level locking. Pessimistic locks block all other writers. Optimistic locking is usually the better fit at scale.

Deadlocks

Both patterns can produce deadlocks when transactions acquire locks in different orders. Two transactions, each holding a lock the other wants, will wait forever — the database detects this and aborts one.

Mitigations:

• Acquire locks in a consistent order (always update users before orders, never the reverse)
• Keep transactions short
• Use SKIP LOCKED when applicable
• Handle deadlock exceptions in application code by retrying

A small amount of deadlock noise is normal under load. A lot of deadlock noise is a design problem — usually transactions holding locks too long or acquiring them in non-deterministic orders.

A Practical Default

For most web application code, the right baseline:

• Default to optimistic locking with a version column on rows that have concurrent update potential
• Use lockForUpdate() for short, critical-path operations (inventory decrement, balance update)
• Use atomic SQL updates (UPDATE ... SET balance = balance - ?) when the new value is a pure function of the old
• Reserve elaborate conflict resolution for fields where the user genuinely needs to see what changed

Eloquent supports optimistic locking through traits and Laravel packages, or you can implement the version-check pattern in a service. The mechanism matters less than the discipline of always checking.

Working on a feature where two users editing the same record is a real possibility? We help teams pick concurrency models that match the workload and the user experience. scopeforged.com