How to lock a table while reading using Entity Framework?

Question

I have SQL Server (2012) that I am accessing using Entity Framework (4.1). In the database, I have a table called URL in which an independent process passes the new URLs. The entry in the URL table can be in the New, In Progress, or Processed state.

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

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 read two different computers and update the same record in the database.

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

Answer 1

This answer does not interfere with simultaneous reading! This is not the right answer to this question!

Author here: I was wrong. I cannot delete this answer because it is an accepted answer. @Gilad, if you do not accept my answer, I will delete it.

Try the following:

 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(); } 

IsolationLevel.RepeatableRead apply the lock to all read lines so that Thread 2 cannot read from Table A if Table A was read by Thread 1 and Thread 1 did not complete the transaction. (According to the comments this is not so)

Just pay attention to TransactionScopes:

When you surround your code with TransactionScope , you are not creating any transactions, you are just creating an area in which a transaction may be required. When you read the insinde data in your using , the Entity Framework will create the transaction, but it will not be anymore, because now you are responsible for the transaction due to TransactionScope. That is: you define IsolationLevel, and you are responsible for committing or rolling back the transaction. By default, the isolation level will use write lock. That is, if I write a URL, it will block the table of URLs for reading and writing. To apply a read lock, you use IsolationLevel.RepeatableRead .

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

Answer 2

I could only do this manually by issuing the lock statement to the table. This makes the table lock complete , so be careful with it! In my case, it was useful for creating a queue that I did not want several processes to touch 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 transactions differently. It was a failure for us after some transformations.

 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 3

I cannot add a comment to Andre, but I am concerned about this comment: "IsolationLevel.RepeatableRead will apply a lock to all rows that are read in such a way that Thread 2 cannot read from table A if table A was read in Thread 1 and Thread 1 did not complete the transaction. "

Repeated reading only says that you will hold all locks until the end of the transaction. When you use this isolation level in a transaction and read the line (say, the maximum value), a “Shared” lock is issued and will be held until the transaction is completed. This shared lock will not allow another thread to update the row (the update will try to apply an exclusive lock in the row and block with the existing shared lock), but it will allow the other thread to read the value (the second thread will add another shared lock in the row - which is allowed (therefore they are called shared) locks)). Therefore, to make the above statement correct, you need to say: "The IsolationLevel.RepeatableRead will apply a lock to all rows that are read in such a way that Thread 2 cannot update table A if table A was read by thread 1 and thread 1 did not complete transaction. "

For the original question, you will need to use the isolation level of the repeated read AND increase the lock to the Exclusive lock to prevent the reading and updating of two processes. All solutions would include mapping EF to user SQL (since escalation of the lock type is not built into EF). You can use the jocull response, or you can use the update with an offer of output to block rows (update statements always get an Exclusive lock and can return a result set in 2008 or higher).

Answer 4

The answer provided by @jocull is great. I suggest this setting:

Instead of this:

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

Do it:

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

This is more common. You can create a helper method that simply takes the table name as a parameter. There is no need to know the data (otherwise column names), and there is no need to actually retrieve the record by pipe (aka TOP 1 )

Answer 5

You can try passing the UPDLOCK hint to the database. Thus, what he chooses also includes an exclusive lock to save the changes (and not just a read lock, which he later tries to update when saving). Holdlock suggested by jocull above is also a good idea.

 private static TestEntity GetFirstEntity(Context context) { return context.TestEntities .SqlQuery("SELECT TOP 1 Id, Value FROM TestEntities WITH (UPDLOCK)") .Single(); } 

I strongly recommend considering optimistic concurrency: https://www.entityframeworktutorial.net/EntityFramework5/handle-concurrency-in-entity-framework.aspx