January 9, 2008 at 12:40 pm
I have several instances of a windows service that are spread across many machines. These services query a job table to pull the next job for processing. I want to get the locking setup correctly so each service does not try to run the same job.
This is a simplified version of what I have so far but will suffice as an example:
Let's say my job table consists of a jobID and jobStatus where jobID is a unique value and jobStatus is either 0 for pending or 1 for in progress.
The stored procedure that each service executes to get the next job for processing has the following syntax:
create procedure getNextJob
jobID int output
as
begin transaction
--get next job
select @jobID = jobID
from jobs with (readpast)
where jobStatus = 0
-- update this as in progress
update jobs with (rowlock,holdlock)
set jobStatus = 1
where jobID = @jobID
commit
go
Is this the best way of ensuring that each service will grab a unique value or is there a better strategy?
Thanks,
Chris
January 9, 2008 at 12:47 pm
Maybe you should try to look up sp_getapplock. I think that should help you with this issue.
-Roy
January 9, 2008 at 1:00 pm
Do your select with an update lock and with rowlock. Add a TOP 1 to the select. Try adding an index on JobStatus, JobID if there isn't one already
Also there's no need for the holdlock on the update, as its the last statement in the transaction.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 9, 2008 at 1:43 pm
I will test UPDLOCK. I've had some deadlocking issues when using that in a select/update transaction in the past but that was with a different version of sql server. Maybe 2005 handles it a little better....
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply