Entity Framework Core: Beware of Lifetime Extension of TransactionScopes using C# 8.0
Entity Framework Core 3.0: "Hidden" GROUP BY Capabilities (Part 1)

Entity Framework Core: Getting more Deadlock Information with named Transactions

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

Comments

Feed You can follow this conversation by subscribing to the comment feed for this post.

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment

Your Information

(Name and email address are required. Email address will not be displayed with the comment.)