Using table locking or some other SQL Server 2003 mechanism (without dot-net programming) is it possible to implement the following "table of tasks" pattern with semaphore or mutex-behavior on the read:
A database table (see below) containing work tasks that many many consumers selecting tasks without the possibility of two consumers running in parallel receiving the same task?
When a consumer needs a task it would run the transaction attached.
I can't implement a dot-net Mutex object to implement a mutual exclusive read because the consumers are on different dot-net servers.
This could be solved if you could add a "mutex" keyword like such if it existed:
BEGIN TRANSACTION MUTEX
CREATE TABLE Task(
--pop next item from queue
select top 1 from Task where AssignedToConsumerID is null order by TaskID
--mark it off as being worked by me
update Task set AssignedToConsumerID=*me*