question

PatMoran-3912 avatar image
0 Votes"
PatMoran-3912 asked TZacks-2728 commented

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

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-databasedotnet-entity-framework
· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@PatMoran-3912 Here is a tutorial about EF concurrency maybe you can refer to. Tutorial: Handle Concurrency with EF in an ASP.NET MVC 5 app.

0 Votes 0 ·

The tutorial refers to the situation where a CRUD user interface is displaying and updating records and a user can be prompted for example how a clash should be resolved.

We are using EF to access and update records from the app service and there is nouser involved which is why I was trying to use a transaction to prevent the double update.

0 Votes 0 ·

I will explain my logic. On each invocation of the method I create a new connection

using (CelticPoolsServiceContext db = new CelticPoolsServiceContext())

set the isolation level on the connection to be SERIALIZABLE
);
and begin a new transaction
using (DbContextTransaction transaction = db.Database.BeginTransaction())

before querying to find a record maked as unallocated and then set it as initially allloated before saving the changes.
Finally I commit the transaction.

I assumed that should mean it was not possible for a record to be set to allocated state more than one.

0 Votes 0 ·
Show more comments

1 Answer

ryanchill avatar image
0 Votes"
ryanchill answered TZacks-2728 commented

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

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks for improving my understanding of the concurrency issue an giving me the llink.

I dont think the isBusy approach will work as t only shortens the time between reading the row and changing it. Both threads can still

read the same row till setting IsPending is set on the database.
I'll takea look at the rowversion field approach.

0 Votes 0 ·

nice example code.

0 Votes 0 ·