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