Optimistic and pessimistic locking

The problem

Two users load the same record, both change it, both save. Without protection the later save silently overwrites the earlier one (a lost update). Two families of solutions exist.

Image: Svetlov Artem · CC0 · Source · Wikimedia Commons: RZD Selizarovo 2016-08 searchlight signal 2

Pessimistic locking: assume conflict

Lock the data before changing it, so others must wait.

BEGIN;
SELECT * FROM seats WHERE id = 7 FOR UPDATE;  -- others block here
UPDATE seats SET taken = true WHERE id = 7;
COMMIT;

• Safe and simple to reason about.
• Cost: waiting, reduced throughput, risk of deadlock, and trouble if the lock is held while waiting for a user.

Optimistic locking: assume no conflict, check at the end

Read freely, and when saving check nothing changed.

UPDATE pages SET body = :new, version = version + 1
WHERE id = 5 AND version = :versionIRead;
-- 0 rows updated means someone else saved first

• No locks held while the user thinks.
• Cost: on conflict the work is rejected and must be retried or merged. Under heavy contention many attempts fail.

Optimistic locking in action

100%

Figure SDA-CORE-03-1 · Two writers with a version check · © forcsguy.com

Choosing

	Optimistic	Pessimistic
Conflicts	rare	frequent
Lock duration	none	whole transaction
Failure mode	retry or merge	waiting, deadlocks
Good for	editing, profiles, most web apps	inventory, seat booking, money moves

Beyond one database

For resources not in a database (a file, an external job), use a distributed lock or a lease, covered in a later lesson. They need extra care because the lock holder can crash or stall.

Real-world reading