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>