3

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?

Improve this question
3
  • Why not use one unique index guarding the end result? The user who is too late could then continue with their previously chosen service and staff, but different time options. And as for the order of statements, if the lucky user was actually microseconds later, who will bother? There's no referee watching (I guess). Of course the final commit should be one transaction. – Gert Arnold Sep 5 '16 at 21:26
  • I can't simply make the combination of datetime + staff unique on the Appointment table unique, because the owner of the business has an admin calendar in which he can create appointments, and he can do whatever he wants, because he is there and knows what he is doing. This was actually a complaint I had a few times. That's why I thought about creating a "temp" table, if you will, to store only in progress appointments. QUestion remains: is unique guaranteed to always "work"? By which I mean if I hit the db with 500 queries at the same time, will it be able to handle it? – victor Sep 5 '16 at 21:32
  • 1
    A unique index can't be violated. Never ever. As for performance. Established databases laugh about a couple of millions of record (well indexed of course), but if it helps, you could archive old appointments. You could pre-create appointments as far in advance as necessary, according to the service-owner's schedule, and remove (archive) them later, so you have a moving window of active appointments. – Gert Arnold Sep 5 '16 at 22:03
3

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.

Improve this answer
2
  • The problem with your solution is that, as I said, I don't have an "available hours" table, so I don't update any tables, I just insert new records, which contain the selected time, and that's a problem because 2 records could, in theory, be insert "at the same time", and then 2 users would have appointments scheduled for the same time. What about having a table where I temporarily insert information about appointments being scheduled, and then check this table before commiting the changes to the db to make sure no one is already scheduling at the same time? – victor Sep 6 '16 at 20:56
  • Temp table or same table, the logic for determining if a row exists/can exist etc is the same. – Erik Philips Sep 6 '16 at 21:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.