Parallellism in a skip locked scenario

During the Oracle 11g for developers session, more specifically when I was talking about the FOR UPDATE SKIP LOCKED, I received a question about the possibility of implementing parallellism if a batch program used the FOR UPDATE SKIP LOCKED strategy. Would it work to just kick off N processes simultaneously without altering the code?

Normally you would have to group the workload somehow - probably using the NTILE analytic function - and assign a piece of the workload to each of the slaves. But using the FOR UPDATE SKIP LOCKED, if one of the processes locks some rows, the other processes would skip those rows, and lock some others, and so on. Interesting question and I didn't know the answer right away, so I just tested it.

Let's create a table with a processed flag. This one is necessary in a skip locked scenario as you need to be able to track down which rows were processed and which ones were not. You can handle the ones that were skipped next day or next week.

rwijk@ORA11G> create table parallel_skiplocked_test
  2  (id, procesnumber, processed_flag, amount, processed_timestamp)
  3  as
  4   select level
  5        , cast(null as integer)
  6        , 'N'
  7        , cast(null as number(9))
  8        , cast(null as timestamp)
  9     from dual
 10  connect by level <= 100000
 11  /

Tabel is aangemaakt.

rwijk@ORA11G> alter table parallel_skiplocked_test add constraint pst_pk primary key (id)
  2  /

Tabel is gewijzigd.

rwijk@ORA11G> exec dbms_stats.gather_table_stats(user,'parallel_skiplocked_test',cascade=>true)

PL/SQL-procedure is geslaagd.

And create a procedure that processes all rows using the FOR UPDATE SKIP LOCKED clause:

rwijk@ORA11G> create procedure process_all(p_procesnumber in integer)
  2  is
  3    cursor c_pst
  4    is
  5    select id
  6         , amount
  7      from parallel_skiplocked_test
  8     where processed_flag = 'N'
  9           for update skip locked
 10    ;
 11    type tr_pst is record
 12    ( id     parallel_skiplocked_test.id%type
 13    , amount parallel_skiplocked_test.amount%type
 14    );
 15    type t_pst is table of tr_pst;
 16    r_pst t_pst;
 17  begin
 18    open c_pst;
 19    loop
 20      fetch c_pst bulk collect into r_pst limit 100
 21      ;
 22      for i in 1..r_pst.count
 23      loop
 24        r_pst(i).amount := trunc(mod(10000 * i * dbms_random.value,10000));
 25      end loop
 26      ;
 27      forall i in 1..r_pst.count
 28        update parallel_skiplocked_test
 29           set procesnumber = p_procesnumber
 30             , amount = r_pst(i).amount
 31             , processed_flag = 'Y'
 32             , processed_timestamp = systimestamp
 33         where id = r_pst(i).id
 34      ;
 35      exit when c_pst%notfound
 36      ;
 37    end loop;
 38    close c_pst;
 39  end process_all;
 40  /

Procedure is aangemaakt.

Now let's run the procedure in a single process using dbms_job:

rwijk@ORA11G> declare
  2    l_job_id1 binary_integer;
  3  begin
  4    dbms_job.submit(l_job_id1,'process_all(1);');
  5  end;
  6  /

PL/SQL-procedure is geslaagd.

rwijk@ORA11G> commit
  2  /

Commit is voltooid.

This is the result:

rwijk@ORA11G> select procesnumber
  2       , count(*)
  3       , min(processed_timestamp) min_timestamp
  4       , max(processed_timestamp) max_timestamp
  5    from parallel_skiplocked_test
  6   group by procesnumber
  7  /

PROCESNUMBER   COUNT(*) MIN_TIMESTAMP             MAX_TIMESTAMP
------------ ---------- ------------------------- -------------------------
           1     100000 15-12-07 01:04:20,734000  15-12-07 01:04:30,375000

1 rij is geselecteerd.

It takes 10 seconds.
Now let's try with 4 processes simultaneously, after recreating the entire scenario the same way as above:

rwijk@ORA11G> declare
  2    l_job_id1 binary_integer;
  3    l_job_id2 binary_integer;
  4    l_job_id3 binary_integer;
  5    l_job_id4 binary_integer;
  6  begin
  7    dbms_job.submit(l_job_id1,'process_all(1);');
  8    dbms_job.submit(l_job_id2,'process_all(2);');
  9    dbms_job.submit(l_job_id3,'process_all(3);');
 10    dbms_job.submit(l_job_id4,'process_all(4);');
 11  end;
 12  /

PL/SQL-procedure is geslaagd.

rwijk@ORA11G> commit
  2  /

Commit is voltooid.

rwijk@ORA11G> pause

rwijk@ORA11G> select procesnumber
  2       , count(*)
  3       , min(processed_timestamp) min_timestamp
  4       , max(processed_timestamp) max_timestamp
  5    from parallel_skiplocked_test
  6   group by procesnumber
  7  /

PROCESNUMBER   COUNT(*) MIN_TIMESTAMP             MAX_TIMESTAMP
------------ ---------- ------------------------- -------------------------
           1      23555 15-12-07 01:05:05,734000  15-12-07 01:05:16,656000
           2      25445 15-12-07 01:05:05,953000  15-12-07 01:05:17,703000
           4      26400 15-12-07 01:05:05,968000  15-12-07 01:05:16,656000
           3      24600 15-12-07 01:05:05,796000  15-12-07 01:05:16,640000

4 rijen zijn geselecteerd.

And look: it works! All 4 processes did approximately 25,000 rows. However, instead of 1 time 10 seconds, you now consumed 4 times 11 or 12 seconds. Likely because all four processes are hitting the same resources at the same time, causing contention. So it is of no practical use, but still fun to know. And thanks to Tijmen for the interesting question.