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

[quoted text, click to view]

Hi Anatoly,

You can solve this by enclosing the read operation (to check available
places) and the update operation (to actually schedule the event if places
are available) in a transaction AND setting the transaction isolation
level to repeatable read or serializable.

You didn't post the code of the UDF, so I can't tell which of the two
isolation levels you need. Repeatable read locks the rows read until the
end of the transaction, preventing updates from other connections.
Serializable locks not only the rows read, but a complete key range,
ensuring no other rows are inserted that would have met the WHERE-clause
of the SELECT statement(s) used. Both have less impact on concurrency than
locking an entire table.

You'll also need to change the UDF. If two connections read and lock the
same data (which is possible, as a SELECT uses a shared lock by default),
neither will be able to upgrade that lock to an exclusive lock. The UDF
should provide the UPDLOCK locking hint. That will force the SELECT to
acquire exclusive locks instead of shared locks.

Best, Hugo
--