Mehdi Akiki
Published on

Optimistic Locking With a Version Number

Authors
  • Mehdi Akiki avatar
    Name
    Mehdi Akiki
    Twitter

Reference · Interrupted execution

Two requests read the same customer at the same time. Both change one field. Both save. The second save wins, and the first change is gone, with no error.

This is the lost update problem, and the worst part is the silence. Nobody did anything wrong, and nobody is told that data disappeared.

The naive version

Read the row, change it in your code, write it back.

request 1: read (balance = 100) -> add 10 -> write 110
request 2: read (balance = 100) -> add 20 -> write 120
final: 120

Both started from 100, so the +10 is lost. The last writer overwrites everything, because neither request knew the other existed.

One fix: lock the row

You can take a lock while you work, with SELECT ... FOR UPDATE. The row is yours until you finish; the other request waits.

This works, but it holds a lock for the whole operation. If a human is "thinking" in the middle, or a network call is slow, everyone else is blocked. On a busy web API, this becomes contention and sometimes deadlocks. It is a strong tool, and often too strong.

The better fix: a version number

Optimistic locking does not hold a lock. It bets that conflicts are rare, and only pays when one actually happens.

Add a version column. Read the row together with its version. When you write, tell the database: "update only if the version is still the one I read."

UPDATE customers
SET balance = 110, version = version + 1
WHERE id = 42 AND version = 7;

If nobody touched the row, version is still 7, the update matches one row, and the version becomes 8. But if someone else wrote first, the version is already 8, your WHERE matches zero rows, and the update changes nothing.

Zero rows affected is your signal: there was a conflict. Now you can re-read the fresh row and try again, or tell the user. The key point is that you find out. The lost update is no longer silent.

The two rules

  • The version must change on every write. A counter that always grows is the safe choice. Do not use a plain timestamp: two writes can land in the same millisecond, and a clock can move backward (the same problem as last-write-wins).
  • You must actually check the result. If you ignore "zero rows affected", you are back to silently losing data.

This is the honest alternative to last-write-wins: instead of letting the newest write quietly erase the others, you detect the conflict and decide what to do. It is also the same version field that a sync between two systems uses to know which copy is newer.