Re: Nowait clause for insert statements?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1998/05/08
Message-ID: <355333b4.13169036@192.86.155.100>#1/1

A copy of this was sent to Claus Jensen <cje_at_stibo.com> (if that email address didn't require changing) On Fri, 08 May 1998 09:53:25 -0400, you wrote:

>Is there any way to check if an insert will be blocked by a lock. I am
>thinking of something similar to "select for update nowait", but for
>insert statements. Alternatively can I query the dba_locks to check it
>that way?
>
>Thanks in advance for any ideas...
>Please respond by e-mail.

Try this:

create table demo ( x int primary key );

create or replace trigger demo_bifer
before insert on demo
for each row
declare

    l_lock_id number;
    resource_busy exception;
    pragma exception_init( resource_busy, -54 ); begin

    l_lock_id := dbms_utility.get_hash_value( to_char( :new.x ), 0, 1024 );

    if ( dbms_lock.request(  id                => l_lock_id,
                             lockmode          => dbms_lock.x_mode,
                             timeout           => 0,
                             release_on_commit => TRUE ) = 1 )
    then
        raise resource_busy;

    end if;
end;
/

---

If, in 2 separate sessions you execute:

insert into demo values (1);

it'll succeed in the first one but immediately issue:

SQL> insert into demo values ( 1 );
insert into demo values ( 1 )

            *
ERROR at line 1:

ORA-00054: resource busy and acquire with NOWAIT specified
ORA-06512: at "TKYTE.DEMO_BIFER", line 12
ORA-04088: error during execution of trigger 'TKYTE.DEMO_BIFER'

in the second session (unless the first session commits and then unique constraint violation will be the error message).

The concept here is to take the PRIMARY KEY of the table in the trigger and put it in a character string. we can then use dbms_utility.get_hash_value to come up with a "mostly unique" hash value for the string. As long as we use a hash table smaller then 1,073,741,823, we can 'lock' that value exclusively using dbms_lock.

We take that hash value and use dbms_lock to request that hash to be X locked with a timeout of ZERO (returns immediately if someone else has locked that value). If we timeout, we raise ORA-54 resource busy. Else, we do nothing.

Of course, if the primary key of your table is an INTEGER and you don't expect the key to go over 1 billion, you can skip the hash and just use the number.

You'll need to play with the size of the hash table (1024 in my example) to avoid artificial 'resource busy' messages due to different strings hashing to the same number. Also, the owner of the trigger will need execute on DBMS_LOCK granted directly to them (not via a role). Lastly, you might find you run out of enqueue_resources if you insert lots of rows this way without committing. If you do, you need to modify the init.ora parameter enqueue_resources to be high enough (you'll get an error message about enqueue_resources if you hit this). You might add a flag to the trigger to allow people to turn the check on and off (if I am going to insert hundreds/thousands of records, I might not want this check enabled for example)

Hope this helps.  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Fri May 08 1998 - 00:00:00 CDT