Re: how to do a "non-blocking" INSERT?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 10 Dec 1999 16:58:49 -0500
Message-ID: <pqt25soprgmqnf3la8cpekg5r5pb0fn2io@4ax.com>

A copy of this was sent to nico_at_arzoon.com (if that email address didn't require changing) On Fri, 10 Dec 1999 04:37:26 GMT, you wrote:

>I'm trying to figure out how to execute an INSERT statement in such a
>way that it would return immediately with an error if it were to block
>due to row or table locking. Kind of like the NOWAIT option in the LOCK
>TABLE statement.
>
>I have a situation where 2 separate transaction threads are INSERTing
>rows (which potentially may have identical unique keys) into the same
>table. the second thread blocks until the first thread commits or rolls
>back, then its INSERT will either succeed or fail. since the transaction
>processing time is relatively long, i'd prefer to know in advance that
>thread 2's INSERT statement will block so I can perform a back off and
>retry strategy without waiting all that time.
>
>Does anyone know how to something like this?
>
>Thanks in advance...
>nico
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.

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.  

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Dec 10 1999 - 15:58:49 CST