Optimistic Locking
In one line
Optimistic locking: read without locking, check version before update — fail if someone else changed the row.
In simple words
Optimistic locking assumes concurrent conflicts are rare. Instead of locking a row during read, it checks at update time that no one else modified the row in between.
Implementation: add a version column to each row. When reading, note the version. When updating, include a WHERE clause checking the version:
UPDATE products SET price = 100, version = version + 1 WHERE id = 1 AND version = 5
If 0 rows are updated, someone else changed the row first — retry or fail.
Benefits:
- No locks held during user think time — better concurrency
- No deadlock risk — no locks to deadlock on
- Good for read-heavy workloads — conflicts are rare
Best when conflicts are infrequent. If conflicts are common, pessimistic locking is better.
Example
-- Optimistic locking with version column
-- Table: products (id, name, price, version)
-- Step 1: Read product (note version)
SELECT id, name, price, version FROM products WHERE id = 1;
-- Returns: (1, 'Widget', 50.00, 5)
-- ... user edits the price ...
-- Step 2: Update with version check
UPDATE products
SET price = 100.00, version = version + 1
WHERE id = 1 AND version = 5;
-- If 1 row updated: success! No one else changed it.
-- If 0 rows updated: conflict! Someone else updated it first.
-- Application must re-read and retry.
-- Alternative: using updated_at timestamp
UPDATE products SET price = 100.00, updated_at = NOW()
WHERE id = 1 AND updated_at = '2024-01-15 10:30:00';Read the version, then update only if version hasn't changed. If 0 rows updated, someone else modified it — retry. No locks held during user think time.
Common confusions
Confused with: pessimistic locking
The difference: Optimistic locking doesn't hold locks — it checks for conflicts at update time using versions. Pessimistic locking holds locks during the entire transaction. Optimistic is better when conflicts are rare; pessimistic is better when conflicts are common.
Related terms
Related Resources
Related Lessons
- Concurrency Control
Optimistic locking.
Decode error messages
Plain-English explanations of common errors — what they mean, why they happen, and how to fix them.