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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy