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).
