Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Oracle deadlock on table with indexes.

Re: Oracle deadlock on table with indexes.

From: Paul <paulz_at_cavendish.co.uk>
Date: 16 Jul 2004 06:47:29 -0700
Message-ID: <a4ca837.0407160547.12b338e0@posting.google.com>


All foreign keys are indexed in all the tables involved in the two delete transactions, and still we have deadloacks. We did a trace and got the following (which seems to imply that indexes are being used):

SQL> delete from transfer where call_ref='FFF1111';

0 rows deleted.

Execution Plan


   0 DELETE STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=13)    1 0 DELETE OF 'TRANSFER'

   2    1     INDEX (UNIQUE SCAN) OF 'TRANSFER_PK' (UNIQUE) (Cost=1 Ca
          rd=1 Bytes=13)




SQL> c/transfer/transfer_event
  1* delete from transfer_event where call_ref='FFF1111' SQL> / 0 rows deleted.

Execution Plan


   0 DELETE STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=21)    1 0 DELETE OF 'TRANSFER_EVENT'

   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'TRANSFER_EVENT' (Cost=
          1 Card=1 Bytes=21)

   3    2       BITMAP CONVERSION (TO ROWIDS)
   4    3         BITMAP INDEX (SINGLE VALUE) OF 'TRANSFER_EVENT_IDX_0
          01'




SQL> exit

Okay - does anyone have any ideas!? Received on Fri Jul 16 2004 - 08:47:29 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US