Re: oracle table/row level locking

Given that there exists a hash cluster in Oracle, why do it yourself.

It is not clear when you say "duplicate return values" if you mean calling the function with the same parameters repeatedly within one session, or different sessions can return the same value. Is the repeated value always 1 for instance. The code as written easily return -1 repeatedly and dbms_output is not reliable if some other code raises errors.

I would suggest a better structure would be:

sequence_nbr := 1;
begin

• following will wait on a lock until commit by another session insert into hash_table( key1, key2, value ) values ( key1_in, key2_in, sequence_nbr ); commit; return sequence_nbr; exception when dup_val_on_index then update hash_table set value = value+1 where key1 = key1_in and key2 = key2_in returning value into sequence_nbr; commit; return sequence_nbr; end;

Nan <nandagopalj_at_hotmail.com> wrote in message news:8193246.0202061114.54ede78a_at_posting.google.com...
> Hello:
>
> I have an oracle function that is basically a hash data-structure
> implementation. There are 2 keys and a value in the hash_table table.
>
> An associated oracle function called get_hash_value is called using
> the 2 keys. If the passed keys are not in the hash_table, a record is
> inserted into the table with 1 in the column "value". If there is a
> row in the table with the same key, then "value" column is incremented
> and returned to the caller.
>
> I am invoking this function from dual table.
> There are 10 processes that invoke this function at the same time.
>
> My problem is that I am getting duplicate return values from the
> function. The value must be unique at any given time as per the code,
> however, I am doing something wrong thats causing duplicate return
> values.
> I tried row level and table level locking and could not solve it.
> Any help is very much appreciated. The actual .sql code is attached
> below.
>
> Thanks
> Nan.
>
>
>
>
> -- This oracle function is a hash implementation that consists of
> -- key1 and key2 associated with a value.
> -- If same keys are passed again the value is incremented and returned
>
>
> create table hash_table ( key1 VARCHAR2(8) not null,
> key2 BATCH_NBR NUMBER(10) NOT NULL,
> value number(5) );
>
> alter table hash_table add constraint
> PK_hash_table primary key ( key1, key2 );
>
>
> create or replace function get_hash_value( key1_in in varchar2,
> key2_in in number );
> return number
> is
>
> -- I invoke this function using the following SQL
> -- select get_hash_value( "key1_data", "2" ) from dual;
> -- This approach requires autonomous transactions
>
> pragma autonomous_transaction;
>
> -- note row level locking is used
>
> cursor hash_read_cursor is
> select value, rowid from hash_table where key1 = key1_in and key2 =
> key2_in
> for update of value;
>
> sequence_nbr number (5) := 0;
>
> row_id_in ROWID;
>
> begin
>
> -- I tried using table level lock as follows
> -- " lock table batch_seq_conv_table in exclusive mode; "
> -- But could not prevent duplicate return values from the function
> call
>
> open hash_read_cursor;
> fetch hash_read_cursor into sequence_nbr, row_id_in;
>
> if hash_read_cursor%FOUND
> then
>
> sequence_nbr := sequence_nbr + 1;
> update hash_table set value = sequence_nbr where rowid= row_id_in;
> commit;
>
> else
>
> sequence_nbr := 1;
> insert into hash_table values ( key1_in, key2_in, sequence_nbr );
> commit;
>
> end if;
>
> return sequence_nbr;
>
>
> exception
> when others then
> errMsg := 'Error Num = ' || TO_CHAR(SQLCODE) || ' - ' ||
> SUBSTR(SQLERRM, 1, 100) ;
> DBMS_OUTPUT.PUT_LINE( errMsg);
> return '-1';
>
>
> end;
Received on Wed Feb 06 2002 - 17:08:28 CST