Re: Unique index lock

From: Mladen Gogala <gogala_at_sbcglobal.net>
Date: Sun, 14 May 2006 22:20:37 GMT
Message-Id: <pan.2006.05.14.22.20.36.262782@sbcglobal.net>

On Sun, 14 May 2006 08:07:31 -0700, sasha 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.

Sasha, sessions waiting for locks are sleeping, which means that they cannot process any "reset" signals until they become unblocked. The ONLY way to terminate this wait is to terminate the session itself. Second method is to use "SELECT FOR UPDATE NOWAIT" or SELECT FOR UPDATE WAIT <seconds>",
Session a)
SQL> delete from emp;

14 rows deleted.

SQL> session b)

SQL> select ename from emp where job='CLERK' for update wait 10; select ename from emp where job='CLERK' for update wait 10

                  *

ERROR at line 1:
ORA-30006: resource busy; acquire with WAIT timeout expired

Elapsed: 00:00:10.03
SQL> Be aware of the following restriction, directly from the manual:

Restriction on the WAIT Clause

    If you specify WAIT and the table is locked in exclusive mode, then the database will not return the results of the SELECT statement until the lock on the table is released regardless of the wait time specified.

Second, Oracle*Net has a parameter to "enable out of band breaks" delivery. You should put something like DISABLE_OOB=OFF in you sqlnet.ora. That should be the default.

-- 
http://www.mgogala.com

Received on Sun May 14 2006 - 17:20:37 CDT