isn't SaveChanges() managed by transactions?-CodePudding

I've several "thread" (i.e. Hangfire task) that will run in parallel this kind of function:

private void UpdatePartners(int systemId)
{
    try
    {
        using (var ctx = new MyEntities())
        {
            var partners = ctx.PartnersUpdate.Select(s => s)
              .Where(w => w.SystemId.Equals(systemId) && statusToEvaluate.Contains(w.Status)).OrderByDescending(p => p.UpdateAt).Take(500);

            var partnersId = partners.Select(p => p.PartnerId).ToList();

            var partnersToUpdate = ctx.vPartnersSystems.AsNoTracking().Select(s => s)
                    .Where(w => w.SystemId.Equals(systemId) && partnersId.Contains(w.Id) && w.Validity.Equals(true))
                    .ToList();

            foreach (var partner in partners)
            {
                // some operations on "partner", using data from "partnersToUpdate"
                
                ctx.PartnersUpdate.AddOrUpdate(partner);
            }

            ctx.SaveChanges();
        }
    }
    catch (Exception ex)
    {
        Logger(ex, "Error on UpdatePartners");
    }
}

Unfortunately, sometimes I get this error:

UpdatePartners(Int32 systemId) in C:\myProjects\XYZ\Controllers\TestController.cs:line 16 Void HandleReaderException(System.Exception)
System.Data.SqlClient.SqlException (0x80131904): Transaction (Process ID 130) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

It seems when iterate the partners (but I could be wrong):

foreach (var partner in partners)

why this? isn't SaveChanges() managed by transactions? How can I fix this problem?

I'm on .Net 4.6.1, with Entity Framework 6.0.0.0

CodePudding user response：

A DbContext instance operates with a transaction, however if you have several tasks running each accessing DbContext instances that are operating against the same tables, you can encounter deadlocks.

One issue I see is that you shouldn't be calling AddOrUpdate if you are inspecting partners against the alternative source, and making changes. The partners already contains tracked instances, so simply call SaveChanges(). AddOrUpdate was only really intended to be used for seeding databases.

You will want to be very careful about running bulk-like operations like this in parallel with other data operations. The more entities you load at a time to process, the longer EF will need to perform reads and saves, and the larger the window is for deadlocks. 500 records is a fair few to process in one hit. If this task is running constantly checking and updating rows you may want reduce that and also have it bail out without further queries if there are no rows to process.

If this is SQL Server, you may also want to consider checking whether the database is set up for read committed snapshot. This can help prevent deadlock scenarios by utilizing row versioning to handle concurrency.

CodePudding user response：

as you mentioned that you sometimes use partnersToUpdate, you should remove the AsNoTracking() option , becouse entity framework loses the track of entities , and it can't update them