Re: Re:RE: Deadlock

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