What SQL Server 2005/2008 locking approach should be used to process single rows of a table in multiple server instances?

Question

I need to develop a server application (in C #) that will read rows from a simple table (in SQL Server 2005 or 2008), do some work, such as calling a web service, and then update the rows using status (success, error) .

It looks pretty simple, but harder when I add the following details of the application:

• Multiple application instances should work simultaneously for load balancing and fault tolerance. Typically, an application will be deployed to two or more servers and will access the same database table at the same time. Each row of the table should be processed only once , so a common synchronization / locking mechanism should be used between several instances of the application.

• When an application instance processes a set of strings, other application instances do not need to wait for it to complete in order to read another set of strings awaiting processing.

• If the application crashed, manual intervention should not occur in the rows of the table that were being processed (for example, removing the temporary state used to lock the application in the rows that processed the instance of the failure).

• Lines must be processed in the order of the queue, i.e. process the oldest lines first.

Although these details do not look too complicated, I am having problems with the solution.

I have seen suggestions for hints for locking, such as XLOCK , UPDLOCK , ROWLOCK , READPAST , etc., but I do not see combinations of hints combinations that will allow me to implement these details.

Thanks for any help.

Hi,

Nuno Guerreiro

Answer 1

I originally proposed SQL Server Service Broker for this. However, after some research, it turns out that this is probably not the best way to deal with this problem.

What remains for you is the architecture of the table you requested. However, as you have found, it is unlikely that you will be able to come up with a solution that meets all the specified criteria, due to the high complexity of blocking, transactions, and the pressure imposed on such a scheme by high concurrency and high transactions per second.

Note. I am currently studying this issue and will come back to you later. The following script was my attempt to fulfill these requirements. However, he suffers from frequent dead ends and processes out of order. Please stay tuned and in the meantime consider the destructive read method (DELETE WITH OUTPUT or OUTPUT INTO).

 SET XACT_ABORT ON; -- blow up the whole tran on any errors SET TRANSACTION ISOLATION LEVEL READ COMMITTED; BEGIN TRAN UPDATE X SET X.StatusID = 2 -- in process OUTPUT Inserted.* FROM ( SELECT TOP 1 * FROM dbo.QueueTable WITH (READPAST, ROWLOCK) WHERE StatusID = 1 -- ready ORDER BY QueuedDate, QueueID -- in case of items with the same date ) X; -- Do work in application, holding open the tran. DELETE dbo.QueueTable WHERE QueueID = @QueueID; -- value taken from recordset that was output earlier COMMIT TRAN; 

In the case when several / several rows are blocked at once by one client, there is a possibility that the row lock will increase to a degree, page or table lock, so keep this in mind. In addition, usually long-running transactions that support locks are large, no, no. It may work in this special use case, but I'm afraid that high tps by several clients will cause the system to break. Note that, as a rule, the only processes that request your queue table should be those that do the work in the queue. Any reporting processes should use READ UNCOMMITTED or WITH NOLOCK to avoid queuing in some way.

What does it mean that strings are processed out of order? If the application instance crashes when another instance successfully completes the lines, this delay is likely to delay at least one line at its completion, which will lead to an incorrect processing order.

If the transaction / lock method above does not meet your satisfaction, another way to deal with the application crash is to provide the names of your instances, and then set up a monitoring process that can periodically check if these named instances are running. When the named instance is started, there will always be reset any unprocessed lines that have their instance id (something simple like "instance A" and "instance B"). In addition, the monitoring process checks to see if the instances are running, and if one of them does not work, reset the lines for this missing instance, allowing any other instances to run. There would be a slight lag between failure and recovery, but with proper architecture, this could be perfectly reasonable.

Note. The following links should be called:

• information about XLOCK
• Tables as Queues

Answer 2

This is a typical table as a queue template, as described in Using Tables as Queues . You must use the pending queue, and the dequeue transaction must also schedule a retry in a reasonable timeout. Unable to hold locks for the duration of web calls. If successful, you delete the pending item.

In addition, you should be able to deactivate batch loading, one at a time, too slowly if you switch to a serious load (100 and thousands of operations per second). Therefore, taking an example of a pending queue from a related article:

 create table PendingQueue ( id int not null, DueTime datetime not null, Payload varbinary(max), cnstraint pk_pending_id nonclustered primary key(id)); create clustered index cdxPendingQueue on PendingQueue (DueTime); go create procedure usp_enqueuePending @dueTime datetime, @payload varbinary(max) as set nocount on; insert into PendingQueue (DueTime, Payload) values (@dueTime, @payload); go create procedure usp_dequeuePending @batchsize int = 100, @retryseconds int = 600 as set nocount on; declare @now datetime; set @now = getutcdate(); with cte as ( select top(@batchsize) id, DueTime, Payload from PendingQueue with (rowlock, readpast) where DueTime < @now order by DueTime) update cte set DueTime = dateadd(seconds, @retryseconds, DueTime) output deleted.Payload, deleted.id; go 

Upon successful processing, you remove the item from the queue using the identifier. In the event of a failure or failure, it will automatically retry after 10 minutes. You might think that you should be aware that until HTTP offers transactional semantics, you can never do this with a 100% sequence of semantics (for example, you guarantee that no element will be processed twice). You can get a very high margin for error, but there will always be a moment when the system can crash after the HTTP call succeeds before updating the database and cause the same element to repeat again, since you cannot distinguish this case from when the system crashed before calling HTTP.

Answer 3

You cannot do this with SQL transactions (or rely on transactions as the main component here). In fact, you can do it, but you shouldn't. Transactions are not intended to be used in this way for long locks, and you should not abuse them like this.

Saving a transaction for this long enough (retrieving rows, calling a web service, returning to some updates) is simply not very good. And there is no optimistic locking isolation level that allows you to do what you want.

Using ROWLOCK is also not a good idea, because it is so. Hint. Subject to escalation locks and can be converted to table locks.

Can I offer one entry point to your database? I think it fits in the pub / subproject. Thus, there will be only one component that reads / updates these records:

• Reads batches of messages (enough for all your other instances to consume) - 1000, 10000, whatever you think is necessary. This makes these batches available to other (parallel) components through some queues. I will not say MSMQ :) (the second time today I recommend it, but it really fits in your case too).
• It marks messages as in progress or something similar.
• Your customers are attached, transactionally, to the incoming queue and perform their actions.
• When they are ready, after calling the web service, they send messages to the outgoing queue.
• The central component selects them and inside the distributed transaction , makes an update in the database (if this fails, messages remain in the queue). Since this is the only way that this operation can be performed, you will not have problems with concurrency. At least not in the database.
• At the same time, he can read the next pending packet, etc.