Re: Posting problem in Forms V4.5

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sat, 29 Aug 1998 17:31:18 GMT
Message-ID: <35ef3a97.5816623@192.86.155.100>

A copy of this was sent to glucas4189_at_aol.com (GLucas4189) (if that email address didn't require changing) On 29 Aug 1998 13:30:18 GMT, you wrote:

>Anybody know how to get around a problem whereby if you attempt to insert two
>rows, one a duplicate of another, but only post in the form, the form hangs.
>This is an acknowledged bug from Oracle, but I was wondering if, on the server
>side, anything could be done. To repeat the scenario, do the following :-
>
>1. Create a table with a column e.g. temp_value
>
>2. Put a unique index on the table.
>
>3. Create a form which inserts a value into the row, on some user event, e.g. a
>button. Do not commit, only post the row.
>
>4. Run the form twice, inserting the same value into the row e.g 3. The 2nd
>form will hang.
>
>Any ideas ?
>
>Many thanks in advance
>
>Graham

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 Sat Aug 29 1998 - 12:31:18 CDT