Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> 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.
You can have the insert statement run in an autonomous transaction which automatically commits. So you would change the logic so that, instead of doing an insert of all the data you want inserted, you instead call an autonomous transaction which inserts the row (perhaps a dummy row with just the PK, for example) and commits. Then you do a select for update nowait on that row, then do an update on that row to populate it with whatever you want it populated with, then commit the main transaction.
Xho
-- -------------------- http://NewsReader.Com/ -------------------- Usenet Newsgroup Service $9.95/Month 30GBReceived on Sun May 14 2006 - 18:00:15 CDT