Concurency issues with scheduling app

Question

Our application needs a simple scheduling mechanism - we can schedule only one visit per room for the same time interval (but one visit can be using one or more rooms). Using SQL Server 2005, sample procedure could look like this:

CREATE PROCEDURE CreateVisit
    @start datetime, @end datetime, @roomID int
AS
BEGIN
DECLARE @isFreeRoom INT

BEGIN TRANSACTION

SELECT @isFreeRoom = COUNT(*) 
FROM visits V
INNER JOIN visits_rooms VR on VR.VisitID = V.ID
WHERE @start = start AND @end = [end] AND VR.RoomID = @roomID

IF (@isFreeRoom = 0)
BEGIN
    INSERT INTO visits (start, [end]) VALUES (@start, @end)
    INSERT INTO visits_rooms (visitID, roomID) VALUES (SCOPE_IDENTITY(), @roomID)
END

COMMIT TRANSACTION
END

In order to not have the same room scheduled for two visits at the same time, how should we handle this problem in procedure? Should we use SERIALIZABLE transaction isolation level or maybe use table hints (locks)? Which one is better?

Answer 1

I have done this in the past...

...

BEGIN TRANSACTION

SELECT @isFreeRoom = COUNT(*) 
FROM visits V  WITH (HOLDLOCK, ROWLOCK)

... The lock will be released at the end of the transaction.

Answer 2

I would have the calling application pass in a comma separated list of room IDs and split them in the SQL, inserting all rows with one INSERT. Doing that, with the proper locking hints (on a single SELECT), should allow your scheduling procedure to work.

I prefer the number table approach to split a string in TSQL, but you can use your own split method if you have one. Here is how to make the number table split approach work:

For this method to work, you need to do this one time table setup:

SELECT TOP 10000 IDENTITY(int,1,1) AS Number
    INTO Numbers
    FROM sys.objects s1
    CROSS JOIN sys.objects s2
ALTER TABLE Numbers ADD CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number)

Once the Numbers table is set up, create this split function:

CREATE FUNCTION [dbo].[FN_ListToTable]
(
     @SplitOn  char(1)      --REQUIRED, the character to split the @List string on
    ,@List     varchar(8000)--REQUIRED, the list to split apart
)
RETURNS TABLE
AS
RETURN 
(

    ----------------
    --SINGLE QUERY-- --this will not return empty rows
    ----------------
    SELECT
        ListValue
        FROM (SELECT
                  LTRIM(RTRIM(SUBSTRING(List2, number+1, CHARINDEX(@SplitOn, List2, number+1)-number - 1))) AS ListValue
                  FROM (
                           SELECT @SplitOn + @List + @SplitOn AS List2
                       ) AS dt
                      INNER JOIN Numbers n ON n.Number < LEN(dt.List2)
                  WHERE SUBSTRING(List2, number, 1) = @SplitOn
             ) dt2
        WHERE ListValue IS NOT NULL AND ListValue!=''

);
GO 

You can now easily split a CSV string into a table and join on it:

select * from dbo.FN_ListToTable(',','1,2,3,,,4,5,6777,,,')

OUTPUT:

ListValue
-----------------------
1
2
3
4
5
6777

(6 row(s) affected)

This is what I would then make your procedure:

CREATE PROCEDURE CreateVisit
    @start datetime, @end datetime, @roomIDs varchar(8000)
AS
BEGIN
DECLARE @RowID INT

BEGIN TRANSACTION

IF NOT EXISTS (SELECT
                   1
                   FROM visits_rooms           (HOLDLOCK,UPDLOCK)  v 
                       INNER JOIN dbo.FN_ListToTable(',',@roomIDs) r ON v.RoomID=r.ListValue
                   WHERE @start = start AND @end = [end] AND VR.RoomID = @roomID --copy of your logic, but shouldn't it be WHERE start>=@start AND [end]<=@end
              )
BEGIN
    INSERT INTO visits (start, [end]) VALUES (@start, @end)
    SELECT @RowID=SCOPE_IDENTITY()
    INSERT INTO visits_rooms
            (visitID, roomID)
        SELECT 
            @RowID, r.ListValue
            FROM dbo.FN_ListToTable(',',@roomIDs) r

END

COMMIT TRANSACTION
END

GO

if you have many RoomIDs one one schedule attempt, you could split them into a @TempTable variable or an actual table #TempTable first and then reuse it in the IF EXISTS and INSERT SELECT.

Answer 3

WHERE @start = start AND @end = [end] AND VR.RoomID = @roomID

This check is incorrect because you'll only find if the room is scheduled exactly between @start and @end. You'll find the room as 'free' if is scheduled between (@start-1, @end) or (@start, @end+1), in other words any overlapping interval that doesn't exactly match your desired start and end. A correct check is like this:

WHERE [start] < @end AND [end] > @start AND RoomID = @roomID

As for the scheduling, the simplest is to do as you do under a serializable transaction. This is simple, but far from ideal: you will hit deadlocks and you will have scalability issues. But fir a light load, this will work pretty well. But in most scheduling systems the resource allocation (seats, rooms etc) first display the selected resource to the user, then allocates it. This is because humans are picky and choosy and they may want to tweak the allocated resource (have room preference, need adjacent rooms etc).

If you want a highly scalable and deadlock free system that can automate the resource allocation you'll need to use a resource table for rooms with one row for each room and visit time. That is, if your visits last 1 hour and the program is 12 hours a day, you have 12 rows per room per day. From this table you schedule the rooms in one single pass, with high efficiency:

UPDATE TOP(@numberOfRooms) RoomHours WITH (ROWLOCK, READPAST)
 SET Free = 0
OUTPUT DELETED.RoomID
WHERE Free = 1
AND RoomHour BETWEEN @start AND @end;