Thursday, June 20, 2013

Some good things to learn for "for update” in oracle.

Here, we have tried cover a scenario where one procedure is called by 2 process on same time and they want unique record from table. To understand this scenario, we have created tables Departments, Employee and Contact (Some good things to learn for for update in oracle 0.sql).

We are going to learn 3 different cases which comes in our actual life.

1) Simple way of use “update of column” on table. (Some good things to learn for for update in oracle 2.sql)

I have tried to execute same script in 3 windows which is using for update of column. I see below timeframes. Here, 2nd window’s open cursor is waiting till 1st window is not committed and 3rd window’s open cursor is waiting till 2nd window is not completed. (Query1)

In below output. 1st window is committing at 04:10:41, On same time2nd window open cursor is opening. Same for 3rd window also.

It means we will get unique records from different process if it’s called on same time.

1st window output.                   2nd window output.                   3rd window output.

image

2) Use of order by clause with “update of column” (Some good things to learn for for update in oracle 2.sql)

This will also give unique records to different process. 

3) Use of order by clause with “update of column” and also fetch together column/ columns from different table using inner join (Some good things to learn for for update in oracle 3.sql)

In this case, where current of will not work to update column. We need to write explicit query with where clause to update column. But it will also give unique records. 

4) Use of distinct and order by clause with “update of column” and also fetch together column/ columns from different table using inner and left join (Some good things to learn for for update in oracle 4.sql)

This scenario is always confusing to developer while reading oracle documentation or some other link that “distinct is not supporting for “for_update_clause”. Below is comments from Oracle Document.

image

It doesn’t mean that we can’t use distinct in our query with “for_update_clause”. It means that we can’t use it in outer query. There are always 2 sides of any statement. We all need to understand it properly before assuming anything.

I have taken example of Department to show this scenario. I have 5 departments and each department having more than 2 employees. I tried to create script which will return me employees of top 2 distinct departments from employee table in descending order of department. I tried to execute this script in 3 separate window on same time and it was giving distinct results. It means we can use distinct with “for_update_clause”. Below is screen shot of same.

1st window output.                   2nd window output.                   3rd window output.

image

5) Simple way of use “update skipped locked” on table. (Some good things to learn for for update in oracle 5.sql)

I tried to test 1st case for “for update skip locked” by executing same script in 3 separate window on same time. I noticed that 1st window is returning only 1 record and skipped 2nd record, 2nd window is returning one record which was skipped by 1st window, 3rd query is not returning any records. It means all windows are trying to lock on same rows and skipping which is locked by other session and continue.

Hope this will help all. Gimme some comments if you like it. I will try to make some scenarios which will satisfy our requirements and also can use “for update skip locked”.

Below scripts to download.

Some good things to learn for for update in oracle 0.sql

Some good things to learn for for update in oracle 1.sql

Some good things to learn for for update in oracle 2.sql

Some good things to learn for for update in oracle 3.sql

Some good things to learn for for update in oracle 4.sql

Some good things to learn for for update in oracle 5.sql

2 comments:

  1. Hello Asif

    I came late into this blog and found it very useful. currently i am searching for a solution in which i need to implement select for update. My requirement is that i have a table with status ='N' i want to fetch not more than 25 records at time by ordering based timestamp (say a column gen_time). I want to run multiple threads at the same time . How i need to form my query. when i checked ur query 5.sql i found based on rownum <=2 . if i do a order by gen_time and put rownum <=25 then all threads will get the same records ??. can u please help me

    Thanks
    Nibin

    ReplyDelete
    Replies
    1. Hi Nibin,

      No, you will not get same record. All the threads will try to look for the first 25 records and try to update if it's not locked by another thread.

      You can try 2nd option for your requirement which I also implemented for similar requirement.

      Thanks,
      Asif Ghanchi

      Delete