How, specifically, can I find out the cause of deadlock from a trace file?

From: theRat <john_hilgedick_at_yahoo.com>
Date: 16 May 2006 08:35:58 -0700
Message-ID: <1147793758.524661.203480@j33g2000cwa.googlegroups.com>

Hello all,

my multi-threaded application is having deadlock problems. Oracle is creating trace files for me whenever this happens. I can see from the trace file what SQL each thread is executing at the time the deadlock occurs - but this isn't enough information for me to actually figure out why the deadlock is occurring.

Is there a resource out there that can tell me how to interpret these files. In particular, each trace file shows a "deadlock graph". Here's mine:

Deadlock graph:

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


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

Resource Name          process session holds waits  process session
holds waits
TX-00500015-00000434       112      13           X       93      98
      S
TX-00630018-00000357        93      98     X             45      39
      S
TX-00500015-00000434        45      39     X            112      13
      X
session 13: DID 0001-0070-00004B10      session 98: DID
0001-005D-00007059
session 98: DID 0001-005D-00007059      session 39: DID
0001-002D-0000C4BA
session 39: DID 0001-002D-0000C4BA      session 13: DID

0001-0070-00004B10
Rows waited on:

Session 98: no row
Session 39: no row
Session 13: obj - rowid = 00008DB5 - AAAI21AANAAAF+SAAS

  (dictionary objn - 36277, file - 13, block - 24466, slot - 18)

Is there a way for me to find out what these "resources" are? From the above, I can see that session 98 is waiting for session 13, and session 13 is waiting on session 39, and session 39 is waiting on session 98 - but I can't figure out which resources are involved... How can I determine this?

If anyone knows the answer or can point me to a document, I'd really appreciated it.

Thanks!

-john
Received on Tue May 16 2006 - 10:35:58 CDT