none
Entity Framework concurrent savechanges deadlock RRS feed

  • Question

  • 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.

    I am using SqlServerExpress as Database server.

    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();
                }
            }
        }



    Monday, March 11, 2019 9:12 AM

All replies

  • https://www.c-sharpcorner.com/UploadFile/ff2f08/prevent-dead-lock-in-entity-framework/
    Monday, March 11, 2019 11:38 AM
  • Thats not the answer, i tried all of this solutions already but none of them worked. If you look 

    at the data it is not possible that a deadlock can happen, because the Method calls are deleting different data.

    if i change the DeleteHouses Method to this:

                using (var ctx = new Context())
                {
                    ctx.Database.ExecuteSqlCommand($"delete from houses where [code] = '{code}'");
                }

    it works perfectly. So i am assuming that EF does something wrong when deleting the entities.

    Monday, March 11, 2019 12:01 PM
  • Thats not the answer, i tried all of this solutions already but none of them worked. If you look 

    I never had a problem and no deadlocks when sing EF., including on reads too.

    it works perfectly. So i am  and that EF does something wrong when deleting the entities.I have used the

    You are using raw T-SQL and EF is not involved.

    Monday, March 11, 2019 5:35 PM
  • Have you ever tried the code i provided? You will see that it ends in a deadlock. 

    The raw sql just demonstrates that the deadlock does not happen when i use raw sql instead of 

    entity frameworks savechanges method.

    Tuesday, March 12, 2019 7:37 AM