Tuesday, May 20, 2014

PRAGMA AUTONOMOUS_TRANSACTION

I tested this on Oracle 11.2. If you update a table in SQL*Plus then update it again with a different value in a PL/SQL block, the second update replaces the first:
 
SQL> create table tab1 as
  2  select 1 col1 from dual
  3  /
 
Table created.
 
SQL> update tab1 set col1 = 2
  2  /
 
1 row updated.
 
SQL> begin
  2   update tab1 set col1 = 3;
  3   commit;
  4  end;
  5  /
 
PL/SQL procedure successfully completed.
 
SQL> select col1 from tab1
  2  /
 
      COL1
----------
         3
 
SQL>
 
… but, if you DECLARE the PL/SQL block as a PRAGMA AUTONOMOUS_TRANSACTION, Oracle runs it independently from the underlying session. The PL/SQL block then cannot complete until the underlying SQL has finished. However, the underlying SQL cannot finish until the PL/SQL block has finished. This sets up a deadlock situation, which Oracle resolves after a few seconds. The PL/SQL block then fails with an ORA-00060 and only the first update remains:
 
SQL> create table tab1 as
  2  select 1 col1 from dual
  3  /
 
Table created.
 
SQL> update tab1 set col1 = 2
  2  /
 
1 row updated.
 
SQL> declare
  2   pragma autonomous_transaction;
  3  begin
  4   update tab1 set col1 = 3;
  5   commit;
  6  end;
  7  /
declare
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for
resource
ORA-06512: at line 4
 
SQL> select col1 from tab1
  2  /
 
      COL1
----------
         2
 
SQL>
 

No comments: