Optimistic Lock

Optimistic locking is a concurrency‑control technique that assumes conflicts are rare. Instead of acquiring a lock for the entire duration of a data operation, the system proceeds with the work and validates the data only when it tries to commit the changes. If the validation fails—because another transaction has modified the same record—the current operation is aborted or retried. The guiding principle can be summed up as “do the work first, check later.”

Core Mechanisms

Version‑Based Lock

The most common implementation adds an integer column (often called version) to the table. Every update increments this column by one. The WHERE clause of the UPDATE statement must match the current version; if it does not, the statement affects zero rows, signalling a conflict.

UPDATE user
SET name = ?, version = version + 1
WHERE id = ? AND version = ?;

When the affected‑row count is zero, the application can decide whether to retry the operation or surface an error to the caller.

Timestamp‑Based Lock

A timestamp column records the moment a row was last modified. The update checks that the stored timestamp has not changed since the row was read.

UPDATE orders
SET status = ?, updated_at = CURRENT_TIMESTAMP
WHERE id = ? AND updated_at = ?;

Timestamps are intuitive because they convey “when” the data changed, but they rely on a consistent system clock across all nodes; clock skew can produce false positives.

Compare‑And‑Swap (CAS)

At the memory level, CAS is an atomic instruction that compares a value with an expected old value and, if they match, swaps in a new value. Languages such as Java expose CAS through classes like AtomicInteger and AtomicReference.

AtomicInteger counter = new AtomicInteger(0);
int oldValue, newValue;
do {
    oldValue = counter.get();
    newValue = oldValue + 1;
} while (!counter.compareAndSet(oldValue, newValue));

The loop repeats until the compare‑and‑set succeeds, which means no other thread has altered the value in the meantime.

Practical Example: Optimistic Lock with MyBatis

Below is a complete workflow that demonstrates how to apply a version‑based optimistic lock in a MyBatis‑driven Java application.

1. Table Definition

CREATE TABLE product (
    id      BIGINT PRIMARY KEY,
    name    VARCHAR(100),
    stock   INT,
    version INT DEFAULT 0,
    CONSTRAINT uq_product_id UNIQUE (id)
);

2. Domain Object

public class Product {
    private Long id;
    private String name;
    private Integer stock;
    private Integer version;   // optimistic‑lock field
    // getters and setters omitted for brevity
}

3. Mapper XML

<!-- Decrease stock while checking the version -->
<update id="decreaseStock" parameterType="Product">
    UPDATE product
    SET stock = stock - #{decrease},
        version = version + 1
    WHERE id = #{id}
      AND version = #{version}
      AND stock >= #{decrease}
</update>

4. Service Logic

@Service
public class ProductService {
    @Autowired
    private ProductMapper productMapper;

    @Transactional
    public void decreaseStock(Long productId, int amount) {
        final int MAX_RETRIES = 5;
        int attempts = 0;

        while (true) {
            Product p = productMapper.selectById(productId);
            if (p.getStock() < amount) {
                throw new IllegalArgumentException("Insufficient stock");
            }

            int rows = productMapper.decreaseStock(p);
            if (rows > 0) {
                // Update succeeded
                break;
            }

            // Conflict detected – version changed
            attempts++;
            if (attempts >= MAX_RETRIES) {
                throw new IllegalStateException("Failed to update after several retries");
            }
            // Loop continues: re‑read the row and try again
        }
    }
}

Key take‑aways

1. The WHERE clause must contain the primary key, the version field, and any business constraints (e.g., stock >= ?).
2. The number of rows affected by the UPDATE tells the service whether the operation succeeded.
3. A bounded retry loop prevents endless spinning and gives the application a chance to back off or report a failure.

When to Use Optimistic Lock

• Read‑heavy workloads – If most transactions are reads and writes are infrequent, the probability of a conflict is low, making optimistic locking highly efficient.
• Distributed environments – Version numbers or timestamps can be stored in a shared database without requiring a global lock manager, reducing coordination overhead.
• Cache updates – CAS operations on in‑memory caches (e.g., Redis WATCH/MULTI or Java Atomic* classes) help keep cached copies consistent with the underlying data store.

Optimistic vs. Pessimistic Locking

Best Practices

• Never omit the version column – If the column is not updated on every write, the validation loses its meaning.
• Keep transactions short – Long‑running transactions increase the window for conflicts, leading to more retries.
• Limit retries – Unbounded loops can consume CPU cycles; define a maximum number of attempts and consider exponential back‑off.
• Add business constraints to the WHERE clause – Simple version checks may not catch domain‑specific conflicts (e.g., insufficient inventory).
• Watch hot rows – If a particular record is a write hotspot, the retry cost may outweigh the benefits of optimism; switching to a pessimistic lock or sharding the data can be more effective.

Conclusion

Optimistic locking offers a lightweight alternative to traditional locking mechanisms. By relying on version numbers, timestamps, or CAS instructions, it enables high‑throughput, low‑latency access in environments where conflicts are the exception rather than the rule. Successful adoption hinges on diligent version management, thoughtful conflict detection, and sensible retry policies. When applied correctly, optimistic locking becomes a powerful tool for building scalable, responsive systems—especially in microservices, distributed databases, and high‑concurrency applications.