Deadlock caused by Oracle autonomous transactions

Find the specific trace file based on alert. Note that unlike normal deadlocks, the following Blocker (s) and Waiter (s) are the same sid, and the trace contains the SQL statements being executed and

Find the specific trace file based on alert. Note that unlike normal deadlocks, the following Blocker (s) and Waiter (s) are the same sid, and the trace contains the SQL statements being executed and

1. Oracle interpretation of deadlocks caused by autonomous transactions

Note: 65961.

Because the parent and child transactions are independent, they also are not ableto share any locks; if a parent transaction has a resource locked that a childattemptsto obtain, then a deadlock situation occurs. in this case, the offendingstatement is automatically rolled back with an "ORA-00060: deadlock detected while waiting for resource" exception raised within the child. information on how to troubleshoot a deadlock can be found in:

2. deadlock caused by simulated autonomous transactions

SQL> create table eymit (id int );

Table created

SQL> insert into eymit select 1 from dual;

1 row inserted

SQL> commit;

Commit complete

SQL> delete from eymit;

1 row deleted

SQL>

SQL> declare

2 pragma autonomous_transaction;

3 begin

4 delete from eymit;

5 commit;

6 end;

7/

Declare

Pragma autonomous_transaction;

Begin

Delete from eymit;

Commit;

End;

ORA-00060: deadlock detected while waiting for resources

ORA-06512: In line 5

3. Analyze logs

View Alert file information

Mon Dec 24 18:26:04 2012

ORA-00060: Deadlock detected. More info in file/opt/local/oracle/diag/rdbms/gs3421/gs3421/trace/gs3421_ora_19373.trc

Find the specific trace file based on alert. Note that unlike normal deadlocks, the following Blocker (s) and Waiter (s) are the same sid ,, at the same time, the trace contains the SQL statement being executed and the locked object ID.

[Oracle @ cn01l03082425 trace] $ more gs3421_ora_19373.trc

Trace file/opt/local/oracle/diag/rdbms/gs3421/gs3421/trace/gs3421_ora_19373.trc

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

ORACLE_HOME =/opt/local/oracle/product/11.2.0/dbhome_1

System name: Linux

Node name: cn01l0302.1625

Release: 2.6.18-164. el5

Version: #1 SMP Thu Sep 3 03:28:30 EDT 2009

Machine: x86_64

Instance name: gs3421

Redo thread mounted by this instance: 1

Oracle process number: 46

Unix process pid: 19373, image: oracle @ cn01l03038525

* ** 18:26:04. 290

* ** Session id: (1152.197) 18:26:04. 290

* ** Client id: () 18:26:04. 290

* ** Service name :( gs3421) 18:26:04. 290

* ** Module name: (PL/SQL Developer) 18:26:04. 290

* ** Action name :( Command Window-New) 18:26:04. 290

* ** 18:26:04. 290

Deadlock detected (ORA-00060)

[Transaction Deadlock]

The following deadlock is not an ORACLE error. It is

Deadlock due to user error in the design of an application

Or from issuing incorrect ad-hoc SQL. The following

Information may aid in determining the deadlock:

Deadlock graph:

--------- Blocker (s) -------- --------- Waiter (s )---------

Resource Name process session holds waits

TX-0003001b-0001dd36 46 1152X46 1152 X

Session 1152: DID 0001-002E-0000CA27 session 1152: DID 0001-002E-0000CA27

Rows waited on:

Session 1152: obj-rowid = 000700C5-AABwDFAAEAAAv5cAAA

(Dictionary objn-458949, file-4, block-196188, slot-0)

----- Information for the OTHER waiting sessions -----

----- End of information for the OTHER waiting sessions -----

Information for THIS session:

----- Current SQL Statement for this session (SQL _id = g3mk1njgws241 )-----

DELETE FROM EYMIT

----- PL/SQL Stack -----

----- PL/SQL Call Stack -----

Object line object

Handle number name

0x15960f918 5 anonymous block

========================================================== ==============

PROCESS STATE

4. Solution

The autonomous transaction and the main transaction are two independent tasks. Based on the above information, find the relevant autonomous transaction and submit it in advance.