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: VC <boston103_at_hotmail.com>
Date: Fri, 16 Jul 2004 14:18:57 GMT
Message-ID: <ltRJc.69399$WX.35336@attbi_s51>


Well, now that we know you are using bitmaps, the answer is sort of obvious -- you are dead-locking on concurrent bitmap indexes updates since the latter are not a good tool if concurrent DMLs take place:

Session1 updates/deletes key1. It'll lock the key1 entry in the bitmap index.
Session 2 updates/deletes key2. It'll lock the key2 entry in the bitmap index.
Session 1 tries to update/deletes key2 and will block on Session 2. Session 2 tries to update/delete key1 and will block on Session 1.

VC

"Paul" <paulz_at_cavendish.co.uk> wrote in message news:a4ca837.0407160547.12b338e0_at_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 - 09:18:57 CDT

Original text of this message

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