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}"
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
2 answers
Sort by: Newest
-
Nideesh Manian Sivasubramanian 21 Reputation points
2023-04-12T08:43:32.4033333+00:00 -
Ryan Hill 25,981 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.