Nowait clause for insert statements?

[Claus Jensen]

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.

--
------------------------------------------------------------------
Claus Jensen The Stibo Technology Group...since 1794
Stibo Datagraphics Inc.
400 Franklin Road E-mail: c...@stibo.com
Marietta Phone: (001) 770 425 3282, ext. 197
GA 30067-7735 Fax: (001) 770 425 3012
------------------------------------------------------------------
"From a programmers point of view, a user is just a peripheral,
that types when you issue a 'read' command" - Peter Williams
------------------------------------------------------------------

[Thomas Kyte]

A copy of this was sent to Claus Jensen <c...@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
tk...@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.