6 Replies Latest reply on May 29, 2020 6:22 AM by Jonathan Lewis

    SELECT FOR UPDATE and FETCH FIRST in single query

    Sanjib Saha

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

        • 1. Re: SELECT FOR UPDATE and FETCH FIRST in single query
          mathguy

          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.

          • 2. Re: SELECT FOR UPDATE and FETCH FIRST in single query
            BEDE

            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;

            • 3. Re: SELECT FOR UPDATE and FETCH FIRST in single query
              Jonathan Lewis

              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

              • 4. Re: SELECT FOR UPDATE and FETCH FIRST in single query
                Sanjib Saha

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

                • 5. Re: SELECT FOR UPDATE and FETCH FIRST in single query
                  Paulzip

                  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.

                  • 6. Re: SELECT FOR UPDATE and FETCH FIRST in single query
                    Jonathan Lewis

                    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