This question circulates through the office about once a year. “Um, I have Windows service which processes requests which are queued in a database table. Everything is working great, but I would like to add a second instance of the service and I need to know the best way to ensure the services don’t step on each other. In other words, how do I mark each row as it is being processed so it won’t be picked up again?”
Here are the ingredients in my current ideal solution:
- The table must include the following columns: a Unique Identifier such as RequestID, Status, LockedBy, and LockedTime. I also like to include RequestedTime and CompletionTime columns as I’ve found that capturing these values help when/if system stats are required.
- The key is to lock the current request at the time of processing. To do so, you should use a transaction and an update lock.
- I think it is important to pull requests off the queue in an intelligent order. FIFO is the best bet under most circumstances. In this case, the RequestedTime column becomes imperative (unless your RequestID is an auto-incrementing identity column and you are comfortable ordering on it.)
- Finally, I like to make sure no request is left behind. Let’s say Service A locks a request for processing and then the application server crashes. I want the lock to eventually expire so Service B can pick up the row and save the day.
Assuming new requests are added to the queue as ‘Pending’ , one may use a routine similar to the one below to safely pull the next request off the queue:
create procedure [GetNextItemInQueue] ( @LockedBy varchar(75) ) as set nocount on declare @Error int, @ExitCode int, @RowCount int, @PendingStatus int, @MinutesBeforeBreakLock int, @RequestId int select @Error = 0, @ExitCode = 0, @RowCount = 0, @PendingStatus = 1, @MinutesBeforeBreakLock = 30 begin tran ProcessQueue select top 1 @RequestId = RequestId from Queue with (updlock) where Status = @PendingStatus and (LockedTime IS NULL OR DateDiff(mi, LockedTime, GetDate()) >= @MinutesBeforeBreakLock) order by RequestedTime Asc select @RowCount = @@RowCount, @Error = @@Error if (@RowCount = 0) goto ExitProc if (@Error <> 0) goto ErrorHandler update Queue set LockedBy = @LockedBy, LockedTime = GetDate() where RequestId = @RequestId select @Error = @@Error if (@Error <> 0) goto ErrorHandler select @RequestId as RequestId select @Error = @@Error if (@Error <> 0) goto ErrorHandler goto ExitProc ErrorHandler: raiserror (@Error, 16, 1) if @@trancount > 0 rollback tran ProcessQueue select @ExitCode = -100 ExitProc: commit tran ProcessQueue return (@ExitCode)
Once the request is processed, a database call will be made to update the row’s Status to ‘Complete’ and CompletionTime.
Note, you may choose to change the status of each row from ‘Pending’ to ‘Processing’ when you do the initial lock. This is perfectly fine, but I’ve found that is unnecessary as the Processing status can be determined based on the value of other columns.
Again, that’s my current preferred implementation. Perhaps there are better solutions out there?