Producer-Consumer-Problem using SQL Server and ASP.NET

Classic producer-consumer-problem.

I have x app servers which write records in a DB table (same DB).

On each server, a service is running which polls the DB table and is supposed to read the oldest entry, process it and delete it. The issue is now that the services get into a race condition: service on server A starts reading, server B starts reading the same record. I'm a bit stuck on this...I have impleme开发者_运维知识库nted producer-consumer so often but never across server barriers.

The server cannot talk to each other except over the DB.

Environment is SQL Server 2005 and ASP-NET 3.5.

If you pick up work in a transactional way, only one server can pick it up:

set transaction isolation level repeatable read

update  top 1 tbl
set     ProcessingOnServer = HOST_NAME()
from    YourWorkTable tbl
where   ProcessingOnServer is null
        and Done = 0

Now you can select the details, knowing the work item is safely assigned to you:

select  *
from    YourWorkTable tbl
where   ProcessingOnServer = HOST_NAME()
        and Done = 0

The function host_name() returns the client name, but if you think it's safer you can pass in the hostname from your client application.

We usually add a timestamp, so you can check for servers that took too long to process an item.