Re: Unique index lock

>create table X ( i Integer );
>
>create unique index IDX_X on X ( i );
>
>1st transaction:
>insert into X values (1);
>
>2nd transaction:
>insert into X values (1); -- waits for 1st transaction to complete
>
>Same situation occurs on update to the same value in different
>transactions.

Same situation cannot occur with updates if each updating transaction first locks rows it intends to update with SELECT FOR UPDATE NOWAIT - second transaction will immediately know someone is already updating that row. As of inserts - the problem is that since first TX didn't commit yet, second TX doesn't know if insertion of the same value will violate the constraint or not. You need to design your application in a way that prevents situations like described, when a transaction inserts a unique value and then goes for an extended sleep while other transactions wait to try inserting that same value. I think this is purely a design issue - you either should not allow different transactions to even attempt inserting the same value or make inserting transactions as short as possible and do not allow them to stay uncommitted for extended periods of time.

Regards,

     Vladimir M. Zakharychev
     N-Networks, makers of Dynamic PSP(tm)
     http://www.dynamicpsp.com