How to write SQL to get a record to process in a cluster of processing computers

  • Article

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