Optimistic vs. Pessimistic Locking: Trade-offs in Concurrency Control

"Two users modify the same row at the same time": every backend developer eventually encounters this problem. The simplest implementation is to read, modify, and write back. It appears sound, but once concurrency rises, a later request silently overwrites an earlier change and corrupts the data. This error raises no exception and causes no crash. It may be discovered only during reconciliation months later, making it one of the most insidious and expensive classes of bugs.

There are only two ways to address this problem: optimistic locking (assume there is no conflict and check at commit time) and pessimistic locking (assume there will be a conflict and lock during the read). Which should you choose? It depends on the contention rate. This article explains the principles, implementations, and use cases of both approaches, then presents my default choice in practice: start optimistic and switch to pessimistic only if necessary.

1. Define the Problem First: How Lost Updates Happen

Suppose the balance for id=1 in the accounts table is 500. Two requests arrive almost simultaneously, and each needs to deduct 100. The simplest code looks like this:

-- Step 1: read
SELECT balance FROM accounts WHERE id = 1;   -- both read 500
-- Step 2: the application calculates 500 - 100 = 400
-- Step 3: write back
UPDATE accounts SET balance = 400 WHERE id = 1;

The timeline for the two transactions is:

T1: SELECT balance = 500
T2: SELECT balance = 500     ← both read the old value
T1: calculate 400, UPDATE writes 400
T2: calculate 400, UPDATE writes 400   ← overwrites T1
COMMIT / COMMIT

The correct result should be 500 - 100 - 100 = 300, but the actual result is 400. One hundred units have vanished into thin air. This is a lost update: the later write overwrites the earlier one, as though the first change never happened.

Many people assume that changing the isolation level will solve the problem, but the default READ COMMITTED level in PostgreSQL/MySQL does not prevent it. Each read independently sees the latest committed value, so there is no logical conflict. Even raising the level to SERIALIZABLE merely turns the problem into "abort and make the application retry"; the application must still handle the conflict. Lost updates therefore cannot be solved automatically by the isolation level. You must actively prevent them with locking or version checks. There are two defensive mechanisms: optimistic locking and pessimistic locking.

2. Optimistic Locking: Check Only When Committing, Without Locking First

Optimistic locking follows the assumption, "I expect that no one else will contend with me." It acquires no lock while reading. Only when writing the data back does it check whether anyone has changed the row since it was read. How? Add a version field:

-- Read without locking
SELECT id, balance, version FROM accounts WHERE id = 1;
-- Suppose the result is balance=500, version=7

-- Include the version in the write condition
UPDATE accounts
SET balance = 400, version = version + 1
WHERE id = 1 AND version = 7;
-- Rows affected = 1: no one changed it, so the update succeeded
-- Rows affected = 0: version has changed, so there is a conflict → retry

The key is WHERE id = 1 AND version = 7. If someone changes the version from 7 to 8 between your read and write, your UPDATE no longer matches the row, and the affected-row count is 0. The database is telling you, "The version you used is stale." When the application sees that zero rows were affected, it follows the retry path: read again, recalculate, and try again.

Throughout this process, there are no row locks and no waiting. The read is an ordinary read, and the write is conditional. If it fails, the operation starts over. Optimistic locking therefore provides high throughput: as long as conflicts are rare, its performance is almost the same as having no lock at all.

3. Pessimistic Locking: Lock During the Read and Make Others Wait

Pessimistic locking takes the opposite view: "I assume someone will contend with me, so I will lock the row first." When reading data inside a transaction, it explicitly acquires a row lock with SELECT ... FOR UPDATE:

BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
-- The current transaction now holds a lock on this row
-- The application reads 500 and calculates 400
UPDATE accounts SET balance = 400 WHERE id = 1;
COMMIT;   -- The lock is released only here

When the second transaction reaches SELECT ... FOR UPDATE, it blocks and waits because the row is locked. Only after the first transaction commits and releases the lock can the second transaction read the latest value, which is now 400, and continue to calculate 300. The two deductions are forcibly serialized, so neither can overwrite the other.

The cost is obvious: the second request must wait. Pessimistic locking prevents conflicts but directly reduces concurrency. Only one transaction can modify the row at a time, while every other transaction queues behind it. The longer the lock is held, the longer the queue and the lower the throughput. (The previous article, "PostgreSQL Locking and Deadlocks," covered the four FOR UPDATE lock strengths, NOWAIT/SKIP LOCKED, and related details, so they are not repeated here.)

4. Use Cases: Contention Rate Is the Only Dividing Line

The choice between optimistic and pessimistic locking depends on one metric: contention rate, or the probability that the same row will be modified concurrently. The typical scenarios compare as follows:

In one sentence: optimistic locking wins when contention is low because it does not block; pessimistic locking wins when contention is high because it avoids an avalanche of retries. In an extreme case such as deducting inventory during a flash sale, hundreds of people may contend for the same product. Optimistic locking would make most requests retry and fail while wasting CPU. Pessimistic locking, or even moving the deductions into a queue for serialization, is the correct solution in that case.

5. Implementing Optimistic Locking: Version Fields, CAS, and ABA

Several implementation details of optimistic locking deserve close attention.

Using a version field is the most reliable approach. Use an incrementing integer, execute version = version + 1 on every UPDATE, and include the version that was read in the WHERE clause. The version must increase monotonically, which makes the ABA problem virtually impossible. Create a regular index on it so WHERE version = X remains fast.

An updated_at timestamp also works, but it has many pitfalls. Replace the WHERE condition with WHERE updated_at = :read_ts, and the semantics are the same. However, timestamps may lack sufficient precision, causing equal values to collide during rapid updates. Clocks on different machines may drift, and an ORM may silently change updated_at through an automatic update. Any of these can make the condition unexpectedly fail to match. Use a version field whenever possible; use a timestamp only when no version field exists and the schema cannot be changed.

This is CAS semantics. Optimistic locking is fundamentally Compare-And-Swap: compare the version, and replace the value if it matches. This is the same idea as atomic operations in concurrent programming, such as Java's AtomicInteger.compareAndSet and Go's sync/atomic. Here, however, the comparison and swap happen in a single SQL statement. The database guarantees that the UPDATE's WHERE and SET operations execute atomically, with nothing interleaving between them. You therefore do not need an additional lock to protect this CAS; the SQL statement itself is atomic.

The retry loop looks like this in pseudocode:

for attempt in range(MAX_RETRY):           # Usually 3 attempts
    row = db.query("SELECT id, balance, version FROM accounts WHERE id = ?", id)
    new_balance = row.balance - 100
    affected = db.execute(
        "UPDATE accounts SET balance = ?, version = version + 1 WHERE id = ? AND version = ?",
        new_balance, id, row.version)
    if affected == 1:
        return success
    # affected == 0 → conflict; back off before retrying
    sleep(random_backoff())
return conflict                              # Retries exhausted; return a conflict

The ABA problem. Some people worry that "version changes from 7 → 8 → 7" could defeat the check. As long as the version is a monotonically increasing integer that always increments by 1 and never resets, it cannot follow A→B→A and return to its original value. An incrementing version therefore makes optimistic locking inherently immune to ABA. This is much simpler than handling ABA in lock-free data structures, so there is no need to overthink it.

6. Implementing Pessimistic Locking: FOR UPDATE and Deadlocks

The core of pessimistic locking is SELECT ... FOR UPDATE, but several practical points matter.

It must run inside a transaction, and the transaction must be short. A FOR UPDATE lock is held until COMMIT or ROLLBACK. The entire read-calculate-write sequence must therefore happen in one transaction, and that transaction should be as short as possible. The previous article established an iron rule: never call an external HTTP service, send email, or perform expensive computation inside a transaction. These operations prolong the transaction, leave the lock held, queue every concurrent request, and make the endpoint appear frozen.

Know how to use the two modifiers. NOWAIT fails immediately if the lock is unavailable. It suits idempotency checks and repeated user clicks where failure is preferable to queuing. SKIP LOCKED skips locked rows and is ideal for workers claiming tasks from a queue (WHERE status='pending' FOR UPDATE SKIP LOCKED LIMIT 10), allowing multiple workers to run in parallel without colliding.

Deadlocks are the most important side effect of pessimistic locking to watch for. In the classic scenario, transaction A locks row 1 and then row 2, while transaction B locks row 2 and then row 1. Each waits for the other to release its lock, creating a deadlock. PostgreSQL and MySQL both detect deadlocks and terminate one transaction with an error, as discussed in detail in the previous article. The fundamental solution, however, is a consistent lock-acquisition order: if every transaction acquires locks in ascending primary-key order, cyclic waits cannot form. When updating multiple rows, use WHERE id IN (...) ORDER BY id to acquire locks in order, and deadlocks will virtually disappear.

Do not lock too much or for too long. Larger lock granularity and longer holding times reduce concurrency. Prefer row locks to table locks, prefer locking one row to locking a batch, and commit as soon as the transaction is complete. The engineering discipline for pessimistic locking comes down to one rule: acquire late, release early, and use the smallest possible granularity.

7. Engineering Judgment: Default to Optimistic, Then Switch If Contention Rises

With the theory covered, how should you choose in practice? My default strategy is simple: use optimistic locking for a new feature, observe its contention rate for a while, and switch to pessimistic locking if the rate becomes high.

There are three reasons. First, most business workloads naturally have low contention. Web applications are usually read-heavy and write-light, with users modifying their own separate data. Two requests rarely collide on the same row, so optimistic locking almost never retries and delivers maximum throughput. Second, the cost of pessimistic locking is deterministic. It continuously reduces concurrency even when there is no conflict. An endpoint that handles 1,000 QPS without locks might handle only 200 QPS after adding FOR UPDATE, because every request queues for the same row. An unnecessary pessimistic lock sacrifices performance to buy insurance you do not need. Third, optimistic locking can be upgraded at any time. If a table shows a high contention rate, frequent retries, and surging conflict logs, switch that specific path to pessimistic locking. The change remains confined to that write path and does not affect anything else.

Conversely, do not start by adding pessimistic locks everywhere. I have seen too many developers wrap every write operation in FOR UPDATE "for safety," only to find that lock waits are preventing the system from scaling. Safety has a cost, and locking by default means reducing concurrency by default. Unless you already know that a row will be hot, such as inventory or a balance, start with optimistic locking and let the data determine the next step.

What should you monitor? Contention rate, measured as the percentage of optimistic UPDATE statements that affect zero rows, and the distribution of retry counts. If the contention rate remains below 5%, optimistic locking is entirely adequate. If it rises above 30% for a table and each request retries two or three times on average, consider pessimistic locking. One compromise is optimistic locking + N retries, followed by a fallback to pessimistic locking if conflicts continue. Most requests receive lock-free high throughput, while the small number of highly contended cases are serialized as a second layer of protection.

8. What About Multiple Processes? Database Row Locks vs. Distributed Locks

One final common question: when multiple service processes, or even multiple services, must coordinate access to the same resource, which lock should you use?

If the resource is in the database, prefer a database row lock (SELECT FOR UPDATE). PostgreSQL/MySQL row locks apply to every connection. Whether the connections come from one process's connection pool or different services on ten machines, FOR UPDATE is a reliable arbiter as long as they connect to the same database. The database itself guarantees consistency. This is the simplest and most reliable cross-process lock, and it requires no additional component.

Use a Redis distributed lock only when the resource is outside the database, such as enforcing that only one instance of a task may run at a time. The key points of a Redis lock are SET NX EX with a unique token and atomic release through a Lua script to prevent one client from releasing another's lock. The previous article, "Redis's Role in Distributed Systems," covered this in detail. Remember one point here: a Redis lock provides "high performance + best effort," not an absolute correctness guarantee. Do not use a Redis lock for strongly consistent operations such as financial debits; use a database row lock or etcd.

The decision is simple: resource in the DB → DB row lock; resource outside the DB and requiring cross-process mutual exclusion → Redis distributed lock. Do not push a problem that a database row lock can solve into Redis, and do not expect a Redis lock to protect strong database consistency. Each mechanism has its own responsibility.

Summary

1. Lost updates are the most insidious concurrency bug: two transactions interleave their read-calculate-write sequences, and the later write silently overwrites the earlier one. The default isolation level cannot prevent this; optimistic or pessimistic locking must actively defend against it.
2. Optimistic locking uses version checks: a version field plus WHERE version=X UPDATE; zero affected rows indicates a conflict, and the application retries. There are no locks or waits, so it offers the highest throughput under low contention. An incrementing version is inherently immune to ABA.
3. Pessimistic locking uses FOR UPDATE: it locks the row during the read and forces serialization, preventing conflicts at the cost of concurrency. Because this cost is deterministic, it is worthwhile only when contention is high. Keep transactions short and acquire locks in a consistent order to avoid deadlocks.
4. Contention rate is the only dividing line: choose optimistic locking for low contention, such as read-heavy workloads with dispersed users, and pessimistic locking for high contention, such as hot rows, inventory deductions, and flash sales.
5. Default to optimistic, then switch if contention rises: do not apply FOR UPDATE indiscriminately in the name of "safety." Start with optimistic locking, monitor the contention rate, and upgrade when retries become frequent. This offers the best balance of performance and correctness. For cross-process resources, prefer database row locks; use Redis distributed locks only for resources outside the DB.

In one sentence: optimistic locking bets that no one will contend, while pessimistic locking assumes someone will. A successful bet is fast; justified caution is reliable. Start with the bet, and become cautious only when it stops paying off. That is the most pragmatic approach to concurrency control.