Optimistic vs pessimistic locking?

Understand the concept

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.

Important ideas

• 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.