Entity Framework concurrent savechanges deadlock

[ghost]

I am writing a quite simple Multiuser WPF application where each user can modify / delete his own data. Means each user can see all the data of other users but is not allowed to modify or delete it. The whole application performs well until 2 or more clients hit the savechanges method at the same time, then i often run into a Database Deadlock.

I have no idea why it comes to that deadlock, as it is not possible that the same data is modified or deleted at the same time by different users.

I wrote a short program that demonstates the behaviour. The delete method at the end simulates two users deleting their own data at the same time.

Here is my DbContext and Datamodel:

public class Context : DbContext
{
    public DbSet<City> dbsCities          { get; set; }
    public DbSet<House> dbsHouses   { get; set; }
    public DbSet<Person> dbsPersons  { get; set; }

    public Context() : base(@"Server=.\SQLExpress;Database=test;Trusted_Connection=Yes;Connection Timeout=5")
    {
        Configuration.AutoDetectChangesEnabled = true;
        Configuration.LazyLoadingEnabled = false;
        Configuration.ProxyCreationEnabled = false;
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<City>().HasMany(c => c.Houses).WithRequired(c => c.City).WillCascadeOnDelete(true);
        modelBuilder.Entity<House>().HasMany(p => p.Residents).WithRequired(p => p.House).WillCascadeOnDelete(true);
    }
}

public class City
{
    public Guid Id { get; set; }
    public string Name { get; set; }
    public virtual ICollection<House> Houses { get; set; }

    public City()
    {
        Id = Guid.NewGuid();
        Houses = new List<House>();
    }
}

public class House
{
    public Guid Id                                    { get; set; }
    public int Number                             { get; set; }
    public string Code                             { get; set; }
    public virtual City City                       { get; set; }
    public virtual ICollection<Person> Residents    { get; set; }

    public House()
    {
        Id = Guid.NewGuid();
        Residents = new List<Person>();
    }
}


public class Person
{
    public Guid Id          { get; set; }
    public string Firstname { get; set; }
    public string Lastname  { get; set; }

    public virtual House House { get; set; }

    public Person()
    {
        Id = Guid.NewGuid();
    }
}

And here the code to generate testdata:

using (Context ctx = new Context())
        {
            List<City> cities = new List<City>() { new City() { Name = "New York" } };
            for (int h = 1; h <= 50; h++)
            {
                string code = "A";
                if (h % 2 == 0)
                    code = "B";
                House house = new House() {Number = h, Code = code };
                cities[0].Houses.Add(house);
                for (int i = 0; i <= 100; i++)
                    house.Residents.Add(new Person() { Firstname = "A", Lastname = "B" });
            }

            ctx.dbsCities.AddRange(cities);
            ctx.SaveChanges();
        }

Finally the Method that causes the deadlock

private void Delete(object sender, RoutedEventArgs e)
    {
        string[] to = new string[] {"A", "B"};
        Parallel.ForEach(to, code =>
        {
            DeleteHouses(code);
        });
    }
    private void DeleteHouses(string code)
    {
        using (var ctx = new Context())
        {
            ctx.Database.Log += Console.WriteLine;
            var todel = ctx.dbsHouses.Where(d=>d.Code == code);
            if (todel != null)
            {
                ctx.dbsHouses.RemoveRange(todel);
                ctx.SaveChanges();
            }
        }
    }

Attached you can find a solutions that demonstrates the exact problem.

DisconnectedEntities.zip

[ghost]

I found out that it works when i delete the child entities manually, means calling ctx.dbsPersons.RemoveRange(people). So it must have something todo with the cascading delete!
Any chance to get an answer on this?

[ajcvickers]

@divega @AndriySvyryd Could one (or both of you) take a quick look at this? It might be the same pattern for deadlocks that we were discussing recently.

[divega]

I looked at this superficially and the model looks very similar to the the issue reported on EF Core (dotnet/efcore#14371). However, the repro in that case was alternatively inserting and deleting data.

The issue described here also repros in EF Core. Here is the EF Core version of the repro:

using System;
using System.Linq;
using System.Collections.Generic;
using Microsoft.EntityFrameworkCore;
using System.Threading.Tasks;

namespace EF6Issue723
{
    class Program
    {
        static void Main(string[] args)
        {

            using (Context ctx = new Context())
            {
                ctx.Database.EnsureDeleted();
                ctx.Database.EnsureCreated();
                List<City> cities = new List<City>() { new City() { Name = "New York" } };
                for (int h = 1; h <= 50; h++)
                {
                    string code = "A";
                    if (h % 2 == 0)
                        code = "B";
                    House house = new House() { Number = h, Code = code };
                    cities[0].Houses.Add(house);
                    for (int i = 0; i <= 100; i++)
                        house.Residents.Add(new Person() { Firstname = "A", Lastname = "B" });
                }

                ctx.Cities.AddRange(cities);
                ctx.SaveChanges();
            }

            string[] to = new string[] { "A", "B" };
            Parallel.ForEach(to, code =>
            {
                DeleteHouses(code);
            });
        }

        private static void DeleteHouses(string code)
        {
            using (var ctx = new Context())
            {
                var todel = ctx.Houses.Where(d => d.Code == code);
                if (todel != null)
                {
                    ctx.Houses.RemoveRange(todel);
                    ctx.SaveChanges();
                }
            }
        }


    }

    public class Context : DbContext
    {
        public DbSet<City> Cities { get; set; }
        public DbSet<House> Houses { get; set; }
        public DbSet<Person> Persons { get; set; }

        public Context() : base()
        {
        }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=test;Trusted_Connection=Yes;ConnectRetryCount=0");

        }
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<City>().HasMany(c => c.Houses).WithOne(c => c.City).IsRequired().OnDelete(DeleteBehavior.Cascade);
            modelBuilder.Entity<House>().HasMany(p => p.Residents).WithOne(p => p.House).IsRequired().OnDelete(DeleteBehavior.Cascade);
        }
    }

    public class City
    {
        public Guid Id { get; set; }
        public string Name { get; set; }
        public virtual ICollection<House> Houses { get; set; }

        public City()
        {
            Id = Guid.NewGuid();
            Houses = new List<House>();
        }
    }

    public class House
    {
        public Guid Id { get; set; }
        public int Number { get; set; }
        public string Code { get; set; }
        public virtual City City { get; set; }
        public virtual ICollection<Person> Residents { get; set; }

        public House()
        {
            Id = Guid.NewGuid();
            Residents = new List<Person>();
        }
    }


    public class Person
    {
        public Guid Id { get; set; }
        public string Firstname { get; set; }
        public string Lastname { get; set; }

        public virtual House House { get; set; }

        public Person()
        {
            Id = Guid.NewGuid();
        }
    }
}

I trust that @AndriySvyryd can dive deeper and come to conclusions.

[ghost]

@AndriySvyryd any updates on this? I need to know if there will be a workaround or if i just should remove the cascading delete and delete the child objects manually?

[ajcvickers]

@blenet Given that you have a workaround, is there some reason you are looking urgently for another?

[ghost]

@ajcvickers well this workaround only works if i remove the cascading delete constraint. Which works in this example. My real application contains way more entities, and the whole model has cascade delete constraints, which would mean i have to remove all constraints and write my own delete methods.
I guess cascading delete also performs better than deleting each child item separately.

[ghost]

@ajcvickers @divega will there be an update or bugfix on my problem or should i go and remove all cascading deletes and write my own delete methods?

[ajcvickers]

@blenet It is unlikely but not impossible that we will end up making a change to EF6 for this case. If we do make a change, it will be for the next release, so until then you will need to come up with a workaround. Once we have fully investigated the issue we may be able to suggest some other workarounds, but investigating this is not currently super high priority, so if you are blocked then you should do whatever you need to do to unblock.

[ajcvickers]

EF Team Triage: This issue is not something that our team is planning to address in the EF6.x code base. This does not mean that we would not consider a community contribution to address this issue.

Moving forwards, our team will be fixing bugs, implementing small improvements, and accepting community contributions to the EF6.x code base. Larger feature work and innovation will happen in the EF Core code base (https://github.com/aspnet/EntityFramework).

Closing an issue in the EF6.x project does not exclude us addressing it in EF Core. In fact, a number of popular feature requests for EF have already been implemented in EF Core (alternate keys, batching in SaveChanges, etc.).

BTW this is a canned response and may have info or details that do not directly apply to this particular issue. While we'd like to spend the time to uniquely address every incoming issue, we get a lot traffic on the EF projects and that is not practical. To ensure we maximize the time we have to work on fixing bugs, implementing new features, etc. we use canned responses for common triage decisions.