Stored Procedure (Locking) which is working fine on SQL Server 2000 is not working on SQL Server 2005

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

vijay singh - 28 Jul 2008 13:42 GMT

OS - Vindows 2003
Database - SQL Server 2005

Hi,

Below Stored Procedure has been developed to get new log key which is
working fine on version SQL Server 2000 whenever same is not working on SQL
Server 2005 which is returning error as given

"Description [Optimistic concurrency check failed. The row was modified
outside of this cursor".

Can anyone help me what can be error?

Thanks in advance.

Vijay

SQL Server Stored Procedure
--------------------------------

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[ABI_SP_GETNEWLOGKEY] @strkeyname nvarchar(255),
@nkeyvalue integer OUTPUT

as

begin

declare cur cursor for

select keyvalue from LOGKEY with ( ROWLOCK, UPDLOCK) where
keyname=@strkeyname;
open cur;

begin
fetch next from cur into @nkeyvalue;

if @@FETCH_STATUS <> 0
begin
insert into logkey values(@strkeyname,1);
set @nkeyvalue = 1;
end
else
begin
set @nkeyvalue = @nkeyvalue+1;
update dbo.LOGKEY set keyvalue=@nkeyvalue where current of cur;
end
end

close cur;
DEALLOCATE cur;

end

Table LOGKEY has two field keyname(text) and keyvalue(integer).

Reply to this Message

Andrew J. Kelly - 28 Jul 2008 15:05 GMT

Why on earth would you use a cursor to do something like that. For one thing
it is a single row. You don't need a cursor for a single row regardless of
what you want to do with it. Try this instead:

ALTER PROCEDURE [dbo].[ABI_SP_GETNEWLOGKEY] @strkeyname nvarchar(255),
@nkeyvalue integer OUTPUT

as

BEGIN TRAN

select keyvalue from LOGKEY with ( ROWLOCK, UPDLOCK) where
keyname=@strkeyname;

IF @@ROWCOUNT = 0
begin
insert into logkey values(@strkeyname,1);
set @nkeyvalue = 1;
end
ELSE
BEGIN
set @nkeyvalue = @nkeyvalue+1;
update dbo.LOGKEY set keyvalue=@nkeyvalue where keyname = @nkeyvalue
END

RETURN

Signature

Andrew J. Kelly SQL MVP
Solid Quality Mentors

> OS - Vindows 2003
> Database - SQL Server 2005
[quoted text clipped - 55 lines]
>
> Table LOGKEY has two field keyname(text) and keyvalue(integer).

Reply to this Message

vijay singh - 28 Jul 2008 16:59 GMT

Dear Andrew J. Kelly,

I have changed Stored Procedure as per your suggestion. Now I am facing
duplicate key error. Error detail

"Violation of PRIMARY KEY constraint 'InputTable_PK'. Cannot insert
duplicate key in object 'dbo.InputTable'."

I used cursor to acquire lock on a row. In the ABI_SP_GETNEWLOGKEY Stored
Procedure lock will be acquire at line
"fetch next from cur into @nkeyvalue"

Please suggest what can be another solution. I would like to mention one
more thing is that Stored Procedure ABI_SP_GETNEWLOGKEY is being called from
VC++ application which uses multi thread.

Thanks,

Vijay

> Why on earth would you use a cursor to do something like that. For one
> thing it is a single row. You don't need a cursor for a single row
[quoted text clipped - 82 lines]
>>
>> Table LOGKEY has two field keyname(text) and keyvalue(integer).

Reply to this Message

vijay singh - 28 Jul 2008 16:59 GMT

Reply to this Message

Andrew J. Kelly - 28 Jul 2008 18:15 GMT

Sorry when I copied the code I forgot to change one of the lines. Try this:

select keyvalue from LOGKEY with ( HOLDLOCK, XLOCK) where

And I assumed you would handle the commit but make sure you have something
like this at the end of the sp.

IF @@TRANCOUNT > 0
COMMIT TRAN

Signature

Andrew J. Kelly SQL MVP
Solid Quality Mentors

> Dear Andrew J. Kelly,
>
[quoted text clipped - 102 lines]
>>>
>>> Table LOGKEY has two field keyname(text) and keyvalue(integer).

Reply to this Message

vijay singh - 29 Jul 2008 07:08 GMT

Dear Andrew J. Kelly,

Thanks for yout response. My problem is resolved after your help. Final
changed in stored procedure

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[ABI_SP_GETNEWLOGKEY] @strkeyname nvarchar(255),
@nkeyvalue integer OUTPUT

as

BEGIN TRAN

select @nkeyvalue=keyvalue from LOGKEY with ( HOLDLOCK, XLOCK) where
keyname=@strkeyname;

IF @@ROWCOUNT = 0
begin

insert into logkey values(@strkeyname,1);
set @nkeyvalue = 1;
end
ELSE
BEGIN
set @nkeyvalue = @nkeyvalue+1;
update dbo.LOGKEY set keyvalue=@nkeyvalue where keyname = @strkeyname
END

IF @@TRANCOUNT > 0
COMMIT TRAN

Thanks.

Vijay

> Sorry when I copied the code I forgot to change one of the lines. Try
> this:
[quoted text clipped - 115 lines]
>>>>
>>>> Table LOGKEY has two field keyname(text) and keyvalue(integer).

Reply to this Message