Optimistic vs Pessimistic Locking

Pessimistic locking prevents conflicts by locking a record before changing it, so other transactions wait. Optimistic locking lets transactions proceed without locks and checks at write time that nobody else changed the record, retrying or failing if they did. Both stop the lost update problem, where two users read the same value, both change it, and one change silently overwrites the other. Which to use depends on how often conflicts actually happen.

The lost update problem

Pessimistic locking

Lock the row while you work on it. In SQL databases this is usually SELECT ... FOR UPDATE inside a transaction:

begin;
select seats_left from shows where id = 42 for update;   -- others wait here
-- application checks seats_left > 0
update shows set seats_left = seats_left - 1 where id = 42;
insert into bookings (show_id, user_id) values (42, 7);
commit;                                                   -- lock released

• Correct by construction: the second transaction waits, then sees 0 seats and fails cleanly.
• Costs: waiting transactions hold connections; long transactions or slow application code while holding a lock hurt throughput badly.
• Deadlocks: two transactions locking rows in opposite order wait on each other. Databases detect and abort one; lock rows in a consistent order and keep transactions short.
• Variants: FOR UPDATE NOWAIT fails immediately instead of waiting; SKIP LOCKED lets workers take the next unlocked row, which is how many job queues claim work.

Optimistic locking

Add a version column (or use an updated-at timestamp or ETag). Read the row with its version, then update only if the version hasn't changed:

-- read: seats_left = 1, version = 17
update shows
   set seats_left = seats_left - 1, version = version + 1
 where id = 42 and version = 17 and seats_left > 0;
-- 1 row updated: success.  0 rows updated: someone else changed it, so re-read and retry, or fail.

• No locks held while the user thinks or the application computes, so it scales well when conflicts are rare.
• Conflicts cost a retry, so under heavy contention (everyone buying the same item) most attempts fail and retry, wasting work.
• Works across requests: a web form can carry the version it loaded and submit it with the edit; the API returns 409 Conflict if it changed. See API design.
• Many ORMs support version columns out of the box.

Choosing between them

Often better: avoid the read-modify-write

• Atomic conditional updates: UPDATE stock SET qty = qty - 1 WHERE id = 42 AND qty > 0 does the check and write in one statement, with no lock held across round trips.
• Unique constraints: a unique index on (show_id, seat_id) makes the database reject a double booking of the same seat outright.
• Reservations with expiry: hold a seat for 10 minutes with a status and timestamp, release it if payment doesn't complete.
• Queues for extreme contention: funnel purchases for one hot item through a queue processed by a single consumer.
• Split hot rows: divide inventory into buckets so concurrent buyers update different rows. See hot partitions.

Isolation levels interact with all of this: under snapshot isolation, two transactions can still create write skew. See isolation levels.