Lost Update Phenomena

There are several approaches to prevent the problem:

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.

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.

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.

So, every 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.