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:

1. 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.
2. 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.
3. 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.)
4. 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?