Hi everybody,
I am writing a scheduling system that has appointments table. For each
appointment I am adding a new record that has EventID and ClientID. Each
event has capacity that needs to be checked before adding new appointments,
if it is reached no new appointments could be added. This logic is
implemented as a stored procedure.
Problem that I am trying to solve is concurrent scheduling -- when two or
more clients are trying to schedule an event, it is possible that they would
check capacity all together before adding new records to appointments table,
get permission and then create appointments, exceeding capacity.
Simply wrapping capacity checking and adding new appointment into explicit
transaction does not help. The best solution that I found so far is adding
following statement in the beginning of the transaction:
SELECT 0 FROM Appointments WITH(TABLOCKX)
It effectively locks the whole table till the end of the transaction thus
insuring that until the first client checked and updated table nobody else
can access it. Are there any better solutions? Is there any explicit
statements in MS SQL to lock table?
Any advice is really appreciated.
Thanks,
Anatoly
Here is the code:
BEGIN TRAN
/* If uncommented following statement solves the problem */
/* SELECT 0 FROM Appointments WITH(TABLOCKX) */
IF dbo.GetAvailableSpotsNum(@EventID) <= 0
BEGIN
ROLLBACK TRAN
RETURN
END
INSERT INTO Appointments
(
Client,
Event
)
VALUES
(
@ClientID,
@EventID
)
COMMIT TRAN
And function GetAvailableSpotsNum:
FUNCTION dbo.GetAvailableSpotsNum
(
@EventID INT
)
RETURNS INT
AS
BEGIN
DECLARE @RES INT
SELECT @RES = Capacity - (SELECT COUNT(*) FROM Appointments WHERE Event =
@EventID)
FROM Events WHERE EventID = @EventID
RETURN @RES
END
