| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| 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
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:
---------Blocker(s)--------
---------Waiter(s)---------
Resource Name process session holds waits process session holds
waits
TM-0000457a-00000000 24 37 SX 17 14
SSX
TM-00004571-00000000 17 14 SSX 24 37
SX
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:
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?
Branimir
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Branimir Petrovic INET: BranimirP_at_cpas.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-LReceived on Mon Nov 03 2003 - 15:39:28 CST
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
 |
 |