Unlock MySQL Performance: How Optimistic Locking Works

MySQL optimistic lock is a concurrency control strategy that assumes data conflicts are rare, so it does not lock rows when reading and only checks for conflicts during updates.

Implementation typically adds a version column to the business table. When reading, the current version is retrieved together with the data. During an update, the statement includes a condition WHERE id = ? AND version = ?. If the version matches, the update succeeds and the version is incremented (e.g., SET version = version + 1); otherwise the update fails, indicating a conflict.

Example SQL workflow:

-- Query data with version
SELECT id, stock, version FROM product WHERE id = 1;

-- Update stock using optimistic lock
UPDATE product
SET stock = stock - 1,
    version = version + 1
WHERE id = 1 AND version = 1;

The overall process consists of reading the data and its version, submitting the update with the version in the WHERE clause, and finally verifying that the version has not changed. If the version matches, the update is applied and the version is incremented, avoiding the blocking overhead of pessimistic locks and offering better performance in read‑heavy, low‑conflict scenarios.