SaveChanges() in EF core gives TimeOut Error - Intermittent issue

Question

I have a code where we are using 1 PMDbContext and multiple DbSets in it as shown below: I am getting Timeout Error on SaveChanges() method which is an intermittent issue. What could be the possible solutions to avoid this?

public class PMDbContext : DbContext{
public PMDbContext();

public DbSet<pm_Requests> pm_Requests { get; set; }
public DbSet<am_RDocuments> am_RDocuments { get; set; }
public DbSet<pm_RDocAtt> pm_RDocAtt { get; set; }
//Having other DBsets which are not used in below code



using (var pmDbContext = new PMContext())
{
    var custProfile = GetProfileDetails(prevResponse, pmDbContext);
    var request = GetRequestDetails(prevResponse, pmDbContext);
    if (custProfile != null)
    {
        custProfile. = prevResponse.AccessId.ToString(CultureInfo.InvariantCulture);
        custProfile.LastModifiedDate = DateTime.Now;

        if (ServiceVo.PmAgreementDate != ServiceVo.PEffectiveDateEffectiveDate)
        {
            custProfile.EffectiveDate = ServiceVo.AgreementDate;
            if (custProfile.EffectiveDate.HasValue)
            {
                var initRId = pmDbContext.pm_Requests.Where(i => i.custProfileID == custProfile.custProfileId && i.RequestTypeID == 1 && i.StatusId == (int)RunningServiceStatus.Completed).Select(p => p.RequestId).FirstOrDefault();
                if (initRId != 0)
                {
                    var rDocumentId = pmDbContext.am_RDocuments.Where(p => p.RequestId == initRtId && p.ATypeId == custProfile.MainATypeId).Select(p => p.RDocId).FirstOrDefault();
                    if (rDocumentId != 0)
                    {
                        var rDocAtt = pmDbContext.pm_RDocAtt.FirstOrDefault(p => p.RId == rDocumentId);
                        if (rDocAtt != null)
                        {
                            rDocAtt.EffFrom = ServiceVo.AgreementDate;
                        }
                    }
                }
            }
        }
        }
        pmDbContext.SaveChanges();
        }
        

Answer 1

Is your real code using a using block around the DbContext or was that added solely for the example and it's using an injected DbContext?

A number of things with your database could result in this problem, and they don't necessarily have to do with EF, at least not the SaveChanges, it is just the victim of an underlying problem.

A start would be confirming exactly how many entities are being updated, inserted, or deleted in relation to this operation. This may require a bit of debug code to help narrow down some debug info to this particular operation.

For example, adding an enableWriteCount flag to your DbContext constructor then adjusting your OnSaveChanges() event handler:

if (_enableWriteCount)
{
    var debugCount = ChangeTracker.Entries().Where( p => p.State == System.Data.EntityState.Added 
        || p.State == System.Data.EntityState.Deleted 
        || p.State == System.Data.EntityState.Modified)
        .Count(); 
    // Write this out to a log record.
}

That way in the code in question:

using (var pmDbContext = new PMContext(enableWriteCount: true))
   // ...
}

This way just when this code executes we can output a count of pending tracked changes.

When you trip a timeout what is the count? According to the code presented it should be 1 or 2 per call. Certainly nothing that stands out as timeout-specific. If the count is significantly higher then you have code making far more changes being committed than you expect. If you aren't using using blocks then consider trying one with the above to get the count and see if the error still occurs. The issue may be that your DbContext lifetime scope is longer than it should be (shared between requests) or doing more than you expect to be saved.

External factors that can affect a SaveChanges would be Table/Page locks from other operations on other DbContext instances or data access code. Models that are not mapped correctly/efficiently and missing indexes / constraints can result in queries with large footprints during reads. The locking mechanisms prevent dirty reads so long running read operations end up blocking writes. The problem in this case isn't the SaveChanges call, but other operations that might be running at the time.

The key to locating these problematic reads and the like is by running statistic reports and profiling on the database to find queries that are A) taking long, B) touching a lot of rows (high read counts) and then C) diagnosing each of the most expensive queries to see if there are index suggestions to improve them. Sometimes this means re-looking at code that is triggering these expensive queries to determine if there are improvements to avoid the heavy load. (I.e. leveraging projection, ensuring there aren't premature .ToList() calls, enabled client-side evaluation running, or lazy load calls, etc.

It will be hard to nail down a solution to your particular system, but this should give you some ideas on things to start checking into.