调度应用程序的并发性问题
我们的应用程序需要一个简单的调度机制--在相同的时间间隔内，我们只能为每个房间安排一次访问(但一次访问可以使用一个或多个房间)。使用SQL Server 2005时，示例过程可能如下所示：
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为了不让同一个房间同时被安排两次访问，我们应该如何在程序上处理这个问题？我们应该使用SERIALIZABLE事务隔离级别，还是应该使用表提示(锁)？哪一个更好？
回答 3
Stack Overflow用户
发布于 2010-03-08 22:05:11
我会让调用应用程序传入一个以逗号分隔的房间in列表，并在SQL中拆分它们，用一次插入插入所有行。这样做，使用适当的锁定提示(在单个SELECT上)，应该允许您的调度过程工作。
I prefer the number table approach to split a string in TSQL，但如果您有自己的拆分方法，也可以使用它。下面是如何使用数字表拆分方法：
要使此方法起作用，您需要执行以下一次时间表设置：
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)设置Numbers表后，创建此拆分函数：
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 现在，您可以轻松地将CSV字符串拆分到表中并在其上进行连接：
select * from dbo.FN_ListToTable(',','1,2,3,,,4,5,6777,,,')输出：
ListValue
-----------------------
1
2
3
4
5
6777
(6 row(s) affected)这就是我要做的程序：
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去
如果您在一次调度尝试中有多个RoomID，您可以首先将它们拆分为一个@TempTable变量或一个实际的表#TempTable，然后在IF EXISTS和INSERT SELECT中重用它。
Stack Overflow用户
发布于 2010-03-08 21:48:44
我以前也这么做过……
..。
BEGIN TRANSACTION
SELECT @isFreeRoom = COUNT(*)
FROM visits V WITH (HOLDLOCK, ROWLOCK)..。锁将在事务结束时释放。
Stack Overflow用户
发布于 2010-03-09 00:23:57
WHERE @start = start AND @end = [end] AND VR.RoomID = @roomID
此检查是不正确的，因为您只能发现房间是否恰好安排在@start和@end之间。如果在(@ start -1，@ end )或(@start，@end+1)之间安排时间，你会发现房间是“空闲”的，换句话说，任何重叠的时间间隔都不完全符合你想要的开始和结束。正确的检查如下所示：
WHERE [start] < @end AND [end] > @start AND RoomID = @roomID至于调度，最简单的方法是像在可序列化事务下那样做。这很简单，但并不理想:您将遇到死锁，并且会遇到可伸缩性问题。但是如果负载较轻，这将会很好地工作。但在大多数调度系统中，资源分配(座位、房间等)首先向用户显示选定的资源，然后进行分配。这是因为人类是挑剔和挑剔的，他们可能想调整分配的资源(有房间偏好，需要相邻的房间等)。
如果您想要一个高度可伸缩且无死锁的系统来自动化资源分配，则需要为房间使用资源表，每个房间和访问时间占一行。也就是说，如果您的访问持续1小时，而程序为每天12小时，则每个房间每天有12行。在此表中，您可以高效地在一次遍历中安排房间：
UPDATE TOP(@numberOfRooms) RoomHours WITH (ROWLOCK, READPAST)
SET Free = 0
OUTPUT DELETED.RoomID
WHERE Free = 1
AND RoomHour BETWEEN @start AND @end;https://stackoverflow.com/questions/2401557
复制相似问题
腾讯云开发者
