1

I am trying to find a better way to accomplish what I am trying to do.

I have two database tables (Booking and TimeSlots)

Booking is a list of appointments, TimeSlots is a list of time slots.

What I am trying to do is when something gets inserted into Booking, get the first row of TimeSlot that is not marked as taken (no one should have the same TimeSlot) and then mark the TimeSlot as taken, so its not longer the first row that is not marked as taken

I originally had this: 

SELECT TOP 1 @DateSlot = dateSlot, @TimeSlot = timeSlot FROM TimeSlots WHERE taken = 0

UPDATE TimeSlots SET taken = 1 WHERE dateSlot = @DateSlot AND timeSlot = @TimeSlot

INSERT INTO Booking (email, dateSlot, timeSlot, startTime, dateCreated, createdBy, dateModified, modifiedBy, isWaitList) VALUES (@Email, @DateSlot, @TimeSlot, @StartTime, GETDATE(), @Email, GETDATE(), @Email, @IsWaitListed)

But the problem with this if two inserts were made at the exact same time, they both with get the same date and time slot.

So I came up with this:

SELECT @AppointmentAccepted = 1
SELECT @IsWaitListed = 0

//Insert Into the database

INSERT INTO Booking (email, dateSlot, timeSlot, startTime, dateCreated, createdBy, dateModified, modifiedBy, isWaitList) VALUES (@Email, ‘’, '', @StartTime, GETDATE(), @Email, GETDATE(), @Email, @IsWaitListed)

//Get the ID of the new insert

SET @TableA_PK=SCOPE_IDENTITY()

//Get Time Slot Where slotOrder = New Insert ID

SELECT @DateSlot = dateSlot, @TimeSlot = timeSlot FROM TimeSlots WHERE slotOrder = @TableA_PK

//Update The database with Date and Time Slot

UPDATE Booking SET dateSlot = @DateSlot, timeSlot = @TimeSlot WHERE id = @TableA_PK

//Update Time Slot as taken

UPDATE TimeSlots SET taken = 1 WHERE slotOrder = @TableA_PK

Which is not bad, I get the ID for the insert grab the time slot based on slotOrder and ID from the insert, then update the time slot as taken and update the appointment with date and time slot.

The issue with this, my database does not always start with 1 and I can’t grab the first Timeslot that is not marked as taken and if a TimeSlot gets deleted, an insert into the appointment table will not have a Time Slot, I don’t like the idea of grabbing another value from another table based off the recent inserted ID

Are there any suggestions on how to improve this where I am not getting a row based off ID, Ideally I would love to get it based off Row Order instead of IDs

user979331
  • 11,039
  • 73
  • 223
  • 418
  • Can you post your table schemas and some sample data? – SE1986 Nov 13 '17 at 21:42
  • Why not just wrap everything in a transaction so that the 2nd user is forced to wait for the 1st user's process to complete? – James L. Nov 13 '17 at 21:50
  • @JamesL. with transaction, will that prevent 2 people getting the same date and time slot? they are making their appointments via web so if two people call the same stored procedure at the time, one will have to wait with transaction, what if 3 or 4 or 5 or 10 people are making an appointment at the same time, wont that slow it down? – user979331 Nov 13 '17 at 21:55
  • If you keep the # of lines of SQL to a minimum inside the transaction, then it will be fast enough that other users won't see the lag. And it will stop users from getting the same time slot. – James L. Nov 13 '17 at 22:04

1 Answers1

0

Use a transaction to get the timeslot value and then update it to set it to taken. This will keep other users from getting the same time slot.

begin transaction
SELECT TOP 1 @DateSlot = dateSlot, @TimeSlot = timeSlot FROM TimeSlots WHERE taken = 0

UPDATE TimeSlots SET taken = 1 WHERE dateSlot = @DateSlot AND timeSlot = @TimeSlot
commit transaction

INSERT INTO Booking (email, dateSlot, timeSlot, startTime, dateCreated, createdBy, dateModified, modifiedBy, isWaitList) VALUES (@Email, @DateSlot, @TimeSlot, @StartTime, GETDATE(), @Email, GETDATE(), @Email, @IsWaitListed)

Now no two users will get the same timeslot, even if they submit the request at the same time. Works for dozens of users too. Users will be blocked while the transaction isn't committed. And since you only have 2 lines of SQL inside the transaction, it will be very quick. I doubt users will even notice (as long as the select top 1 ... query returns fast -- if not, you will need to index that table so it returns instantly).

Also, do you need an order by clause on your select top 1 ... query to ensure that it always returns the available time slots in the same order?

James L.
  • 9,384
  • 5
  • 38
  • 77
  • Whats the down side to using transaction? – user979331 Nov 13 '17 at 23:13
  • I can't think of any down-sides. It makes everything easier to code: https://stackoverflow.com/a/13268453/822072 However, records that receive updates or that are inserted inside of a transaction are locked so other processes cannot touch them until the transaction is committed. So, if you don't write your code right, you could end up with blocking. So that is one down-side, but it's not an issue if you write code that avoids leaving locks in place for long periods of time. – James L. Nov 13 '17 at 23:48