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" <[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: Joe Testa
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).
