Gilad Gilad - 3 years ago 190
C# Question

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

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 Source

This answer not preventing simultaneous reads! It is not the correct answer to this question!

Author here: I was wrong. I cannot delete this answer because it's the accepted answer. @Gilad, if you un-accept my answer, I will delete it.

Try this:

using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions { IsolationLevel = IsolationLevel.RepeatableRead }))
{
    var newUrl = dbEntity.URLs.FirstOrDefault(url => url.StatusID == (int) URLStatus.New);
    if(newUrl != null)
    {
        newUrl.StatusID = (int) URLStatus.InProcess;
        dbEntity.SaveChanges();
    }
    scope.Complete();
}

The IsolationLevel.RepeatableRead will apply a lock to all rows that are read in such a way that a Thread 2 cannot read from Table A if Table A has been read by Thread 1 and Thread 1 did not complete the Transaction. (Per the comments it appears not to be the case)

Just a side note about TransactionScopes:

When you surround your code with a TransactionScope you are not creating any transaction, you are just creating a scope in which a transaction may be required. When you read data insinde your using, Entity Framework would create a transaction, but it won't anymore, because now, due to the TransactionScope, you are in charge of the transaction. That is: You will determine the IsolationLevel and you are responsible for commiting or rolling back the transaction. By default, the isolation level will apply a lock on write. That is, if I write an URL, it will lock the URLs table for reading and writting. In order to apply a lock on read, you use IsolationLevel.RepeatableRead.

If you create more transactions inside your TransactionScope it will be promoted to a distributed transaction, but that is outside the scope of my answer.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download