An update is lost when a user overrides the current database state without realizing, that someone else changed it between the moment of data loading and the moment the update occurs. In this post, I will give detailed description of this phenomena and typical ways to prevent it.
1. Lost Update Phenomena
2. Changing isolation level
Most databases use
READ COMMITTED isolation level by default (MySQL -
REPEATABLE READ). Choosing isolation level is always a trade-off between consistency and scalability.
If lost update is pretty common scenario in the system, sometime it will make sense to use higher isolation level. For example, either
REPEATABLE READ or
SERIALIZABLE will prevent lost update from happening.
In the situation above, if two transactions try to change the same record, the second will be forced to wait while the first either commits or rollbacks. And if the first transaction commits, the second will be aborted.
The drawback of such approach, is that isolation level is set per database connection, that is not desirable or acceptable in most cases.
3. Using pessimistic locking
Pessimistic locking is a common tool used to maintain data consistency.
In relational databases it is usually achieved by using
SELECT … FOR UPDATE with default
READ COMMITTED isolation level (these combination will lead to acquiring write lock).
So, if two transactions try to change the same record, the second will be forced to wait while the first either commits or rollbacks. After first transaction terminates, the second one will see changes and, therefore, no updates will be lost.
It should be mentioned, that both transactions should acquire write locks, otherwise lost update won’t be prevented.
4. Using optimistic locking
The optimistic locking doesn’t rely on acquiring write locks. It uses versioning to detect, that data was changed concurrently. If two transactions try to change the same record, the second one won’t change anything since it will use version, that no longer exist.
UPDATE will take version into the
WHERE clause. Basically, it optimistically assumes, that no one changing the row concurrently. However, if another transaction commits a newer record version, the second transaction will no longer match any row and therefore the lost update is prevented.
ORMs (e.g. Hibernate) use updated results count to check the number of updated rows. If no row was matched, OptimisticLockException is thrown. After exception is thrown, the current transaction and persistence context are aborted.