Optimistic vs pessimistic locking: how to choose

Two requests read the same row, each computes a new value, and each writes it back. The second write wins, the first vanishes, and no error is raised anywhere. That is a lost update, and it is the problem both locking styles exist to solve.

The lost update, concretely

SELECT balance FROM accounts WHERE id = 42;   -- reads 100.00
UPDATE accounts SET balance = 55.00 WHERE id = 42;  -- blind write

Neither statement is wrong alone. The bug lives in the gap: the UPDATE carries no assumption about what the row contained when it was read. A support agent approving a refund while the customer cancels the same order hits this in production, usually on a Friday.

There are exactly two families of fix. Prevent the conflict, or detect it.

Pessimistic locking: claim the row before you read it

Pessimistic locking assumes a conflict is likely, so it takes a lock first and makes everyone else wait.

BEGIN;
SELECT balance FROM accounts WHERE id = 42 FOR UPDATE;
-- decide, then write
UPDATE accounts SET balance = 55.00 WHERE id = 42;
COMMIT;

The second transaction blocks at the SELECT until the first commits, then reads the committed value. Correct by construction, paid for with a held lock.

The lock lives until COMMIT or ROLLBACK. Never hold it across a network call, a file upload or a user prompt.
In InnoDB, FOR UPDATE locks the scanned index range with next-key locks, so a WHERE clause without an index locks far more rows than you expect. Lock by primary key or a unique index.
FOR UPDATE NOWAIT fails instantly instead of waiting; FOR UPDATE SKIP LOCKED skips busy rows entirely. Both are documented in the MySQL locking reads and PostgreSQL explicit locking chapters.
MySQL waits up to innodb_lock_wait_timeout (50 seconds by default) before error 1205, so one hot row becomes a queue of sleeping connections.

SKIP LOCKED is for claiming work, not reading state

BEGIN;
SELECT id, payload FROM jobs
WHERE status = 'queued'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;
UPDATE jobs SET status = 'running', started_at = NOW() WHERE id = 7;
COMMIT;

Ten workers pull ten different rows instead of blocking on one. Use it to hand out work items. Do not use it to read a balance, because skipping a locked row means reading a set that is not a consistent snapshot.

Optimistic locking: a version column and a retry

Optimistic locking assumes conflicts are rare, so it detects them at write time. Add a version column and make every update conditional on the version you read:

UPDATE documents
SET title = :title, version = version + 1
WHERE id = :id AND version = :expected_version;

Zero affected rows means someone else won the race. The application reloads the row and tries again.

A retry loop in PHP with PDO

function updateTitle(PDO $pdo, $id, $title) {
    for ($i = 0; $i < 5; $i++) {
        $stmt = $pdo->prepare('SELECT title, version FROM documents WHERE id = ?');
        $stmt->execute(array($id));
        $row = $stmt->fetch();
        $upd = $pdo->prepare(
            'UPDATE documents SET title = ?, version = version + 1 '
            . 'WHERE id = ? AND version = ?'
        );
        $upd->execute(array($title, $id, $row['version']));
        if ($upd->rowCount() === 1) {
            return;
        }
        usleep(mt_rand(1000, 5000) * (1 << $i));  // backoff with jitter
    }
    throw new RuntimeException('Could not update document ' . $id);
}

Details that keep this honest: re-read inside the loop, cap the attempts, add jitter so retries do not resynchronise into a thundering herd, and only retry work that is safe to repeat. If the retried block already sent an email or charged a card, you have written a duplicate-charge generator with extra steps.

Isolation levels change what you still have to do

READ COMMITTED is the default in PostgreSQL, Oracle and SQL Server, and it allows the lost update above. Here a version check or FOR UPDATE is mandatory.
REPEATABLE READ is the InnoDB default. Plain reads become a consistent snapshot, and a losing PostgreSQL writer is aborted with could not serialize access due to concurrent update (SQLSTATE 40001). The database detects the conflict for you, but the retry loop is still your job.
SERIALIZABLE removes most anomalies by converting them into serialization failures. Expect retries and lower throughput, not zero application code.

A version column is not a substitute for an isolation level, and neither one removes the need for retry logic.

Deadlocks, and why retries are not optional

Pessimistic locking adds a failure mode the optimistic style does not have: two transactions locking rows in opposite order deadlock. InnoDB detects it and rolls one transaction back with error 1213; PostgreSQL aborts with SQLSTATE 40P01. The victim loses every statement in that transaction, so it must be retried from BEGIN, not from the failed statement. Consistent lock ordering (always A before B) and short transactions prevent most deadlocks; the retry loop handles the rest.

When neither lock is the right answer

Extreme contention on one row. A counter updated thousands of times per second serialises no matter which mechanism you pick. Use UPDATE counters SET n = n + 1, which needs no read at all, or split the value across shards.
Cross-service workflows. A row lock cannot span two services. Use a queue with idempotent consumers, or a saga with compensating actions.
Genuine single-writer designs. When one process owns the data, a log shipper or a single-threaded actor, there is no concurrent writer to guard and both mechanisms are pure overhead.

A rule of thumb you can apply today

Default to optimistic for web CRUD. Conflicts are rare, nothing is locked while a page renders, and the version value doubles as a useful ETag.
Switch to FOR UPDATE when the value must stay trustworthy for a while: allocating a limited resource, enforcing a financial invariant, or handing out sequence numbers.
Use SKIP LOCKED only to claim work items, never to read state that must be consistent.
Give every concurrent write path a bounded retry with backoff and jitter, and make retried work idempotent.
When contention is structural, stop tuning locks and change the design: queue it, shard it, or make the update relative instead of absolute.