Thursday, December 24, 2009

Autonomous SERVERERROR trigger

Last month I posted about the 'disappearing row trick'. In my response on stackoverflow I posted an additional demonstration of the issue using a SERVERERROR trigger. That was flawed because, as I just read in 5th Edition Oracle PL/SQL Programming, the AFTER SERVERERROR trigger fires as an autonomous transaction.

Take the following example:

drop table test_se_auto_tbl;

create table test_se_auto_tbl 
  (id number(2) primary key, val varchar2(20));

create or replace trigger test_se_auto_trg 
after servererror on schema
begin
  dbms_output.put_line('Trigger ran');
  for c_rec in (select id, val from test_se_auto_tbl) loop
      dbms_output.put_line(c_rec.id||':'||c_rec.val);
  end loop;
end;
/

Running a transaction as three separate independent SQL statements shows that, when the third statement fails the SERVERERROR trigger fires but the two successful inserts are not visible (because the trigger is an autonomous transaction).

insert into test_se_auto_tbl values (1,'test ');
insert into test_se_auto_tbl values (10,'test 10');
insert into test_se_auto_tbl values (100,'test 100');

select id, val from test_se_auto_tbl;

rollback;

Revising the example (as I've done on stackoverflow), it is still possible to demonstrate the disappearing row trick however.

create or replace trigger test_se_auto_trg after servererror on schema
begin
  dbms_output.put_line('Trigger ran');
  insert into test_se_auto_tbl values (1,'test in trigger');
end;
/

begin
  insert into test_se_auto_tbl values (1,'test 1');
  insert into test_se_auto_tbl values (10,'test 10');
  insert into test_se_auto_tbl values (100,'test 100');
end;
/

select id, val from test_se_auto_tbl;

In this case the trigger fires and is successfully able to insert the "test in trigger" row. This indicates the insert of the "test 1" row has been rolled back. If you run the three individual statements, the last insert errors but reports a deadlock as the autonomous transaction in the SERVERERROR trigger fails trying to violate the primary key on ID.

truncate table test_se_auto_tbl;
insert into test_se_auto_tbl values (1,'test ');
insert into test_se_auto_tbl values (10,'test 10');
insert into test_se_auto_tbl values (100,'test 100');

The "test" row from the first insert is still there but is not directly visible to the autonomous transaction. It can be detected indirectly by trying to insert the duplicate row. This finds the uncommitted "test" row. Normally it would wait until a blocking transaction is committed or rolled-back and then either fail or allow the insert. But because the original transaction cannot complete until the trigger completes and the trigger can't complete until the original transaction completes, the deadlock situation arises.

2 comments:

Coskan Gundogar said...

Hi Gary,



You said " but the two successful inserts are not visible (because the trigger is an autonomous transaction)."

Is this a typo or am I missing something ?

I got on 11GR1 and 10GR2

SQL> create table test_se_auto_tbl
2 (id number(2) primary key, val varchar2(20));

Table created.

SQL>
SQL> create or replace trigger test_se_auto_trg
2 after servererror on schema
3 begin
4 dbms_output.put_line('Trigger ran');
5 for c_rec in (select id, val from test_se_auto_tbl) loop
6 dbms_output.put_line(c_rec.id||':'||c_rec.val);
7 end loop;
8 end;
9 /

Trigger created.

SQL> insert into test_se_auto_tbl values (1,'test ');

1 row created.

SQL> insert into test_se_auto_tbl values (10,'test 10');

1 row created.

SQL> insert into test_se_auto_tbl values (100,'test 100');
Trigger ran
insert into test_se_auto_tbl values (100,'test 100')
*
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column


SQL>
SQL> select id, val from test_se_auto_tbl;

ID VAL
---------- --------------------
1 test
10 test 10



Two rows are visible ?

Gary Myers said...

I wasn't clear, it should say "not visible to the trigger".
They are visible to the main transaction. However the SERVERERROR trigger runs as a separate transaction. You see it output the "Trigger ran" line, but the loop doesn't see any rows in the table so you don't see a "1:test" or "10:test 10" output line as those rows belong to a separate, uncommitted transaction.