Re: Insert into with nowait?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sun, 08 Nov 1998 14:58:21 GMT
Message-ID: <3645b146.47161084@192.86.155.100>

A copy of this was sent to NBronke_at_t-online.de (Nicolas Bronke) (if that email address didn't require changing) On 8 Nov 1998 11:58:39 GMT, you wrote:

>Sure that could be a way but in a multi-user environment (several users)
>working with the data (updates, inserts etc.) you cannot insert any record
>if another user updates one record which he has locked before (select * from
>table where-condition for update nowait).
>
>Regards
>Nicolas Bronke
>
>victor schrieb in Nachricht <01be0995$49a77180$6b14abcc_at_victor>...
>>you can lock table before insert like this
>> lock table name in exclusive mode nowait;
>

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 Sun Nov 08 1998 - 08:58:21 CST