After SP4, proc w. Cursor doesnt release keylocks

Existing Stored Procedure, has been running well on SQL since 7.0.
(but needed some tweaking to migrate to 2000).

Now all of a sudden after installing SP4 of SQL 2000,
this process slows down, and SQL Spotlight shows the number of locks
just climbing throughout the processing run.

According to the MS Knowledge Base Articles on KeyLocks .. this was a
problem that was *fixed* in the service pack ... where as for me it is
now broken.

Article ID: 260652
PRB: Nested Loop Join That Uses A "BOOKMARK LOOKUP ...WITH PREFETCH"
May Hold Locks Longer http://support.microsoft.com/kb/260652/
Article ID: 828096
FIX: Key Locks Are Held Until the End of the Statement for Rows That
Do Not Pass Filter Criteria http://support.microsoft.com/kb/828096/
Anybody else have this issue, or have any "eazy" solutions?

The proc cursors thru a list and runs a proc on each item in the "work
list".
This is an existing system
with no plans to turn the process into a set oriented one,
as is going away shortly.rawheiser (rawheiser@.gmail.com) writes:

Quote:

Originally Posted by

Now all of a sudden after installing SP4 of SQL 2000,
this process slows down, and SQL Spotlight shows the number of locks
just climbing throughout the processing run.
>
According to the MS Knowledge Base Articles on KeyLocks .. this was a
problem that was *fixed* in the service pack ... where as for me it is
now broken.
>
Article ID : 260652
PRB: Nested Loop Join That Uses A "BOOKMARK LOOKUP ...WITH PREFETCH"
May Hold Locks Longer http://support.microsoft.com/kb/260652/
>
Article ID : 828096
FIX: Key Locks Are Held Until the End of the Statement for Rows That
Do Not Pass Filter Criteria http://support.microsoft.com/kb/828096/
>
Anybody else have this issue, or have any "eazy" solutions?
>
The proc cursors thru a list and runs a proc on each item in the "work
list".
This is an existing system with no plans to turn the process into a set
oriented one, as is going away shortly.

It's not clear why think your problem is related to the problem discussed
in the KB article. Apparently, your procedure has a cursor, so there
might be other reasons for these locks.

Is the entire loop one transaction?

As a shot in the dark, if the cursor declaration just says:

DECLARE cur CURSOR FOR

try adding INSENSITIVE before CURSOR.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||It IS an insensitive cursor.

Looking thru DBArtisan I see the KeyLocks in its lock display window,
and in SQL Spotlight I see the number of locks jump by 1000 or so every
few seconds.

How can I monitor what procedure is locking what resource?

Other than injecting some debugging code into the procedures?|||rawheiser (rawheiser@.gmail.com) writes:

Quote:

Originally Posted by

It IS an insensitive cursor.

As I said, it was a shot in the dark.

Quote:

Originally Posted by

Looking thru DBArtisan I see the KeyLocks in its lock display window,
and in SQL Spotlight I see the number of locks jump by 1000 or so every
few seconds.

And it retains the locks?

Quote:

Originally Posted by

How can I monitor what procedure is locking what resource?

Maybe my aba_lockinfo can be of help? If your procedure releases the locks,
then you will have to get the right moment, but if it retains the locks
you will see what it is. You find aba_lockinfo at
http://www.sommarskog.se/sqlutil/aba_lockinfo.html.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks for the link, I will look at it for future issues.
(and browse about your site for other goodies).

I did find the problem,
though I am not exactly sure of the mechanism involved.

The outer procedure was calling a procedure
that a procedure three levels down was also calling,
when I removed the duplicate updating,
it then flew like the wind.

I also changed a Set NoCount OFF to ON, which may have helped as well.

The proc is called by SQLAgent,
and I'm not exactly sure where all that 'client chatter'
goes to when the server is running the process.
(the NULL device?).

after loop wants to start for top with out closing cursor

Hi All,

I need your help urgently. I am using SQL Server. First i try to explain what i want to do. Imagine a warehouse having thousands of books or whatever. Sometimes multiple copies of same books can be on the same shelf but its also possible that 10 copies of a book are placed in 10 different shelf means one book per shelf. So i am writing program to pick location for orders received. My progarm works fantastic if a customer order single copy of any number of differnt books. But if a buyer order a 5 copies of the same book and if first location my progarm picked has only 1 or any number less than actual quantity ordered then my program fails to pick second or third location.
I am Using a cursor to fetch and process every single order and execute a stored procedures to pick location and update inventory. I want that after it has picked first location program should run last step without with same values in cursor but one updated value: (just want to redirect to last step) In VB we can use rst.Requery but what about SQL server

Following is a my code

CREATE PROCEDURE OPENCRSR2

--parametes needed to birng orders info in to program
@.var_orid varchar(25),
@.var_itemid varchar(25),
@.var_sku Varchar(25),
@.var_qtsold numeric(9)

AS

--variables to store fetched info from tables

Declare @.var_shelf numeric(9) ,
@.var_dateadded smalldatetime,
@.var_qtonhand numeric(9),
@.var_sporder varchar(25),
@.var_supplier varchar(25),
@.var_qtstore numeric(9)


DECLARE CrsrInside CURSOR FOR

SELECT Min(A.Shelf), A.DateAdded, Sum(A.QuantityOnHand) FROM UniversalBooks.dbo.tblInventory AS A
WHERE ((A.ISBN) = @.var_sku AND (A.QuantityOnHand>0)
AND (A.DateAdded=(SELECT Min(B.DateAdded)
From UniversalBooks.dbo.tblInventory as B
WHERE ((A.ISBN =B.ISBN) AND (B.QuantityOnHand>0)))))
GROUP BY A.DateAdded, A.QuantityOnHand

OPEN CrsrInside

FETCH NEXT FROM CrsrInside
INTO @.var_shelf, @.var_dateadded, @.var_qtonhand
--SELECT @.var_sku as ISBN, @.var_shelf as Shelf, @.var_dateadded as Dateadded
EXEC de_fetchstage2 @.var_sku, @.var_shelf, @.var_dateadded, @.var_qtonhand OUTPUT, @.var_sporder OUTPUT, @.var_supplier OUTPUT

--SELECT @.var_qtonhand as QtAvialable



IF @.var_qtsold > @.var_qtonhand
BEGIN
SET @.var_qtstore = @.var_qtsold - @.var_qtonhand
SET @.var_qtsold = @.var_qtonhand

--Following excute statement will insert picked shelf and other info into different table and subtarct quantity sold from quantity on hand but still i have few books to be picked for same order and @.var_qtstore has that number i want cursor should use same varibles but new value from @.var_qtsold which has the number of books to be picked(after execute Statement)


Exec de_insertstage3 @.var_sku, @.var_shelf, @.var_dateadded, @.var_qtonhand, @.var_sporder, @.var_supplier, @.var_orid, @.var_itemid, @.var_qtsold

SET @.var_qtsold = @.var_qtstore

--**** HERE I want to redirect my code to start from the current if statement instead of starting from next fetched cursor
END

ELSE
--REST OF THE CODE

--SELECT @.var_sku as ISBN, @.var_shelf as Shelf, @.var_dateadded as Dateadded, @.var_qtsold as Qtsold,
@.var_qtonhand as QuantityAvailable, @.var_sporder as OrderNumber, @.var_supplier as Supplier
END

CLOSE CrsrInside

DEALLOCATE CrsrInside
GO

Any help will be appericiated

Thanks in advance
DevWould a WHILE statement work for you?:

WHILE @.var_qtsold > @.var_qtonhand
BEGIN
SET @.var_qtstore = @.var_qtsold - @.var_qtonhand
SET @.var_qtsold = @.var_qtonhand
Exec de_insertstage3 @.var_sku, @.var_shelf, @.var_dateadded, @.var_qtonhand, @.var_sporder, @.var_supplier, @.var_orid, @.var_itemid, @.var_qtsold
SET @.var_qtsold = @.var_qtstore
END

blindman