@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.
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.
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.
@Pat Moran do you see range lock on the table in SQL activity monitor?
Just to make sure I understand the situation:
Let me know if I'm misunderstanding anything.
Regards,
Ryan
You understand the situation perfectly.
The double allocation does not happen normally but only under very heavy load when there are several instances of the method executing simultaneously. Wen the lock() is used the double allocation never happens.
I am not sure how I can use the SQl activity monitor because the double interaction is unpredictable and very brief.
If I run a load test by firing up say 100 threads each of which try to allocate a ticket then the number allocated will vary between 40 and 86. When I record the allocated ticket numbers I can see many that have been allocated twice or three times.
I set the isolation level on the connection to be SERIALIZABLE to try to ensure a lock was placed on the record when it was read. I presume I dont understand the locking.
Sign in to comment