Sequential read in sql server

Question

We have a .Net service that reads data from sql server. do some processing and then again update the sql server table. Now we need to run multiple instance of the this service. So i am presented with below issue.

1. Since we don't have any column in sql server end that let me know whether a row has been read or not. so i may get same value in different instance.

In my opinion i need to create a column to specify a row is read or not. Can you suggest anything like MSMQ or Service Broker

Answer 1

Polling the database by multiple instances of the service is an option you need to take care of several issues:

1. The select and update should be in one atomic transaction
2. In case your service pulls some records to process and then crashes, how will you return them to the database
3. You might get deadlocks, so your c# code should implement retries with random wait

You could use code like this to have atomic update/select by utilizing an UPDATE statement with OUTPUT clause:

DECLARE @fetched TABLE (id INT)
UPDATE D
SET D.Processed=1,D.ProcessId=123,D.StartTime=GETDATE()
OUTPUT inserted.id INTO @fetched
FROM queueData D
     INNER JOIN (
        SELECT TOP 10 T10.id
        FROM queueData T10
        WHERE T10.Processed=0
        ORDER BY id
     ) Top10 ON D.id=Top10.id

SELECT * FROM @fetched

You can use MSMQ or Azure Service Bus directly, but also through the MassTransit API that will abstract details of the queue system for you.

There is another option, and that is to use the SQL Server Service Broker. The advantage of this approach is that you do not need to introduce an additional dependency on MSMQ or some other queue system, you just need to enable the Service Broker inside SQL server and then use TSQL to put messages in queues and to get messages from queues. Furthermore, SQL Server will take care of transactions.

-- enable the Service Broker
ALTER DATABASE test SET ENABLE_BROKER
GO

-- Message Type
CREATE MESSAGE TYPE TestMessage VALIDATION = NONE
GO

-- Contract
CREATE CONTRACT TestContract (TestMessage SENT BY INITIATOR)
GO

-- Send Queue
CREATE QUEUE TestSendQueue
GO

-- Receive Queue
CREATE QUEUE TestReceiveQueue

-- Create Send Service
CREATE SERVICE TestSendService ON QUEUE TestSendQueue (TestContract)
GO

-- Create Receive Service
CREATE SERVICE TestReceiveService ON QUEUE TestReceiveQueue (TestContract)
GO

-- Dialog using on contract
DECLARE @testDialog uniqueidentifier
DECLARE @Message VARCHAR(128)
BEGIN DIALOG CONVERSATION @testDialog 
        FROM SERVICE TestSendService
        TO SERVICE 'TestReceiveService'
        ON CONTRACT TestContract
WITH ENCRYPTION = OFF

-- Send messages
SET @Message = 'Very First Message';
SEND ON CONVERSATION @TestDialog MESSAGE TYPE TestMessage (@Message)
GO

-- Receive messages from Receive Queue
RECEIVE TOP(1) CONVERT(VARCHAR(MAX), message_body) AS Message
FROM TestReceiveQueue
GO

The above code is partly from http://blog.sqlauthority.com/2009/09/21/sql-server-intorduction-to-service-broker-and-sample-script/.

Answer 2

You can consider creating a table, something like ReservedRows to keep track of rows that are being processed, it contains IDs of rows in target table.

Then in the service, first lock the ReservedRows table, then read the data you want to process, excluding the ones already in ReservedRows, insert into ReservedRows the ids of the records you are reading, then unlock the table. Since you inserted the ids of rows you are processing, other instances of the service won't process those until you are done.

Once you are done processing the rows and updating the target table. Delete the IDs of rows you just processed from the ReservedRows table so that other instances can process those later.