Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> [ORA-000060: Deadlock detected] Finding BOTH pieces of code that

[ORA-000060: Deadlock detected] Finding BOTH pieces of code that

From: Branimir Petrovic <>
Date: Mon, 03 Nov 2003 13:39:28 -0800
Message-ID: <>

I realize ORACLE-L could not be the best place to ask this question but
(googling/metalink-ing did not help overly)...

ORA-000060 happens in our own application in the worst of possible places - at the customer's site. It happens intermittently. All I can do is look at the alert log for errors and follow the trail to the extremely verbose trace dumps and wonder. Clearly - I am over my head here as I have very little clue of what am I actually looking at.

The question is how to interpret what trace dumps tries to tell (should I be scratching my head with it or is there some kind of magic behind TAR that would save the day)?

For instance - what is the meaning of this trace snippet (coming from 8.1.7.x):

Deadlock graph:

Resource Name          process session holds waits  process session holds
TM-0000457a-00000000        24      37    SX             17      14
TM-00004571-00000000        17      14   SSX             24      37
session 37: DID 0001-0018-00000002	session 14: DID 0001-0011-00000002
session 14: DID 0001-0011-00000002	session 37: DID 0001-0018-00000002
Rows waited on:
Session 14: no row
Session 37: no row

On the assumption that the source of the above problem has nothing to do with INITTRANS/PCTFREE combo (as per metalink thread # 247579.999) but the culprit really is the broken app - what would be the 'proper' way to find which two pieces of code deadlocked each other?

What comes to my mind ain't a pretty sight - turning on sql tracing on the database level (and suffering worsened performance), then once error happens finding out exact time from alert log, converting it to matching TIC number in 3 sec range (if I understand correctly Oracle will detect and break deadlock
after 3 seconds). Once this range is known for 3 sec. window, digging through
session traces would reveal what were other sessions doing at the time may be
narrowing down the search to few possible sources of contention.

Problem with this approach is way too much work to set up then 'dig' through

all (tons of) trace files without any real guarantees source of deadlocking would be obvious after all this effort. So the question is - how to help duhveloper(s) by pointing closer to the 'root' of deadlocking?


Please see the official ORACLE-L FAQ:
Author: Branimir Petrovic

Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services
To REMOVE yourself from this mailing list, send an E-Mail message
to: (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L

(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Mon Nov 03 2003 - 15:39:28 CST

Original text of this message