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)

Answer 3

I cannot add a comment to Andre's answer, but I am concerned about this comment "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."

The repeatable read only says that you will hold all locks until the end of a transaction. When you use this isolation level in a transaction and read a row (say the max value) a "Shared" lock is issued and will be held until the transaction completes. This shared lock will prevent another thread from updating the row (the update would try to apply an Exclusive lock on the row and that would be blocked by the existing shared lock), but it will allow another thread to read the value (the second thread will put another shared lock on the row - which is allowed (that is why they are called shared locks)). So to make the above statement correct, it would need to say "The IsolationLevel.RepeatableRead will apply a lock to all rows that are read in such a way that a Thread 2 cannot update Table A if Table A has been read by Thread 1 and Thread 1 did not complete the Transaction."

For the original question, you would need to use a repeatable read isolation level AND escalate the lock to an Exclusive lock to prevent two processes from reading and updating the same value. All the solutions would involve mapping EF to custom SQL (as escalating the lock type is not built into EF),. You could use jocull answer or you could use an update with an output clause to lock the rows (update statements always get Exclusive locks and in 2008 or above can return a result set).

Answer 4

You could try passing a UPDLOCK hint to the database and just lock specific rows.. So that what it selects to update it also aquires an exclusive lock on so it can save its changes (rather than just acquiring a readlock in the beginning, that it later tries to upgrade later when saving). The Holdlock suggested by jocull above is also a good idea too.

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

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

Answer 5

Wanted to share my combination of the above answers:

public class Repository<TEntity, TKey>
    : IRepository<TEntity, TKey> where TEntity : class, IEntity<TKey>
{
    protected readonly DbContext DbContext;

    ...

    private sealed class Transaction<TTransactionEntity> : IDisposable
    {
        private readonly IDbContextTransaction dbTransaction;

        public Transaction(DbContext context)
        {
            var tableName = context.Model
                .FindEntityType(typeof(TTransactionEntity))
                .GetTableName();

            this.dbTransaction = context.Database
                .BeginTransaction(IsolationLevel.RepeatableRead);

            context.Database
                .ExecuteSqlRaw($"SELECT TOP 0 NULL FROM {tableName} WITH (TABLOCKX)");
        }

        public void Dispose()
        {
            this.dbTransaction.Commit();
            this.dbTransaction.Dispose();
        }
    }

    public IDisposable LockingTransaction()
        => new Transaction<TEntity>(this.DbContext);
}

usage:

using (this.unitOfWork.MyRepository.LockingTransaction())
{
    ...
}