Click here to Skip to main content
14,037,996 members
Rate this:
 
Please Sign up or sign in to vote.
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 sometimes run into a Database Deadlock.
Transaction (Process ID 57) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

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


What I have tried:

I have tried using a TransactionScope using IsolationLevel Snapshot which was
recommended in some other forum posts without success.
Anyway i think it has nothing to do with the IsolationLevel as my queries are deleting
different rows.
Posted
Updated 7-Mar-19 6:22am

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

Who told you to use "parallel" programming?

I can see now why EF gets a bad rap.

Hammering the system with "Save Changes", etc.

You need to get back to basics.

c# - Parallel doesnt work with Entity Framework - Stack Overflow[^]
   
Comments
m.bleimuth 8-Mar-19 3:08am
   
Why does it have anything todo with parallel programming? If i start the software twice and hit savechanges at the same time (one client deletes all A houses, the other one all B houses) it also runs into a deadlock.
I guess it is not uncommon that clients delete records at the same time that do not have any relationships to each other.
My parallel Method just simulates two clients deleting differnt records at the same time. The weird thing is if i only delete the Persons, it works without deadlock, so i guess it has something todo with the cascading delete of houses.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Cookies | Terms of Service
Web02 | 2.8.190425.1 | Last Updated 7 Mar 2019
Copyright © CodeProject, 1999-2019
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100