Optimistic Locking: What Happens When Two Users Update the Same Row?
Let's say you're building a todo app.
Nothing complicated. You have a list of todos, and each todo has a title and a checkbox to mark it as completed.
Your database looks something like this:
CREATE TABLE todos (
id SERIAL PRIMARY KEY,
title TEXT NOT NULL,
completed BOOLEAN DEFAULT FALSE
);
And you have a todo:
id: 1
title: Buy milk
completed: false
Everything works fine.
But now imagine two users have the same todo open in their browsers.
Both of them can edit it.
And this is where things get interesting.
The problem
Let's say Alice and Bob both open the todo at the same time.
Alice sees:
Buy milk
Bob sees exactly the same thing.
Now Alice decides to change the title to:
Buy almond milk
She clicks Save.
Your backend executes:
UPDATE todos
SET title = 'Buy almond milk'
WHERE id = 1;
The database now contains:
id: 1
title: Buy almond milk
completed: false
So far, everything is fine.
But Bob still has the old title in his browser.
He doesn't know Alice has changed anything.
Bob changes the title to:
Buy oat milk
And clicks Save.
Your backend executes:
UPDATE todos
SET title = 'Buy oat milk'
WHERE id = 1;
And now the database contains:
id: 1
title: Buy oat milk
completed: false
Alice's change is gone.
No error. No warning. Nothing.
Bob simply overwrote it.
This is called a lost update.
And the interesting part is that neither Alice nor Bob did anything wrong.
Both of them edited the todo they originally received from the server.
The problem is that our database update doesn't care whether the todo has changed since they loaded it.
It only cares about the ID.
So the question is:
How can we know whether we're updating the same version of the todo that we originally read?
Let's add a version
We can add one more column to our table.
ALTER TABLE todos
ADD COLUMN version INTEGER NOT NULL DEFAULT 1;
Now our todo looks like this:
id: 1
title: Buy milk
completed: false
version: 1
The idea is simple.
Every time we successfully update the todo, we increment its version.
For example:
version 1 → version 2 → version 3
But the important part isn't just incrementing a number.
It's checking that number before we update anything.
Let's go through the same example again.
Alice and Bob, one more time
Both Alice and Bob open the todo.
The server sends the same data to both:
{
"id": 1,
"title": "Buy milk",
"completed": false,
"version": 1
}
Alice changes the title to Buy almond milk.
When she clicks Save, the frontend sends:
{
"title": "Buy almond milk",
"version": 1
}
Notice that we're sending the version as well.
We're essentially telling the backend:
"I want to update this todo, but only if it's still the version I originally read."
Now our SQL looks a little different:
UPDATE todos
SET
title = 'Buy almond milk',
version = version + 1
WHERE id = 1
AND version = 1;
Look at the WHERE clause.
We no longer update the todo just because its ID is 1.
We also require its current version to be 1.
Since Alice is the first one to update it, the condition matches.
The database updates the row:
id: 1
title: Buy almond milk
completed: false
version: 2
Now Bob clicks Save.
Remember, Bob still has version 1 in his browser.
His request contains:
{
"title": "Buy oat milk",
"version": 1
}
So the backend executes:
UPDATE todos
SET
title = 'Buy oat milk',
version = version + 1
WHERE id = 1
AND version = 1;
But something is different now.
The database contains version 2.
Bob is trying to update version 1.
The condition doesn't match.
Zero rows are updated.
And Alice's change stays exactly where it is.
This is the entire idea behind optimistic locking.
We allow multiple users to read the same data, but when they try to write, we check whether the data has changed since they read it.
But how do we know the update failed?
Let's use PostgreSQL.
We can add RETURNING to our query:
UPDATE todos
SET
title = $1,
version = version + 1
WHERE id = $2
AND version = $3
RETURNING *;
RETURNING gives us the updated row when the update succeeds. When no row matches the condition, the query returns no rows.
Let's put this into a small JavaScript function using pg.
async function updateTodo(id, title, version) {
const result = await db.query(
`
UPDATE todos
SET
title = $1,
version = version + 1
WHERE id = $2
AND version = $3
RETURNING *
`,
[title, id, version]
);
if (result.rows.length === 0) {
throw new Error("Todo was modified by someone else");
}
return result.rows[0];
}
There are only two possible outcomes here, assuming the query itself executes successfully.
If the version matches, we get the updated todo back.
If it doesn't match, we get an empty result.
One thing worth mentioning: an empty result could also mean the todo was deleted or the ID never existed. If our API needs to distinguish those cases, we can perform a separate lookup after the failed update.
But for now, the important thing is that we didn't overwrite somebody else's changes.
Why not just SELECT the version first?
You might be thinking:
Why don't we just read the current version, compare it in JavaScript, and then update?
Something like this:
const todo = await getTodo(id);
if (todo.version !== version) {
throw new Error("Conflict");
}
await updateTodo(id, title);
It looks reasonable.
But let's see what happens if Alice and Bob execute this code at almost the same time.
Two requests, one version
Both requests check the version before either one writes.
Alice
- Reads version 1
Version matches ✓
Bob
- Reads version 1
Version matches ✓
Both checks passed
Neither request has written anything yet.
- Writes almond milk
Update succeeds
- Writes oat milk
Overwrites Alice
Final database value: Buy oat milk
Alice's change is lost despite both version checks passing.
Both requests read version 1.
Both checks succeed.
And both requests execute their updates.
We've ended up with the same problem we started with.
The issue is that checking the version and updating the row are two separate operations.
Something can change between them.
That's why we need the version check inside the UPDATE itself.
UPDATE todos
SET
title = $1,
version = version + 1
WHERE id = $2
AND version = $3;
The database handles the condition and the update as part of one statement.
In PostgreSQL's default Read Committed isolation level, if two transactions try to update the same row concurrently, the second updater waits if necessary and rechecks its WHERE condition against the updated row after the first transaction commits.
So if both requests expect version 1, they can't both successfully update that row using this query.
The first successful update changes its version to 2.
The other request no longer matches.
That's the part that makes this work.
What should the frontend do when there's a conflict?
Let's say Bob tries to save his change and the backend detects that the version is outdated.
We could return an HTTP 409 Conflict response.
app.patch("/todos/:id", async (req, res) => {
const { title, version } = req.body;
try {
const todo = await updateTodo(
req.params.id,
title,
version
);
res.json(todo);
} catch (error) {
if (error.message === "Todo was modified by someone else") {
return res.status(409).json({
message: "This todo has changed. Please reload."
});
}
res.status(500).json({
message: "Something went wrong"
});
}
});
For simplicity, this example uses the error message to identify a conflict. In a real application, I'd use a dedicated error type and distinguish an outdated version from a missing todo.
Something like:
async function updateTodo(id, title, version) {
const result = await db.query(
`
UPDATE todos
SET
title = $1,
version = version + 1
WHERE id = $2
AND version = $3
RETURNING *
`,
[title, id, version]
);
if (result.rows.length > 0) {
return result.rows[0];
}
// Was the todo deleted, or did its version change?
const existingTodo = await db.query(
"SELECT id FROM todos WHERE id = $1",
[id]
);
if (existingTodo.rows.length === 0) {
throw new TodoNotFoundError();
}
throw new VersionConflictError();
}
Notice that we only execute the additional SELECT when the update fails
Next, the frontend can then tell Bob that somebody else modified the todo.
It could reload the latest data and let him decide what to do with his changes.
We could also implement a more advanced conflict resolution system, but that's a separate problem.
The important thing is that we detected the conflict instead of silently losing data.
And one more thing: we shouldn't automatically retry Bob's original update using the latest version without thinking about it.
That would simply overwrite Alice's change again.
We'd technically be using optimistic locking, but we'd be defeating its purpose.
Why is it called optimistic?
Because we're optimistic that conflicts won't happen very often.
We don't lock the todo when Alice opens it.
We don't prevent Bob from opening it.
We don't make Bob wait until Alice is done editing.
Both users can read and work with the todo normally.
We only check for a conflict when somebody tries to save.
This is different from pessimistic locking, where we might acquire a database lock before working with the data.
And depending on the application, either approach might make sense.
For a todo app, holding a database lock while somebody spends five minutes deciding what kind of milk to buy would be pretty impractical.
A version number is a much simpler solution.
Also, optimistic locking and optimistic UI updates aren't the same thing.
An optimistic UI update means changing the interface before the server confirms the operation succeeded.
Optimistic locking is about detecting conflicting writes.
You can use both together, but they solve different problems.
