Forum Stats

  • 3,728,029 Users
  • 2,245,522 Discussions
  • 7,853,251 Comments

Discussions

SELECT FOR UPDATE and FETCH FIRST in single query

Sanjib Saha
Sanjib Saha Member Posts: 4
edited May 2020 in SQL & PL/SQL

Hi, Can you please suggest if 'SELECT FOR UPDATE' and 'FETCH FIRST N ROWS' is compatible or not?

Answers

  • mathguy
    mathguy Member Posts: 9,717 Gold Crown
    edited May 2020

    What happened when you tried? It's pretty easy to write a very small test case, which will tell you if they are compatible or not.

  • BEDE
    BEDE Oracle Developer Member Posts: 2,221 Silver Trophy
    edited May 2020

    Test and see.

    For instance:

    create table zz as

    select to_char(sysdate,'mmss')+level n1, ora_hash( to_char(sysdate,'mmss')+level) c1

    from dual

    connect by level<1000

    ;

    select *

    from zz

    order by n1

    fetch first 10 rows only

    for update

    ;

    ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc.

    This I have tested in Live SQL.

    So, you may well fetch the rowid and lock the rows as fetched.

    I think of something like:

    declare

       type tab_rid is table of varchar2(100);

       tb_rid tab_rid;

       v_rid varchar2(100);

    begin

    tb_rid:=tab_rid();

    for r in (

    select t.rowid rid, t.*

    from zz t

    order by n1

    fetch first 10 rows only

    )

    loop

       begin

          select rowidtochar(t.rowid) into v_rid

          from zz t

          where t.rowid=r.rid

          for update nowait;

          tb_rid.extend;

          tb_rid(tb_rid.count):=v_rid;

       exception

          when others then null; --- what to do if one row can't be locked? suppose leave it for some other time...

       end;

    end loop;

    --- do whatever with the locked rows

    ---

    commit;

    end;

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,579 Gold Crown
    edited May 2020

    As Bede points out - Oracle does not allow "for update" with this basic query as "fetch first" is implemented through a view over an analytic function.

    However the following is an approach that will work in pure SQL - fetch the rowids of the rows you want in an inline non-mergeable view - and, if necessary, force a nested loop joinback by rowid:

    select

            *

    from

            t1

    where

            t1.rowid in (

                    select

                            /*+ no_merge */

                            t1a.rowid

                    from

                            t1 t1a

                    order by

                            t1a.n1

                    fetch

                            first 10 rows only

            )

    ;

    Regards

    Jonathan Lewis

  • Sanjib Saha
    Sanjib Saha Member Posts: 4
    edited May 2020

    Thanks Jonathan, but this will not lock the desire row / rows.

  • Paulzip
    Paulzip Member Posts: 8,242 Blue Diamond
    edited May 2020
    Sanjib Saha wrote:Thanks Jonathan, but this will not lock the desire row / rows.

    ?

    cursor curSomething is

    select

            *

    from

            t1

    where

            t1.rowid in (

                    select

                            /*+ no_merge */

                            t1a.rowid

                    from

                            t1 t1a

                    order by

                            t1a.n1

                    fetch

                            first 10 rows only

            )

    for update;

    -- Do stuff with cursor.

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,579 Gold Crown
    edited May 2020
    Sanjib Saha wrote:Thanks Jonathan, but this will not lock the desire row / rows.

    As PaulZip points out, my example does need the FOR UPDATE clause if you want to lock the rows.

    Regards

    Jonathan Lewis

Sign In or Register to comment.