Optimistic locking
Detecting a conflict at the moment of writing, in place of preventing it with a lock, and when that is the better deal.
5 minute lesson
The edit that vanished
Ana and Ben open the same article. Ana fixes the title and saves. Ben, who still has the old text on his screen, fixes a typo and saves. Ben's save writes his whole copy, and Ana's title is gone. Nobody got an error. This is a lost update.
- Both readthe same text
- Ana savesnew title
- Ben savesover Ana's
Check at the moment of writing
UPDATE articles
SET body = '...', version = 6
WHERE id = 9 AND version = 5;With optimistic locking every row carries a version number. A save says: change it, but only if it is still the version I read. If someone was faster, the condition matches no row, and the update changes nothing. The application sees "0 rows updated" and knows there was a conflict.
Ana and Ben both read an article at version 5. Ana saves first, and the row is now at version 6. Ben's save runs: UPDATE ... WHERE id = 9 AND version = 5.
What does Ben's update do?
- It changes no row, and the application sees a conflict
- It overwrites Ana's text and sets the version to 6
- It waits until Ana has released her lock on the row
- It merges Ben's text with Ana's in the database
Show the answer
- It changes no row, and the application sees a conflict
The row with id 9 is at version 6 now, so the condition version = 5 matches nothing. The statement succeeds and reports zero rows changed. That zero is the signal: someone else got there first. Ana's edit is intact, and the application can show Ben what changed.
An update with a stale version matches no row. Zero rows updated means a conflict.
Prevent or detect
Pessimistic
lock first
- A conflict
- Cannot happen
- The cost
- Others wait
- Fits
- Frequent conflicts and short work
Optimistic
check on write
- A conflict
- Is detected
- The cost
- The loser tries again
- Fits
- Rare conflicts and long edits
A wiki has thousands of pages. A page is edited for several minutes at a time, and two people rarely edit the same page at once.
Which approach fits?
- Optimistic: conflicts are rare and edits are long
- Pessimistic: a page must be locked while it is open
- Neither: the last save should simply win each time
- Both: a lock on the page and a version check too
Show the answer
- Optimistic: conflicts are rare and edits are long
A database lock held for minutes while someone types would block others and pile up open transactions. And since two editors rarely meet, almost every save would wait for nothing. A version check costs nothing in the usual case and catches the rare collision when it happens.
Long edits and rare conflicts call for optimistic locking.
What to do with the loser
- Retry automatically when the change can be recomputed: read the row again, apply the change and save. Fine for adding to a counter.
- Ask the person when it cannot: show both versions and let them merge. Right for a text someone wrote.
- Never save anyway. Dropping the version check brings the lost update straight back.
How does optimistic locking stop a lost update without holding a lock?
Show the answer
Each row has a version number. A writer includes the version it read in the condition of its update and raises it by one. If another writer has changed the row in the meantime, the version no longer matches, no row is updated, and the writer learns that it lost. It then reads again and retries, or asks the user. It fits when conflicts are rare.
Recap
- A lost update: two writers start from the same value, and the second silently overwrites the first.
- Optimistic locking adds a version to each row and updates only if the version is unchanged.
- Lock when conflicts are frequent and work is short. Check versions when conflicts are rare or edits are long.