Understanding MySQL Optimistic Lock: Principles and Implementation

MySQL optimistic lock, as the name suggests, avoids locking during updates by using a version number or timestamp to detect whether the data has been modified by another transaction.

The core principle is to check the version (or timestamp) of a row when updating; if the version matches the one read earlier, the update proceeds and the version is incremented, otherwise the update fails.

Implementation steps:

1. Add a version column to the table, typically named version :

ALTER TABLE orders ADD COLUMN version INT DEFAULT 1;

This column records the version of each row and increments automatically on each update.

2. Read the data and retrieve the current version (or timestamp):

SELECT id, stock, version FROM product WHERE id = 1;

Store the retrieved version value for later comparison.

3. Submit the update by checking that the version has not changed:

UPDATE table SET column1 = new_value, version = version + 1 WHERE id = target_id AND version = old_version;

For example, to update inventory safely:

UPDATE product SET stock = stock - 1, version = version + 1 WHERE id = 1 AND version = 1;

If the affected rows count is greater than 0, the update succeeded; otherwise, it indicates that another transaction modified the row, and the operation should be retried after re‑reading the data.

Optimistic locking is advantageous in read‑heavy, write‑light scenarios because it avoids the overhead of locking during reads, reducing contention and improving performance. However, for workloads requiring strong consistency, such as many financial transactions, pessimistic locking may still be necessary.