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.