Tips to avoid deadlocks in Entity Framework applications

UPDATE: Andres Aguiar has published some additional tips on his blog that you may also find helpful when dealing with deadlocks .

Recently a customer asked a question about how to avoid deadlocks when using EF. Let me first say very clearly that I don’t actually hear about deadlock issues with EF often at all. But deadlocks are a general problem with database applications using transactions, and to answer this particular customer I collected some information that then I thought could be worth sharing in my blog in case someone else runs into problems:

I won’t spend time in the basics of deadlocks or transaction isolation levels. There is plenty of information out there. In particular, here is a page in SQL Server’s documentation that provides general guidelines on how to deal with deadlocks in database applications. Two of the main recommendations that apply to EF applications are to examine the isolation level used in transactions and the ordering of operations.

Transaction isolation level

Entity Framework never implicitly introduces transactions on queries. It only introduces a local transaction on SaveChanges (unless an ambient System.Transactions.Transaction is detected, in which case the ambient transaction is used).

The default isolation level of SQL Server is actually READ COMMITTED, and by default READ COMMITED uses shared locks on reads which can potentially cause lock contention, although locks are released when each statement is completed. It is possible to configure a SQL Server database to avoid locking on reads altogether even in READ COMMITTED isolation level by setting the READ_COMMITTED_SNAPSHOT option to ON. With this option, SQL Server resorts to row versioning and snapshots rather than shared locks to provide the same guarantees as the regular READ COMMITED isolation. There is more information about it in this page in the documentation of SQL Server.

Given SQL Server’s defaults and EF’s behavior, in most cases each individual EF query executes in its own auto-commit transaction and SaveChanges runs inside a local transaction with READ COMMITED isolation.

That said, EF was designed to work very well with System.Transactions. The default isolation level for System.Transactions.Transaction is Serializable. This means that if you use TransactionScope or CommitableTransaction your are by default opting into the most restrictive isolation level, and you can expect a lot of locking!

Fortunately, this default can be easily overridden. To configure the Snapshot, for instance, using TransactionScope you can do something like this:

  1: using (var scope = new TransactionScope(TransactionScopeOption.Required, new
  2: TransactionOptions { IsolationLevel= IsolationLevel.Snapshot }))
  3: {
  4: // do something with EF here
  5: scope.Complete();
  6: }

My recommendation would be to encapsulate this constructor in a helper method to simplify its usage.

Ordering of operations

EF does not expose a way to control the ordering of operations during SaveChanges. EF v1 indeed had specific issues with high isolation levels (e.g. Serializable) which could produce deadlocks during SaveChanges. It is not a very well publicized feature, but in EF 4 we changed the update pipeline to use more deterministic ordering for uncorrelated CUD operations. This helps ensure that multiple instances of a program will use the same ordering when updating the same set of tables, which in turns helps reduce the possibility of a deadlock.

Besides SaveChanges, if you need to have transactions with high isolation while executing queries, you can manually implement a similar approach: make sure your application always accesses the same pair of tables in the same order, e.g. use alphabetical order.

Conclusion

The recommendations to avoid deadlocks in EF applications boils down to:

• Use snapshot transaction isolation level (or snapshot read committed)
• Use EF 4.0 or greater
• Try to use the same ordering when querying for the same tables inside a transaction

 

Hope this helps,
Diego

Comments

• Anonymous
  March 12, 2013
  In the EF code, I have  TransactionScope  as ReadCommitted Isolation. I set the SNAPSHOT ISOLATION to ON. But READ COMMITTED SNAPSHOT is OFF. I am still facing locking issues. Any ideas?

• Anonymous
  March 12, 2013
  Hello Mukesh, As long as the TransactionScope is created with ReadCommitted and READ_COMMITTED_SNAPSHOT is not enabled in the database then you are not really following the recommendations I explained in this blog post and I assume that you might be seeing locking issues because you are in fact using the default implementation of READ COMMITTED, which uses locking. While READ COMMITTED and SNAPSHOT are two different transaction isolation levels you can choose from by passing an argument to TransactionScope, READ_COMMITTED_SNAPSHOT setting on the other hand is a database-wide setting that replaces the default implementation of READ COMMITTED which uses locking with one that has READ COMMITTED semantics but uses snapshots.

• Anonymous
  June 05, 2013
  Hi Diego, Nice post. My question is that if I use "IsolationLevel.Snapshot" do I need to change SQL Server's default isolation level from READ COMMITTED to something else? If so then what? Thanks, P

• Anonymous
  September 28, 2013
  The comment has been removed

• Anonymous
  December 25, 2013
  Hi Diego, Thank You very much such a nice and helpful post.

• Anonymous
  August 19, 2014
  my dbcontext is per request can i still use TransactionScope right where i need to make a select statement and not affecting other operations?

• Anonymous
  August 19, 2014
  @amir: I can't think of a reason this wouldn't work. Keep in mind that your SELECT query will get executed when you do something to iterate over its results.

  • Anonymous
    April 18, 2017
    Hello Diego,could you, please, clarify this: if I set explicitly READ_COMMITTED with a scope and in another scope set READ_UNCOMMITED, does the first scope (READ_COMMITED) influence on the second one (READ_UNCOMMITTED) and restricts it as READ_COMMITTED too? Or each scope is independent of any other scopes?

• Anonymous
  February 11, 2015
  I just want to warn you about behaviour of the database connection and connection pooling together with transaction scope and setting isolation level on this. If you work with SQL Server 2012, the last set isolation level will follow the connection all the time, even when retrieved back from the connection pool by a different context. This will for example do that if you set a transaction scope to read uncommitted the connection will do dirty reads all the time until next change of transaction scope On SQL Server 2014, the isolation level set on the transaction scope is only valid for the first command done within the scope, the rest of the commands will be executed on the default setting of the database. As for SQL Server 2014; instead of using transaction scopes it is better to use ordinary db transaction context (context.Database.BeginTransaction()) instead, this seems to honor the isolation level throughout the transaction and resetting it on disposing of the transaction. The limitation of this is of course the distributed transaction logic handled by transaction scopes. This does however not work in SQL Server 2012. I have written a longer description of my findings on stack overflow: stackoverflow.com/.../entity-framework-and-transactionscope-doesnt-revert-the-isolation-level-after-d

  • Anonymous
    April 18, 2017
    Hello Rune,thank you for sharing this information! >On SQL Server 2014, the isolation level set on the transaction scope is only valid for the first command done within the scope, the rest >of the commands will be executed on the default setting of the database.I checked it and it really doesn't work sometimes even for the 1-st command within the scope: if "sp_reset_connection" goes then on "Audit Loing" "set transaction isolation level read committed" goes. I am very surprised that (TransactionScope scope ..) doesn't work as expected and there are no updates since then.