"Work Queue Table" Pattern--How consumers "pop" next task off queue without duplication between consumers?

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?

Example Rows:

TaskID=1 CodeForWorkToBeDone='WashClothes'  AssignedToConsumerID=1
TaskID=2 CodeForWorkToBeDone='CleanRoom'  AssignedToConsumerID=null
...

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.

Any ideas?

This could be solved if you could add a "mutex" keyword like such if it existed:

BEGIN TRANSACTION MUTEX

Pete
CREATE TABLE Task(
	TaskID int,
	CodeForWorkToBeDone varchar(50),
	AssignedToConsumerID int
)
 
BEGIN TRANSACTION
 
--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*
 
COMMIT

Open in new window

LVL 2
ZuZuPetalsAsked:
Who is Participating?
 
tigin44Connect With a Mentor Commented:
yes you can do that...
just set transaction property to SERIALIZABLE to prevent  other users from seeing uncommtted data.
u can set this in a procedure easily e.g.


SET TRANSACTION ISOLATION LEVEL
     SERIALIZABLE

BEGIN TRANSACTION
 
--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*
 
COMMIT
0
 
ZberteocConnect With a Mentor Commented:
What if instead of selecting and updating the same task table you would have another table like

CustomerTask:
------------------------------------------------
CustomerTaskID (identity, PK clustered)
------------------------------------------------
CustomerID,
TaskID,
DateAssigned - default getdate()
-------------------------------------------------
( CustomerID, TaskID - unique index)


and insert to it form task with cutomerid. Because the index is unuque it will prevent the same task to be assigned to 2 different users. Always look for the user tasks in this second table. Inserts are faster than updates.
0
 
Mark WillsTopic AdvisorCommented:
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I will leave the following recommendation for this question in the Cleanup topic area:
   Accept: tiggin44 {http:#22915975}

Any objections should be posted here in the next 4 days. After that time, the question will be closed.

mark_wills
EE Cleanup Volunteer
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.