Re: Unique index lock

On 14 May 2006 08:07:31 -0700, 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.

Did you try
LOCK table x in row shared mode nowait
?

--
Sybrand Bakker, Senior Oracle DBA