Saturday, December 15, 2007

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.

No comments:

Post a Comment