| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Re:RE: Deadlock
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:
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 -----
>
> 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 <ORACLE-L_at_fatcity.com>
> 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: 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).Received on Tue Dec 24 2002 - 17:08:37 CST
 |
 |