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.