Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, George.

Asked: April 07, 2016 - 10:09 am UTC

Last updated: December 04, 2020 - 3:25 am UTC

Version: 11.2.0.3.0

Viewed 50K+ times! This question is

You Asked

Hi Team

Have a scenario to select a particular set of rows from a table for further processing.
We need to ensure that multi users do not work on the same set of rows.

We use SELECT FOR UPDATE SKIP LOCKED in order to achieve this.

EG:a simplified version looks like this...
The calling program is in java and the locking portion is as follows.
select * 
  from a 
where a.pkey in (select a1.pkey
                   from (SELECT rownum as rnk
                               ,a.pkey
                           FROM a
                          WHERE col1=? /*input from java*/ 
                            AND col2=? /*input from java*/ 
                        ORDER BY <deterministic_column /*input from java*/>
                          ) 
                   where rnk<=i_val /*input from java*/ 
                  )
for update skip locked


Assume the query provides an output of 10 rows without the condition "where rnk<=i_val"

In the multiuser scenario

Assume the
user1 provides value of i_val=3 rows
user2 process i_val=5 rows

What happens is that user1 gets the 3 rows to be locked(say rowids==>1,2,3)
but user2 would not get only 2 rows(4,5) to be locked, even though he requested for 5 rows.

is there any option to get user2 to to lock rows 4,5,6,7,8

We tried
select * from(
select *
from a
order by deterministic_colum
) where rownum<=ival for update skip locked

but that query fails to parse.

The option we did is as follows


 i=0;
for x in (select a1.pkey
                   from (SELECT rownum as rnk
                               ,a.pkey
                           FROM a
                          WHERE col1=? 
                            AND col2=?
                        ORDER BY <deterministic_column>
                          ) 
           )
loop
    
     select a.pkey from a where a.pkey=x.pkey for update skip locked;
     i++;
     if i== ival then
       exit;
     end if;
end loop;


Even though this works wanted to know if there is a better approach in skip locked rows with a limit clause or any alternative in SQL itself.

and Connor said...

You probably want to do explicit control of your fetching, because

open cursor for select ... for update;

will lock all rows the moment you open the cursor. However,

open cursor for select ... for update skip locked;

does not lock *any* rows. They are locked as you *fetch*

So you could something like this (pseudo code)

cursor C is select ... for update skip locked;
open C;
loop

  loop
    fetch C bulk collect into :my_array limit 100;
    append :my_array to :to_be_processed_array;
    exit when c%notfound or :to_be_processed_array.count > 1000;
  end loop;

-- process any rows in :to_be_processed_array

exit when c%notfound;

end loop;
close C;


So we're going to repeatedly try get/lock 100 rows, but of course, we might anywhere between 0 and 100 because they might already be locked), but we finally get to 1000 rows (or we run out entirely) then we'll go ahead and process them.

Hope this helps.

Rating

  (13 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

RE

George Joseph, April 07, 2016 - 12:55 pm UTC

Thanks for the reply Connor.

I tried the bulk collect using the limit clause and passed the limit value as ival;


create table t as select level as lvl,mod(level,3) as rep from dual connect by level<=10;


create or replace procedure sp_locktest(p_val in number)
as
cursor c 
    is select * 
         from t for update skip locked;

type tp is table of c%rowtype;

l_tp tp:=tp();

l_val number:=p_val;

begin

open c; /* opening the cursor doesnt lock the table.*/
loop
       /* This fetch should lock as many rows for update as p_val which are available*/
       fetch c bulk collect into l_tp limit l_val; 
       dbms_output.put_line('Number of array objects '||l_tp.count);       
       exit;
end loop;

for i in 1 .. l_tp.count
loop
       dbms_output.put_line('The value of array object lvl at '||i||' is ' || l_tp(i).lvl);
end loop;

end;


Then i run this

begin
sp_locktest(2);
end;

This gives the output as follows.
Number of array objects 2
The value of array object lvl at 1 is 3
The value of array object lvl at 2 is 4

In the same session when i rerun
I still get the output as follows.
Number of array objects 2
The value of array object lvl at 1 is 3
The value of array object lvl at 2 is 4


I was expecting to see a different set this time since the first run has already blocked 3,4==>lvl

I am using SQLDeveloper Version 4.0.2.15. Running as Script.
Connor McDonald
April 08, 2016 - 2:43 am UTC

Perhaps a better syntax would have been:

"skip locked by another session"

Skip locked was introduced (for AQ) to allow concurrent access to rows without each session getting 'tangled' with the other (which I think is also your requirement).

Hope this helps.

Jan, April 07, 2016 - 1:55 pm UTC

This seems to work, although based on a row by row method, but for few rows n it should be ok:

==
CREATE TABLE t AS SELECT rownum ID FROM all_objects;
==

===
DECLARE
CURSOR c IS
SELECT *
FROM t
FOR UPDATE SKIP LOCKED;

l_row c%ROWTYPE;
i INTEGER:=0;
BEGIN

OPEN c;
LOOP
FETCH c INTO l_row;
IF l_row.id IS NOT NULL THEN
i:=i+1;
END IF;
dbms_output.put_line(l_row.id);
EXIT WHEN i>=5;
END LOOP;
CLOSE c;
END;
===

To reduce contention in multi session environment, you can put ORDER BY dbms_random.random.

To Joseph | Check if this helps.

Rajeshwaran Jeyabal, April 07, 2016 - 2:53 pm UTC

rajesh@ORA11G>
rajesh@ORA11G> create table t
  2  as
  3  select *
  4  from all_users ;

Table created.

rajesh@ORA11G> select * from ( select * from t order by user_id ) where rownum <=10;

USERNAME                          USER_ID CREATED
------------------------------ ---------- -----------
SYS                                     0 09-OCT-2013
SYSTEM                                  5 09-OCT-2013
OUTLN                                   9 09-OCT-2013
DIP                                    14 09-OCT-2013
ORACLE_OCM                             21 09-OCT-2013
DBSNMP                                 30 09-OCT-2013
APPQOSSYS                              31 09-OCT-2013
WMSYS                                  32 09-OCT-2013
EXFSYS                                 42 09-OCT-2013
CTXSYS                                 43 09-OCT-2013

10 rows selected.

rajesh@ORA11G> variable x number
rajesh@ORA11G> exec :x :=3;

PL/SQL procedure successfully completed.

rajesh@ORA11G> declare
  2     l_cnt int := 0;
  3  begin
  4     for x in ( select *
  5                             from t
  6                             where rowid in
  7                                       (
  8                                     select *
  9                                     from (
 10                                     select rowid rid
 11                                     from t
 12                                     order by user_id
 13                                               )
 14                                       )
 15                             order by user_id
 16                             for update skip locked)
 17     loop
 18             l_cnt := l_cnt + 1;
 19             dbms_output.put_line (' userid ='|| x.user_id);
 20             exit when l_cnt = :x;
 21     end loop;
 22  end;
 23  /
 userid =0
 userid =5
 userid =9

PL/SQL procedure successfully completed.

rajesh@ORA11G>


We sorted based on the user_id and fetched first three rows. ( Remember, we have not yet committed yet - so that doesn't release the locks acquired on these three rows).


rajesh@ORA11G>
rajesh@ORA11G> exec :x :=5;

PL/SQL procedure successfully completed.

rajesh@ORA11G> declare
  2     l_cnt int := 0;
  3     pragma autonomous_transaction ;
  4  begin
  5     for x in ( select *
  6                             from t
  7                             where rowid in
  8                                       (
  9                                     select *
 10                                     from (
 11                                     select rowid rid
 12                                     from t
 13                                     order by user_id
 14                                               )
 15                                       )
 16                             order by user_id
 17                             for update skip locked)
 18     loop
 19             l_cnt := l_cnt + 1;
 20             dbms_output.put_line (' userid ='|| x.user_id);
 21             exit when l_cnt = :x;
 22     end loop;
 23     rollback ;
 24  end;
 25  /
 userid =14
 userid =21
 userid =30
 userid =31
 userid =32

PL/SQL procedure successfully completed.

rajesh@ORA11G>


Rather than switching to a new session/sql*plus - initiated AT then ran this sql with a different limit, since first three records are locked by different transaction, we skip those and start from the fourth record in this sorted set and proceed further to get next five records.

@Rajesh,@Jan

George Joseph, April 07, 2016 - 3:32 pm UTC

Thanks for the inputs and the examples Rajesh, Jan.

The solution you have provided is what I have implemented in the code and is working as expected. I was checking if there exists a SQL solution to this issue or a PL/SQL solution which is more efficient.

The rational being, the loop code would need to iterate through every row in the base table and check if it is locked and then decide to lock or grab the next one.

Connor provided me with a way to make use of bulk collect and using his BULK COLLECT and by passing the p_val as the LIMIT clause i can lock the rows in one fetch itself.

So from my initial example of table a, which has a full set of 10 rows

user1 would pass p_val=3 he would get rows
(1,2,3)


user2 would pass p_val=5 in a different session and he would get rows
(4,5,6,7,8)


Here is something else i noticed while running the code, if i were to run the locking procedure(sp_locktest(2)) twice in the same session i find that the output is repeated, even though i have neither committed nor done a rollback of the first begin..end block.

Does SELECT FOR UPDATE release locks after a begin..end?

Thanks
George
Chris Saxon
April 08, 2016 - 2:45 am UTC

commit and rollback are the only things that release locks.

SELECT FOR UPDATE release locks

Rajeshwaran Jeyabal, April 08, 2016 - 2:20 am UTC

Here is something else i noticed while running the code, if i were to run the locking procedure(sp_locktest(2)) twice in the same session i find that the output is repeated, even though i have neither committed nor done a rollback of the first begin..end block.

Does SELECT FOR UPDATE release locks after a begin..end?


Lets say Table T has ten rows with column X having values between 1 and 10, and sort this table based on column X.

From the Session#1, you do this transaction -that says sort by X and fetch 3 rows from the Table 'T' and skip those locked rows.

since we the only game in town, we got all three records. ( remember, no commit yet ! )

declare
 l_limit int := 3;
begin 
 for x in (select * from 
    (select * from t order by x) 
   where rownum <= l_limit
   FOR UPDATE SKIP LOCKED)
 loop
  NULL ;
 end loop;
end;
/ 


Now, when you run this code again within the same session again. It check if there is any other transaction from different session OR AT from the same session - is holding locks for the resource which i am requesting, if so skip those. - again we the only game in town, we got all those three records (again!). Hence the Same final output is repeated.

Re:

George Joseph, April 08, 2016 - 4:54 am UTC

Thanks, that clears it up.

testing

George Joseph, April 11, 2016 - 6:16 am UTC

Hi

If i were to user an order by dbms_random.value while doing select for update skip locked i would get a different set of records than the first time i ran the sp_locktest(2) without commiting or rolling back.


create or replace procedure sp_locktest(p_val in number)
as
cursor c 
    is select * 
         from t order by dbms_random.value for update skip locked;

type tp is table of c%rowtype;

l_tp tp:=tp();

l_val number:=p_val;

begin

open c; /* opening the cursor doesnt lock the table.*/
loop
       /* This fetch should lock as many rows for update as p_val which are available*/
       fetch c bulk collect into l_tp limit l_val; 
       dbms_output.put_line('Number of array objects '||l_tp.count);       
       exit;
end loop;

for i in 1 .. l_tp.count
loop
       dbms_output.put_line('The value of array object lvl at '||i||' is ' || l_tp(i).lvl);
end loop;

end;
/

select * from v$mystat where rownum=1;

       SID STATISTIC#      VALUE
---------- ---------- ----------
         3          0          0 


begin
 sp_locktest(2);
end;
/


Number of array objects 2
The value of array object lvl at 1 is 7
The value of array object lvl at 2 is 9
anonymous block completed

----
The rows which were selected for for update are at 7 and 9.

select * from v$lock where sid=3;
ADDR             KADDR                   SID TYPE        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
0000000411459238 0000000411459290          3 AE          100          0          4          0        529          0 
FFFFFFFF79FD6190 FFFFFFFF79FD61F0          3 TM       216135          0          3          0        414          0 
000000040E7A3068 000000040E7A30E0          3 TX       655401     322570          6          0        414          0 

There are two locks which are held, TM and TX

--Running the code again...
begin
 sp_locktest(2);
end;
/

Number of array objects 2
The value of array object lvl at 1 is 8
The value of array object lvl at 2 is 10

New locks have not been created although the "rows" which have been used for update have changed to 8 and 10 from 7 and 9

select * from v$lock where sid=3;

ADDR             KADDR                   SID TYPE        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
0000000411459238 0000000411459290          3 AE          100          0          4          0        665          0 
FFFFFFFF79FD71C8 FFFFFFFF79FD7228          3 TM       216135          0          3          0        550          0 
000000040E7A3068 000000040E7A30E0          3 TX       655401     322570          6          0        550          0 

Diff Results possible

George Joseph, April 11, 2016 - 8:16 am UTC

Hi

If i were to user an order by dbms_random.value while doing "select for update skip locked" i would get a different set of records than the first time i ran the sp_locktest(2).


create or replace procedure sp_locktest(p_val in number)
as
cursor c 
    is select * 
         from t order by dbms_random.value for update skip locked; /*Note: added the dbms_random.value*/

type tp is table of c%rowtype;

l_tp tp:=tp();

l_val number:=p_val;

begin

open c; /* opening the cursor doesnt lock the table.*/
loop
       /* This fetch should lock as many rows for update as p_val which are available*/
       fetch c bulk collect into l_tp limit l_val; 
       dbms_output.put_line('Number of array objects '||l_tp.count);       
       exit;
end loop;

for i in 1 .. l_tp.count
loop
       dbms_output.put_line('The value of array object lvl at '||i||' is ' || l_tp(i).lvl);
end loop;

end;
/

select * from v$mystat where rownum=1;

       SID STATISTIC#      VALUE
---------- ---------- ----------
         3          0          0 


begin
 sp_locktest(2);
end;
/


Number of array objects 2
The value of array object lvl at 1 is 7
The value of array object lvl at 2 is 9
anonymous block completed

---Note: The code is not committed or rolled back
---The rows which were selected for for update are at 7 and 9.

select * from v$lock where sid=3;
ADDR             KADDR                   SID TYPE        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
0000000411459238 0000000411459290          3 AE          100          0          4          0        529          0 
FFFFFFFF79FD6190 FFFFFFFF79FD61F0          3 TM       216135          0          3          0        414          0 
000000040E7A3068 000000040E7A30E0          3 TX       655401     322570          6          0        414          0 

There are two locks which are held, TM and TX

--Running the code again...
begin
 sp_locktest(2);
end;
/

Number of array objects 2
The value of array object lvl at 1 is 8
The value of array object lvl at 2 is 10

--New locks have not been created although the "rows" which --have been used for update have changed to 8 and 10 from 7 --and 9

select * from v$lock where sid=3;

ADDR             KADDR                   SID TYPE        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
0000000411459238 0000000411459290          3 AE          100          0          4          0        665          0 
FFFFFFFF79FD71C8 FFFFFFFF79FD7228          3 TM       216135          0          3          0        550          0 
000000040E7A3068 000000040E7A30E0          3 TX       655401     322570          6          0        550          0 

update and select for those rows

Nathan, January 31, 2019 - 9:34 am UTC

Hi All,

Can this procedure resolve the multi-user concurrency issue.It is generates a sequence number and update
it to the table and after that it is accessing those rows which are updated by itself.

The table emp is get populated continuously as oltp application and also the after returning the cursor the values
of emp will get updated to stat P (processed)/F (failed) with individual transaction with unique id(empno).

So the question is update happens both request and response time. Is this will cause any problem.

ALTER TABLE emp ADD (
    stat      VARCHAR2(1) DEFAULT 'N',
    batchno   NUMBER
);

CREATE SEQUENCE test_emp_seq;

create or replace    PROCEDURE test_conten (
        cur_emp OUT   SYS_REFCURSOR
    ) IS
        l_batch   NUMBER;
    BEGIN
    --everytime unique
        l_batch := test_emp_seq.nextval;
        --updating the  sequence can be access by particular session  by locking
        UPDATE emp
        SET
            stat = 'Y',
            batchno = l_batch
        WHERE
            ROWNUM <= 3;

--getting the result which is updated

        OPEN cur_emp FOR SELECT
                            *
                        FROM
                            emp
                        WHERE
                            stat = 'N'
                            AND batchno = l_batch;

        NULL;
    END;


Thank you all
Connor McDonald
February 01, 2019 - 3:19 pm UTC

If two sessions try to do:

UPDATE emp
        SET
            stat = 'Y',
            batchno = l_batch
        WHERE
            ROWNUM <= 3;


at the same time, then one will block (no matter what the value for l_batch) until the other commits.

skip skip locked

Racer I., February 04, 2019 - 7:38 am UTC

Hi,

@Nathan : Queue processing is always tricky. Assuming that the batch-id is a worker-chunk id (i.e. each worker would process chunks of size 3 in your example with emp beeing the whole batch to be processed) using AQ would be the simple answer.

Your examples have some strange parts, like updating to state 'Y' but then fetching the state 'N' rows? Also you should update only where batch_id is still null or your workers will steal each others rows.

Our experience so far is that SKIP LOCKED is finicky to the extreme. It was internally developed by Oracle for their AQ-system and only later opened for public use. We feel it never reached general production state probably because Oracle uses it only in exactly the way for which it was implemented which we don't know, so we usually end up misusing it unknowingly.

We switched to hard locking with repeated retries wherever it is necessary. Our main queueing system however doesn't need it to form chunks because our workload comes in many simultaneous types so we can do the chunking per type (after locking on the type) thus getting parallel throughput without meeting on the same rows. Only processing, which as usual happens per chunk (i.e. your batch_id), needs the repeated retry locking (on a "chunk-table") so each worker gets another chunk. But with on average 1000 rows per chunk this is tolerable compared with locking on the original rows.

regards,

Achieve concurrency

A reader, March 22, 2019 - 10:31 am UTC

Hi All,

We are trying to implement concurrent approach but I found that sometimes same records are inserted if we call the procedure parallel. When the record fetched the stat column will be updated to Y in emp table. It is given demo tables actually we have tested for 10000 records and out of that 4000 records fetched by 2 jobs with seconds interval gaps. As the lock happening whenever fetched instead of opening of cursor is reason of the scenario is going wrong.

In another approach (test_prl_process4) if we include rownum in the select query then the first thread fetched some records then second thread could not fetch any record as the rownum is constant to both.


 create table emp_log (empno number,thread number ,logdate date) ;

CREATE OR REPLACE PACKAGE test_pkg1 AS
  TYPE typ_emp_rec IS RECORD(
    empno  NUMBER,
    ename  VARCHAR2(10),
    status VARCHAR2(10));
    
  TYPE typ_emp_tab IS TABLE OF typ_emp_rec;
  
  PROCEDURE test_prl_process3(p_thread IN NUMBER,
                              cur_emp  OUT SYS_REFCURSOR);

END test_pkg1;
/


CREATE OR REPLACE PACKAGE BODY test_pkg1 IS

  PROCEDURE test_prl_process3(p_thread IN NUMBER,
                              cur_emp  OUT SYS_REFCURSOR) IS
  
    l_cnt   NUMBER;
    l_batch NUMBER;
    CURSOR c1 IS
      SELECT empno, ename, stat
        FROM emp1
       WHERE stat = 'N'
         FOR UPDATE SKIP LOCKED;
  
    l_emp_tab typ_emp_tab;
  BEGIN
    OPEN c1;
    --locking the rows
    FETCH c1 BULK COLLECT
      INTO l_emp_tab LIMIT 3;
    CLOSE c1;
  
    --inserting to log table 
    INSERT INTO emp_log
      (empno, thread, logdate)
      SELECT empno, p_thread, SYSDATE FROM TABLE(l_emp_tab);
  
    --returning the cursor
  
    OPEN cur_emp FOR
      SELECT * FROM TABLE(l_emp_tab);
  
  END test_prl_process3;

  PROCEDURE test_prl_process4(p_thread IN NUMBER,
                              cur_emp  OUT SYS_REFCURSOR) IS
  BEGIN
  
    --returning the cursor
    OPEN cur_emp FOR
      SELECT *
        FROM emp1
       WHERE stat = 'N'
         AND ROWNUM <= 3
         FOR UPDATE SKIP LOCKED;
  
  END test_prl_process4;

END test_pkg1;
/


Regards
Connor McDonald
April 04, 2019 - 4:21 am UTC

Not sure what you mean. As long as you don't have spurious commits in there, multiple sessions will see only unlocked rows, eg

--
-- session 1
--
SQL> create table t as
  2  select rownum r, 'X' stat
  3  from dual
  4  connect by level <= 1000;

Table created.

SQL>
SQL> set serverout on
SQL> declare
  2     CURSOR c1 IS
  3        SELECT * from t FOR UPDATE SKIP LOCKED;
  4     type t is table of c1%rowtype;
  5     r t;
  6  begin
  7      OPEN c1;
  8      FETCH c1 BULK COLLECT INTO r LIMIT 50;
  9      CLOSE c1;
 10
 11      dbms_output.put_line(r(1).r);
 12      dbms_output.put_line(r(50).r);
 13  end;
 14  /
1
50

PL/SQL procedure successfully completed.

--
-- session 2
--
SQL> set serverout on
SQL> declare
  2     CURSOR c1 IS
  3        SELECT * from t FOR UPDATE SKIP LOCKED;
  4     type t is table of c1%rowtype;
  5     r t;
  6  begin
  7      OPEN c1;
  8      FETCH c1 BULK COLLECT INTO r LIMIT 50;
  9      CLOSE c1;
 10
 11      dbms_output.put_line(r(1).r);
 12      dbms_output.put_line(r(50).r);
 13  end;
 14  /
51
100


update (select ... for update skip locked) problem

Dusan Valasek, December 01, 2020 - 1:49 pm UTC

I am a bit dissapointed why I cannot use the clause FOR UPDATE SKIP LOCKED in following example:
Oracle version 19c

-- this works:
scott@PDB1> select * from emp a
2 where a.empno in(
3 7369
4 ,7499
5 ,7521
6 ,7566
7 ,7654
8 ,7698)
9 for update skip locked;
6 rows selected.

-- this also works:
scott@PDB1> update (
2 select * from emp a
3 where a.empno in(
4 7369
5 ,7499
6 ,7521
7 ,7566
8 ,7654
9 ,7698)
10 ) a
11 set a.job=job;

6 rows updated.

-- but what is wrong here? :
scott@PDB1> update (
2 select * from emp a
3 where a.empno in(
4 7369
5 ,7499
6 ,7521
7 ,7566
8 ,7654
9 ,7698)
10 for update skip locked
11 ) b
12 set b.job=b.job;
for update skip locked
*
ERROR at line 10:
ORA-00907: missing right parenthesis



Chris Saxon
December 01, 2020 - 5:40 pm UTC

You can't use the for update clause in subquery, as the docs state:

You can specify this clause only in a top-level SELECT statement, not in subqueries.

If you want to update only the unlocked rows, you'll have to split this into steps:

- Open a cursor
- Fetch the unlocked rows
- Run the update

e.g. something along the lines of:

declare
  cursor cur is
    select * from ... 
    for update skip locked;
begin
  open cur;
  loop
    fetch cur bulk collect into ...;
    exit when ...
    
    forall rws in ...
      update t ...
  end loop;
  close cur;
end;
/

nice to have this functionality in bext Oracle version ..

Dusan Valasek, December 02, 2020 - 1:18 pm UTC

I always prefer to write one dml command instead of performing the command in loop, I cannot see any logical reason why this is not possible. So, I hope that this will be possible in the future version of Oracle.
Connor McDonald
December 04, 2020 - 3:25 am UTC

I encourage you to log this as an idea here

https://community.oracle.com/tech/welcome/categories/15571