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" <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> > 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" <[EMAIL PROTECTED]> > >Sent: Friday, December 20, 2002 3:33 PM > > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Jonathan Lewis > INET: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).