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.
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,
var connection = (SqlConnection)database.GetDbConnection();
var transaction = connection.BeginTransaction(name);
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.
<process id="process1dba0057468" spid="55" transactionname="Product Update" ...>
(@p1 uniqueidentifier,@p0 nvarchar(4000),@p2 varbinary(8));
UPDATE [Products] SET [Name] = @p0
WHERE [Id] = @p1 AND [RowVersion] = @p2;
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