Sql-server – Handle concurrency on sql server for a booking system

cconcurrencyentity-frameworksql server

I've been banging my head against the wall trying to solve this problem and I couldn't come up with a solution that absolutely guarantees two users can't book an appointment for the same time.

The flow and scenario:

1 – The user enters the system and selects a service, say a haircut, and then clicks "Book".

2 – Upon clicking "Book", the server receives the date and time and the staff the user selected to perform the haircut. Based on this information, the available hours the selected staff has are calculated on the fly and sent to the user, who will see something like: 10:00, 12:00, 13:00, 15:00, for example.

3 – The user, then, selects one of the available hours and clicks "Confirm".

The problem:

Two (or more) users can select the same service and staff to perform that service, and when that happens, all the users will be presented the same available hours.

Now, what happens is that when more than one user selects the same available hour, I need to be able to guarantee 100% that only the first will be able to schedule an appointment, and the other will be faced with an error.

I could not come up with a mechanism that ensures no racing condition will occur.
Note that this is a bit different from transport and theather domains, because I do not have a table with all the available hours to simply flag them.

The closest I've come to a solution is to create an extra table, such as "AppointmentsInProgress", and add the starting date and time and the staff to the table, and make both columns unique.
Then, before proceeding to process the user request, check this table to see if nobody else is currently scheduling for the same date, time, and staff.

However, the performance of this solution is a big concern, and I read that the order of sql commands is not guaranteed, which means if two sqls hit the server with a microsecond of difference, there is no guarantee the first will have precedence. Furthermore, something tells me this doesn't really solve my problem.

Any ideas?

Best Answer

However, the performance of this solution is a big concern, and I read that the order of sql commands is not guaranteed, which means if two sqls hit the server with a microsecond of difference, there is no guarantee the first will have precedence.

I don't quite understand how the order of sql commands will affect performance per this sentence.

Now, what happens is that when more than one user selects the same available hour, I need to be able to guarantee 100% that only the first will be able to schedule an appointment, and the other will be faced with an error.

Well first is relative, I mean really nobody will know if in a nano second if B hit his after A in regards to your previous SQL. 99.999% of the time it won't happen and if it does happen... well someone is going to get kicked, it really doesn't matter who.

I could not come up with a mechanism that ensures no racing condition will occur.

Here is one way (fairly simple and easy):

Let say you have a table (Appointment) like:

(Int/Guid) (DateTime2)      (DateTime2)      (int/guid/varchar)
ID         AptDatTime      ReservedOn      ReservedBy
-----------------------------------------------------
1          1/1/2016 08:00

The SQL for someone who starts the reservation would be: (mixing C# and TSQL for brevity)

Update 
  Appointment
Set 
  ReservedOn = DateAdd('mi', 5, getdate()),
  ReservedBy = <whateverUserSomething>
Where 
  (ReservedOn is Null or ReservedOn > DateAdd('mi', 5, getdate())

(There isn't a way that I am aware of to pull this off with natively, you'll have to run the command manually through EF)

Then when the user accepts you do another update

Update 
  Appointment
Set 
  ReservedOn = DateAdd('yyyy', 1000, getdate()), -- or c# DateTime.Max
Where 
  (ReservedOn is Null or ReservedOn > DateAdd('mi', 5, getdate())
  AND (ReservedBy == <whateverUserSomething>)

Actual reservations (can be EF-afied):

Select
  (columns)
From
  Appointment
Where 
  (ReservedOn > DateAdd('mi', 5, getdate())

Temporary reservations (can be EF-afied):

Select
  (columns)
From
  Appointment
Where 
  (ReservedOn > getdate() 
    AND ReservedOn < DateAdd('mi', 5, getdate())

By which I mean if I hit the db with 500 queries at the same time, will it be able to handle it?

SQL Server is extremely robust, 500 queries is very small. Unless you're using like Compact DB or Local DB maybe or a server with extremely limited cpu/memory/low IO it shouldn't be a problem.

Related Question