How to write SQL to get a record to process in a cluster of processing computers
Working with a friend at work, we were discussing how to create some SQL to go after records in a table and process in a farm of computers (processing cluster). After few minutes of thinking I came up with the following.
BEGIN TRAN MyTran
UPDATE TOP 1 [dbo].[SomeTable] WITH (READPAST,UPDLOCK)
SET [IsCompleted] = 1, @RowId = ID
WHERE [IsCompleted] = 0
-- Do som manual processing on the record @RowId
COMMIT TRAN MyTran
The transaction issues the locks and keeps everything ACID. The update writes or holds a lock on the 1st available record due to the READPAST hint and the UPDLOCK hint holds the lock until the commit tran. If you have 1 machine it works fine and if you add 1000 machines then each machine grabs 1 record and proesses it. It helps with multi-concurrency and blocking that might occur without the hints.
Now this is just a form of load balancing using SQL (smile).
Comments
- Anonymous
March 01, 2008
PingBack from http://msdnrss.thecoderblogs.com/2008/03/01/how-to-write-sql-to-get-a-record-to-process-in-a-cluster-of-processing-computers/