Why would a foreign key from a table to itself cause a deadlock when running two deletes?

I have a database with a table "SelfRef". SelfRef has two fields:

Id (guid, PK, not null)
SelfRefId (guid, nullable)

There is a foreign key constraint that maps the SelfRefId field back to the Id field.

I have an EntityFrameworkCore project that references the database. I am running the following test:

• Create two entries in the SelfRef table. In each case, the SelfRefId is null. Save changes.

• Delete both entries in separate, more-or-less simultaneous, tasks.

I am finding that step 2 often causes a deadlock. I don't understand why it should.

I'm showing my code below, though I doubt the issue is specific to this code:

public class TestSelfRefDeadlock
{
    private async Task CreateSelfRef_ThenDelete_Deletes() {
        var sr = new SelfRef
        {
            Id = Guid.NewGuid(),
            Name = "SR"
        };
        var factory = new SelfRefDbFactory();
        using (var db = factory.Create()) {
            db.Add(sr);
            await db.SaveChangesAsync();  // EDIT: Changing this to db.SaveChanges() appears to fix the problem, at least in this test scenario.
        }
        using (var db = factory.Create()) {
            db.SelfRef.Remove(sr);
            await db.SaveChangesAsync();
        }
    }

    private IEnumerable DeadlockTasks() {
        for (int i=0; i await Task.WhenAll(DeadlockTasks());
}

EDIT: I have confirmed that the same deadlock happens in EF6.

To create the table in my database:

```
USE [SelfReferential]
GO

/ Object: Table [dbo].[SelfRef] Script Date: 3/20/2018 3:43:50 PM /
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].SelfRef NULL,
CONSTRAINT [PK_SelfRef] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

A

The deadlock XML indicates that the two sessions are fighting over two different rows, each session having an e(X)clusive lock on one and requesting a (S)hared lock on the other.

Given:

• Deleting a PK requires that it first ensure that there are no existing FK references to it

• The Transaction Isolation Level is "Read Committed"

• You are using connection pooling

• The two competing sessions are coming from the same application (as per the hostpid value)

• It seems that doing Step 1 as async allows for the problem while not saving async doesn't

• There is no indication that transactions are being used, though EF could be doing that behind the scenes, especially since the deadlock XML shows trancount="2" for both sessions

it is possible that either:

• using the async option on the save changes the timing and/or whether or not the app layer is starting a transaction, or

• connection pooling is allowing the threads to swap which connection/session they were using

Now, the connection pooling stuff (number 2) generally shouldn't cause any issues, but since there are scenarios where it can (such as if Distributed Transactions are being used), I didn't want to rule it out. And, since I do not know how EF and/or the async option handles things, it could very well be a combination of async and connection pooling.

So, why don't you first try keeping the async save for Step 1 but disable connection pooling by adding Pooling=false; to your connection string.

Of course, whether or not disabling connection pooling helps, given that not using async on the save solves the issue (or at least appears to thus far), you should consider not using async when creating items. Maybe only use that for deletes and selects? Even if we determine the exact change in behavior between using and not using async on Step 1, it might not be anything that can be worked around (or at least not worked around without doing things that probably shouldn't be done).

StackExchange Database Administrators Q#202379, answer score: 2