Why does this TransactionScope not block subsequent requests until the first request is done?

Question

I've been looking into transactions for two days now, perhaps I'm missing something obvious after taking in so much information. The goal here is to block simultaneous request. If condition is true, data is inserted, after which condition will be false. Simultaneous requests will both check condition before data could be inserted and then will both try to insert data.

public async Task<ActionResult> Foo(Guid ID)
{
    Debug.WriteLine("entering transaction scope");

    using (var transaction = new TransactionScope(
        TransactionScopeOption.Required,
        new TransactionOptions() { IsolationLevel = IsolationLevel.Serializable },
        TransactionScopeAsyncFlowOption.Enabled
        ))
    {
        Debug.WriteLine("entered transaction scope");

        var context = new DbContext();

        Debug.WriteLine("querying");
        var foo = context.Foos.FirstOrDefault(/* condition */);
        Debug.WriteLine("done querying");

        context.Foos.Add(new Foo());

        /* async work here */

        Debug.WriteLine("saving");
        context.SaveChanges();
        Debug.WriteLine("saved");
        Debug.WriteLine("exiting transaction scope");
        transaction.Complete();
        Debug.WriteLine("exited transaction scope");

        return View();
    }
}

This is the debug output when executing two requests at once with Fiddler:

entering transaction scope
entered transaction scope
querying
done querying
entering transaction scope
entered transaction scope
querying
done querying
saving
saving
saved
A first chance exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll
exiting transaction scope
exited transaction scope

This is my understanding of how the code is supposed to work:

• A transaction with serialisable isolation level is required, to prevent phantom reads.
• I cannot use DbContext.Database.Connection.BeginTransaction, when executing a query an error is thrown:

  ExecuteReader requires the command to have a transaction when the connection assigned to the command is in a pending local transaction.
  The Transaction property of the command has not been initialized.

• TransactionScope normally causes causes problems when using task-based async. http://entityframework.codeplex.com/discussions/429215 However, .NET 4.5.1 adds additional constructors to deal with this. How to dispose TransactionScope in cancelable async/await?
• EF 6 has improved transaction support, but I'm still on EF 5. http://msdn.microsoft.com/en-us/data/dn456843.aspx

So obviously it's not working like I want it to. Is it possible to achieve my goal using TransactionScope? Or will I have to do an upgrade to EF 6?

Answer 1

Serialization does not solve the old 'check then insert' problem. Two serializable transactions can concurrently evaluate the condition, conclude that they have to insert, then both attempt to insert only for one to fail and one to succeed. This is because all reads are compatible with each other under serializable isolation (in fact they are compatible under all isolation levels).

There are many schools of thought how to solve this problem. Some recommend using MERGE. Some recommend using a lock hint in the check query to acquire an X or U lock instead. Personally I recommend always INSERT and gracefully recover the duplicate key violation. Another approach that does work is using explicit app locks.

EF or System.Transactions really add only noise tot he question. This is fundamentally a back-end SQL problem. As for the problem of how to flow a transaction scope between threads see Get TransactionScope to work with async / await (obviously, you already know this, from reading the OP... I didn't register in first read). You will need this to get your async code to enlist in the proper context, but the blocking/locking is fundamental back end problem, still.