Pessimistic vs Optimistic Locking

Concurrent updates to the same row need a policy. Pessimistic locking assumes conflict is likely and serialises writers with locks. Optimistic concurrency control (OCC) assumes conflict is rare, lets transactions proceed, and aborts when a version check fails. Neither is universally better; contention rate and conflict cost decide.

Pessimistic locking

A transaction acquires a lock before reading or updating protected data. Others wait (or fail quickly) until the lock is released at commit/rollback.

BEGIN;
SELECT balance FROM accounts WHERE id = @id FOR UPDATE;
-- application logic using balance
UPDATE accounts SET balance = @newBalance WHERE id = @id;
COMMIT;

Pessimistic lock

A database lock taken early so other transactions cannot modify the row until the holder commits or rolls back.

Optimistic concurrency

Each row carries a version (integer, timestamp, or rowversion). Readers note the version; writers include it in the update predicate. If zero rows update, someone else won—retry or surface a conflict.

UPDATE accounts
SET balance = @newBalance, version = version + 1
WHERE id = @id AND version = @expectedVersion;
-- if rowcount = 0 → conflict

public async Task WithdrawAsync(AccountId id, Money amount, CancellationToken ct)
{
    for (var attempt = 0; attempt < 5; attempt++)
    {
        var account = await _store.GetAsync(id, ct);
        account.Withdraw(amount); // domain invariant

        var updated = await _store.TrySaveAsync(account, ct); // WHERE version = ...
        if (updated) return;

        // conflict — reload and retry
    }

    throw new ConcurrencyException(id);
}

Optimistic concurrency control

Allow concurrent work without locks; detect write-write conflicts at commit time using versions or compare-and-swap predicates.

Side-by-side

BEGIN;SELECT * FROM documentsWHERE id = @idFOR UPDATE;UPDATE documentsSET body = @bodyWHERE id = @id;COMMIT;

UPDATE documentsSET body = @body,  version = version + 1WHERE id = @idAND version = @version;-- 0 rows → reload / retry / conflict

Choosing a strategy

Often the best “lock” is neither: a single SQL statement that encodes the invariant (UPDATE ... WHERE quantity >= @qty), which is atomic without an explicit select-for-update round trip.

Isolation levels and surprises

Pessimistic locks interact with isolation levels (READ COMMITTED vs REPEATABLE READ vs SERIALIZABLE). OCC often pairs with detecting lost updates even under weaker isolation by using versions. Do not assume “we use transactions” alone prevents lost updates—lost update is classic under read-modify-write without protection.

Fit in application architecture

In hexagonal architecture, version fields live on persistence models or aggregates; adapters translate conflicts into domain results. Under scaling writes, hot keys may need queues in addition to locking so the database is not the only scheduler. With sharding, locks do not span shards—design aggregates to stay on one shard.

Practical guidelines

1. Prefer atomic SQL for simple counters and balances
2. Use OCC for collaborative edits and APIs
3. Use pessimistic locks for brief, high-conflict server-side sections
4. Cap retries with backoff; surface conflicts to users when appropriate
5. Measure conflict rates—OCC that retries endlessly is a design smell