How do I prevent a record being simultaneousy updated using Entity Framework 6

Pat Moran 1 Reputation point
2020-12-16T16:52:29.167+00:00

I am using EF 6 from an azure web app. I have a table with a set of records which I neeed to access and update robustly when handling multiple simultaneous request. I want to find a record which has a status value of 0 initially and set the status to 1 to indicate it has been allocated. I have the method using its own connection and using TransactionBegin to prevent a record being allocated 2.

THe code I am using is showm below

       using (CelticPoolsServiceContext db = new CelticPoolsServiceContext())
        {
            db.Database.ExecuteSqlCommand(
                   @"SET TRANSACTION ISOLATION LEVEL SERIALIZABLE"
                   );
            **lock (lockObject) //&&**
            using (DbContextTransaction transaction = db.Database.BeginTransaction())
            {


                var lquery = from t in db.WindfallTickets
                             where (t.Status == Constants.TicketInitialStatus) && (t.CompetitionId == compid)
                             orderby t.IntA ascending
                             select t;
                var fquery = lquery.Take<WindfallTicket>(1);
                if (fquery != null)
                {
                    WindfallTicket next = fquery.First<WindfallTicket>();
                    tno = firstTicketnoStr = next.TicketNo;
                    next.Status = Constants.TicketPurchaseInitiated;

                    CopyTicketInfo(next, ticket);
                    next.Status = Constants.TicketPurchaseInitiated;
                    next.Purchased = DateTime.UtcNow;
                    next.TransactionId = ticket.Email;
                    db.SaveChanges();
                    epti = next.TicketNo;
                    transaction.Commit();
                }

             }
        }

I have several threads invoking this method. When the lock(lockObject) (at //&&) is present then no records are double allocated. However if I comment out the Lock() records are being allocated multiple times. I thought the Transaction should prevent this.

Apart from using the Lock how can Iprevent th multiple simultaneous updates from occuring

Azure SQL Database
Entity Framework 6.0
Entity Framework 6.0
A Microsoft open-source object-database mapper for .NET.
231 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Ryan Hill 16,696 Reputation points Microsoft Employee
    2020-12-23T02:17:31.733+00:00

    @Pat Moran I did some digging and came across https://stackoverflow.com/a/53403274/10987310 and I think this is what's happening to you. SERIALIZABLE will only apply the lock when UPDATE/INSERT is executed and before that happens, your subsequent thread has already came in and read the row.

    It's still a concurrency issue though and you can employ those tactics such as a rowversion field for concurrency detection.

    You could consider something like...

    using (CelticPoolsServiceContext db = new CelticPoolsServiceContext())  
    {  
        using (DbContextTransaction transaction = db.Database.BeginTransaction())  
        {  
            var lquery = from t in db.WindfallTickets  
                        where (t.Status == Constants.TicketInitialStatus) && (t.CompetitionId == compid)  
                        orderby t.IntA ascending  
                        select t;  
            var next = lquery.FirstOrDefault<WindfallTicket>;  
            if (next != null)  
            {  
                try  
                {  
                    tno = firstTicketnoStr = next.TicketNo;  
                    next.Status = Constants.TicketPurchaseInitiated;  
                    CopyTicketInfo(next, ticket);  
                    next.Purchased = DateTime.UtcNow;  
                    next.TransactionId = ticket.Email;  
                    db.SaveChanges();  
                    transaction.Commit();  
                    epti = next.TicketNo;  
                }  
                catch (DbUpdateConcurrencyException)  
                {  
                    transaction.RollBack();  
                }  
            }  
        }  
    }  
    

    You could also do employ a 'IsBusy' flag, something like...

            var lquery = from t in db.WindfallTickets  
                        where (t.Status == Constants.TicketInitialStatus) && (t.CompetitionId == compid) && !IsPending  
                        orderby t.IntA ascending  
                        select t;  
      
            var next = lquery.FirstOrDefault<WindfallTicket>;  
            if (next != null)  
            {  
                next.IsPending = true;  
                db.SaveChanges();  
                try  
                {  
    

    Another thing I can suggest is to leverage using a thread safe list like ConcurrentQueue<T> and your method above simply reads from this global list as a background service.