That old restart problem again...

Not something entirely learned new today - but rather a revisited "something I learned" coupled with an "I didn't necessarily expect it in this case".

It is the old "statement restart" and "evil triggers" issue. I had an asktom question asking why a row seemed to be getting deleted twice. You should read that link, I'll be referring to it here.

I immediately knew what the issue was (I was pretty sure). Before I even read the test case - I had already pointed them to three articles I wrote on the subject a while ago...

But when I looked at their test case and modified it slightly to be smaller and easier to read - I was frankly surprised at what was happening - but ok with the behavior.

It takes a series of bad things to happen for this issue the poster was seeing to have happen, you have to

a) use a trigger (evil)
b) do something non-transactional in that trigger - for example modify a package global variable
c) do slow by slow processing (if they did a big mass operation - they could still see a restart, but it would be less likely perhaps)

Here is a snippet of the code again:

ops$tkyte%ORA11GR2> create or replace PACKAGE pacepack
2  as
3      type array is table of number index by varchar2(40);
4      g_data array;
5      g_cnt  number;
6  end pacepack;
7  /

Package created.

That package just has some global variables (evil global variables - evil evil - they didn't use globals in their API, they had getter/setter functions - but they behaved like globals as well). We'll create a row trigger to save the rowids of the rows our trigger has processed - even if Oracle rolls back our firing statement - modifications we've made in our trigger to the globals won't rollback.

Now, we'll code the trigger:

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create table delete_data
2  as
3  select owner, object_name
4  from dba_objects
5  where rownum  < 53001;

Table created.




ops$tkyte%ORA11GR2> create or replace trigger delete_data_bt
2  before delete or update on delete_data
3  for each row
4  begin
5      if ( pacepack.g_data.exists(rowidtochar(:old.rowid)) )
6      then
7          dbms_output.put_line( 'doing "' || :old.rowid ||
                   '" again was called ' || pacepack.g_cnt );
8      else
9          pacepack.g_data(rowidtochar(:old.rowid)) := 1;
10      end if;
11      pacepack.g_cnt := pacepack.g_cnt + 1;
12  end;
13  /

Trigger created.

The trigger checks to see if the PLSQL global table variable has the rowid already entered in it - if so, it says "I already did this one", else it sets a flag showing it processed that one for the first time. We also increment a count (you know, to count the rows we've deleted or updated).

Now we process the data:

ops$tkyte%ORA11GR2> declare
2      CURSOR L_DELETE_CSR IS
3      SELECT *
4        FROM delete_data
5         for update ;
6      l_cnt number := 0;
7  BEGIN
8       pacepack.g_data.delete;
9       pacepack.g_cnt := 0;
10      for l_delete_row in l_delete_csr
11      loop
12            update delete_data
13             set owner = lower(owner)
14           where current of L_delete_CSR;
15          l_cnt := l_cnt + 1;
16      end loop;
17      dbms_output.put_line( 'trigger count = ' || pacepack.g_cnt ||
                     ' local count = ' || l_cnt );
18  END;
19  /
doing "AAAYMdAAEAABFrnABH" again was called 8827
doing "AAAYMdAAEAABFwiAAx" again was called 20140
doing "AAAYMdAAEAABFyeACk" again was called 31405
doing "AAAYMdAAEAABFzaABE" again was called 42670
trigger count = 53004 local count = 53000

PL/SQL procedure successfully completed.

As you can see - our trigger was fired 53,004 times - for 53,000 rows. Our counts do not match and we can see the four updates that were restarted.

Why they were restarted - not really relevant - the fact is - they CAN be restarted, we can show that they can be restarted, it is documented that they can be. Any statement can be restarted - we do a savepoint before executing the statement and if we deem necessary (which we did obviously), we rollback to that savepoint and do it over.

So, expect the restart - and stop using triggers. If you do use triggers - be very very very careful to make sure they are not subject to issues with restarts (eg: NO autonomous transactions - NO modifications of global variables - NO API calls that have side effects that don't roll back (setter functions for example)!