How to find out the cause of an Oracle deadlock

Question

When testing my application with multiple users, I have encountered an ORA-00060 deadlock error. I'm not sure how to determine the cause of this error, so if anyone could help me it would be greatly appreciated.

I looked in the trace file generated for this event and it shows me which query caused this:

UPDATE TABLE_A SET CK=CK+1 WHERE A_ID=(
    SELECT A_ID FROM TABLE_B WHERE SOME_COLUMN=:SOMECOLUMN
)

It also generated this:

[Transaction Deadlock]

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
TM-0002a675-00000000        51     200    SX   SSX       52      22    SX   SSX
TM-0002a675-00000000        52      22    SX   SSX       51     200    SX   SSX

session 200: DID 0001-0033-0000014A session 22: DID 0001-0034-0000005A 
session 22: DID 0001-0034-0000005A  session 200: DID 0001-0033-0000014A 

Rows waited on:
  Session 200: obj - rowid = 0002A6B1 - AAAqaxAAUAAAAFTAAA
  (dictionary objn - 173745, file - 20, block - 339, slot - 0)
  Session 22: obj - rowid = 0002A6B1 - AAAqaxAAUAAAAFTAAA
  (dictionary objn - 173745, file - 20, block - 339, slot - 0)

How do I use the above information to find out what caused the problem? There aren't any unindexed foreign keys on either TABLE_A or TABLE_B (which I believe is commonly the cause of this type of error).

When I run this query:

SELECT OWNER, OBJECT_NAME, OBJECT_TYPE 
FROM DBA_OBJECTS 
WHERE OBJECT_ID = 173745;

I get the name of a primary key on a completely different table! Could this be a clue as to why the deadlock is happening?

Answer 1

Because the enqueue involved is a 'TM' enqueue, and because the mode the lock is being held is 'SX' and the mode the lock is waited on is 'SSX', I can tell you, with absolute certainty, that this is due to a foreign key relationship, where the referring table (child table) column is not indexed. If you look for unindexed foreign keys and add indexes, this problem should disappear.

Hope that helps....

P.S. My presentation, "Understanding and Interpreting Deadlocks, or, What to do with an ORA-00060", is available for download at the OakTable site, http://www.oaktable.net. Look in the "Contributed Files" section.