• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 660
  • Last Modified:

"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

0
ZuZuPetals
Asked:
ZuZuPetals
2 Solutions
 
tigin44Commented:
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
 
ZberteocCommented:
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now