Concurency issues with scheduling app
- by Sazug
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?
