Re: Deadlock

Bitmap indexes?

joe

Shaleen wrote:

>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
>
>
>
>
>>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: Joe Testa
  INET: jtesta_at_dmc-it.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).