Re: Locking issue from multi thread

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.
>
> Thanks in advance.
>
> Vijay

What you have provided us here, on many levels, is an example of how to not use Oracle effectively.

First, as Robert has said, you should be using a sequence to generate your surrogate key. There is no excuse for reinventing the wheel.

But far worse is that your attempt to do so uses obsolete technology that should be forgotten ... the cursor loop and forces a totally unnecessary serialization.

Best practice, in 9i or 10g, would be something such as:

CREATE SEQUENCE nkeyvalue;

CREATE OR REPLACE TRIGGER bi_t
BEFORE INSERT
ON t
FOR EACH ROW BEGIN
   SELECT nkeyvalue.NEXTVAL INTO :NEW.pk_column FROM dual; END row_level;
/

-- 
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org