C# 2 instances of same app reading from same SQL table, each row processed once

Question

I'm writing a Windows Service in C# in .Net 4.0 to forfill the following functionality:
At a set time every night the app connects to SQL Server, opens a User table and for each record retrieves the user's IP address, does a WCF call to the user's PC to determine if it's available for transacting and inserts a record into a State table (with y/n and the error if there is one).

Once all users have been proccessed the app then reads each record in the State table where IsPcAvailable = true, retrieves a list of reports for that user from another table and for each report fetches the report from the Enterprise doc repository, calls the user's PC via WCF and pushes the report onto their harddrive, then updates the state table to its success.

The above senario is easy enough to code if single threaded running on 1 app server; but due to redundancy & performance there will be at least 2 app servers doing exactly the same thing at the same time.

So how do I make sure that each user is processed only once in firstly the User table then the State table (same problem) as fetching the reports and pushing them out to PCs all across the country is a lengthy process. And optimally the app should be multithreaded, so for example, having 10 threads running on 2 servers processing all the users.

I would prefer a C# solution as I'm not a DataBase guru :) The closest I've found to my problem is:
SQL Server Process Queue Race Condition - it uses SQL code
and multithreading problems with the entity framework, I'm probally going to have to go 1 layer down and use ADO.net?

score 1 · Answer 1 · answered Jun 07 '12 at 22:22

I would recommend using the techniques at http://rusanu.com/2010/03/26/using-tables-as-queues/ That's an excellent read for you at this time.

Here is some sql for a fifo

create procedure usp_dequeueFifo
as
  set nocount on;
  with cte as (
    select top(1) Payload
      from FifoQueue with (rowlock, readpast)
    order by Id)
  delete from cte
    output deleted.Payload;
go

And one for a heap (order does not matter)

create procedure usp_dequeueHeap 
as
  set nocount on;
  delete top(1) from HeapQueue with (rowlock, readpast)
      output deleted.payload;      
go

This reads so beautifully its almost poetry

score 1 · Answer 2 · answered Jun 07 '12 at 22:26

You could simply just have each application server polling a common table (work_queue). You can use a common table expression to read/update the row so you don't have them stepping on each other.

;WITH t AS
(
    SELECT TOP 1 * 
    FROM work_queue WHERE NextRun <= GETDATE()
    AND IsPcAvailable = 1
)
UPDATE    t  WITH (ROWLOCK, READPAST)
SET
     IsProcessing = 1,
     MachineProcessing = 'TheServer'
OUTPUT INSERTED.*

Now you could have a producer thread in your application checking for unprocessed records periodically. Once that thread finishes it's work, it pushes the item in to a ConcurrentQueue and consumer threads can process the work as it's available. You can set the number of consumer threads yourself to the optimum level. Once the consumer threads are done, it simply sets IsProcessing = 0 as to show that the PC was updated.

C# 2 instances of same app reading from same SQL table, each row processed once

2 Answers2