Why row is visible to several sessions when selected FOR UPDATE SKIP LOCKED?

Question

Assume there are two tables TST_SAMPLE (10000 rows) and TST_SAMPLE_STATUS (empty).

I want to iterate over each record in TST_SAMPLE and add exactly one record to TST_SAMPLE_STATUS accordingly.

In a single thread that would be simply this:

begin
  for r in (select * from TST_SAMPLE)
  loop 

    insert into TST_SAMPLE_STATUS(rec_id, rec_status)
    values (r.rec_id, 'TOUCHED');

  end loop;

  commit;
end;
/

In a multithreaded solution there's a situation, which is not clear to me. So could you explain what causes processing one row of TST_SAMPLE several times.

Please, see details below.

create table TST_SAMPLE(
  rec_id       number(10) primary key 
);

create table TST_SAMPLE_STATUS(
  rec_id       number(10),
  rec_status   varchar2(10),
  session_id   varchar2(100)
);


begin
  insert into TST_SAMPLE(rec_id)
  select LEVEL from dual connect by LEVEL <= 10000;

  commit;
end;
/


CREATE OR REPLACE PROCEDURE tst_touch_recs(pi_limit int) is
  v_last_iter_count int;
begin

   loop

     v_last_iter_count := 0;

     --------------------------
     for r in (select *
                 from TST_SAMPLE A
                where rownum < pi_limit
                  and NOT EXISTS (select null
                                    from TST_SAMPLE_STATUS B
                                   where B.rec_id = A.rec_id)
                  FOR UPDATE SKIP LOCKED)
     loop

        insert into TST_SAMPLE_STATUS(rec_id, rec_status, session_id)
        values (r.rec_id, 'TOUCHED', SYS_CONTEXT('USERENV', 'SID'));

        v_last_iter_count := v_last_iter_count + 1;
     end loop;

     commit;
     --------------------------

     exit when v_last_iter_count = 0;

   end loop;
end;
/

In the FOR-LOOP I try to iterate over rows that: - has no status (NOT EXISTS clause) - is not currently locked in another thread (FOR UPDATE SKIP LOCKED)

There's no requirement for the exact amount of rows in an iteration. Here pi_limit is just a maximal size of one batch. The only thing needed is to process each row of TST_SAMPLE in exactly one session.

So let's run this procedure in 3 threads.

declare
 v_job_id number;
begin

  dbms_job.submit(v_job_id, 'begin tst_touch_recs(100); end;', sysdate);
  dbms_job.submit(v_job_id, 'begin tst_touch_recs(100); end;', sysdate);
  dbms_job.submit(v_job_id, 'begin tst_touch_recs(100); end;', sysdate);

  commit;
end;

Unexpectedly, we see that some rows were processed in several sessions

select count(unique rec_id) AS unique_count,
       count(rec_id)        AS total_count
  from TST_SAMPLE_STATUS;


| unique_count | total_count |
------------------------------
|        10000 |       17397 |
------------------------------


-- run to see duplicates
select * 
  from TST_SAMPLE_STATUS 
 where REC_ID in (
                    select REC_ID 
                      from TST_SAMPLE_STATUS
                     group by REC_ID
                    having count(*) > 1
                 )
 order by REC_ID;

Please, help to recognize mistakes in implementation of procedure tst_touch_recs.

Sounds like may you need some sort of queuing system. Take a look at [Oracle's Advanced Queuing](https://docs.oracle.com/cd/E11882_01/server.112/e11013/aq_intro.htm) (it's simpler than it looks! You create a table to hold the queue data, a queue on top of that and then you enqueue and dequeue messages as appropriate and voila!) — Boneist, Feb 26 '19 at 08:47
[It seems](https://asktom.oracle.com/pls/apex/asktom.search?tag=select-for-update-skip-locked) that FOR UPDATE + SKIP LOCKED doesn't lock all records at once but locks each row one after another during a loop so while you go through the cursor loop and locks rows another transaction can lock a row, insert row into TST_SAMPLE_STATUS and release a lock on commit (after the loop). So you can fetch and lock a row which has been processed already by another transaction. So try to fetch and lock rows at once into a table variable and then go through it in the loop. — valex, Feb 26 '19 at 09:24
@valex, in the AskTOM post you've provided there's a phrase "will lock all rows the moment you open the cursor". That's definitely so, because I've tested this case and found several blogs and articles, supporting the point. Nevertheless, thank you, I will try fetching all rows at once. — diziaq, Feb 26 '19 at 10:08
@Boneist, probably the whole AQ environment is extremely huge for my tiny purpose. — diziaq, Feb 26 '19 at 10:10
@diziaq Whole phrase: "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*" — valex, Feb 26 '19 at 10:17
@diziaq you can go about inventing a queue yourself or use Oracle's AQ; it's your choice. I don't think it's as difficult as you might think to set up - see https://oracle-base.com/articles/9i/advanced-queuing-9i — Boneist, Feb 26 '19 at 10:23
@valex, you're right that's what I meant (of course FETCH, not OPEN). But the point remans: all rows are locked together, not one by one. — diziaq, Feb 27 '19 at 03:30

score 3 · Accepted Answer · answered Feb 26 '19 at 17:19

Here's a little example that shows why you're reading rows twice.

Run the following code in two sessions, starting the second a few seconds after the first:

declare
  cursor c is 
    select a.*
     from TST_SAMPLE A
    where rownum < 10
      and NOT EXISTS (select null
                        from TST_SAMPLE_STATUS B
                       where B.rec_id = A.rec_id)
      FOR UPDATE SKIP LOCKED;

  type rec is table of c%rowtype index by pls_integer;
  rws rec;
begin
  open c; -- data are read consistent to this time

  dbms_lock.sleep ( 10 );

  fetch c 
  bulk  collect 
  into  rws;

  for i in 1 .. rws.count loop
    dbms_output.put_line ( rws(i).rec_id );
  end loop;

  commit;

end;
/

You should see both sessions display the same rows.

Why?

Because Oracle Database has statement-level consistency, the result set for both is frozen when you open the cursor.

But when you have SKIP LOCKED, the FOR UPDATE locking only kicks in when you fetch the rows.

So session 1 starts and finds the first 9 rows not in TST_SAMPLE_STATUS. It then waits 10 seconds.

Provided you start session 2 within these 10 seconds, the cursor will look for the same nine rows.

At this point no rows are locked.

Now, here's where it gets interesting.

The sleep in the first session will finish. It'll then fetch the rows, locking them and skipping any that are already locked.

Very shortly after, it'll commit. Releasing the lock.

A few moments later, session 2 comes to read these rows. At this point the rows are not locked!

So there's nothing to skip.

How exactly you solve this depends on what you're trying to do.

Assuming you can't move to a set-based approach, you could make the transactions serializable by adding:

set transaction isolation level serializable;

before the cursor loop. This will then move to transaction-level consistency. Enabling the database to detect "something changed" when fetching rows.

But you'll need to catch ORA-08177: can't serialize access for this transaction errors in your within the outer loop. Or any process that re-reads the same rows will drop out at this point.

Or, as commenters have suggested used Advanced Queueing.

Why row is visible to several sessions when selected FOR UPDATE SKIP LOCKED?

1 Answers1