Optimistic vs pessimistic locking — when does each one win?

#The core trade-off

Both optimistic and pessimistic locking solve the same problem — two transactions want to touch the same row and you need to stop them from silently clobbering each other's work (the "lost update" anomaly). They just disagree on when to pay the cost of coordination.

Pessimistic locking assumes conflict is likely, so it grabs a lock before doing any work and holds it until the transaction finishes. Optimistic locking assumes conflict is rare, does the work against a private copy of the data, and only checks for conflict at the very end, right before committing.

#Pessimistic locking, in SQL

BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE; -- row is now locked
-- application computes new balance
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT; -- lock released

SELECT ... FOR UPDATE takes a row-level exclusive lock immediately. Any other transaction that tries to SELECT ... FOR UPDATE (or update) that same row simply blocks until this transaction commits or rolls back. No other transaction can even read a stale version and race you to the update.

#Optimistic locking, with a version column

-- Table has a "version" integer column
SELECT id, balance, version FROM accounts WHERE id = 1;
-- application reads balance = 500, version = 7, computes new balance = 400

UPDATE accounts
SET balance = 400, version = version + 1
WHERE id = 1 AND version = 7;
-- application checks: did this UPDATE affect 1 row?

If another transaction updated the row (and bumped the version) in between, this UPDATE matches zero rows — the WHERE version = 7 clause fails — and the application knows to retry: re-read the row, recompute, and try again. No lock was ever held while the application was "thinking."

int updated = jdbcTemplate.update(
    "UPDATE accounts SET balance = ?, version = version + 1 WHERE id = ? AND version = ?",
    newBalance, accountId, expectedVersion
);
if (updated == 0) {
    throw new OptimisticLockException("Row changed concurrently, retry");
}

#When each one wins

#Deadlocks are the tax on pessimistic locking

Pessimistic locking introduces a new failure mode that optimistic locking doesn't have: deadlock. If transaction A locks row 1 then tries to lock row 2, while transaction B locks row 2 then tries to lock row 1, neither can proceed. The database detects the cycle and kills one transaction (the "deadlock victim") to break it.

-- Transaction A
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- ... does some work ...
SELECT * FROM accounts WHERE id = 2 FOR UPDATE; -- blocks, waiting on B

-- Transaction B, running concurrently
BEGIN;
SELECT * FROM accounts WHERE id = 2 FOR UPDATE;
-- ... does some work ...
SELECT * FROM accounts WHERE id = 1 FOR UPDATE; -- blocks, waiting on A -> deadlock