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

From: Rajesh Dayal <rajesh_at_iitcoman.com>
Date: Mon, 03 Nov 2003 21:34:25 -0800
Message-ID: <F001.005D57B6.20031103213425@fatcity.com>

Hi Branimir,

Two Suggestions:

This looks like having an issue with foreign key indexes. You can check for foreign keys and their indexes for tables having objects-id 17786 (457a)and 17775 (4571). If any of the columns of these two tables have foreign key relationship with other tables then probably you need to create one index on that column.
Another suggestion is to check initrans/maxtrans and PCTFREE parameter. If these are very low and number of concurrent updates are higher then you need to increase these values. I know you are not considering this as an option, but this might help in this case. In fact you can't change these parameter for existing data blocks. So you can re-load your data with these parameters in effect.

     Of course we have Gurus here who can add more inputs to this and correct me as well if found 
     to be wrong.

HTH Rajesh Dayal
Senior Oracle DBA (OCP 8,8i,9i)
International Information Technology Company LLC

-----Original Message-----

Sent:	Tuesday, November 04, 2003 1:39 AM
To:	Multiple recipients of list ORACLE-L
Subject:	Fwd: [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:
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?

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-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).

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Rajesh Dayal
INET: rajesh_at_iitcoman.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------