Lite serialisation in 11gR2 with WAIT_ON_PENDING_DML

Back in September I posted about some new 11gR2 features.
One I fancied was WAIT_ON_PENDING_DML. Chet has already posted about one aspect of it on Oraclenerd, but this walkthough will be a bit different.

It caters for the scenario when,  every hour (or day) you want to look at rows inserted or updated since you last checked.The trick is to avoid missing entries that have been inserted before you start but not committed.

Firstly, create the necessasary tables
drop table test purge;
drop sequence test_seq;
create table test 
  (id number, val varchar2(40), created_on date, insert_sid number);
create sequence test_seq;

Now the first procedure. This simulates a user starting a transaction, waiting a few seconds (eg trying to upsell the customer with a 'Do you want fries with that') before committing.

create or replace procedure test_users (p_num in number)is
begin
  for i in 1..20 loop
    insert into test values 
      (test_seq.nextval,  to_char(sysdate,'DD/Mon/YYYY hh24:mi:ss'), 
       sysdate, sys_context('USERENV','SID'));
    dbms_lock.sleep(seconds => p_num);
    commit;
  end loop;
end test_users;
/

Now the complex demonstration procedure.
This starts up and reports on pending transactions on the table. Then it waits until all those transactions have committed (using the new routine). it finishes by reporting what it saw in the table and the outstanding transactions.

create or replace procedure test_sum is
  v_date date;
  v_flag boolean;
  v_num  number;
  v_fm   varchar2(30) := 'DD/Mon/YYYY hh24:mi:ss';
  cursor c_1 is
    select val, insert_sid from test 
    where created_on <= v_date 
    order by insert_sid, created_on ;

begin
  v_date := sysdate;
  dbms_output.put_line('Transactions at start');
  for i in (select start_time from v$transaction) loop
      dbms_output.put_line(i.start_time);
  end loop;
  dbms_output.put_line(rpad('Wait on pending at',32)||to_char(sysdate,v_fm));
  v_flag := DBMS_UTILITY.wait_on_pending_dml(
          tables => 'TEST',timeout => 6000, scn => v_num);
  dbms_output.put_line(rpad('Wait completed on pending at',32)
                                 ||to_char(sysdate,v_fm));
  IF v_flag THEN
    dbms_output.put_line('Transactions after wait');
    for i in (select start_time from v$transaction) loop
      dbms_output.put_line(i.start_time);
    end loop;
    dbms_output.put_line('Inserts after after wait');
    for i in c_1 loop
      dbms_output.put_line(rpad(i.insert_sid,5)||i.val);
    end loop;
  else
    dbms_output.put_line('Timeout');
  end if;
  dbms_output.put_line('Finished at '||to_char(sysdate,v_fm));
end test_sum;
/


I set off a simulation of three users hitting the table. They all work at different speeds, just so they are not always in sync when committing (to be more realistic).

declare
  v_num number;
begin
  dbms_job.submit(v_num, 'begin test_users(7); end;');
  dbms_job.submit(v_num, 'begin test_users(11); end;');
  dbms_job.submit(v_num, 'begin test_users(13); end;');
  commit;
end;
/

I then run the test_sum procedure and see the results:

Transactions at start
01/11/10 17:51:10
01/11/10 17:51:09
01/11/10 17:51:14
Wait on pending at              11/Jan/2010 17:51:15
Wait completed on pending at    11/Jan/2010 17:51:27
Transactions after wait
01/11/10 17:51:21
01/11/10 17:51:23
Inserts after after wait
36   11/Jan/2010 17:50:48
36   11/Jan/2010 17:51:01
36   11/Jan/2010 17:51:14
52   11/Jan/2010 17:50:48
52   11/Jan/2010 17:50:59
52   11/Jan/2010 17:51:10
53   11/Jan/2010 17:50:48
53   11/Jan/2010 17:50:55
53   11/Jan/2010 17:51:02
53   11/Jan/2010 17:51:09
Finished at 11/Jan/2010 17:51:27

When it started waiting at 17:51:15 there were three transactions in progress with the last starting just a second earlier. Twelve seconds later that transaction commits and the wait ends. We see that by now there are two new transactions so there hasn't been any exclusive lock preventing our other users from working. Session 53, firing every seven seconds, would have started a transaction at 17:51:16 and committed it at 17:51:23 before starting that transaction.

In my example I set the 'date/time' of interest before the wait_on_pending_dml call. Then, after it has returned, I only pick out transactions dated on or prior to that date. Since, in this example, the created date/time is sysdate I could store this date in a table and run the job on a schedule to pick out records added between the two runs. The date logic would ensure I only see each record once and the wait_on_pending_dml means that I would never miss a record because it has been inserted but not committed.

I can do all that without taking a lock on the table or forcing any serialization or additional waiting onto my user sessions. This was all possible before 11gR2, with repeated checks on v$transaction. This just makes things a lot simpler.

It should also be possible with sequence numbers, rather than date/timestamps, but you'd have to be careful to avoid caching of the sequence number in either RAC (where nodes may have their own cache and so use sequences out of order) or in the application layer.