What is optimistic locking — and where did the new price go

Your shop has two managers. Anna opens the sneakers' product card and changes the price from 5000 to 4500. She saves. A minute later Boris, who opened the same card a bit earlier, fixes a typo in the description. He saves too.

The typo is fixed. And the price is 5000 again. Nobody saw an error, nobody got a warning. Anna's edit simply vanished.

This is called a lost update. And it has a simple cure — optimistic locking.

What happened to the price

Step by step. Boris's form loaded the whole card while the price was still 5000. Boris changed the description. On save, the form sent the entire card to the server — with the new description and the old price it remembered.

The server honestly wrote what it received. The last one to save won. It's a classic race condition: the result depends on who hit "Save" later.

The catch: not a single line of code is broken here. Each request on its own is correct. Only their combination in time breaks — which is why you'll never see this bug testing alone.

What optimistic locking is

The idea: add a version number to the record. Every save increments it by one.

When the form loads the card, it remembers not only the data but also the version: "I saw version 3". On save, it tells the database: "write my changes, but only if the version is still 3".

In SQL that's one statement:

UPDATE products
SET description = 'New description', price = 5000, version = version + 1
WHERE id = 7 AND version = 3;

Back to Anna and Boris. Anna saved first — the version became 4. Boris sends his request with version = 3. The WHERE clause matches no rows. The database replies: 0 rows updated.

That's the signal. The code sees the zero and tells Boris: "Someone changed this card while you were editing. Refresh the page." Anna's price stays put.

Why "optimistic"

Because it locks nothing in advance. It optimistically assumes conflicts are rare. Everyone edits freely; the check happens only at the moment of saving.

The opposite approach is pessimistic locking. There, the first person to open a record locks it, and the rest wait. In Postgres you do it with SELECT … FOR UPDATE inside a transaction.

Pessimistic is more airtight but costlier. A locked record keeps others in line. And if a manager opens the card and leaves for lunch, the lock hangs until it times out.

For forms people edit for minutes, optimistic is almost always the choice. For short operations within one transaction, like debiting a balance, pessimistic.

How to add it yourself

Three steps.

1. Add an integer version column to the table, default 1.
2. When you send a record to the form, send its version too. The form keeps it in a hidden field.
3. On save, add version = <what came in> to the condition and bump the version by one. If 0 rows were updated, that's a conflict, not a success.

In Supabase it looks like this:

const { data } = await supabase
  .from("products")
  .update({ description, price, version: version + 1 })
  .eq("id", id)
  .eq("version", version)
  .select();

if (data.length === 0) showConflict();

Instead of a version number you can compare the last-modified time updated_at. It works the same way, but a number is safer: two edits in the same millisecond look identical by time.

HTTP has a built-in form of this idea. The server sends an ETag header — a fingerprint of the version. The client sends it back in If-Match. If it doesn't match, the server replies 412 Precondition Failed. There's a separate rundown of HTTP status codes.

How to ask AI for it

By default a neural net writes "last one wins". Ask explicitly:

Does optimistic locking lock the record?

No — that's the point. Nothing is locked; everyone edits freely. The conflict is simply detected on save rather than prevented in advance.

What should the user see on a conflict?

The simplest: "This card was changed, refresh the page". Better: show both versions and let them choose. The key is never to save silently and pretend all went well.

What if my app has a single user?

Conflicts are still possible: one person, two open tabs. Or a phone and a laptop. A version costs one column — cheaper than explaining where an edit went.