For TM locks the first id value is the OBJECT_ID from DBA_OBJECTS. In this case the deadlock involves objects 12293 and 12301. Session 74 has a lock on a row in object 12293 and wants one on a row in object 12301. But first, it must get a lock on the table in SX mode, and is blocked by the conflicting table lock held in S mode by session 15. The "no row" reflects that it is waiting for a table lock, not a row lock. At the same time, session 15 wants to convert its SX lock on object 12293 to SSX mode and is blocked by the SX lock held by session 74 in association with the row lock that it holds in that table.

The question is, If session 74 is just doing inserts and deletes, why does it want shared mode locks on these tables? The normal answer is that unless the application is attempting explicit locking, then it is due to a missing foreign key index on one of these tables or a related table. If so, you should be able to identify the problem with the APT script missing_fk_indexes.sql on the Ixora web site.

I am encountering deadlocks on inserts and deletes with no rows waited. A portion of the user dump trc file:

Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TM-0000300d-00000000        23      15     S             31      74          SX
TM-00003005-00000000        31      74    SX             23      15    SX   SSX
session 15: DID 0001-0017-00000002      session 74: DID 0001-001F-00000002
session 74: DID 0001-001F-00000002      session 15: DID 0001-0017-00000002
Rows waited on:
Session 74: no row
Session 15: no row