Tips to avoid deadlocks in Entity Framework applications

  • Article

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