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?

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.

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;

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

Sanjib Saha

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

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.

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