1. The problem:
Lost Update
Example: Increase Count
Flow:
a. I query the count. It is 5
b. You increase the count immediately. It is 6 now.
c. I don’t know it is 6 now. I still assume it is 5. So I increase it by set it as 6.
Result: We both try to increase it the count. But it is only increased by 1, not by 2.
2. Solution:
I should have locked the count before I submit my increased number.
3. Two Kinds of Locking.
a.
Pessimistic Locking: Keep the row locked until I submit the update.
i.Implmentation: select …
for update
ii.Advantage: Very Simple
iii.Drawback:
It requirse a keep-alive connection. When the transaction is time-consuming, the connection make live too long, which the concurrency may fall.
b.Optimistic Locking:
Don’t do the locking until the updating, and only update the row if the row has not been changed by others.
i.Implementation:
How to check if the row is updated?
a. Add an extra
column for versioning (Recommended)
b.
Checksum of the row data (CPU-consuming)
c.
ORA_ROWSCN (Oralce embedded versioning column). (Use it with ROWDEPENDENCIES)
ii.Advantage: Locking period is short. Concurrency is good.
iii.Drawback: Complicated. Retry is needed if the updating fails.