Oracle Scratchpad

June 3, 2020

Fetch First Update

Filed under: 12c,Hints,Oracle,Tuning — Jonathan Lewis @ 1:48 pm BST Jun 3,2020

A question about mixing the (relatively new) “fetch first” syntax with “select for update” appeared a few days ago on the Oracle Developer Forum. The requirement was for a query something like:


select
        *
from
        t1
order by
        n1
fetch
        first 10 rows only
for     update
;

The problem with this query is that it results in Oracle raising error ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc. The error doesn’t seem to be particularly relevant, of course, until you remember that “fetch first” creates an inline view using the analytic row_number() under the covers.

One suggested solution was to use PL/SQL to open a cursor with a pure select then use a loop to lock each row in turn. This would need a little defensive programming, of course, since each individual “select for update” would be running at a different SCN from the driving loop, and there would be some risk of concurrency problems (locking, or competing data change) occuring.

There is a pure – thought contorted – SQL solution though where we take the driving SQL and put it into a subquery that generates the rowids of the rows we want to lock, as follows:


select
        /*+
                qb_name(main)
        */
        *
from
        t1
where
        t1.rowid in (
                select
                        /*+ qb_name(inline) unnest no_merge */
                        t1a.rowid
                from
                        t1 t1a
                order by
                        t1a.n1
                fetch 
                        first 10 rows only
        )
for update
;

The execution plan for this query is critical – so once you can get it working it would be a good idea to create a baseline (or SQL Patch) and attach it to the query. It is most important that the execution plan should be the equivalent of the following:


select  /*+   qb_name(main)  */  * from  t1 where  t1.rowid in (
select    /*+ qb_name(inline) unnest no_merge */    t1a.rowid   from
t1 t1a   order by    t1a.n1   fetch    first 10 rows only  ) for update

Plan hash value: 1286935441

---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |      |      1 |        |     10 |00:00:00.01 |     190 |       |       |          |
|   1 |  FOR UPDATE                   |      |      1 |        |     10 |00:00:00.01 |     190 |       |       |          |
|   2 |   BUFFER SORT                 |      |      2 |        |     20 |00:00:00.01 |     178 |  2048 |  2048 | 2048  (0)|
|   3 |    NESTED LOOPS               |      |      1 |     10 |     10 |00:00:00.01 |     178 |       |       |          |
|*  4 |     VIEW                      |      |      1 |     10 |     10 |00:00:00.01 |     177 |       |       |          |
|*  5 |      WINDOW SORT PUSHED RANK  |      |      1 |  10000 |     10 |00:00:00.01 |     177 |  2048 |  2048 | 2048  (0)|
|   6 |       TABLE ACCESS FULL       | T1   |      1 |  10000 |  10000 |00:00:00.01 |     177 |       |       |          |
|   7 |     TABLE ACCESS BY USER ROWID| T1   |     10 |      1 |     10 |00:00:00.01 |       1 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("from$_subquery$_003"."rowlimit_$$_rownumber"<=10)
   5 - filter(ROW_NUMBER() OVER ( ORDER BY "T1A"."N1")<=10)

Critically you need the VIEW operation to be the driving query of a nested loop join that does the “table access by user rowid” joinback. In my case the query has used a full tablescan to identify the small number of rowids needed – in a production system that would be the part of the statement that should first be optimised.

It’s an unfortunate feature of this query structure (made messier by the internal rewrite for the analytic function) that it’s not easy to generate a correct set of hints to force the plan until after you’ve already managed to get the plan. Here’s the outline information that shows the messiness of the hints I would have needed:


Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      DB_VERSION('12.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"INLINE")
      OUTLINE_LEAF(@"SEL$A3F38ADC")
      UNNEST(@"SEL$1")
      OUTLINE(@"INLINE")
      OUTLINE(@"MAIN")
      OUTLINE(@"SEL$1")
      NO_ACCESS(@"SEL$A3F38ADC" "from$_subquery$_003"@"SEL$1")
      ROWID(@"SEL$A3F38ADC" "T1"@"MAIN")
      LEADING(@"SEL$A3F38ADC" "from$_subquery$_003"@"SEL$1" "T1"@"MAIN")
      USE_NL(@"SEL$A3F38ADC" "T1"@"MAIN")
      FULL(@"INLINE" "T1A"@"INLINE")
      END_OUTLINE_DATA
  */

You’ll notice that my /*+ unnest */ hint is now modified – for inclusion at the start of the query – to /*+ unnest(@sel1) */ rather than the /*+ unnest(@inline) */ that you might have expected. That’s the side effect of the optimizer doing the “fetch first” rewrite before applying “missing” query block names. If I wanted to write a full hint set into the query itself (leaving the qb_name() hints in place but removing the unnest and merge I had originally) I would need the following:


/*+
        unnest(@sel$1)
        leading(@sel$a3f38adc from$_subquery$_003@sel$1 t1@main)
        use_nl( @sel$a3f38adc t1@main)
        rowid(  @sel$a3f38adc t1@main)
*/

I did make a bit of a fuss about the execution plan. I think it’s probably very important that everyone who runs this query gets exactly the same plan and the plan should be this nested loop. Although there’s a BUFFER SORT at operation 2 that is probably ensuring that every would get the same data in the same order regardless of the execution plan before locking any of it, I would be a little worried that different plans might somehow be allowed to lock the data in a different order, thus allowing for deadlocks.

6 Comments »

  1. Hello Jonathan,

    A very interesting topic, that comes back after a long time.

    While reading it, I suddenly remembered of the following related older but equally interesting post:

    https://hoopercharles.wordpress.com/2011/11/21/select-for-update-in-what-order-are-the-rows-locked/

    Thanks a lot & Best Regards,
    Iudith Mentzel

    Comment by Iudith Mentzel — June 3, 2020 @ 3:28 pm BST Jun 3,2020 | Reply

    • Iudith,

      Thanks for the comment and the link – good memory.

      I won’t be able to put a finger on it, but I do remember a discussion a few years ago (maybe about the time Charles wrote that blog note) about a bug (or possibly a “notable change inbehaviour”) in Oracle where the order in which rows were locked changed on an upgrade if there were an ORDER BY clause so that the order of locking matched the ORDER BY clause rather than the order of data access.

      (I don’t remember the details but I think this was fairly important when two non-trivial “literal” statements would fetch overlapping data but one would used a nested loop join and the other would use a hash join so the order of data access was different and the statements would deadlock if the lock was in access order, but one statement would simply wait for the other if it was in “order by” order.)

      Regards
      Jonathan Lewis

      Comment by Jonathan Lewis — June 3, 2020 @ 7:01 pm BST Jun 3,2020 | Reply

  2. Hi Jonathan,

    One suggested solution was to use PL/SQL to open a cursor with a pure select then use a loop to lock each row in turn. This would need a little defensive programming, of course, since each individual “select for update” would be running at a different SCN from the driving loop, and there would be some risk of concurrency problems (locking, or competing data change) occuring.

    I would rather take a look at SKIP LOCKED + BULK COLLECT LIMIT N. It locks the rows while fetching them, so that it does not provide the exact behaviour of “CURSOR … FOR UPDATE” which locks the whole resultset when the cursor is opened.

    I put an abridged example of the proposed technique below:

    create table t1
    as
    select level n1 
      from dual
      connect by level <= 10;
    
    declare
      cursor csr is
      select *
        from t1
       order by n1
       for update skip locked;
      type csr_tbl_type is table of csr%rowtype;
      tbl csr_tbl_type;
    begin
      null;
      open csr;
      fetch csr bulk collect into tbl limit 3;
      for i in 1..cardinality(tbl)
      loop
        dbms_output.put_line(tbl(i).n1);
      end loop;
    end;
    /
    
    -- I am running this one with the AUTONOMOUS_TRANSACTION pragma to emulate a different session
    declare
      pragma autonomous_transaction;
      cursor csr is
      select *
        from t1
       order by n1
       for update skip locked;
      type csr_tbl_type is table of csr%rowtype;
      tbl csr_tbl_type;
    begin
      open csr;
      fetch csr bulk collect into tbl limit 3;
      for i in 1..cardinality(tbl)
      loop
        dbms_output.put_line(tbl(i).n1);
      end loop;
      rollback;
    end;
    /
    
    -- the output of the last two blocks is as follows:
    SQL> declare
    ..skip..
     17  end;
     18  /
    1
    2
    3
    
    PL/SQL procedure successfully completed.
    
    SQL>
    SQL> declare
    ..skip..
     19  end;
     20  /
    4
    5
    6
    
    PL/SQL procedure successfully completed.
    

    Regards,
    Mikhail.

    Comment by Mikhail Velikikh — June 3, 2020 @ 6:06 pm BST Jun 3,2020 | Reply

    • Mikhail,

      Thanks for the comment.

      That’s a fascinating difference between “select for update” and “select for update skip locked” when used as cursors in that way. Is it documented anywhere (sorry, being lazy) or is it something you discovered experimentally.

      It’s possible that it’s also demonstrating an underlying change in “skip locked”. I can’t be certain when it changed because it’s been a long time (10 years) since I looked at it, but in this posting the comment discussion says that rownum should applied before the test for the lock — and I’m wondering if the “limit N” used to be the same. I’ll have to look at it again when I have a little time.

      Comment by Jonathan Lewis — June 3, 2020 @ 6:53 pm BST Jun 3,2020 | Reply

      • Jonathan,

        [quote]Is it documented anywhere (sorry, being lazy) or is it something you discovered experimentally.[/quote]
        I must have read about it somewhere, probably on this blog post: Rob van Wijk: http://rwijk.blogspot.com/2009/02/for-update-skip-locked.html

        I see that it is documented that opening a cursor locks the rows of the result set:
        https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/static-sql.html#GUID-87B4D958-520C-4483-BC63-2D1FE4C4CC01
        I am afraid I cannot find when it is said in so many words that ‘for update skip locked locks the rows as they are fetched’.
        Yet it can be confirmed by a test, and I would love to see an experiment proving the opposite.

        I just did a small test dumping the redo entries for two open cursor commands, so as to substantiate this “SKIP LOCKED” behaviour with some facts:
        https://github.com/mvelikikh/oracle/blob/master/blog/202006_for_update_vs_skip_locked_redo_dump/for_update_skip_locked_redo_dump.sql

        1. FOR UPDATE:

        exec open :rc for select * from t1 order by n1 for update
        

        its trace file: https://github.com/mvelikikh/oracle/blob/master/blog/202006_for_update_vs_skip_locked_redo_dump/orcl_ora_17319_FOR_UPDATE.trc

        [oracle@db-19-siha trace]$ grep -E 'RECORD|CHANGE' orcl_ora_17319_FOR_UPDATE.trc | sed -e '/CHANGE/s/^/ /'
        REDO RECORD - Thread:1 RBA: 0x00002f.00008fa8.0010 LEN: 0x0208 VLD: 0x05 CON_UID: 3628136135
                CHANGE #1 CON_ID:3 TYP:0 CLS:31 AFN:10 DBA:0x018000f0 OBJ:4294967295 SCN:0x00000000004a08b9 SEQ:1 OP:5.2 ENC:0 RBL:0 FLG:0x0000
                CHANGE #2 CON_ID:3 TYP:0 CLS:32 AFN:10 DBA:0x0180298e OBJ:4294967295 SCN:0x00000000004a08b8 SEQ:1 OP:5.1 ENC:0 RBL:0 FLG:0x0000
                CHANGE #3 CON_ID:3 TYP:0 CLS:1 AFN:11 DBA:0x02c0b4c3 OBJ:23729 SCN:0x00000000004a0a70 SEQ:10 OP:11.4 ENC:0 RBL:0 FLG:0x0000
                CHANGE #4 MEDIA RECOVERY MARKER CON_ID:3 SCN:0x0000000000000000 SEQ:0 OP:5.20 ENC:0 FLG:0x0000
        REDO RECORD - Thread:1 RBA: 0x00002f.00008fa9.0028 LEN: 0x00f4 VLD: 0x01 CON_UID: 3628136135
                CHANGE #1 CON_ID:3 TYP:0 CLS:32 AFN:10 DBA:0x0180298e OBJ:4294967295 SCN:0x00000000004a0b26 SEQ:1 OP:5.1 ENC:0 RBL:0 FLG:0x0000
                CHANGE #2 CON_ID:3 TYP:0 CLS:1 AFN:11 DBA:0x02c0b4c3 OBJ:23729 SCN:0x00000000004a0b26 SEQ:1 OP:11.4 ENC:0 RBL:0 FLG:0x0000
        
        ..skipping 7 similar records..
        
        REDO RECORD - Thread:1 RBA: 0x00002f.00008fad.0010 LEN: 0x00f4 VLD: 0x01 CON_UID: 3628136135
                CHANGE #1 CON_ID:3 TYP:0 CLS:32 AFN:10 DBA:0x0180298e OBJ:4294967295 SCN:0x00000000004a0b26 SEQ:9 OP:5.1 ENC:0 RBL:0 FLG:0x0000
                CHANGE #2 CON_ID:3 TYP:0 CLS:1 AFN:11 DBA:0x02c0b4c3 OBJ:23729 SCN:0x00000000004a0b26 SEQ:9 OP:11.4 ENC:0 RBL:0 FLG:0x0000
        

        2. FOR UPDATE SKIP LOCKED:

        exec open :rc for select * from t1 order by n1 for update skip locked
        

        its trace file: https://github.com/mvelikikh/oracle/blob/master/blog/202006_for_update_vs_skip_locked_redo_dump/orcl_ora_17319_FOR_UPDATE_SKIP_LOCKED.trc

        [oracle@db-19-siha trace]$ grep -E 'RECORD|CHANGE' orcl_ora_17319_FOR_UPDATE_SKIP_LOCKED.trc | sed -e '/CHANGE/s/^/     /'
        REDO RECORD - Thread:1 RBA: 0x00002f.00008fba.0010 LEN: 0x0168 VLD: 0x05 CON_UID: 3628136135
                CHANGE #1 CON_ID:3 TYP:0 CLS:21 AFN:10 DBA:0x018000a0 OBJ:4294967295 SCN:0x00000000004a08bd SEQ:1 OP:5.2 ENC:0 RBL:0 FLG:0x0000
                CHANGE #2 CON_ID:3 TYP:0 CLS:22 AFN:10 DBA:0x018025eb OBJ:4294967295 SCN:0x00000000004a08bc SEQ:1 OP:5.1 ENC:0 RBL:0 FLG:0x0000
                CHANGE #3 MEDIA RECOVERY MARKER CON_ID:3 SCN:0x0000000000000000 SEQ:0 OP:5.20 ENC:0 FLG:0x0000
        

        We can see that the first command generated 10 Redo Op Codes 11.4 – a “lock row piece” Op Code, where 10 is the number of rows in the table, whereas the second command generated one redo record with the usual begin transaction Redo Op codes. Therefore, opening the FOR UPDATE SKIP LOCKED cursor did not lock any rows as opposed to its FOR UPDATE counterpart.

        Regards,
        Mikhail.

        Comment by Mikhail Velikikh — June 4, 2020 @ 12:31 pm BST Jun 4,2020 | Reply

  3. Hello Jonathan & Mikhail,

    You can easily demonstrate the behavior for FOR UPDATE SKIP LOCKED using an autonomous transaction,
    like in your first example:

    create table t1
    as
    select level n1
    from dual
    connect by level <= 10
    /

    declare
    cursor csr is
    select *
    from t1
    order by n1
    for update skip locked;
    type csr_tbl_type is table of csr%rowtype;
    tbl csr_tbl_type;

    procedure check_locked (p_n1 in t1.n1%type )
    is
    pragma autonomous_transaction;

    cursor c
    is
    select 'dummy' from t1
    where n1 = p_n1
    for update nowait ;

    e_resource_busy exception;
    pragma exception_init(e_resource_busy,-54);
    begin
    open c;
    close c;
    dbms_output.put_line('Record ' || to_char(p_n1) || ' is not locked.');
    rollback;
    exception
    when e_resource_busy then
    dbms_output.put_line('Record ' || to_char(p_n1) || ' is locked.');
    end check_locked;

    begin
    open csr;

    dbms_output.put_line('Locked after open');
    for n in 1 .. 10
    loop
    check_locked(n);
    end loop;

    fetch csr bulk collect into tbl limit 3;

    dbms_output.put_line('Locked after fetch limit 3');
    for n in 1 .. 10
    loop
    check_locked(n);
    end loop;

    close csr;
    end;
    /

    Locked after open
    Record 1 is not locked.
    Record 2 is not locked.
    Record 3 is not locked.
    Record 4 is not locked.
    Record 5 is not locked.
    Record 6 is not locked.
    Record 7 is not locked.
    Record 8 is not locked.
    Record 9 is not locked.
    Record 10 is not locked.

    Locked after fetch limit 3
    Record 1 is locked.
    Record 2 is locked.
    Record 3 is locked.
    Record 4 is not locked.
    Record 5 is not locked.
    Record 6 is not locked.
    Record 7 is not locked.
    Record 8 is not locked.
    Record 9 is not locked.
    Record 10 is not locked.

    So , from here we see that the records are locked as they are fetched.

    I think that there is an essential difference between ROWNUM < n and FETCH … LIMIT n,
    because the ROWNUM condition is applied while executing the query, on the server side, while the FETCH … LIMIT n is controlled
    by the client side, after all the selected rows were located.

    In a similar way, we can demonstrate not only that the rows are locked ONLY when they are fetched,
    but, also that if a row that was selected by the FOR UPDATE SKIP LOCKED cursor is either:

    a. deleted by another session after the cursor was opened, but before that row was fetched and locked
    or
    b. changed by another session after the cursor was opened, but before that row was fetched and locked
    in such a way that is relevant for its selection through the cursor (for example, changing a column that appears in the cursor's
    SELECT list or WHERE clause or ORDER BY)

    then that row will be skipped when fetching from the FOR UPDATE SKIP LOCKED cursor.

    Thanks a lot & Best Regards,
    Iudith Mentzel

    Comment by Iudith Mentzel — June 4, 2020 @ 3:01 pm BST Jun 4,2020 | Reply


RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by WordPress.com.