"checking out" a record - selecting, locking, and updating 1

[Funka]

We are trying to implement a system for "checking out" records from our SQL Server 7.0 database.

In this distributed application, the client machine needs to select an available record from tblSpool (where the record's Status is -1) and then set its status to 0 to indicate that it has been checked out. The main problems encountered in my stress testing are that: A)the same record is being handed out to more than one computer at a time, B)EOFs are being returned when there really are more available records, or C)deadlocking.

I've explored dozens of possible combinations setting isolation levels and with locking hints in the FROM (WITH) clause, all with no success. Since there will potentially be dozens of computers all contending to "check out" a record at once, we need this to work properly. Here is the (simplified) code of my stored procedure:

CREATE PROCEDURE sp_CheckRecordOut
@ClientID int
AS
BEGIN
DECLARE @theRecord int
BEGIN TRANSACTION

/***** determine next available record *****/
SELECT TOP 1 @theRecord = RecordID FROM tblSpool
-- ugh, i sure have tried a bunch of these combinations, none with success
--WITH (UPDLOCK, HOLDLOCK)
--WITH (HOLDLOCK, ROWLOCK, UPDLOCK, READPAST)
--WITH (UPDLOCK, ROWLOCK, READPAST)
--WITH (UPDLOCK, READPAST)
--WITH (UPDLOCK, ROWLOCK)
WHERE StatusID = -1

/***** "Check it out" *****/
UPDATE tblSpool SET StatusID = 0, ClientID = @ClientID
WHERE RecordID = @theRecord

COMMIT TRANSACTION

/***** now we can return the 'checked out' RecordID to client machine*****/
SELECT @theRecord AS 'theRecord'

END


I've read about strategies using a semaphore/timestamp column to help with situations similar to this, but don't see how that might help me here. Thank you for any assistance or guidance!

 

[tlbroadbent]

You can do a little trick to get the RecordID in the Update statement so you only need one statement rather than two. Here is my suggestion for the stored procedure. It is simpler and should be faster.

CREATE PROCEDURE sp_CheckRecordOut @ClientID int AS

DECLARE @theRecord int
SET @theRecord=0

SET NOCOUNT ON

/** Restrict update to 1 record **/
SET ROWCOUNT 1

/** Update the table and get RecordID at the same time **/

UPDATE tblSpool SET
StatusID = 0,
ClientID = @ClientID,
@theRecord=RecordID
WHERE StatusID = -1

/** Return the 'checked out' RecordID to client machine **/

SELECT @theRecord AS 'theRecord'

SET ROWCOUNT 0
SET NOCOUNT OFF Terry

http://members.home.net/tlbroadbent/sqlarticles.htm

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[Funka]

thank you so much for your reply! i had wondered about the possibility of combing my SELECT and UPDATE operations into one as you had suggested, but i had never seen that clever syntax before. however, i may have done myself a disservice by simplifying my code too much for my posting...

my WHERE clause (in addition to checking the StatusID) actually needs to check criteria from other tables as well(via a JOIN statement in my SELECT). i would love to be able to do this all in one statment, but am back to square one as i'm not sure if i'm able to join inside the UPDATE statement...

again, thank you for any help!

 

[tlbroadbent]

You should be able to use all of your criteria in the UPDATE query.


Update tblSpool set ....
From tblSpool
Join tbl2 On tblSpool.key=tbl2.key
Join tbl3 On ...
WHERE tblSpool.StatusID = -1
AND <more criteria> ...

This should give you some ideas. Let me kow if you have questions. Terry

http://members.home.net/tlbroadbent/sqlarticles.htm

&quot;The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge.&quot; - Daniel J Boorstin

 

[Funka]

here i am again, hitting a wall. i've been able to get my procedure to &quot;check out&quot; a record using the one update statement instead of two (select then update) as you had suggested, but when i stress test it i get empty recordsets returned and duplicate recordIDs are handed out... it doesn't seem like you can use an UPDLOCK on an update statement so i'm back to square one. i did find out, however, that in the format of my first posting, using just the &quot;WITH (UPDLOCK)&quot; solved my problem. having combined it with READPAST was causing me my grief.

so, thank you again for your help, i sure wish i could benefit from the simplicity of your &quot;two-in-one&quot; model, but am not able to get it working as per my needs (i.e., no dupes and no EOFs under constant heavy load). thanks!