Geek Answers Handbook

How would you create this messaging system in .NET / SQL Server?

Let's say I have a SQL Server database table with X (> 1,000,000) records in it that need to be processed (receive data, perform external actions, update status in db) one after another by some workflows (console applications, Windows services, workflows Azure roles, etc.). I have to guarantee that each line is processed only once. Ideally, exclusivity is guaranteed no matter how many machines / processes have been created to process messages. I am mainly concerned about two SELECTs that capture the same rows at the same time.

I know that there are better data warehouses there, but I do not have such luxury for this project. I have ideas for this, but I am looking for more.

+5
source share
2 answers

I had such a situation.

Add a column InProcessto the table, default = 0. In the consumer process:

UPDATE tbl SET Inprocess = @myMachineID WHERE rowID = 
    (SELECT MIN(rowID) WHERE InProcess = 0)

Now this machine owns the string, and you can request its data without any fear. Typically, the following line will look something like this:

SELECT * FROM tbl WHERE rowID = 
    (SELECT MAX(rowID) FROM tbl WHERE ProcessID = @myMachineID)

You also need to add the string flag Doneto the string so that you can determine if the string was declared, but the processing was incomplete.

Edit

UPDATEgets an exclusive lock (see MSDN ). I am not sure if SELECTthe subquery is allowed to share with UPDATE; if so, you will have to put them in the transaction.

@Will A , , :

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

... .

@Martin Smith , OUTPUT ( SQL 2005).

, - . , SO, ?

: 2004 , -, URL- , URL-to-crawl . , .

+7

, N , "" . : NotProcessed (0), (2), (1). sql:

update table_of_records_to_process
set processed = 2
where record_id = 123456
and processed = 0

... , - , 2. , 123456 2:

select count(*)
from table_of_records_to_process
where record_id = 123456
and processed = 2

... . , . , - , N .

0

More articles:


All Articles