Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Multi-threaded processing of a table


Multi-threaded processing of a table

Author
Message
Ed-997158
Ed-997158
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 55
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.
peterhe
peterhe
Mr or Mrs. 500
Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)

Group: General Forum Members
Points: 534 Visits: 448
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



Ed-997158
Ed-997158
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 55
Ah, so you're blocking only for the time to change the status. Nice! Thanks!
diamondgm
diamondgm
SSC-Enthusiastic
SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)

Group: General Forum Members
Points: 149 Visits: 938
Good article:
http://www.sqlservercentral.com/articles/Locking/67952/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search