3

I am using EF6 in my asp.net application, and I have a problem, that is a bit annoying, and I can't seem to figure out a good solution for it.

My code looks like this:

using (var scope = TransactionScopeUtil.RepeatableReadMaxTimeoutRequired())
{
    bool hasConflict = await BookingService.HasConflictAsync(newBooking);
    if (!hasConflict)
    {
        await BookingRepository.InsertAsync(newBooking);
        return Json(AjaxPayload.Success());
    }
    else
    {
        return Json(AjaxPayload.Error());
    }
}

    // The transaction scope builder:
    public static TransactionScope ReadCommittedMaxTimeoutRequired()
    {
        return new TransactionScope(TransactionScopeOption.Required, new TransactionOptions()
        {

            IsolationLevel = IsolationLevel.ReadCommitted,
            Timeout = TransactionManager.MaximumTimeout
        }, TransactionScopeAsyncFlowOption.Enabled);
    }

The problem is, if two clients push the same booking time, a conflict should be registered. And one of the calls should return a message that the timeslot is already booked. But it doesn't if they hit the server exactly right(with in the same milis). Both bookings are saved without a problem.

I can fix this by doing a Serializable hardcore locking scope, but I am sure there is a better way, and I'm just too blind to see it?

What is best practices in situations like this?

André Snede
  • 9,899
  • 7
  • 43
  • 67
  • 1
    Maybe a more generic title for your question would help get people to take a look? – jjj May 12 '15 at 00:06
  • You could handle this in the database by adding a constraint that does not allow the same booking time to be in the table and then catch the exception in your code. Or you could create a queue that is filled with the new bookings and have a consumer thread that inserts one booking after the other. – Stefan May 12 '15 at 08:20
  • @Stefan, thats for your suggestion, unfortunately it is more complex than that, booking collision are allowed in certain circumstances. If a super user wants to force the booking, or even if different resources are booked. – André Snede May 12 '15 at 09:36
  • Maybe the answer to this question could help in your case (IsolationLevel.RepeatableRead): http://stackoverflow.com/questions/13404061/how-can-i-lock-a-table-on-read-using-entity-framework I'd probably implement a producer consumer pattern where the asp.net threads create new bookings and you have one consumer that creates one booking after the other, ensuring that the conditions are met. – Stefan May 12 '15 at 09:41
  • Serializable is actually an appropriate solution here. What are your concerns? Deadlocks are likely here but you can solve them using retry on deadlock. – usr May 12 '15 at 09:43
  • @usr My concern is that is Serializable and the problem with deadlocks will be an expensive solution that is uncalled for. – André Snede May 12 '15 at 09:45
  • I was thinking that maybe inserting the booking, and checking the requirements after would allow me to use repeatable read instead? – André Snede May 12 '15 at 09:46
  • In order to give any other answer we'd need to understand every SQL statement that you execute in that transaction. For complicated code is is hard to make it deadlock safe. Note, that serializable does not lock tables if that is your concern. Perf is good when the data set touched is small. – usr May 12 '15 at 09:46
  • Repeatable read only saves you if you don't do range scans where rows might appear later and if you do not depend on the non-existence of rows even when doing singleton lookups by key. Even if that is true now, are you prepared to make that guarantee forever? Also, in that case serializable is almost equivalent. – usr May 12 '15 at 09:47
  • @usr I can't supply any code right now, but will later. What I do is select where bookings are colliding with this booking time, and doing a .Any(); on that result set. – André Snede May 12 '15 at 09:51
  • That probably does range scans and depends on the non-existence of rows. In that case you need serializable or equivalent locking hints. – usr May 12 '15 at 09:53
  • @usr Would it work with a lover Isolation Level, if I checked for collisions before, and after it has been inserted, I check again to see if another thread has saved in the meantime, and rollback the transaction if so, maybe have a datetime to choose who wins? – André Snede May 12 '15 at 10:13
  • Under that scheme two transactions can make an insert, both conclude that there is a duplication and both roll back. Regarding "expensive" (your word) - what do you mean by that? – usr May 12 '15 at 10:19
  • @usr Thats where the datetime comes in, the lowest value then wins, the rest rolls back, wouldn't that work? – André Snede May 12 '15 at 10:20
  • First problem there are datetime collisions. DateTime.UtcNow has 15ms precision on most machines. Secondly the scheme would require read uncommitted to peek into the other potentially running transaction. Nasty. Third, the later transaction might actually insert first, conclude that all is well and commit. The earlier one might see both rows but conclude based on timestamp that it should win and commit as well. – usr May 12 '15 at 10:23
  • @usr good point, and thanks for explaining that. The problem is just that Serializable throws a deadlock exception, which to me sounds bad, and like a future giant performance issue(pure guessing). – André Snede May 12 '15 at 11:32
  • 1
    Deadlocks should be rare. Then, their perf cost rounds to zero. If you have many deadlocks that's a sign that you are touching too much data (like table scans). That needs to stop. Pause the debugger before the final commit and use the DMVs too look at all locks currently taken. There should be no object locks and just a few key locks in a healthy serializable tran. I'll compile the commentary here into an answer. – usr May 12 '15 at 11:33
  • @usr thank you, could you provide some info about DMV, object locks etc. Maybe some resources to read more about it? – André Snede May 12 '15 at 11:35
  • 1
    "sql server all locks dmv" seems to be a good search term. Get started by grabbing a random ready-made query from the web :) – usr May 12 '15 at 11:43

1 Answers1

1

if two clients push the same booking time, a conflict should be registered

If I understand correctly, you don't want to prevent two bookings at the same time. (You told Stefan a "superuser" could force one.) You just want to register a conflict?

It's easily done, but you have to use the database. At least, there has to be some arbiter of truth, some single place where there's only one time and one final understanding of the state of things. Usually, that's the database. The logic looks like this

insert into T values (X, time, priority = 1) where X not in T
if rows_affected = 1
    hurrah
else 
    while rows_affected < 1
       priority = max(priority) + 1
       insert into T values (X, time, priority) where (X, priority) not in T
   register conflict, you are $priority in line

Convert that to SQL or whatever you're using, pass in {X, time, priority} as parameters, and you're done.

By the way, in case it helps, this approach has a name: optimistic concurrency. With luck, that term might turn up in the documentation for your environment.

James K. Lowden
  • 7,574
  • 1
  • 16
  • 31