Deadlocks w/Entity Framework - how to rerun the transaction?

Question

Got a bit of a tough situation here: we're experiencing occasional deadlocks in our system. And I don't have a strong background in database concurrency at all.

System.Data.SqlClient.SqlException: Transaction (Process ID 69) was deadlocked on
lock resources with another process and has been chosen as the deadlock victim.
Rerun the transaction.

There are several applications accessing the database: the main MVC app that accesses the database using Entity Framework and several simple console apps that each query the DB with ADO.NET and raw SQL, and insert data via BinaryTap ActiveRecord.

Unfortunately, I'm the FNG at the client organization, so I can't deploy and test new ideas. Also, we're using SSMS Express so I don't have access to SQL Profiler. But it's less important that I fix the problem immediately and more important that I document my analysis of the problem.

Is there any truth to the error message when it says I should rerun the transaction? Here's our DaoBase - we're using one ObjectContext per HttpContext (via the Db property). We're always putting our Dao updates (but not queries) in SafeAction so they get wrapped in a transaction. Am I attempting to rerun the transaction properly?

public abstract class DaoBase
{
    protected static CaseMateEntities Db
    {
        get
        {
            return ContextHelper<CaseMateEntities>.GetCurrentContext();
        }
    }


    protected static void SafeAction(Action<ObjectContext> action)
    {
        Exception exception = null;

        try {
            using (var scope = new TransactionScope()) {
                try {
                    if (Db.Connection.State != ConnectionState.Open)
                        Db.Connection.Open();

                    if (action != null)
                        action(Db);

                    Db.SaveChanges(SaveOptions.DetectChangesBeforeSave);

                    scope.Complete();
                } catch (Exception ex) {
                    exception = ex;
                    if (exception is UpdateException)
                        // TODO: Is this a proper way to rerun a transaction?
                        scope.Complete();
                }
            }

            if (exception == null) {
                Db.AcceptAllChanges();
            } else {
                throw exception;
            }
        } finally {
            Db.Connection.Close();
        }
    }
}

The other apps query the database through ADO.NET/Raw SQL. Their respective SELECT statement do not have the WITH (NOLOCK) designation - perhaps they should? Is there any circumstance where you do want pure queries to lock? And what types of locks would a query create: row and page locks? What about the queries generated by Entity Framework, should I tell EF not to lock on queries?

Thanks to everyone who read this far. I know this is a complex issue and I've got lots of reading to do..

Answer 1

Deadlock analysis require access to SQL profiler to see the situation on database server at time off deadlock. Especially if you are not owner of SQL queries executed on DB this is necessary. When using EF, you are not the owner - EF generates queries. Deadlock must be solved on database queries and order of database operations performed withing transaction = you must know what happened in the database.

Playing with isolation level requires very good knowledge of both your application and any other application running on the database. If you set isolation level to read uncommitted you are breaking one of the core rules of transactions - isolation. Transactions running in read uncommitted mode can read data uncommitted (dirty data) by other transaction - if that transaction rolls back your code can work with invalid data and move database to inconsistent state (or fail on some database constraint). NOLOCK hint in SQL query is the same as using read uncommitted globally but the hint targets only single table in single query.

Is it bad to use NOLOCK or read uncommitted? No but you must be absolutely sure when to do that = you must understand your application (and other applications using the database) and make sure that these queries used to get uncommitted data are not used for any other data modifications or any risk decisions.

Default isolation level for TransactionScope is serializable which is the most restrictive level for transaction (= causes deadlock more often). You should start by using Read committed isolation level (but you must ensure that same data are not read multiple times from the database during a transaction) to reduce database locking but it will most probably doesn't solve the problem (it can reduce the frequency). More about isolation levels.