We've got the following query using LINQ - Entity Framework - SQL:

Context.Dossiers
       .Include(x => x.Persons)
            .ThenInclude(x=> x.Address)
       .Include(x => x.Visits)
       .Include(x => x.Questions)
       .Include(x => x.Signatures)
       .Include(x => x.Areas)
       .Include(x => x.Zones)
       .FirstOrDefaultAsync(x => x.Id == id)

After which we update dossier and save changes.

After running a testing client that will spam the api in parallel multiple times for the same request but with different ids; we notice we're getting deadlocks on Persons.

Persons is not reused for dossiers and each dossier has its own unique set of persons. so there's no overlapping values being locked.

After looking at the deadlock graph we notice there is a page lock on "Persons".

From my understanding page lock will lock additional records (even records that are not related to the query.)

So that's probably what's causing our deadlock.

Now my question is: why is sql turning a simple relationship into a page lock and not a row lock? (there's never more then 10-ish person records for each dossier).

And what would be the best course of action here? Turn off escalation and add an index on the foreign key of Persons with page lock disabled? Or what could we possibly be doing wrong?

Thanks in advance for the input.

CodePudding user response：

There could be a number of issues that trigger this problem. The first obvious one would be to double-check that all related entities have complete and valid FK relationships to one another and these FKs are mapped/interpreted correctly within EF. Another option to consider if this is running in SQL Server would be to look at enabling snapshot isolation allowing SQL Server to rely more-so on row versioning than locks. The biggest consideration for using this is the impact on the Temp DB, so not necessarily something recommended for very large systems especially if there are large bulk operations being run.

When you are updating a Dossier, do you need each and every included related entity? Another common issue when working with EF is finding a balance between satisfying a desire for DNRY (Do Not Repeat Yourself) vs. performance. Often code like this will reside in a Repository method as a GetDossierById()-type method which is called anywhere and everywhere a Dossier is needed, and ends up eager-loading everything because some of those areas, but not all, benefit from having the extra information loaded. With highly concurrent use systems such as web applications that may have hundreds of people updating data at any given time, it can be a good idea to design it to make updates as atomic as possible. Rather than giving a user a massive page for updating an entire dossier and everything related to it in one go, then calling "Submit" with the entire graph, breaking it up to submit changes through individual operations. I.e. AddPerson, RemovePerson, etc. The more data that EF, or any ORM needs to load at one time to perform an operation, the more risk there is of running into locking issues when the system comes under load as more rows are "touched" each time, and the operations generally take longer leaving locks in place longer.