Pessimistic Locking

Pessimistic locking assumes conflicts are likely: it acquires an actual database lock on a row as soon as it's read for update, blocking other transactions from modifying it until the first transaction finishes.

SELECT ... FOR UPDATE is the standard SQL way to take a pessimistic lock — it reads rows and locks them in the same step, so no other transaction can update or lock them until yours commits or rolls back. This guarantees no lost updates, but at the cost of blocking other transactions, potentially for a long time if the lock is held across slow application logic.

Choose pessimistic locking when conflicts are frequent or the cost of a failed optimistic retry is high; choose optimistic locking when conflicts are rare and holding a lock for the whole read-think-write cycle would hurt concurrency too much.

SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- row is locked until this transaction commits or rolls back