oracle table/row level locking

From: Nan <nandagopalj_at_hotmail.com>
Date: 6 Feb 2002 11:14:58 -0800
Message-ID: <8193246.0202061114.54ede78a@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 - 13:14:58 CST