SQL> 
SQL> -- A simple example of how deadlock can occurr with AUTONOMOUS_TRANSACTION
SQL> 
SQL> Create or replace procedure proc2_a (p1 VARCHAR2) as
  2        PRAGMA AUTONOMOUS_TRANSACTION;
  3  BEGIN
  4        update test2 set c2=0 where c1=p1;
  5  END;
  6  /

Procedure created.

SQL> 
SQL> create or replace procedure proc1_a(p1 varchar2)
  2  as
  3  begin
  4    update test2 set c2=9 where c1=p1;
  5    proc2_a(p1);
  6  end;
  7  /

Procedure created.

SQL> 
SQL> -- ORA-00060: deadlock detected while waiting for resource
SQL> exec proc1_a('b');
BEGIN proc1_a('b'); END;

*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource 
ORA-06512: at "JYANG.PROC2_A", line 4 
ORA-06512: at "JYANG.PROC1_A", line 5 
ORA-06512: at line 1 


SQL> 
SQL> Create or replace procedure proc2_a (p1 VARCHAR2) as
  2        PRAGMA AUTONOMOUS_TRANSACTION;
  3  BEGIN
  4        update test2 set c2=0 where c1=p1;
  5        commit;
  6  END;
  7  /

Procedure created.

SQL> 
SQL> -- Avoid deadlock by using 'commit' before AUTONOMOUS_TRANSACTION routine
SQL> create or replace procedure proc1_a(p1 varchar2)
  2  as
  3  begin
  4    update test2 set c2=9 where c1=p1;
  5    commit;
  6    proc2_a(p1);
  7  end;
  8  /

Procedure created.

SQL> 
SQL> select * from test2 where c1='b';

C1         C2                                                                   
-- ----------                                                                   
b           0                                                                   

SQL> exec proc1_a('b');

PL/SQL procedure successfully completed.

SQL> select * from test2 where c1='b';

C1         C2                                                                   
-- ----------                                                                   
b           0                                                                   

SQL> 
SQL> spool off