Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Unique index lock

Re: Unique index lock

From: <xhoster_at_gmail.com>
Date: 14 May 2006 23:00:15 GMT
Message-ID: <20060514191826.501$72@newsreader.com>


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 30GB
Received on Sun May 14 2006 - 18:00:15 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US