Locking issue from multi thread

UI Tool : VC 2005,
Database: oracle
OS - Window XP

Hi Everyone,

Given stored procedure generate new key for given key name.

CREATE OR REPLACE PROCEDURE "ABILOGS"."SP_GETNEWLOGKEY"     (strkeyname IN VARCHAR2,nkeyvalue OUT NUMBER) is
begin
  declare

       cursor cur is select keyvalue from logkey where keyname=strkeyname for update of keyvalue;

       nValue integer;
  begin

      open cur;
      fetch cur into  nValue;
      if cur%notfound then
           insert into logkey values(strkeyname, 1);
           nkeyvalue := 1;
      else
             begin
                 nValue := nValue+1;
                 update logkey set keyvalue=nValue where current of
cur;
                 nkeyvalue := nValue;
             end;
      end if;
      close cur;

exception
WHEN OTHERS THEN
      raise_application_error(-20001,'An error was encountered while executing SP_GETNEWLOGKEY - '||SQLCODE||' -ERROR- '||SQLERRM);

end SP_GETNEWLOGKEY;

Stored procedure uses a table Logkey with two column names Keyname(varchar2) and Keyvalue(number).

When i call SP_GETNEWLOGKEY from two different sessions, stored procedure generate key perfectly. Ex.

declare nkeyvalue NUMBER;
call SP_GETNEWLOGKEY("xyz", nkeyvalue); from session 1 call SP_GETNEWLOGKEY("xyz", nkeyvalue); from session 2

I am facing issue when two different threads call SP_GETNEWLOGKEY for same key name from same session. Ex.

declare nkeyvalue NUMBER;
call SP_GETNEWLOGKEY("xyz", nkeyvalue); from session 1 call SP_GETNEWLOGKEY("xyz", nkeyvalue); from session 1

In this case program generate duplicate key (not always). I have noticed that oracle provide lock at different session level not at thread level. Is there any way to acquire lock at different thread level?

It will be great help if i get response from any one.

Thanks in advance.

Vijay Received on Sun Dec 30 2007 - 22:51:31 CST