Linq-to-Sql Read-Test-Write as one operation

Question

I have Tasks table: Id (PK), TaskName, Status
Status is one of: Queued, Busy, Complete.
I want to use multiple threads to process the tasks, and for that I need to be able to do in one operation:

var task = db.Tasks.FirstOrDefault(t=>t.Status == (byte) TaskStatus.Queued);
task.Status = (byte) TaskStatus.Busy;
db.SubmitChanges();

Obviously if operation is not atomic I can get concurrency issues. What is (if one exists) an intended way to do the above using Linq-to-Sql?

I know I can do that with 1) storproc or 2) db.ExecuteCommand("...") or 3) handle the conflict with try/catch - but I want to be sure there is no a better way.

I know it is a very basic question, but I wasn't able to find a definite answer to this.

Answer 1

If you are wanting to be sure that this is happening atomically in the database (regardless of what thread or application is calling it), you probably should do it in a sproc and lock at an appropriate level there. The sproc should retrieve and update the record, then release and return it (Ack! I don't mean the TSQL return statement, but you know I mean select it, I trust!)

So L2S just calls the sproc and gets back a task to work on, already set as Busy. L2S neither knows (nor cares) what/how things were locked - and therefore can not complicate matters and introduce more chances for deadlocks.