Re: Locking issue from multi thread

On 31.12.2007 05:51, vijay wrote:
> 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;
> end;
> commit;
>
> 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.

Please do yourself a favor and use sequences. That's the proper tool for what you seem to be trying to do.

Cheers

        robert Received on Mon Dec 31 2007 - 09:17:56 CST