Re: Deadlock problems

I always have to look at a little chart to translate lock names/codes into numbers. But

    SX is mode 3 (update rows, insert, delete)     SSX is mode 5 (lock table in share row exclusive mode)

This suggests that you have a parent/child interaction running through an array processing mechanism with concurrent batch jobs.

Look up object 1c8b3 (116915) in dba_objects, it is like to be table ARO_APP_RIN (although it might be a child thereof, or a target of a trigger from there, or the child of a target of a trigger ...)

It looks like:

    session 9 has modified some rows in the table,     and now wants to modify a parent row of that     table.

whilst

    session 14 has done exactly the same thing on     a different set of child rows, and now wants to do     something to a different parent

AND
    there is no index representing the foreign key     on the child table - which has caused each session     to try and acquire a mode 5 lock on the child table     as it attacks the parent.

There are other variants - even with the index in place - such as trying to delete a load of children and then a parent, whilst allowing the other session to insert some child rows between your two delete statements.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminars
 UK  Sept
 Australia August
 Malaysia September
 USA x 2  November

http://www.jlcomp.demon.co.uk/seminar.html

Ludo wrote in message ...


>Hello,

>

>I've some serious deadlock problem on a 4 instances program.

>Here is the log :

>

>*** SESSION ID:(9.62) 2002-07-14 22:23:40.137

>DEADLOCK DETECTED

>Current SQL statement for this session:

>DELETE FROM ARO_APP_RIN ARO WHERE ARO.NSQ_T16 = :b1  AND ARO.NSQ_RIN = :b2

>----- PL/SQL Call Stack -----

>  object      line  object

>  handle    number  name

>98171684       146  package body PCO00.CDGGBSA4_EPURATION

>98176a2c         2  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-0001c8b3-00000000        16       9    SX   SSX        9      14    SX

>SSX

>TM-0001c8b3-00000000         9      14    SX   SSX       16       9    SX

>SSX

>session 9: DID 0001-0010-00000002 session 14: DID 0001-0009-00000002

>session 14: DID 0001-0009-00000002 session 9: DID 0001-0010-00000002

>Rows waited on:

>Session 14: no row

>Session 9: no row

>

>

>Initrans on ARO_APP_RIN is 16 since the creation of the table, and i'm sure

>it's not a applicative lock.

>

>So is the "Current SQL statement for this session:" is always the last

>statement that lead to the deadlock ?

>There are some trigger on ARO_APP_RIN, Is it possible that a statement in

>the trigger lead to this deadlock (the trigger work on tables whith

initrans


>= 1)

>

>Thanks for any help.

>

>Ludo

>

>

>

>

>--

>Ludovic Cayen (Adventec)

>

>

>