UPDATE SELECT in LINQ to SQL

Question

I am writing a multithreaded service that picks up jobs to process that has the status of 1 (unprocessed). As soon as they are picked up, I need to change the status of those rows to 2 (indicates In Progress) so that another thread (that is spawned within a few seconds) does not pick up these rows for processing.

For select, I would do something like this:

var jobs = from j in db.Jobs
           where j.Status == 1
           select j;

How do I rewrite this to update the rows and also select them at the same time?

Answer 1

Use a transaction. There are some examples of how it works here.

Answer 2

since lots of threads may be stepping on each other, the easiest way is another field set to the current job or thread id:

update t set runner_id = ? where runner_id = 0
select * from t where runner_id = ?
(? = my thread or connection id)

or you could lock the table using lock or a transaction and do 2 queries: select, then update to processing

Answer 3

After your select i'd have a foreach where you update the values then commit them before moving on with the rest of the program

foreach (Job j in jobs)
{
    j.Status = 0;
}
db.SubmitChanges()

then you still have your jobs and the database is updated...

Answer 4

This is the typical queue pattern and to do it correctly and efficiently you must use update with output:

UPDATE TOP (...) table
SET status = 2
OUTPUT deleted.*
WHERE status = 1;

There is no equivalent Linq2Sql, and for this particular job you should not use Linq. Just make a stored procedure and add it to your data context explicitly, and modify the returned type to be Jobs. You will not get this right using transactions, not any other construct that will even come close to the performance of using UPDATE with OUTPUT.

I also added a TOP clause, this is fairly common in the queue pattern each reader to a limited amount of work in order to survive load spikes.