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 core
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 core
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 core
Unnecessary Fuzzy Searches may hurt your Entity Framework Core Performance
After talking about performance issues like N+1 Queries and the Cartesian Explosion that made its comeback in Entity Framework Core 3, we will today look at a performance issue that is not tied to any Entity Framework version but is rather a general one. What do I mean by…
Pawel Gerr
entity framework core
The performance issue "Cartesian Explosion" made its comeback in Entity Framework Core 3
In Entity Framework Core 3.0/3.1 the SQL statement generation underwent significant changes. As we have seen in the previous post these changes removed both the implicit client-side evaluation and the N+1 Query Problem (which is good!). Unfortunately, these changes (re)introduced…
Pawel Gerr