The behaviour of the "for update skip locked" clause is not what I expected it to be. I expected it to try to lock all rows when opening a cursor, just like a regular "for update", and then just skipping the locked rows. Below is a test to show how it works in reality. The setup:
A table with three rows:
rwijk@ORA11GR1> create table t (id)
2 as
3 select level
4 from dual
5 connect by level <= 3
6 /
Tabel is aangemaakt.
A procedure to check if a record of this table t is locked:
rwijk@ORA11GR1> create procedure check_record_locked
2 ( p_id in t.id%type
3 )
4 is
5 cursor c
6 is
7 select 'dummy'
8 from t
9 where id = p_id
10 for update nowait
11 ;
12 e_resource_busy exception;
13 pragma exception_init(e_resource_busy,-54);
14 begin
15 open c;
16 close c;
17 dbms_output.put_line('Record ' || to_char(p_id) || ' is not locked.');
18 rollback;
19 exception
20 when e_resource_busy then
21 dbms_output.put_line('Record ' || to_char(p_id) || ' is locked.');
22 end check_record_locked;
23 /
Procedure is aangemaakt.
First, let's examine the default behaviour of the regular "for update" clause. During the 10 seconds sleep, I copy, paste and execute the code in the comments in another SQL*Plus session.
rwijk@ORA11GR1> declare
2 cursor c is select id from t where id <= 2 for update;
3 l_id t.id%type;
4 begin
5 open c;
6 fetch c into l_id;
7 dbms_lock.sleep(10);
8 --
9 -- In another session run:
10 --
11 -- begin
12 -- check_record_locked(1);
13 -- check_record_locked(2);
14 -- check_record_locked(3);
15 -- end;
16 -- /
17 --
18 close c;
19 end;
20 /
PL/SQL-procedure is geslaagd.
The output from the other session:
rwijk@ORA11GR1> begin
2 check_record_locked(1);
3 check_record_locked(2);
4 check_record_locked(3);
5 end;
6 /
Record 1 is locked.
Record 2 is locked.
Record 3 is not locked.
PL/SQL-procedure is geslaagd.
As expected, the opening of the cursor locks rows 1 and 2. Whether the record is fetched or not, has no influence.
Now a similar test with the "for update skip locked" clause:
rwijk@ORA11GR1> rollback
2 /
Rollback is voltooid.
rwijk@ORA11GR1> declare
2 cursor c is select id from t where id <= 2 for update skip locked;
3 l_id t.id%type;
4 begin
5 open c;
6 dbms_lock.sleep(10);
7 --
8 -- In another session run:
9 --
10 -- begin
11 -- check_record_locked(1);
12 -- check_record_locked(2);
13 -- check_record_locked(3);
14 -- dbms_lock.sleep(10);
15 -- check_record_locked(1);
16 -- check_record_locked(2);
17 -- check_record_locked(3);
18 -- dbms_lock.sleep(10);
19 -- check_record_locked(1);
20 -- check_record_locked(2);
21 -- check_record_locked(3);
22 -- end;
23 -- /
24 --
25 fetch c into l_id;
26 dbms_lock.sleep(10);
27 fetch c into l_id;
28 dbms_lock.sleep(10);
29 close c;
30 end;
31 /
PL/SQL-procedure is geslaagd.
During the first sleep of 10 seconds, the code in the comments is again copied, pasted and executed in a second SQL*Plus session. Note that it prints the situation after just having opened the cursor, after the first fetch and after the second fetch. This is the output:
rwijk@ORA11GR1> begin
2 check_record_locked(1);
3 check_record_locked(2);
4 check_record_locked(3);
5 dbms_lock.sleep(10);
6 check_record_locked(1);
7 check_record_locked(2);
8 check_record_locked(3);
9 dbms_lock.sleep(10);
10 check_record_locked(1);
11 check_record_locked(2);
12 check_record_locked(3);
13 end;
14 /
Record 1 is not locked.
Record 2 is not locked.
Record 3 is not locked.
Record 1 is locked.
Record 2 is not locked.
Record 3 is not locked.
Record 1 is locked.
Record 2 is locked.
Record 3 is not locked.
PL/SQL-procedure is geslaagd.
So opening a cursor with the "for update skip locked" clause doesn't lock a single row. Fetching a row does lock the row, as can be seen in the output. This was the first surprise: I couldn't find text in the documentation telling about this behaviour.
But won't this behaviour lead to a giant "lost update" problem? When you change a record in a second session between the open and the fetch of the cursor - which is allowed because the record is not locked - the cursor could overwrite this change because it has retrieved the value from before the update. Let's check how this works. The setup is a table with bank accounts containing 5 records with an identifying number, an amount and an indicator telling whether interest has been calculated already:
rwijk@ORA11GR1> create table bankaccounts(nr,amount,interest_calculated_indicator)
2 as
3 select level
4 , 100
5 , 'N'
6 from dual
7 connect by level <= 5
8 /
Tabel is aangemaakt.
First a test using the "for update" clause. A piece of code that calculates interest by multiplying the amount of all bankaccounts with the indicator set to 'N' by 5%. During the sleep of 10 seconds, the commented code is copied, pasted and executed in a second SQL*Plus session:
rwijk@ORA11GR1> declare
2 cursor c
3 is
4 select nr
5 , amount
6 from bankaccounts
7 where interest_calculated_indicator = 'N'
8 for update
9 ;
10 type ta_nr is table of bankaccounts.nr%type;
11 type ta_amount is table of bankaccounts.amount%type;
12 a_nr ta_nr;
13 a_amount ta_amount;
14 begin
15 open c;
16 dbms_lock.sleep(10);
17 --
18 -- In another session, try this:
19 --
20 -- update bankaccounts set amount = amount + 100 where nr = 2
21 -- /
22 -- commit
23 -- /
24 -- select * from bankaccounts
25 -- /
26 --
27 loop
28 fetch c bulk collect into a_nr,a_amount limit 2;
29 forall i in 1..a_nr.count
30 update bankaccounts
31 set amount = a_amount(i) * 1.05
32 , interest_calculated_indicator = 'Y'
33 where nr = a_nr(i)
34 ;
35 exit when a_nr.count <> 2;
36 end loop;
37 end;
38 /
PL/SQL-procedure is geslaagd.
The second session has to wait with the update. So all amounts are set to 105.
rwijk@ORA11GR1> select * from bankaccounts
2 /
NR AMOUNT I
---------- ---------- -
1 105 Y
2 105 Y
3 105 Y
4 105 Y
5 105 Y
5 rijen zijn geselecteerd.
rwijk@ORA11GR1> commit
2 /
Commit is voltooid.
After this commit, the second session continues and produces this output:
rwijk@ORA11GR1> update bankaccounts set amount = amount + 100 where nr = 2
2 /
1 rij is bijgewerkt.
rwijk@ORA11GR1> commit
2 /
Commit is voltooid.
rwijk@ORA11GR1> select * from bankaccounts
2 /
NR AMOUNT I
---------- ---------- -
1 105 Y
2 205 Y
3 105 Y
4 105 Y
5 105 Y
5 rijen zijn geselecteerd.
Everything as expected. But now the same code using a "for update skip locked". First restore the situation to its initial state:
rwijk@ORA11GR1> remark Restore situation
rwijk@ORA11GR1> update bankaccounts
2 set amount = 100
3 , interest_calculated_indicator = 'N'
4 /
5 rijen zijn bijgewerkt.
rwijk@ORA11GR1> commit
2 /
Commit is voltooid.
And execute the same code with a "for update skip locked":
rwijk@ORA11GR1> declare
2 cursor c
3 is
4 select nr
5 , amount
6 from bankaccounts
7 where interest_calculated_indicator = 'N'
8 for update skip locked
9 ;
10 type ta_nr is table of bankaccounts.nr%type;
11 type ta_amount is table of bankaccounts.amount%type;
12 a_nr ta_nr;
13 a_amount ta_amount;
14 begin
15 open c;
16 dbms_lock.sleep(10);
17 --
18 -- In another session, try this:
19 --
20 -- update bankaccounts set amount = amount + 100 where nr = 2
21 -- /
22 -- commit
23 -- /
24 -- select * from bankaccounts
25 -- /
26 --
27 loop
28 fetch c bulk collect into a_nr,a_amount limit 2;
29 forall i in 1..a_nr.count
30 update bankaccounts
31 set amount = a_amount(i) * 1.05
32 , interest_calculated_indicator = 'Y'
33 where nr = a_nr(i)
34 ;
35 exit when a_nr.count <> 2;
36 end loop;
37 end;
38 /
PL/SQL-procedure is geslaagd.
The second session doesn't have to wait, and the immediate response in this second SQL*Plus session is:
rwijk@ORA11GR1> update bankaccounts set amount = amount + 100 where nr = 2
2 /
1 rij is bijgewerkt.
rwijk@ORA11GR1> commit
2 /
Commit is voltooid.
rwijk@ORA11GR1> select * from bankaccounts
2 /
NR AMOUNT I
---------- ---------- -
1 100 N
2 200 N
3 100 N
4 100 N
5 100 N
5 rijen zijn geselecteerd.
Now I thought the output in the first session would show a lost update: the amount in record 2 being 105. But it didn't:
rwijk@ORA11GR1> select * from bankaccounts
2 /
NR AMOUNT I
---------- ---------- -
1 105 Y
2 200 N
3 105 Y
4 105 Y
5 105 Y
5 rijen zijn geselecteerd.
It just didn't process the second row. So a "for update skip locked" doesn't only skip the locked rows, it also skips the rows that were modified between the opening and the fetching, thus avoiding the classic "lost update".
Thanks to
James Padfield for hinting at this behaviour.