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.
>
> 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.
You can use dbms_lock to synthesize a lock on an insert. So then each sesssion does:
dbms_lock.request(...)
if lock obtained then
insert...
hth
Connor
-- Connor McDonald Co-author: "Mastering Oracle PL/SQL - Practical Solutions" Co-author: "Oracle Insight - Tales of the OakTable" web: http://www.oracledba.co.uk web: http://www.oaktable.net email: connor_mcdonald_at_yahoo.com "Semper in excremento, sole profundum qui variat." ------------------------------------------------------------Received on Thu May 18 2006 - 09:06:02 CDT