Re: Re:RE: Deadlock

Yes it is 9013. This is not an array based update. As per the trace file same statement is being executed by both sessions. I can directly send you the trace file if there is a need.

There are triggers on the tables, I'll look into parent table activity. But there are indexes on all foreign keys except one which corresponds to a static master table containing PO TYPES. That table is not being updated.

How can I dig deeper into this issue.

Thanks
Shaleen
----- Original Message -----
To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Thursday, December 26, 2002 1:58 AM

>
> This looks like a v9 trace file, which means
> there may be new issues involved that I
> haven't come across yet. For example, 9.2
> introduces a mode 2 TM lock on pk/fk activity
> for some reason that I haven't worked out,
> so this may be a side-effect.
>
> However, (assuming no big changes from v8)
> this is TM lock in mode 5 (SSX) colliding with
> a mode 3 (SX), so it is most likely a pk/fk issue -
> despite your comment to the contrary.
>
> If it were a "purely data" problem I would expect
> to see a mode 6 TX lock, if it were any of the
> "internal structure" issues I would expect to
> see a mode 4 TX lock.
>
> The 'Rows waited on:' line could be down to
> v9 recording the block address of the most
> recent buffer busy wait, write wait, etc. which
> is a very recent enhancement - but since the
> values are not cleared when the wait ends,
> this can cause confusion.
>
> Is this an array-based update ? And is the
> SQL from this session (the one that dumped
> the graph) the same as the SQL that has been
> dumped for the other session ?
>
>
> Most critically - do you have any triggers on
> the child table that may be doing parent
> table activity that you've overlooked ?
>
>
>
>
>
> Regards
>
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> Coming soon a new one-day tutorial:
> Cost Based Optimisation
> (see http://www.jlcomp.demon.co.uk/tutorial.html )
>
> Next Seminar dates:
> (see http://www.jlcomp.demon.co.uk/seminar.html )
>
> ____England______January 21/23
>
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
>
>
>
>
> -----Original Message-----
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> Date: 24 December 2002 23:49
>
>
> >Jonathan,
> >
> >What do you make out of following deadlock graph. This is happenning
> when 2
> >instance of same batch process are running. We are absolutely certain
> that
> >these batch processes are not working on same set of records
> (although
> >records can be in same block). object f9d5 is wcu_po_line table. I am
> unable
> >to understand why the update statements are requesting SSX lock on
> the
> >table. This is not a case of primary/forign key issue with a missing
> index
> >in child table where primary key is change in master table because
> master
> >table is not being updated.
> >
> >Thanks
> >Shaleen
> >
> >Deadlock graph:
> > ---------Blocker(s)-------- ---------Waiter(s
> )------
> >---
> >Resource Name process session holds waits process session
> holds
> >waits
> >TM-0000f9d5-00000000 390 503 SX SSX 290 597
> SX
> >SSX
> >TM-0000f9d5-00000000 290 597 SX SSX 390 503
> SX
> >SSX
> >session 503: DID 0001-0186-00000002 session 597: DID
> 0001-0122-00000002
> >session 597: DID 0001-0122-00000002 session 503: DID
> 0001-0186-00000002
> >Rows waited on:
> >Session 597: obj - rowid = 000098A5 - AAAAAAADFAAAGCsAAA
> > (dictionary objn - 39077, file - 197, block - 24748, slot - 0)
> >Session 503: no row
> >SQL statements executed by the waiting sessions:
> >Session 597:
> >UPDATE wcu_po_line
> > SET po_no = :b21,
> > po_line = :b20,
> > item_price = :b19,
> > po_qty = :b18,
> > invoice_shipped_qty = 0, --invoice_shipped_qty
> > distributor_item_no = :b17,
> > current_status = :b16,
> > created_dtm = SYSDATE,
> > status_change_dtm = SYSDATE,
> > --created_dtm
> > return_id = NULL, --return_id_in,
> > return_line_no = NULL, --return_line_no_in,
> > min_qty = :b15,
> > wrap_code = :b14,
> > invoice_id = :b13,
> > gift_wrap_UPC = :b12,
> > gift_wrap_price = :b11,
> > wrap_to_label = :b10,
> > wrap_from_label = :b9,
> > item_cost = nvl(:b7,:b6),
> > xml_po_line = :b8,
> > wmc_item_cost = nvl(:b7,:b6),
> > distributor_id = :b5,
> > po_type = :b4
> > WHERE po_no = :b3
> > AND co_order_no = :b2
> > AND co_line_no = :b1
> >===================================================
> >----- Original Message -----
> >To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> >Sent: Friday, December 20, 2002 3:33 PM
> >
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Jonathan Lewis
> INET: jonathan_at_jlcomp.demon.co.uk
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Shaleen
  INET: shgarg_orafaq_at_hotmail.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).