Databases · Question 28 of 50

Optimistic or pessimistic locking?

What they're checking

Concurrency control in application code.

The answer

Pessimistic: lock the row while you work on it (SELECT ... FOR UPDATE), so others wait. Safe when conflicts are common, such as booking the last seats, but locks lower throughput and can deadlock.

Optimistic: no lock. Keep a version number and update only if it has not changed (UPDATE ... SET ..., version = version + 1 WHERE id = ? AND version = ?). If no row changed, someone else got there first: reload and retry, or tell the user. Best when conflicts are rare, such as editing a profile.

UPDATE products SET stock = stock - 1, version = version + 1
WHERE id = 42 AND version = 7;
-- 0 rows updated: someone changed it first, so reload and retry

The picture

pessimistic:  A locks the row ── B waits ── A commits ── B goes
optimistic:   A reads v7, B reads v7 ── A writes v8 ✓
              B writes "WHERE version = 7" ✗ → reload and retry
The follow-up they'll ask

What is the simplest fix for overselling stock?

Show the answer

One atomic conditional update: UPDATE ... SET stock = stock - 1 WHERE id = ? AND stock > 0, then check how many rows changed.

Your progress stays on this device.