Whether in an application in production or in integration tests on CI, the deadlocks are virtually unavoidable. The behavior of a deadlock depends on the database (MS SQL Server, MySQL, etc)  and the isolation level (like Snapshot Isolation). Some of the databases are blocking and some of them are not. For example, a deadlock in Microsoft SQL Server is blocking and means that there are at least 2 transactions waiting for each to complete. In the end, one of them is going to "win" and the other is considered a "deadlock victim" and will be aborted by the database. The deadlock victim is easy to spot, it is the one that throws an exception but what about the winner? What is the other transaction the deadlock victim was competing with?

One option is to capture a deadlock graph by using Extended Events, SQL Profiler or in case the database is in Azure then by querying the master database. A deadlock graph itself is represented as XML and can be visualized by tools like the profiler.

Annotation 2019-10-24 185633

In the picture above the deadlock victim is on the left and the transaction that won is on the right. Let's figure out what code is responsible for the transaction on the right side.

First, we can try to find out the responsible Entity Framework Core (EF) query by looking at the SQL statement. Sometimes it is more than enough sometimes it isn't because the queries are not written by the developers but generated by EF. Another approach is to use named transactions because the transaction names are part of the deadlock graph.

By default, there is no method overload for BeginTransaction that takes a name because this feature is not supported by all databases, so let's build an extension method for the SQL Server first.

public static class DatabaseFacadeExtensions
{
   public static IDbContextTransaction BeginTransaction(this DatabaseFacade database,
                                                        string name)
   {
      database.OpenConnection();

      var connection = (SqlConnection)database.GetDbConnection();
      var transaction = connection.BeginTransaction(name);

      return database.UseTransaction(transaction);
   }
}

The usage of the extension method is no different than without the name

using(var tx = myDbContext.Database.BeginTransaction("Product Update"))
{
   ...
}

Let's look at the XML of the deadlock graph after we provided the transaction name.

<deadlock-list>
   <deadlock victim="process1dba0047c28">
       <process-list>
          <process id="process1dba0057468" spid="55" transactionname="Product Update" ...>
          <executionStack>
              <inputbuf>
                   (@p1 uniqueidentifier,@p0 nvarchar(4000),@p2 varbinary(8));
                    UPDATE [Products] SET [Name] = @p0
                    WHERE [Id] = @p1 AND [RowVersion] = @p2;
                    SELECT [RowVersion]
                    FROM [Products]
                    WHERE @@ROWCOUNT = 1 AND [Id] = @p1;
...

Having the transaction name we know exactly what part of our code we have to look at.

Need a deadlock? The demo is on github: NamedTransactionsDemo.cs

Related Articles

entity framework
Entity Framework Core - ROW_NUMBER Support
There are some use cases that force me to use raw T-SQL instead of using LINQ. One of them is the need for ROW_NUMBER. But not anymore... One again, due to great work of Entity Framework (EF) team we are able to add more capabilities to EF quite easily. At the moment there are…
Pawel Gerr
entity framework
Entity Framework Core - Isolation of Integration Tests
When working with Entity Framework Core (EF) a lot of code can be tested using the In-Memory database provider but sometimes you want (or have) to go to the real database. For example, you are using not just LINQ but custom SQL statements due to performance reasons or you want to…
Pawel Gerr
entity framework
Entity Framework - High performance querying trick using SqlBulkCopy and temp tables
Implementing database access with Entity Framework is pretty convenient, but sometimes the query performance can be very poor. Especially using navigational properties to load collections leads to significantly longer execution times and more I/O. To see the impact of the loading…
Pawel Gerr
entity framework
Entity Framework Core 3.0 - "Hidden" GROUP BY Capabilities (Part 2)
In the previous blog post we used a navigational property to work around the limitations of the extension method . The problem is, there is not always such a property we can use for grouping. Especially, when following domain driven design practices, a bidirectional navigation is…
Pawel Gerr