1
SQLIntermediate#procedures
SQLAdvanced#transactions#concurrency
What are optimistic and pessimistic locking?
Pessimistic locking takes a lock up front (SELECT ... FOR UPDATE) so nobody else can change the row. Optimistic locking takes no lock and detects conflicts at write time using a version column.
Example
-- pessimistic SELECT * FROM seats WHERE id = 9 FOR UPDATE; -- optimistic UPDATE seats SET taken = 1, version = version + 1 WHERE id = 9 AND version = 3;