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

Question

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

Answer 1

@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.