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
>
> There is a deadlock here - but I confused the
> issue by making complete garbage of the last
> phrase. Instead of:
>
> >> both X and Y might end up waiting for A.
>
> I should have said
>
> >> both Y and Z might end up waiting for X
> (which is when you won't get the deadlock)
>
> The critical point comes in the previous
> paragraph though:
>
> >> With a little luck, Y will be waiting for Z
> >> and Z will be waiting for Y (i.e. DEADLOCK)
>
> For Oracle 9, I have only introduced the X
> session to take out one ITL slot from each
> of the two blocks because Oracle 9 forces
> a minimum value of 2 entries per ITL.
>
> This really is a deadlock - which will show a
> deadlock graph with holders in mode 6 and
> waiters in mode 4. (X and S if I've got the
> letters right - personally I prefer numbers).
>
>
> 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: 20 December 2002 22:45
>
>
> >Jonathon,
> >
> >This produces ITL waits for sessions Y and Z; but this is not
> deadlock. The
> >deadlock occurs due to a situation where the Session 1 waits for
> something
> >to finish in Session 2, which in turn waits for Session 1 AND, this
> is
> >important, Oracle detects it and kills one of them, rolling back the
> >changes, making a deadlock detected error. Is this not the true error
> >message that occured in the original thread?
> >
> >In your example, sessions Y and Z will wait indefinitely until X
> commits or
> >rolls back. This is not going to be detected by Oracle nor killed by
> it. So
> >you wouldn't see a message DEADLOCK DETECTED in alert log. Therefore
> setting
> >INITRANS higher is not going to help at all. Rather the application
> logic
> >should be checked to remove a real locking conflict.
> >
> >Am I correct, or am I missing something here?
> >
> >Arup Nanda
> >
>
>
> --
> 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).
