Strategy B: Optimistic Execution (CAS)

Introduction to Optimistic Locking

Optimistic locking is a concurrency control strategy that assumes multiple transactions can complete without affecting each other, only checking for conflicts at commit time. This approach is particularly useful in low-contention environments where conflicts are statistically rare. In such scenarios, optimistic locking can support horizontal scalability better than pessimistic locking because it avoids holding locks across network RTTs.

Implementing Compare-And-Swap (CAS) Logic

The Compare-And-Swap (CAS) logic is implemented using a WHERE clause that evaluates both the Primary Key and the expected Version. For instance, in PostgreSQL, the UPDATE statement must increment the version (e.g., version = version + 1) while verifying the old version. The following SQL code snippet demonstrates this:

UPDATE products 
SET 
    stock_count = 45, 
    version = version + 1 
WHERE 
    id = 'prod_001' 
    AND version = 5;

This pattern ensures that the update only occurs if the version matches the expected value, thus preventing the Lost Update Problem.

Handling CAS Failure

When a CAS update fails to affect any rows due to a version mismatch, it signifies that the version has changed since it was last read. In such cases, the application must catch the error, re-read the state, and potentially retry the operation. The Java implementation below throws the required semantic exception when CAS fails:

public void updateProduct(ProductDTO dto) {
    int rowsAffected = jdbcTemplate.update(
        "UPDATE products SET name = ?, version = version + 1 WHERE id = ? AND version = ?",
        dto.name(), dto.id(), dto.version()
    );
    
    if (rowsAffected == 0) {
        throw new OptimisticLockingFailureException("State for entity " + dto.id() + " was updated by another process.");
    }
}

State Transition Table for Optimistic Locking Conflicts

The following table illustrates the state transitions for optimistic locking conflicts:

Conclusion

Optimistic locking, through the implementation of Compare-And-Swap logic, provides an effective strategy for preventing the Lost Update Problem in database transactions. By leveraging versioning and atomic updates, developers can ensure data consistency and integrity in concurrent systems. For further reading, refer to [1] for a detailed explanation of implementing optimistic locking in PostgreSQL and [2] for learning notes on optimistic and pessimistic locking.

Sources

[1] https://reintech.io/blog/implementing-optimistic-locking-postgresql [2] https://parottasalna.com/2025/01/06/learning-notes-42-optimistic-lock-and-pessimistic-lock-postgres-locks/