Optimistic Locking

Q: Does optimistic locking use database locks at all?

The database still locks the row briefly while the single UPDATE statement runs. What optimistic locking avoids is holding a lock during the whole time between reading the data and saving it.

In Turkish: İyimser Kilitleme

Updated September 30, 20262 min read

In short

Optimistic locking is a concurrency technique that lets transactions proceed without holding locks and checks a version number at save time to detect conflicts.

What is optimistic locking?

Optimistic locking assumes that conflicts between writers are rare. Instead of locking a record while someone works on it, each record carries a version number or timestamp. When the change is saved, the database checks that the version is still the one that was read; if someone else has saved in the meantime, the update is rejected.

In practice, you read a row along with its version, say version 3. Later you run an update such as UPDATE ... SET ..., version = 4 WHERE id = 7 AND version = 3 and check how many rows it changed. One row means the save succeeded; zero rows means another writer got there first, so the application reloads the data and retries or shows the user a conflict. Many ORMs support this with a special version column, and HTTP APIs use the same idea with ETag and If-Match headers, answering 412 Precondition Failed when the version is out of date.

It works like editing a shared wiki page: you edit freely, but when you press save, the wiki checks whether someone saved a newer version since you opened the page and, if so, asks you to merge. That makes it a good fit for web forms where a user may take minutes to edit a record, for REST APIs, and for distributed systems where holding a database lock for that long would be impractical.

Optimistic locking is usually compared with pessimistic locking, which locks the row up front, for example with SELECT ... FOR UPDATE, so other writers wait. Pessimistic locking suits cases where conflicts are frequent or a retry is expensive, while optimistic locking avoids waiting and lock-related deadlocks but wastes work when conflicts do happen. Both prevent the lost update, a race condition where one writer silently overwrites another's change.

Key takeaways

Each row carries a version number that is checked on every update.
An update that matches zero rows signals a conflict.
No lock is held while the user or program is working on the data.
It suits low-conflict workloads such as web forms and APIs.
Pessimistic locking is the alternative when conflicts are frequent.

Example

A version check on updatesql

-- 1. Read the row and remember its version
SELECT id, title, version FROM articles WHERE id = 7;
-- => title = 'Old title', version = 3

-- 2. Save only if nobody changed the row in the meantime
UPDATE articles
SET title = 'New title', version = version + 1
WHERE id = 7 AND version = 3;

-- 3. If 0 rows were updated, someone else saved first:
--    reload the row and retry, or show the user a conflict.

Readers ask

What is the difference between optimistic and pessimistic locking?

Pessimistic locking locks data before changing it, so other writers must wait. Optimistic locking takes no lock up front and instead detects conflicts when saving, so writers never wait but sometimes have to retry.

What happens when optimistic locking detects a conflict?

The update affects no rows, or the ORM raises a conflict error. The application then reloads the latest data and either retries the change automatically or asks the user to review it.

Does optimistic locking use database locks at all?

The database still locks the row briefly while the single UPDATE statement runs. What optimistic locking avoids is holding a lock during the whole time between reading the data and saving it.