Optimistic Locking
Optimistic locking avoids database locks by adding a version column. The update fails if another transaction already incremented it.
MySQL
Intermediate
4 min read
Optimistic Locking
Example
-- Schema: products has version INT
-- Read with version
SELECT id, price, version FROM products WHERE id = 10; -- version = 5
-- Update only if version unchanged
UPDATE products SET price = 14.99, version = version + 1
WHERE id = 10 AND version = 5;
-- Check affected rows: 0 means conflict → retry
-- In PHP/Laravel: use Eloquent optimistic locking package or manual check Pro Tip
Optimistic locking is better for low-conflict systems — no held locks means higher throughput.