FOR UPDATE SKIP LOCKED

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.