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>

Deadlocks

This was tested in Oracle 11.2.

Imagine you have two sessions running concurrently called Session A and Session B. Session A cannot continue because Session B has something it needs. Session B needs something which Session A is using so it cannot do anything either. This is called a deadlock.

This is not really an Oracle problem. It is caused by poor application design.

Oracle detects deadlocks and issues an ORA-00060 to one of the sessions. It then cancels an SQL statement to allow the others to finish.

You should design your applications in such a way that the chances of having a deadlock are minimised. If you do get a deadlock, your application should detect it and repeat the failing statement. If this is not possible, the application must fail tidily.

The table below has two columns. The left hand column shows SQL*Plus Session A and the right hand column shows SQL*Plus Session B. Both sessions are updating the same rows in the EMP table at the same time. This is what I meant by poor application design. Session A is updating salaries and Session B is updating managers. The updates produce a deadlock. Oracle detects it in Session A. After both sessions have committed their changes, you can see that the only update which has been lost is the one directly affected by the deadlock:  

Session A SQL> show user USER is "ANDREW" SQL> select * from emp; ID    SALARY MANAGER --- -------- ------- E01  1000.00 M01A E02  2000.00 M02A SQL> update emp   2  set salary = salary * 1.1   3  where id = 'E01'; 1 row updated. SQL> select * from emp; ID    SALARY MANAGER --- -------- ------- E01  1100.00 M01A E02  2000.00 M02A SQL> Go to session B -> SQL> select * from emp; ID    SALARY MANAGER --- -------- ------- E01  1100.00 M01A E02  2000.00 M02A SQL> update emp   2  set salary = salary * 1.1   3  where id = 'E02'; This session is now locked as Session B has not finished updating employee E02. Go to session B -> The deadlock is detected and the 2nd update fails: update emp        * ERROR at line 1: ORA-00060: deadlock detected while waiting for resource SQL> Go to session B -> SQL> commit; Commit complete. SQL> Go to session B -> SQL> select * from emp; ID    SALARY MANAGER --- -------- ------- E01  1100.00 M01B E02  2000.00 M02B SQL>

Session B SQL> show user USER is "ANDREW" SQL> select * from emp; ID    SALARY MANAGER --- -------- ------- E01  1000.00 M01A E02  2000.00 M02A SQL> update emp   2  set manager = 'M02B'   3  where id = 'E02'; 1 row updated. SQL> select * from emp; ID    SALARY MANAGER --- -------- ------- E01  1000.00 M01A E02  2000.00 M02B SQL> <- Go to session A. SQL> select * from emp; ID    SALARY MANAGER --- -------- ------- E01  1000.00 M01A E02  2000.00 M02B SQL> update emp   2  set manager = 'M01B'   3  where id = 'E01'; This sets up the deadlock as the sessions are now waiting on each other. <- Go to session A. This session is still locked. <- Go to session A. Now that Session A has committed, Session B can do its update. 1 row updated. SQL> commit; Commit complete. SQL> select * from emp; ID    SALARY MANAGER --- -------- ------- E01  1100.00 M01B E02  2000.00 M02B SQL> <- Go to session A.