October 30, 2009 at 7:29 am
What is the appropriate approach to have one thread lock a record for exclusive use, and have subsequent threads skip that record and read the next available record?
I'm using a table as a task queue. Each record represents a task. A task processor should read the top record, use the information to execute a task, and on successful completion, delete the record and write its values to an archive table.
I want to have a pool of task processors, so I want to ensure that no two task processors read the same record, duplicating the execution of the task. I can lock the record, but then subsequent task processors will block, effectively resulting in single-threading.
TIA for insights.
October 30, 2009 at 11:51 am
You need to have Status column in your task table, e.g. TaskID int not null, Status bit not null
You can have code something like:
DECLARE @TaskID int
BEGIN TRAN
SELECT @TaskID = MIN(TaskID) FROM TaskTable WITH (HOLDLOCK) WHERE Status=0
UPDATE TaskTable
SET Status=1
WHERE TaskID=@TaskID
-- Process the task....
COMMIT TRAN
October 30, 2009 at 11:55 am
Ah, so you're blocking only for the time to change the status. Nice! Thanks!
October 31, 2009 at 12:54 am
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply