Deadlock on deleting separate entities in parallel

[YashanFernando]

Hi all,

I'm running into a deadlock when I'm deleting separate entities in parallel. From my understanding, this should work without any issues.

This might be related to #15180 or #14371 but they have already been fixed, and the changes should be in the version I'm using (6.0.6)

While I'm struggling to understand exactly what's causing the issue (my knowledge of SQL locks is pretty bad 😅), it appears to be related to the cascading deletes that are configured automatically. Disabling the cascading deletes seems to help, however, I can't always disable the cascading deletes since I have owned entities where I can't configure the delete behavior.

Please let me know if you need any more information.

Code

using Microsoft.EntityFrameworkCore;

namespace DeadlockInvestigation;

public class Blog
{
    public Guid Id { get; init; }
    public ICollection<Post> Posts { get; } = new List<Post>();
    public ICollection<User> Users { get; } = new List<User>();
}

public class Post
{
    public Guid Id { get; init; }
    public Guid BlogId { get; init; }
}

public class User
{
    public Guid Id { get; init; }
    public Guid BlogId { get; init; }
}

public class DeadlockContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer(
            "Data Source=.;Initial Catalog=DeadlockDatabase;Integrated Security=True");
    }
}

public class Program
{
    static async Task Main()
    {
        await using (var db = new DeadlockContext())
        {
            await db.Database.EnsureDeletedAsync();
            await db.Database.EnsureCreatedAsync();
        }

        for (int i = 0; i < 5; i++)
        {
            var createdBlogs = new List<Guid>();

            await using (var db = new DeadlockContext())
            {
                var blogWIthPost = new Blog();
                blogWIthPost.Posts.Add(new Post());
                db.Blogs.Add(blogWIthPost);

                var blogWithUser = new Blog();
                blogWithUser.Users.Add(new User());
                db.Blogs.Add(blogWithUser);

                await db.SaveChangesAsync();

                createdBlogs.Add(blogWIthPost.Id);
                createdBlogs.Add(blogWithUser.Id);
            }

            await Parallel.ForEachAsync(createdBlogs, async (blog, _) =>
            {
                await using (var db = new DeadlockContext())
                {
                    var dbBlog = await db.Blogs
                        .Include(b => b.Posts)
                        .Include(b => b.Users)
                        .SingleAsync(b => b.Id == blog);

                    db.Blogs.Remove(dbBlog);
                    await db.SaveChangesAsync();
                }
            });

            Console.WriteLine($"Iteration {i} was successful");
        }

        Console.WriteLine("All iterations were successful");
    }
}

Stack trace

Unhandled exception. System.InvalidOperationException: An exception has been raised that is likely due to a transient failure. Consider enabling transient error resiliency by adding 'EnableRetryOnFailure' to the 'UseSqlServer' cal
l.
 ---> Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while saving the entity changes. See the inner exception for details.
 ---> Microsoft.Data.SqlClient.SqlException (0x80131904): Transaction (Process ID 60) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
   at Microsoft.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__188_0(Task`1 result)
   at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location ---
   at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
--- End of stack trace from previous location ---
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
ClientConnectionId:9d47ea57-6524-4452-9f44-4077c91a4d34
Error Number:1205,State:53,Class:13
   --- End of inner exception stack trace ---
   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(IList`1 entriesToSave, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(StateManager stateManager, Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
   --- End of inner exception stack trace ---
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
   at DeadlockInvestigation.Program.<>c.<<Main>b__0_0>d.MoveNext() in C:\Users\yashanf\Code\DeadlockInvestigation\DeadlockInvestigation\Program.cs:line 82
--- End of stack trace from previous location ---
   at DeadlockInvestigation.Program.<>c.<<Main>b__0_0>d.MoveNext() in C:\Users\yashanf\Code\DeadlockInvestigation\DeadlockInvestigation\Program.cs:line 83
--- End of stack trace from previous location ---
   at System.Threading.Tasks.Parallel.<>c__50`1.<<ForEachAsync>b__50_0>d.MoveNext()
--- End of stack trace from previous location ---
   at DeadlockInvestigation.Program.Main() in C:\Users\yashanf\Code\DeadlockInvestigation\DeadlockInvestigation\Program.cs:line 72
   at DeadlockInvestigation.Program.<Main>()

Deadlock report

<deadlock-list>
  <deadlock victim="process271816bd468">
    <process-list>
      <process id="process271816bd468" taskpriority="0" logused="580" waitresource="KEY: 12:72057594043367424 (ffffffffffff)" waittime="4427" ownerId="21809778" transactionname="user_transaction" lasttranstarted="2022-06-28T11:57:26.313" XDES="0x2712016c428" lockMode="RangeS-U" schedulerid="14" kpid="24036" status="suspended" spid="67" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2022-06-28T11:57:26.313" lastbatchcompleted="2022-06-28T11:57:26.313" lastattention="1900-01-01T00:00:00.313" clientapp="Core Microsoft SqlClient Data Provider" hostname="REMOVED" hostpid="31636" loginname="REMOVED" isolationlevel="read committed (2)" xactid="21809778" currentdb="12" currentdbname="DeadlockDatabase" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
        <executionStack>
          <frame procname="adhoc" line="2" stmtstart="78" stmtend="150" sqlhandle="0x020000003ffc680de6129f93f1271861de484d8a689cd1850000000000000000000000000000000000000000">
unknown     </frame>
          <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown     </frame>
        </executionStack>
        <inputbuf>
(@p1 uniqueidentifier)SET NOCOUNT ON;
DELETE FROM [Blogs]
WHERE [Id] = @p1;
SELECT @@ROWCOUNT;

    </inputbuf>
      </process>
      <process id="process271816b5848" taskpriority="0" logused="580" waitresource="KEY: 12:72057594043432960 (d4bffe014b8a)" waittime="4427" ownerId="21809777" transactionname="user_transaction" lasttranstarted="2022-06-28T11:57:26.313" XDES="0x271811ac428" lockMode="RangeS-U" schedulerid="13" kpid="22604" status="suspended" spid="66" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2022-06-28T11:57:26.313" lastbatchcompleted="2022-06-28T11:57:26.313" lastattention="1900-01-01T00:00:00.313" clientapp="Core Microsoft SqlClient Data Provider" hostname="REMOVED" hostpid="31636" loginname="REMOVED" isolationlevel="read committed (2)" xactid="21809777" currentdb="12" currentdbname="DeadlockDatabase" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
        <executionStack>
          <frame procname="adhoc" line="2" stmtstart="78" stmtend="150" sqlhandle="0x020000003ffc680de6129f93f1271861de484d8a689cd1850000000000000000000000000000000000000000">
unknown     </frame>
          <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown     </frame>
        </executionStack>
        <inputbuf>
(@p1 uniqueidentifier)SET NOCOUNT ON;
DELETE FROM [Blogs]
WHERE [Id] = @p1;
SELECT @@ROWCOUNT;

    </inputbuf>
      </process>
    </process-list>
    <resource-list>
      <keylock hobtid="72057594043367424" dbid="12" objectname="DeadlockDatabase.dbo.Post" indexname="IX_Post_BlogId" id="lock2710c365180" mode="RangeS-U" associatedObjectId="72057594043367424">
        <owner-list>
          <owner id="process271816b5848" mode="RangeS-U"/>
        </owner-list>
        <waiter-list>
          <waiter id="process271816bd468" mode="RangeS-U" requestType="wait"/>
        </waiter-list>
      </keylock>
      <keylock hobtid="72057594043432960" dbid="12" objectname="DeadlockDatabase.dbo.User" indexname="IX_User_BlogId" id="lock2715bc5e180" mode="X" associatedObjectId="72057594043432960">
        <owner-list>
          <owner id="process271816bd468" mode="X"/>
        </owner-list>
        <waiter-list>
          <waiter id="process271816b5848" mode="RangeS-U" requestType="wait"/>
        </waiter-list>
      </keylock>
    </resource-list>
  </deadlock>
</deadlock-list>

Provider and version information

Database provider: Microsoft.EntityFrameworkCore.SqlServer - 6.0.6
Target framework: .NET 6.0
Operating system: Windows 10 Pro x64 (19044)
SQL Server: Developer (64-bit) 15.0.2080.9
IDE: JetBrains Rider 2021.3.2

[ajcvickers]

Notes from triage: This is something that could potentially be helped by a query hint, although whether EF can do this automatically requires some investigation. Implementation of #6717 would allow easily adding query hints manually.

@YashanFernando Until #6717 is implemented, you could consider using a database interceptor to add a query hint.

[evaldas-raisutis]

I was running into this earlier this week.

Adding a non clustered index on the child entities foreign key seems to have resolved the issue most of the time. Still observed a few occurrences in the logs but I think I might need to revisit other entity configuration. Those few scenarios involved a number of reads/writes within the same transaction seemingly and we are using RowVersions, so I'll need to tet to unpack it into more demo'able example...