1

Using SQL Server 2012, ASP.NET 4.6.2, and EF6. I have a table of urls. each url has to go through a number of third party processes via API calls, and the state reflected in that table. I'm planning to use scheduled background processes of some sort to kick those processes off. I've come up with a structure like:

Id              int (PK)
SourceUrl       varchar(200)
Process1Status  int
Process2Status  int

When rows go into the table, Status flags will be 0 for AwaitingProcessing. 1 would mean InProgress, and 2 would be Complete.

To ensure the overall processing is quicker, I want to run these two processes in parallel. In addition, there may be multiple instances of each of these background processors picking up urls from the queue.

I'm new to multi threaded processing though, so I'm a bit concerned that there will be some conflicting processing going on.

What I want to be able to do is to ensure that no Process1runner selects the same row as another Process1runner, by ensuring that Process1Runner takes only 1 item and flags that it is currently in progress. I'd also like to ensure that when separate third party services call back to notification urls, that no status update is lost if two processes are attempting to update Process1Status and Process2Status at the same time.

I've seen two possible relevant answers: How can I lock a table on read, using Entity Framework?

and: Get "next" row from SQL Server database and flag it in single transaction

But I'm not much clearer about which route I should take for my needs. Could someone point me in the right direction? Am I on the right track?

Community
  • 1
  • 1
Andrew Johns
  • 705
  • 1
  • 6
  • 26
  • To ensure the overall processing is quicker --- how to measure the performance? and is it really slow not using multi-thread? – Lei Yang Nov 21 '16 at 01:38
  • @LeiYang Come on, it's obvious what he's talking about in the question. – tymtam Nov 21 '16 at 01:50
  • What problem exactly you have with approach mentioned in your second link (UPDATE ... OUTPUT)? This is the way to go if I understand your situation correctly. – Evk Nov 21 '16 at 08:15

1 Answers1

-1

If by design multiple actors need access the same row of data I would split the data to avoid this situation.

My first thought is to suggest building a UrlProcessStatus table with URLId, ProcessId, and Status columns. This way the workers can read/write their data independently.

tymtam
  • 31,798
  • 8
  • 86
  • 126