Strategies to issue unique records via db?

Question

Hi,

We have more than 1 instances of a certain exe running from different locations. An exe is supposed to fetch a set of records and do some work based on them. The set of records fetched from exe A should not be fetched by exe B and vice versa. Exes A & B are the same exes; they are running from different locations. The number of instances may increase or decrease. All exes might run simultaneously at times.

So coming to my question...what is the best way I can tackle this problem?

I've thought about using transactions but the table that acts as the source for the exe is also used by others (scheduled jobs, websites, etc). The scheduled jobs insert data into the source table.

However if I had to use transactions can I start a transaction with BEGIN TRAN and then select the data from the source table using the WITH (TABLOCKX) hint. If I were to do this on views would it affect the actual underlying table/tables.

I just want to know what are the strategies used to deal with this...

Answer 1

One option might be to run an UPDATE query that "marks" which items the exe is going to fetch (with a where clause constraining it to only marking items which aren't already marked). Then do a second SELECT which pulls out the marked items. Thus you can run the SELECT query without worrying about delay between the UPDATE and it. As long as UPDATEs are run atomically (via a transaction that could be quickly closed), you shouldn't have concurrency issues.

Answer 2

You want to avoid race conditions between processes. My answer here goes into details: SQL Server Process Queue Race Condition

Transactions are not much use: it's the locking strategy you have to think about, with the knock on effect on concurrency.