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 1^st window is not committed and 3^rd window’s open cursor is waiting till 2^nd window is not completed. (Query1)

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

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

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

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.

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.

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

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 1^st case for “for update skip locked” by executing same script in 3 separate window on same time. I noticed that 1^st window is returning only 1 record and skipped 2^nd record, 2^nd window is returning one record which was skipped by 1^st window, 3^rd 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