Oracle Deadlock

oracle

This is the Deadlock graph, Not able to infer what made deadlock in this!!
Deadlock graph:

                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TM-00046b52-00000000       248       9           S      286    1216          SX
TM-00046b52-00000000       286    1216          SX      220     835           S
TM-00046c4d-00000000       220     835    SX            172     803           S
TM-00046b52-00000000       172     803    SX            248       9           S


session 9: DID 0001-00F8-0000022F   session 1216: DID 0001-011E-000000F6 
session 1216: DID 0001-011E-000000F6    session 835: DID 0001-00DC-00000BE3 
session 835: DID 0001-00DC-00000BE3 session 803: DID 0001-00AC-00000D60 
session 803: DID 0001-00AC-00000D60 session 9: DID 0001-00F8-0000022F

Rows waited on:

  Session 9: obj - rowid = 00046B52 - AAAAAAAAAAAAAAAAAA
  (dictionary objn - 289618, file - 0, block - 0, slot - 0)
  Session 1216: obj - rowid = 00046B52 - AAAAAAAAAAAAAAAAAA
  (dictionary objn - 289618, file - 0, block - 0, slot - 0)
  Session 835: obj - rowid = 00046B52 - AAAAAAAAAAAAAAAAAA
  (dictionary objn - 289618, file - 0, block - 0, slot - 0)
  Session 803: obj - rowid = 00046C4D - AAAAAAAAAAAAAAAAAA
  (dictionary objn - 289869, file - 0, block - 0, slot - 0)

Best Answer

From:

TM-00046b52-00000000
TM-00046c4d-00000000

These are the objects involved in this:

SQL> select to_number('46b52', 'xxxxx') from dual;

TO_NUMBER('46B52','XXXXX')
--------------------------
                    289618

SQL> select to_number('46c4d', 'xxxxx') from dual;

TO_NUMBER('46C4D','XXXXX')
--------------------------
                    289869

And these objects are:

select owner, object_name, object_type from dba_objects where object_id in (289618, 289869);

This is just a far guess, but a very typical reason for TM deadlocks is that you may have unindexed foreign keys.

Find them as: How to find foreign keys that don't have corresponding indexes?

Related Question