The textbook treatment of concurrency control reads like a binary choice between pessimistic locking (lock the row before reading, release after writing) and optimistic locking (read freely, detect conflicts at commit time, retry on conflict). The textbook is right about the mechanics. It is misleading about the choice.
In practice, the right strategy depends on three things: how often two operations contend for the same row, how expensive the work between read and write is, and how often a conflict, if it happens, can be safely retried. Get those three numbers wrong and pessimistic locking turns into a queue that pages on busy days, or optimistic locking turns into a thrash that wastes the work of every losing request.
The lost update problem
The base case is two requests, both reading the same row, both modifying it, both writing it back. Without locking, the second write overwrites the first, and the change made by the first request is lost. The user posts a comment that disappears. The inventory count drops by one when two items were sold. The account balance reflects only the second of two simultaneous deposits.
This is not a bug in the database. SQL with a default isolation level (READ COMMITTED in PostgreSQL, REPEATABLE READ in MySQL) does not protect you from this; it protects you from reading uncommitted writes, but not from the read-modify-write pattern itself. The protection has to come from the application.
Pessimistic locking
The idea is direct: when a request reads a row it intends to modify, it acquires a write lock on the row. Other requests wanting to modify the same row wait. The first request finishes its work, writes the row, commits, and releases the lock. The next request acquires the lock and proceeds.
In SQL, this is SELECT ... FOR UPDATE. The row is locked until the transaction commits or rolls back. While locked, other transactions wanting to modify it block; transactions that only want to read it are not blocked (in most databases).
Pessimistic locking is correct, predictable, and easy to reason about. It also has costs. The lock is held for the duration of the transaction, which means however long the application takes between the SELECT and the COMMIT, that lock is held. If the application calls an external service while holding the lock, that service's latency becomes lock duration, and other requests for the same row stack up.
This is the failure mode worth naming: pessimistic locking turns into a queue under contention. If many requests want the same row and each holds the lock for 200ms, you can serve five requests per second on that row. If your traffic exceeds five per second, the queue grows without bound, requests time out, and the system fails.
Optimistic locking
The idea is the opposite: do not lock anything. Read the row freely. Do whatever work the application needs. When ready to write, include a check that the row has not changed since you read it. If it has changed, the write fails, the application detects the conflict, and decides whether to retry, surface an error to the user, or merge the changes.
The standard implementation is a version column. The row has a version integer that increments on every update. Reads include the version; writes have the form UPDATE rows SET ..., version = version + 1 WHERE id = ? AND version = ?. If the row has been modified since the read, the version no longer matches, the UPDATE affects zero rows, and the application knows there was a conflict.
Optimistic locking does no waiting in the success case. Two requests reading the same row both proceed in parallel; only one commits successfully and the other has to retry. Under low contention, this is faster than pessimistic locking because there are no lock acquisitions and no serialization.
The failure mode is symmetric to the pessimistic one: under high contention, optimistic locking thrashes. Many requests do their full work, then nine out of ten of them lose the race at commit time and have to retry. The retries do the same work and most of them lose again. The system is doing 10x the useful throughput in wasted work.
Choosing between them
The simple rule: pessimistic locking when contention is high and the work between read and write is short. Optimistic locking when contention is low or the work is long.
"High contention" means many requests for the same row at the same time. Inventory of a popular item during a flash sale, the row counting active sessions for a server, a counter incremented on every page view. "Low contention" means most requests touch different rows. Updating a user's profile, modifying a comment, adjusting an account setting.
"Short work" means microseconds to single-digit milliseconds. "Long work" means hundreds of milliseconds, calls to external services, anything where the read-modify-write window includes a network roundtrip.
If you have high contention and long work, neither strategy works well. Pessimistic locking turns into a queue; optimistic locking turns into thrash. The fix is to redesign the operation: hold the lock only across the database write (not the external call), or break the operation into smaller steps that each commit independently, or move the contended state into a more concurrent data structure.
Hybrid patterns
The most robust real-world approach is often hybrid. Use optimistic locking by default for most rows and most operations; the contention is low and the cost is low. Use pessimistic locking for known hot rows: counters, sequence generators, the row that tracks the next ID for a tenant.
Another hybrid: optimistic locking with a retry budget. The application tries to commit, fails on version mismatch, re-reads the row, redoes the work, and tries again. After three or five retries, it gives up and surfaces an error. This bounds the worst-case thrash and gives you a metric to monitor (retry rate per endpoint).
A third hybrid: switch strategies based on the row. Some applications track the contention rate per row and use a feature flag to elevate hot rows to pessimistic locking. This is rare and worth doing only when the contention is severe enough to justify the engineering.
What to monitor
For pessimistic locking, the metric is lock wait time. PostgreSQL exposes this via pg_locks and pg_stat_activity; MySQL via performance_schema.data_locks. Alert on wait times that exceed 100ms; investigate.
For optimistic locking, the metric is conflict rate. Wrap the UPDATE call and count "rows affected = 0" cases. Express it as a percentage of total writes per endpoint. Alert if it crosses 5%; that is a sign of contention high enough to justify a different strategy on that path.
The deeper point: concurrency control is a load problem disguised as a correctness problem. Both strategies guarantee correctness. The choice is about which failure mode you can tolerate at high load. Pessimistic systems queue; optimistic systems thrash. Pick the one whose failure mode degrades more gracefully for your traffic shape.
The four APIs we run at DocuMint, CronPing, FlagBit, and WebhookVault mostly use optimistic locking with version columns. The exception is FlagBit's flag-evaluation counter, which is contended enough to justify a pessimistic SELECT ... FOR UPDATE. The hybrid is what survives.