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.