Re: Deadlock query - no rows waited on

From: Steve Adams <steve.adams_at_ixora.com.au>
Date: Fri, 08 Sep 2000 00:55:29 GMT
Message-ID: <39b833d5.331963728@nsw.nnrp.telstra.net>

Hi Andy,

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!

@ Regards,
@ Steve Adams
@ http://www.ixora.com.au/
@ http://www.christianity.net.au/
@
@ Going to OpenWorld?
@ Catch the Ixora performance tuning seminar too!
@ See http://www.ixora.com.au/seminars/ for details.

-----Original Message-----
From: Andy Hardy <aph_at_ahardy.demon.co.uk> Sent: Thu, 7 Sep 2000 23:03:55 +0100

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 - 19:55:29 CDT