Notes on ‘Expert Oracle’ — No.6.1: Locking — Optimistic & Pessimistic

1. The problem:
Lost Update

      Example: Increase Count


        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.

Pessimistic Locking: Keep the row locked until I submit the update.

     i.Implmentation: select …
for update

    ii.Advantage: Very Simple

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.

How to check if the row is updated?

         a. Add an extra
column for versioning (Recommended)

Checksum of the row data (CPU-consuming)

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. 


Leave a Comment

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.