Re: oracle table/row level locking

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 Wed Feb 06 2002 - 21:31:58 CST