Optimistic vs pessimistic locking?

Definition:

Optimistic locking checks whether data changed before committing an update, often by comparing a version number. Pessimistic locking acquires a database lock before performing a critical change. The first avoids waiting when conflicts are rare; the second coordinates competing writers but can increase waits and deadlocks.

Key points:

• Optimistic: update only if a version still matches; retry or reject conflicts.

• Pessimistic: lock needed rows before changing them, for example with FOR UPDATE.

• A transaction alone does not prevent every lost update; choose the right update and isolation strategy.

• A version check must be part of the UPDATE condition, and the application must check how many rows were affected.

Example — optimistic locking:

UPDATE orders
SET status = 'approved', version = version + 1
WHERE id = $1 AND version = $2;

• Bind the order ID and version read earlier. Zero affected rows means the row is missing or its version changed; reload or report a conflict.

Interview point:

• Choose based on conflict frequency and business behavior on conflict, not the assumption that one strategy is always faster.