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: Connor McDonald <connor_mcdonald_at_yahoo.com>
Date: Thu, 18 May 2006 22:06:02 +0800
Message-ID: <446C7F4A.5CC9@yahoo.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.
>
> 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

Original text of this message

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