Saturday, February 7, 2009

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.

16 comments:

  1. I had a nice race condition yesterday, when I actually used "for update"!

    Consider:
    session 1 runs:
    select id into max_id from my_table where id=(select max(id) from my_table) for update;

    insert into my_table values (max_id+1,some_data);

    session 2 runs same code
    session 1 commits;
    session 2 commits;

    first session got max_id of 1, and locked the row.
    it inserted a column with id 2.
    Meanwhile session 2 is waiting for row with id=1 (!)
    when session 1 commits, session 2 will select for update the row with id=1, not knowing that it is no longer maximum id. Then it attempts to insert a row with id=2 and gets unique key error.

    In this case, I can just use sequences.

    However, merge has the same behavior!

    Try running two sessions doing:
    MERGE into foo f
    USING (SELECT 1 AS ID, 2 AS Bar from dual) new_foo
    ON (f.ID = new_foo.ID)
    WHEN MATCHED THEN
    UPDATE SET f.Bar = new_foo.Bar
    WHEN NOT MATCHED THEN
    INSERT (ID, Bar)
    VALUES (new_foo.ID, new_foo.Bar);

    The first session will insert a row. After it commits, the second session will also try to insert a row, not update the existing one. The matching seems to happen before the locking.

    ReplyDelete
  2. Rob,

    very interesting and a fundamental difference in behaviour.

    One particular detail that shouldn't change the outcome: Your first attempt using the normal "FOR UPDATE" clause actually fetches from the cursor opened, whereas the "FOR UPDATE SKIP LOCKED" code doesn't before sleeping for 10 secs.

    I'm asking this because in recent releases by default Oracle fetches bulks of 100 rows behind the scenes even if you perform only a single fetch.

    I still think that it doesn't change the findings, the "FOR UPDATE" should lock the rows even without performing a single fetch.

    According to your samples posted I assume you've tested this on 11.1? Which point release was this, 11.1.0.6 or 11.1.0.7?

    Although your demo shows that the "classic" lost update issue is not happening, still the results are quite different, so this is definitely something a developer needs to be aware of when evaluating this option.

    Best Regards,
    Randolf

    ReplyDelete
  3. Chen,

    by the way: Your first example regarding the "race" condition represents a design flaw. That's not the way the FOR UPDATE is meant to be used in order to generate a unique ID. Obviously using a sequence is the most efficient approach, although it's not guaranteed to be gap-free, if this was the original intention of that code snippet.

    Regarding your MERGE example: This has nothing to do with the MERGE command. If you perform your example on a table without a unique index on ID, you won't get this locking issue. Concurrent inserts are not blocking each other. You'll see that both MERGE commands perform an insert without blocking each other.

    However if you have a unique index defined on ID, the second insert will have to wait for the first to either commit or rollback, and succeed or fail accordingly.

    At the time the MERGE command evaluates the match, both sessions can't see each other changes therefore both decide to perform a INSERT. This is Oracle's default behaviour regarding read consistency and nothing special to the MERGE command.

    Best Regards,
    Randolf

    ReplyDelete
  4. Hi Chen,

    I see Randolf already answered/explained the situation well. When waiting for a lock, the SQL statement already started and the results are retrieved as from this time.

    The code reminded me of this old blog post of mine. And as you said: you should be using a sequence. And if you need to display something gap free, you can use the row_number analytic function.

    Regards,
    Rob.

    ReplyDelete
  5. Hi Randolf,

    I've never seen Oracle automatically fetch 100 rows when issuing a single fetch call. When does that happen? I have seen the PL/SQL optimization from version 10 onwards transforming a cursor-for-loop into a 100-row bulk fetching loop under the covers.

    When I leave out the fetch call in the for update scenario, the results are the same: two records locked.

    It was tested on 11.1.0.7.

    Regards,
    Rob.

    ReplyDelete
  6. Rob,

    thanks for the clarification, and yes, you're right, I was referring to the cursor-for-loop optimization but that doesn't apply to your single fetch call. Good catch!

    Best Regards,
    Randolf

    ReplyDelete
  7. Rob,

    I recently stumbled upon this interesting paragraph in the 11g Advanced Application Developers Guide (which was already there in the 10g and 9i versions of the manuals, so not really new):

    http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28424/adfns_sqlproc.htm#i1025003

    It doesn't explicitly mention the different behaviour of the SKIP LOCKED option but some of the descriptions are interesting, in particular the "When using FOR UPDATE in a cursor...". Makes me wonder if it tries to suggest that FOR UPDATE behaves differently when used interactively (which still needs a cursor of course but I assume the text refers to PL/SQL cursor definitions).

    Regards,
    Randolf

    Oracle related stuff blog:
    http://oracle-randolf.blogspot.com/

    SQLTools++ for Oracle (Open source Oracle GUI for Windows):
    http://www.sqltools-plusplus.org:7676/
    http://sourceforge.net/projects/sqlt-pp/

    ReplyDelete
  8. Hi Randolf,

    Thanks for your comment, but unfortunately I don't understand your point, even after reading it multiple times :-).

    I cannot find the quote you refer to in that paragraph and I can't find a piece of text hinting to the behaviour described in this blog post. I thought the chapter was clear.

    Can you explain what part caught your attention?

    Regards,
    Rob.

    ReplyDelete
  9. Rob,

    sorry for the confusion. As mentioned in my comment there is nothing mentioned about the SKIP LOCKED option in particular. The part I was wondering about is the following:

    "If a SELECT FOR UPDATE statement is used when defining a cursor, the rows in the return set are locked when the cursor is opened (before the first fetch) rather than being locked as they are fetched from the cursor.
    ...
    Each row in the return set of a SELECT FOR UPDATE statement is locked individually;
    ..."

    In particular the first sentence leaves room for speculation. Why does it specifically say "If a SELECT FOR UPDATE statement is used when defining a cursor"? What happens if SELECT FOR UPDATE is not part of a PL/SQL cursor, e.g. used in plain SQL rather than PL/SQL cursors? Does it then lock while fetching from the cursor, as the SKIP LOCKED option does?

    This is where my association with this post came from.

    Simply ignore if my thoughts are confusing :-))

    Best Regards,
    Randolf

    ReplyDelete
  10. Hi Randolf,

    I think I now understand what you mean. Probably the sentence "If a SELECT FOR UPDATE statement is used when defining a cursor ..." should be read as "If a SELECT FOR UPDATE statement is used when Oracle defines a cursor under the covers like in any SQL statement ...", rather than your explanation.

    Different behaviour for SQL statements depending on their calling environment seems highly unlikely.

    Regards,
    Rob.

    ReplyDelete
  11. Rob,

    Here is a test case for SKIP LOCKED behavior.

    -- create a table T
    CREATE TABLE t (id NUMBER, name VARCHAR2 (10), hire_time timestamp);

    -- Force use for binds.
    ALTER SESSION SET cursor_sharing=force;

    INSERT INTO t VALUES (1, 'a', NULL);
    INSERT INTO t VALUES (2, 'a', NULL);
    INSERT INTO t VALUES (3, 'a', NULL);
    INSERT INTO t VALUES (4, 'a', NULL);
    INSERT INTO t VALUES (5, 'a', NULL);
    INSERT INTO t VALUES (6, 'a', NULL);
    INSERT INTO t VALUES (7, 'a', NULL);
    INSERT INTO t VALUES (8, 'a', NULL);
    INSERT INTO t VALUES (9, 'a', NULL);
    INSERT INTO t VALUES (10, 'a', NULL);
    COMMIT;

    -- Query T
    SQL> SELECT * FROM T;

    ID NAME HIRE_TIME
    ---------- ---------- ------------------
    1 a
    2 a
    3 a
    4 a
    5 a
    6 a
    7 a
    8 a
    9 a
    10 a

    10 rows selected.

    --
    -- So far so good...
    --

    ReplyDelete
  12. ... Continued.

    -- Now run following block in one session.
    SQL> l
    1 DECLARE
    2 CURSOR c
    3 IS
    4 SELECT id, name
    5 FROM t
    6 FOR UPDATE SKIP LOCKED;
    7
    8 l_id t.id%TYPE;
    9 l_name t.name%TYPE;
    10 BEGIN
    11 OPEN c;
    12
    13 LOOP
    14 FETCH c
    15 INTO l_id, l_name;
    16
    17 EXIT WHEN c%NOTFOUND;
    18
    19 DBMS_LOCK.sleep (1);
    20
    21 UPDATE t
    22 SET name = 'c', hire_time = SYSTIMESTAMP
    23 WHERE CURRENT OF c;
    24 END LOOP;
    25
    26 COMMIT;
    27* END;
    SQL> /

    PL/SQL procedure successfully completed.

    -- Run following block in another session about 1 or 2 seconds after the above block started.

    SQL> l
    1 DECLARE
    2 CURSOR c
    3 IS
    4 SELECT id, name
    5 FROM t
    6 FOR UPDATE SKIP LOCKED;
    7
    8 l_id t.id%TYPE;
    9 l_name t.name%TYPE;
    10 BEGIN
    11 OPEN c;
    12
    13 LOOP
    14 FETCH c
    15 INTO l_id, l_name;
    16
    17 EXIT WHEN c%NOTFOUND;
    18
    19 DBMS_LOCK.sleep (1);
    20
    21 UPDATE t
    22 SET name = 'f', hire_time = SYSTIMESTAMP
    23 WHERE CURRENT OF c;
    24 END LOOP;
    25
    26 COMMIT;
    27* END;
    SQL> /

    PL/SQL procedure successfully completed.

    -- After both the blocks are complete, query table T.

    SQL> SELECT * FROM T;

    ID NAME HIRE_TIME
    ---------- ---------- -----------------------------------
    1 c 15-OCT-09 11.54.45.133076 AM
    2 c 15-OCT-09 11.54.46.133904 AM
    3 c 15-OCT-09 11.54.47.134838 AM
    4 f 15-OCT-09 11.54.47.418692 AM
    5 c 15-OCT-09 11.54.48.136714 AM
    6 f 15-OCT-09 11.54.48.419968 AM
    7 c 15-OCT-09 11.54.49.137703 AM
    8 f 15-OCT-09 11.54.49.421900 AM
    9 c 15-OCT-09 11.54.50.139538 AM
    10 f 15-OCT-09 11.54.50.423831 AM

    10 rows selected.


    As you can see, the rows were updated one at a time by each session and when a row was locked by one session, the other one just skipped it.

    One thing that confused me is the above behavior with this statement from the documentation:

    "If a SELECT FOR UPDATE statement is used when defining a cursor, the rows in the return set are locked when the cursor is opened (before the first fetch) rather than being locked as they are fetched from the cursor."

    What I see is that the rows are not locked when you open the cursor. They are not even locked when you fetch them. They are locked when you update them and the lock is released right away even before a COMMIT occurs.

    So how do you explain this behavior?

    BTW, this is on 11.1.0.7

    ReplyDelete
  13. Developer,

    > What I see is that the rows are not locked when you open the cursor.

    That's what this blogpost is about: when using FOR UPDATE SKIP LOCKED, records are locked when they are fetched, not when the cursor is opened.

    > They are not even locked when you fetch them. They are locked when you update them and the lock is released right away even before a COMMIT occurs.

    That's not what your test results showed, and it's not what is happening. Rows do get locked when you fetch them in a FOR UPDATE SKIP LOCKED cursor. And locks are released when you commit. Why do you think this is not the case?

    Regards,
    Rob.

    ReplyDelete
  14. Rob,
    Yes, you are correct. Rows get locked after fetch and locks are held until a commit/rollback occurs.
    I don't know what I was thinking!
    Thank you.

    ReplyDelete
  15. Please refer great example with different scenarios.
    http://www.ghanchiasif.blogspot.in/2013/06/some-good-things-to-learn-for-update-in.html

    ReplyDelete
  16. Nice Article !
    This is my pleasure to read your article.
    Really this will help to people of PostgreSQL Community.

    I have also prepared one article about, PostgreSQL 9.5 FOR UPDATE SKIP LOCKED option to select only committed records.
    You can also visit my article, your comments and reviews are most welcome.

    http://www.dbrnd.com/2016/08/postgresql-9-5-using-for-update-skip-locked-option-select-only-committed-records-mvcc-lock-with-nowait-thread/

    ReplyDelete