From:Steve Adams
Date:08-Sep-2000 11:59
Subject:   Why "rows waited on: no row" in this deadlock?

Note that the TM enqueue names are different. The other session (34) is the culprit. It has taken a shared lock on HANGER_CONTENTS and is waiting for a shared lock on object_id 1260. This session (69) already has a row lock on that object and now wants one on HANGER_CONTENTS. The trace shows that session 69 is not waiting for a particular row because although it wants a particular row, it is not blocked by a row lock.

To make progress here you will need to find object 1260 in DBA_OBJECTS, determine the relationship between that object and HANGER_CONTENTS (if any), and work out why session 34 would need shared locks on both objects concurrently. If this is happening repeated, you could disable table locks on HANGER_CONTENTS to make the culprit get an error when it attempts to take the shared lock. That should help you to work out where the problem is coming from!

I have the following deadlock appearing in a trace file: 

*** SESSION ID:(69.36004) 2000.08.10.22.29.01.203

DEADLOCK DETECTED

Current SQL statement for this session:
DELETE FROM HANGER_CONTENTS WHERE HANGER_ID = :b1
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
 c7f1204      1224  package body CLKOWNER.MATERIAL_ROUTING
 c632e9c         1  anonymous block
 c632e9c         1  anonymous block

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-000004ec-00000000        17      69    SX             12      34           S
TM-000004e7-00000000        12      34     S             17      69          SX

Rows waited on:
Session 34: no row
Session 69: no row
My understanding is that SX is a row exclusive lock, and S a share lock. The resource names are prefixed by TM, which I understand to be a DML enqueue?

I'm confused as to what is going on, primarily because of the "Rows waited on: no row" statement.

I suspect that this means that during the deletion some foreign tables are being referred to, that there are indexes missing from the foreign keys and so all rows in the child tables are being locked? In this case, I'm assuming child tables of the HANGER_CONTENTS table?