Re: Unique index lock

sasha_at_literal.ru wrote:
> Hi!
>
> Have simple situation with locking on unique index:
>
> 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.
>
> The problem is what I did not find any way like "SELECT FOR UPDATE
> NOWAIT" which will give me the information on potential lock to make
> proper processing before actually execute INSERT or UPDATE.
>
> Situation redoubled by inability to break waiting statement with
> OCI_BREAK or with any other way except for killing one of sessions --
> 1st of 2nd.
>
> Can anybody help to find the decision which will not make any
> transaction owner sad?)))
> Oracle can raise "deadlock detected" by itself and rollback current
> statement, there must be a way to do it manually! 8-)))
>
> Thanks in advance.

I have no idea what you did or precisely what you are trying to do. Why would Tx2 wait for Tx1 ... they are both inserts? Is there a primary key constraint? Why is your application written in such a way that insert deadlocks are possible? What version number?

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org