When we ran one of our applications we are getting deadlocks.
I know that UnIndexed Foreign Keys can cause Deadlocks. But i don't know how to analyse the trace file and find out which table's Foreign keys should be indexed. Below is an excerpt from the trace file.
Any inputs?
*** SESSION ID:(563.48001) 2007-11-21 05:22:48.647
DEADLOCK DETECTED
Current SQL statement for this session:
SELECT ITEM_DTL.CURR_WT,
ITEM_DTL.CURR_VOL,
ITEM_DTL.CURR_UOM_QTY
FROM ITEM_DTL WHERE
( ITEM_DTL.LOCN_ID = :1 ) FOR UPDATE WAIT 10
The following deadlock is not an ORACLE error. It is a
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 process session holds waits
TX-0018002a-0002cd6d 403 563 X 377 121 X
TX-00090029-0007961c 377 121 X 403 563 X
session 563: DID 0001-0193-0000056C session 121: DID 0001-0179-000021AA
session 121: DID 0001-0179-000021AA session 563: DID 0001-0193-0000056C
Rows waited on:
Session 121: obj - rowid = 00092808 - AAFSALAAqAAAUP1AAJ
(dictionary objn - 600072, file - 42, block - 82933, slot - 9)
Session 563: obj - rowid = 00092BE9 - AAFR0hAApAAAK8lAA3
(dictionary objn - 601065, file - 41, block - 44837, slot - 55)
Information on the OTHER waiting sessions:
Session 121:
pid=377 serial=377 audsid=21194213 user: 33/WMPA1
O/S info: user: wmpa1, term: , ospid: 5046314, machine: LRES