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;
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

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

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@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org