Multi-threaded processing of a table

  • 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.

  • 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

  • Ah, so you're blocking only for the time to change the status. Nice! Thanks!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply