sql server:試圖了解RCSI示例-讀取提交的快照隔离級別的潜在危险

在鎖定讀取下提交時，很有可能（但不能保證）当两个会话執行此代碼時

BEGIN TRANSACTION
DECLARE @QtyRequired int, @QtyRemain int
SELECT @QtyRequired = 4
SELECT @QtyRemain = SUM(QTY) FROM Inventory WHERE ItemID = 796 AND LocationID = 1
IF @QtyRemain - @QtyRequired >= 0 
BEGIN
    UPDATE Inventory SET Qty = Qty - @QtyRequired
    WHERE ItemID = 796 AND LocationID = 1
    -- Do other stuff in other tables or databases to check out the item
    WAITFOR DELAY '00:00:10'
    SELECT 'Checkout complete'
END
ELSE
    SELECT 'Not enough qty!'
COMMIT TRANSACTION

第二次会话執行的時間会有足够的差異

SELECT @QtyRemain = SUM(QTY) FROM Inventory WHERE ItemID = 796 AND LocationID = 1

在第一个会话執行之後

UPDATE Inventory SET Qty = Qty - @QtyRequired
WHERE ItemID = 796 AND LocationID = 1

因此，第二个会话將無法在该行上获取S鎖，並且將被阻塞，直到第一个会话提交為止.但是，這不能保證，因為两个会话都很有可能執行

SELECT @QtyRemain = SUM(QTY) FROM Inventory WHERE ItemID = 796 AND LocationID = 1

在其中任何一个執行更新之前。

使用RCSI時，它们更有可能会為@QtyRemain讀取相同的值，因為SELECT永远不会在另一个会话上被掛起的UPDATE阻止，而仅返迴该行的"最後一个好"值 从版本儲存中。

但是由於鎖定讀取的提交版本存在相同的問题，尽管在较小的視窗中，它们都已损壞，應通過將第一个查詢更改為以下內容来解決：

SELECT @QtyRemain = SUM(QTY) FROM Inventory with (UPDLOCK) WHERE ItemID = 796 AND LocationID = 1

這保證即使两个会话同時尝試进行查詢，一个会话也將被授予U鎖，而另一个將被阻止，直到第一个会话提交為止。

試圖解釋如何 我们在表中以-3結尾：我们將事務A和B稱為事務。A首先開始：

Begin tran
Reads the value 5, store it in @QtyRemain and verify that we have >= 0 after subtraction of @QtyRequired (being 4)
Update, value in table after update is 1 (current val 5 minus required 4 = 1).
Waits...
End tran

這裏是B，在等待tran A期間開始：

Begin tran
Reads the value 5, store it in @QtyRemain and verify we have >= 0 after subtraction of @QtyRequired (being 4)
Update, blocked until end of tran A...
...Update now occurs. Value in table after update is -3 (current val 1 minus required 4 = -3)