在並發呼叫上使用 CTE 和 OUTPUT 塊進行更新

我遇到了死鎖問題，所以我開始使用我在某處讀到的這個 CTE 技巧。沒有更多的僵局。

但是現在我添加的每個客戶端都會減慢（阻塞？）儲存過程。例如，1 個客戶端的 1-2 秒更新變為兩個客戶端的 2-4 秒。（這個簡單的隱喻查詢執行時間為 0.001 秒，但在少數客戶之後會達到 0.03 秒——所以這個問題與我的實際實現無關。）

是鎖定問題嗎？我是否需要將其包裝在（某種）交易中？

WITH UpdateView AS (
   SELECT TOP 1 W.*
   FROM [WidgetSandbox].[dbo].[Widgets] W
   INNER JOIN [WidgetSandbox].[dbo].[Sizes] S ON W.SizeId = S.Id
   WHERE W.StatusId = @availableStatusId
   AND W.ColorCode = @colorCode
   ORDER BY S.DiameterInches
)

UPDATE UpdateView 
SET StatusId = @soldOutStatusId
OUTPUT INSERTED.Id INTO @outputIds;

SET @singleUpdatedId = (SELECT TOP 1 Id FROM @outputIds);

SELECT * FROM [WidgetSandbox].[dbo].[Widgets]
WHERE Id = @singleUpdatedId;

我希望我對具體問題有一個更好的了解，但事實上我只是卡住了……

DDL，如果有幫助：https ://gist.github.com/RobertBaldini/3740c7bb85eea47d7fe63cb8602ac2d6

回購：https ://github.com/RobertBaldini/WidgetSandbox

您看到的許多問題都是由低效的執行計劃引起的：

並不是說提供的計劃和查詢與問題匹配，但即便如此，我正在使用所提供的內容。

無論如何，您應該實現我在上一個問題中提到的Name列數據類型更改（來自nvarchar(max) ） 。更重要的是，你需要添加我推薦的索引，這樣不需要排序就可以找到要更新的行：

CREATE NONCLUSTERED INDEX IX_dbo_Sizes__DiameterInches
ON dbo.Sizes (DiameterInches);

CREATE NONCLUSTERED INDEX IX_dbo_Widgets__SizeId_ColorCode_StatusId__Name
ON dbo.Widgets (SizeId, ColorCode, StatusId)
INCLUDE (Name);

然後，您可以更新所選項目的狀態並返回受影響的行：

DECLARE
   -- Constant values guessed, replace with the real ones
   @availableStatusId integer = 1,
   @soldOutStatusId integer= 9,
   @colorCode nvarchar(6) = N'Red';

WITH UpdateView AS 
(
   SELECT TOP (1)
       W.Id,
       W.Name,
       W.StatusId,
       W.ColorCode,
       W.SizeId
   FROM dbo.Widgets AS W WITH (UPDLOCK, ROWLOCK, READPAST)
   JOIN dbo.Sizes AS S 
       ON W.SizeId = S.Id
   WHERE 
       W.StatusId = @availableStatusId
       AND W.ColorCode = @colorCode
   ORDER BY 
       S.DiameterInches
)
UPDATE UpdateView 
SET StatusId = @soldOutStatusId
OUTPUT
   Inserted.Id,
   Inserted.Name,
   Inserted.StatusId,
   Inserted.ColorCode,
   Inserted.SizeId;

注意UPDLOCK, ROWLOCK, READPAST那裡的提示。這些是並發 FIFO 訪問隊列類型表所需的標準提示。有關詳細資訊，請參閱Remus Rusanu 的將表用作隊列。

你應該得到的執行計劃形狀（沒有排序！）是：

順便說一句，除非您有充分的理由，否則您的數據庫不應該使用SET AUTO_CLOSE ON.

引用自：https://dba.stackexchange.com/questions/135842