Pessimistic vs Optimistic Locking
To Nha Notes | May 3, 2021, 9:04 a.m.
In Concurrency Control theory, there are two ways you can deal with conflicts:
- You can avoid them, by employing a pessimistic locking mechanism (e.g. Read/Write locks, Two-Phase Locking)
- You can allow conflicts to occur, but you need to detect them using an optimistic locking mechanism (e.g. logical clock, MVCC)
Pessimistic locking
Both users will acquire a read (shared) lock on a table row upon reading it. Neither of them can change it until one releases the read lock they acquired. This is because a write operation requires a write (exclusive) lock acquisition, and read (shared) locks prevent write (exclusive) locks.
For above diagram, Bob’s UPDATE blocks until Alice releases the shared lock she has acquired previously.
Optimistic Locking
Optimistic Locking allows a conflict to occur, but it needs to detect it at write time. We are going to use a version column to capture the read-time row snapshot information.
The version column is going to be incremented every time an UPDATE or DELETE statement is executed while also being used for matching the expected row snapshot in the WHERE clause.
For above diagram, when reading the record, both users read its current version. However, when Bob changes the account balance, he also changes the version from 1 to 2.
Afterward, when Alice wants to change the account balance, her UPDATE statement will not match any record since the version column value is no longer 1, but 2. Therefore, the UPDATE is going to return a value of 0, meaning that no record was changed, and the underlying data access framework will throw an OptimisticLockException that will cause Alice’s transaction to rollback.
So, the Lost Update is prevented by rolling back the subsequent transactions that are operating on state data.