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.

Answer 2

Call the select statement in first thread with provision to convert to update lock. So that other read on the same record will wait untill the first thread has released the lock. But EF does not comes with query hint. Hence we need to use Plain SQL query in EF Execute the below statement as inline query in the Entity framework $"select * from WindfallTickets WITH (UPDLOCK) where CompetitionId= compid}"