Deadlock query - no rows waited on

From: Andy Hardy <aph_at_ahardy.demon.co.uk>
Date: Thu, 7 Sep 2000 23:03:55 +0100
Message-ID: <PgSRqvALDBu5Ewfw@ahardy.demon.co.uk>

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

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?

Any thoughts?

Andy

-- 
Andy Hardy. PGP ID: 0xA62A4849
===============================================================

Received on Thu Sep 07 2000 - 17:03:55 CDT