Unique index lock

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. Received on Sun May 14 2006 - 10:07:31 CDT