Optimistic vs pessimistic concurrency control — what are the tradeoffs?
Quick Answer
**Pessimistic** concurrency acquires a lock upfront before reading/modifying a row, blocking other transactions from touching it until release — safe by construction, but reduces concurrency and risks deadlocks/long waits. **Optimistic** concurrency assumes conflicts are rare: it reads without locking, then checks at write time (typically via a version/timestamp column) whether the row changed since it was read, retrying or failing if it did. Optimistic control scales better under low contention; pessimistic is safer and simpler under high contention.
Detailed Answer
Pessimistic concurrency control
Lock the row before doing anything, so no one else can modify it until you're done:
BEGIN;
SELECT * FROM seats WHERE id = 42 FOR UPDATE; -- acquires an exclusive row lock
-- ... application logic decides whether the seat can be booked ...
UPDATE seats SET status = 'booked' WHERE id = 42;
COMMIT; -- lock released here
Any other transaction trying to SELECT ... FOR UPDATE (or update) row 42 simply blocks until this transaction commits or rolls back. Pros: conceptually simple, guaranteed to prevent conflicts, no retry logic needed. Cons: reduces concurrency (other transactions wait), and holding locks across slow operations (a network call, user think-time) can cause serious contention or even deadlocks under load.
Optimistic concurrency control
Read without locking, then verify at write time that nothing changed:
-- Read
SELECT id, status, version FROM seats WHERE id = 42;
-- app gets: status='available', version=7
-- ... application logic, possibly slow (user confirms booking) ...
-- Write: only succeeds if version is still 7
UPDATE seats SET status = 'booked', version = version + 1
WHERE id = 42 AND version = 7;
-- Check rows affected: if 0, someone else updated it first -- retry or fail
Pros: no locks held during the "thinking" period, so no blocking of other transactions, and no deadlock risk from this pattern. Cons: requires a version/timestamp column and retry logic in the application, and under high contention, many transactions may repeatedly fail and retry (worse throughput than a lock would have given, ironically) — optimism is a bad bet exactly when conflicts are actually common.
When to use which
- Pessimistic: high contention on the same rows, or when the cost of retrying a failed operation is high (e.g., a multi-step external side effect that's hard to safely redo).
- Optimistic: low contention, read-heavy workloads, or web applications where holding a database lock across a slow client round-trip (think-time) would be unacceptable — e.g., editing a document where two users rarely edit the exact same record simultaneously.
Where this shows up in ORMs
Most ORMs (Entity Framework, Hibernate, etc.) implement optimistic concurrency natively via a RowVersion/@Version column, throwing a concurrency exception on a version mismatch that the application must catch and handle (typically by reloading and prompting the user, or retrying). This is usually preferred over pessimistic locking in typical web applications specifically because holding a database transaction open across an HTTP request/user interaction is almost always the wrong design.