How can I lock a table on read, using Entity Framework?

Question

I have a SQL Server (2012) which I access using Entity Framework (4.1). In the database I have a table called URL into which an independent process feeds new URLs. An entry in the URL table can be in state "New", "In Process" or "Processed".

I need to access the URL table from different computers, check for URL entries with status "New", take the first one and mark it as "In Process".

var newUrl = dbEntity.URLs.FirstOrDefault(url => url.StatusID == (int) URLStatus.New); if(newUrl != null) {     newUrl.StatusID = (int) URLStatus.InProcess;     dbEntity.SaveChanges(); } //Process the URL 

Since the query and update are not atomic, I can have two different computers read and update the same URL entry in the database.

Is there a way to make the select-then-update sequence atomic to avoid such clashes?

Answer 1

I was only able to really accomplish this by manually issuing a lock statement to a table. This does a complete table lock, so be careful with it! In my case it was useful for creating a queue that I didn't want multiple processes touching at once.

using (Entities entities = new Entities()) using (TransactionScope scope = new TransactionScope()) {     //Lock the table during this transaction     entities.Database.ExecuteSqlCommand("SELECT TOP 1 KeyColumn FROM MyTable WITH (TABLOCKX, HOLDLOCK)");      //Do your work with the locked table here...      //Complete the scope here to commit, otherwise it will rollback     //The table lock will be released after we exit the TransactionScope block     scope.Complete(); } 

Update - In Entity Framework 6, especially with async / await code, you need to handle the transactions differently. This was crashing for us after some conversions.

using (Entities entities = new Entities()) using (DbContextTransaction scope = entities.Database.BeginTransaction()) {     //Lock the table during this transaction     entities.Database.ExecuteSqlCommand("SELECT TOP 1 KeyColumn FROM MyTable WITH (TABLOCKX, HOLDLOCK)");      //Do your work with the locked table here...      //Complete the scope here to commit, otherwise it will rollback     //The table lock will be released after we exit the TransactionScope block     scope.Commit(); } 

Answer 2

The answer that @jocull provided is great. I offer this tweak:

Instead of this:

"SELECT TOP 1 KeyColumn FROM MyTable WITH (TABLOCKX, HOLDLOCK)" 

Do this:

"SELECT TOP 0 NULL FROM MyTable WITH (TABLOCKX)" 

This is more generic. You can make a helper method that simply takes the table name as a parameter. No need to know of the data (aka any column names), and there is no need to actually retrieve a record down the pipe (aka TOP 1)