Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Multi-threaded processing of a table Expand / Collapse
Author
Message
Posted Friday, October 30, 2009 7:29 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 9, 2013 2:43 PM
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.
Post #811559
Posted Friday, October 30, 2009 11:51 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 7:18 AM
Points: 531, Visits: 433
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





Post #811773
Posted Friday, October 30, 2009 11:55 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 9, 2013 2:43 PM
Points: 17, Visits: 55
Ah, so you're blocking only for the time to change the status. Nice! Thanks!
Post #811776
Posted Saturday, October 31, 2009 12:54 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 12:10 AM
Points: 141, Visits: 860
Good article:
http://www.sqlservercentral.com/articles/Locking/67952/
Post #811980
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse