Have you ever tried to buy the last concert ticket only to see the message ‘This item has already been sold’? This is a typical concurrency issue. In web applications, it’s common to encounter situations where multiple users try to modify the same data simultaneously. To prevent overwriting changes made by others, a mechanism called optimistic locking is often used.
ORMs like Hibernate or JPA support this out of the box using a version column. But how does it actually work under the hood? Below is a minimal SQL example — no frameworks involved.
How Optimistic Locking Works
Let’s assume there is a table: cats with columns id, name, and version:
CREATE TABLE cats ( id SERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL, version INT NOT NULL );
When a user wants to update a record, they first fetch it from the database, including its version value. Later, when the user makes changes and attempts to save them back, the application executes an SQL update with a condition that checks whether the record’s version in the database matches the version the user originally retrieved:
UPDATE cats SET name = 'New Name', version = version + 1 WHERE id = ? AND version = ?;
If the number of updated rows is 0, it means someone else modified the record in the meantime, and the application can notify the user of a conflict.
Practical Example
Suppose there is a cat record with id = 1, name = ‘Whiskers’, and version = 1.
User A fetches the record:
SELECT id, name, version FROM cats WHERE id = 1; -- Result: (1, 'Whiskers', 1)
User B fetches the same record:
SELECT id, name, version FROM cats WHERE id = 1; -- Result: (1, 'Whiskers', 1)
User A changes the cat’s name to ‘Fluffy’ and updates the record:
UPDATE cats SET name = 'Fluffy', version = version + 1 WHERE id = 1 AND version = 1; -- Rows updated: 1
Meanwhile, User B changes the cat’s name to ‘Mittens’ and tries to save it:
UPDATE cats SET name = 'Mittens', version = version + 1 WHERE id = 1 AND version = 1; -- Rows updated: 0
The key is that the WHERE clause checks the object version.
Receiving a zero number of updated rows signals the application that the data was being modified by another process in the meantime. Since the version no longer matches User B, the update fails, and the user must attempt the update again.
Trade-offs of Optimistic Locking
While offering great benefits for scalability, optimistic locking is not without its trade-offs. The main drawback lies in the required retry logic: when a conflict occurs (i.e., another user has already modified the data), the application must be designed to handle this error, typically by throwing an error and asking the client to retry or re-fetch the latest version of the record, reapplying the user’s changes, and attempting the update again. This necessity for complex error and retry logic adds developmental complexity.
Furthermore, in scenarios with high collision rates, where a record is frequently edited by many concurrent users, the constant cycle of conflicts and retries can degrade the user experience and ultimately slow down the system, mitigating the performance benefits that optimistic locking typically provides.
Yet, for most applications, benefits outweigh drawbacks.
Summary
Optimistic locking is an effective way to manage concurrent access to data in web applications. By maintaining a simple version counter, you can prevent conflicts and ensure data integrity, and it minimizes database locking time, which is crucial for performance and scalability.
Understanding the underlying mechanisms is invaluable, as it helps in diagnosing complex issues and comprehending the precise behavior of the application.
I’ve also shared a small project that demonstrates optimistic locking in action.
You can find it here:
https://github.com/MaciejTomczyk/Demistyfying-Optimistic-Locking
