The problem is in your hash function. Looks like it is relying on some side
effect. It is not an Oracle lock problem, readers don't block other readers
or other writers.
Jim
"Nan" <nandagopalj_at_hotmail.com> wrote in message
news:8193246.0202061931.78a7ddd4_at_posting.google.com...
> Thanks for the response.
>
> I am actually trying to generate a running sequence for a given key
> value.
>
> I have multiple processes that invole the SQL function using dual in
> the select statement.
> "select get_hash_value("abcd",1) from dual;
>
> For e.g
>
> when process 1 calls this function:
> > select get_hash_value("abcd",1) from dual;
> > return value is: 1
>
> when process 2 calls this function:
> > select get_hash_value("abcd",1) from dual;
> > return value is: 2
>
> Note all processes may use the same key as is the above case.
> when that happens I am getting same return values.
> It may be because the lock on the same row is not working for some
> reason.
> The DBMS thingy was just introduced while trying to debug..
>
> I am yet to try ur suggestion - will try once I get to work tomorrow.
>
> Thanks
> Nan.
>
> "Keith Boulton" <kboulton_at_ntlworld.com> wrote in message
news:<2Di88.7906$ZM5.891706_at_news2-win.server.ntlworld.com>...
> > 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 Thu Feb 07 2002 - 00:23:46 CST
