SQL Server 在 UPDATE 期間如何同時返回新值和舊值？

September 17, 2021

在高並發期間，我們遇到了查詢返回無意義結果的問題——結果違反了發出查詢的邏輯。重現該問題需要一段時間。我已經設法將可重現的問題提煉為少量的 T-SQL。
注意：有問題的實時系統部分由 5 個表、4 個觸發器、2 個儲存過程和 2 個視圖組成。我已將真實係統簡化為更易於管理的已發布問題。事情已經被削減，列被刪除，儲存過程被內聯，視圖變成了公用表表達式，列的值發生了變化。這就是說，雖然下面的內容重現了錯誤，但它可能更難以理解。您必須避免想知道為什麼某些東西是這樣構造的。我在這裡試圖弄清楚為什麼錯誤情況會在這個玩具模型中重複發生。
/*
The idea in this system is that people are able to take days off. 
We create a table to hold these *"allocations"*, 
and declare sample data that only **1** production operator 
is allowed to take time off:
*/
IF OBJECT_ID('Allocations') IS NOT NULL DROP TABLE Allocations
CREATE TABLE [dbo].[Allocations](
   JobName varchar(50) PRIMARY KEY NOT NULL,
   Available int NOT NULL
)
--Sample allocation; there is 1 avaialable slot for this job
INSERT INTO Allocations(JobName, Available)
VALUES ('Production Operator', 1);

/*
Then we open up the system to the world, and everyone puts in for time. 
We store these requests for time off as *"transactions"*. 
Two production operators requested time off. 
We create sample data, and note that one of the users 
created their transaction first (by earlier CreatedDate):
*/
IF OBJECT_ID('Transactions') IS NOT NULL DROP TABLE Transactions;
CREATE TABLE [dbo].[Transactions](
   TransactionID int NOT NULL PRIMARY KEY CLUSTERED,
   JobName varchar(50) NOT NULL,
   ApprovalStatus varchar(50) NOT NULL,
   CreatedDate datetime NOT NULL
)
--Two sample transactions
INSERT INTO Transactions (TransactionID, JobName, ApprovalStatus, CreatedDate)
VALUES (52625, 'Production Operator', 'Booked', '20140125 12:00:40.820');
INSERT INTO Transactions (TransactionID, JobName, ApprovalStatus, CreatedDate)
VALUES (60981, 'Production Operator', 'WaitingList', '20150125 12:19:44.717');

/*
The allocation, and two sample transactions are now in the database:
*/
--Show the sample data
SELECT * FROM Allocations
SELECT * FROM Transactions
交易都插入為**WaitingList**. 接下來，我們有一個週期性任務執行，尋找空槽並將 WaitingList 上的任何人撞到 Booked 狀態。
在一個單獨的 SSMS 視窗中，我們有模擬的循環儲存過程：
/*
   Simulate recurring task that looks for empty slots, 
   and bumps someone on the waiting list into that slot.
*/
SET NOCOUNT ON;

--Reset the faulty row so we can continue testing
UPDATE Transactions SET ApprovalStatus = 'WaitingList'
WHERE TransactionID = 60981

--DBCC TRACEON(3604,1200,3916,-1) WITH NO_INFOMSGS

DECLARE @attempts int
SET @attempts = 0;

WHILE (@attempts &lt; 1000000)
BEGIN
   SET @attempts = @attempts+1;

   /*
       The concept is that if someone is already "Booked", then they occupy an available slot.
       We compare the configured amount of allocations (e.g. 1) to how many slots are used.
       If there are any slots leftover, then find the **earliest** created transaction that 
       is currently on the WaitingList, and set them to Booked.
   */

   PRINT '=== Looking for someone to bump ==='
   WITH AvailableAllocations AS (
       SELECT 
           a.JobName,
           a.Available AS Allocations, 
           ISNULL(Booked.BookedCount, 0) AS BookedCount, 
           a.Available-ISNULL(Booked.BookedCount, 0) AS Available
       FROM Allocations a
           FULL OUTER JOIN (
               SELECT t.JobName, COUNT(*) AS BookedCount
               FROM Transactions t
               WHERE t.ApprovalStatus IN ('Booked') 
               GROUP BY t.JobName
           ) Booked
           ON a.JobName = Booked.JobName
       WHERE a.Available &gt; 0
   )
   UPDATE Transactions SET ApprovalStatus = 'Booked'
   WHERE TransactionID = (
       SELECT TOP 1 t.TransactionID
       FROM AvailableAllocations aa
           INNER JOIN Transactions t
           ON aa.JobName = t.JobName
           AND t.ApprovalStatus = 'WaitingList'
       WHERE aa.Available &gt; 0
       ORDER BY t.CreatedDate 
   )


   IF EXISTS(SELECT * FROM Transactions WHERE TransactionID = 60981 AND ApprovalStatus = 'Booked')
   begin
       --DBCC TRACEOFF(3604,1200,3916,-1) WITH NO_INFOMSGS
       RAISERROR('The later tranasction, that should never be booked, managed to get booked!', 16, 1)
       BREAK;
   END
END
最後在第三個 SSMS 連接視窗中執行它。這模擬了一個並發問題，即早期事務從占用一個槽到等待列表：
/*
   Toggle the earlier transaction back to "WaitingList".
   This means there are two possibilies:
      a) the transaction is "Booked", meaning no slots are available. 
         Therefore nobody should get bumped into "Booked"
      b) the transaction is "WaitingList", 
         meaning 1 slot is open and both tranasctions are "WaitingList"
         The earliest transaction should then get "Booked" into the slot.

   There is no time when there is an open slot where the 
   first transaction shouldn't be the one to get it - he got there first.
*/
SET NOCOUNT ON;

--Reset the faulty row so we can continue testing
UPDATE Transactions SET ApprovalStatus = 'WaitingList'
WHERE TransactionID = 60981

DECLARE @attempts int
SET @attempts = 0;

WHILE (@attempts &lt; 100000)
BEGIN
   SET @attempts = @attempts+1

   /*Flip the earlier transaction from Booked back to WaitingList
       Because it's now on the waiting list -&gt; there is a free slot.
       Because there is a free slot -&gt; a transaction can be booked.
       Because this is the earlier transaction -&gt; it should always be chosen to be booked
   */
   --DBCC TRACEON(3604,1200,3916,-1) WITH NO_INFOMSGS

   PRINT '=== Putting the earlier created transaction on the waiting list ==='

   UPDATE Transactions
   SET ApprovalStatus = 'WaitingList'
   WHERE TransactionID = 52625

   --DBCC TRACEOFF(3604,1200,3916,-1) WITH NO_INFOMSGS

   IF EXISTS(SELECT * FROM Transactions WHERE TransactionID = 60981 AND ApprovalStatus = 'Booked')
   begin
       RAISERROR('The later tranasction, that should never be booked, managed to get booked!', 16, 1)
       BREAK;
   END
END
從概念上講，碰撞過程一直在尋找任何空槽。如果它找到一個，它將獲取最早的事務WaitingList並將其標記為Booked。
在沒有並發的情況下進行測試時，邏輯有效。我們有兩個交易：
12:00 pm：等候名單
12:20 pm：等候名單
有 1 個分配和 0 個已預訂交易，因此我們將較早的交易標記為已預訂：
12:00 pm：預訂
12:20 pm：等候名單
下次任務執行時，現在有 1 個插槽被佔用 - 所以沒有什麼可更新的。
如果我們然後更新第一個事務，並將其放入WaitingList：
UPDATE Transactions SET ApprovalStatus='WaitingList'
WHERE TransactionID = 60981
然後我們回到我們開始的地方：
12:00 pm：等候名單
12:20 pm：等候名單
注意：您可能想知道為什麼我將交易放回等候名單。這是簡化玩具模型的犧牲品。在實際系統中事務可以PendingApproval，其中也佔用一個槽。PendingApproval 交易在被批准後被放入等待列表。沒關係。別擔心。
但是當我引入並發性時，通過第二個視窗不斷地將第一筆交易在被預訂後放回等待列表中，那麼後來的交易成功地獲得了預訂：
12:00 pm：等候名單
12:20 pm：預訂
玩具測試腳本捕捉到這一點，並停止迭代：
Msg 50000, Level 16, State 1, Line 41
The later tranasction, that should never be booked, managed to get booked!
為什麼？
問題是，為什麼在這個玩具模型中會觸發這種救助條件？
第一筆交易的審批狀態有兩種可能的狀態：
Booked：這種情況下slot被佔用，後面的事務不能擁有它
WaitingList：在這種情況下，有一個空槽和兩個需要它的事務。但由於我們總是select最舊的交易（即ORDER BY CreatedDate）第一個交易應該得到它。
我想可能是因為其他索引
我了解到，在 UPDATE 開始並修改數據後，可以讀取舊值。在初始條件下：
聚集索引：Booked
非聚集索引：Booked
然後我進行更新，雖然聚集索引葉節點已被修改，但任何非聚集索引仍然包含原始值並且仍然可以讀取：
聚集索引（排他鎖）：Booked WaitingList
非聚集索引：（解鎖）Booked
但這並不能解釋觀察到的問題。是的，交易不再是Booked，這意味著現在有一個空槽。但是該更改尚未送出，它仍然被排他地持有。如果碰撞程序執行，它會：
block：如果快照隔離數據庫選項關閉
讀取舊值（例如Booked）：如果快照隔離開啟
無論哪種方式，碰撞工作都不會知道有一個空槽。
所以我不知道
幾天來，我們一直在努力弄清楚這些荒謬的結果是如何發生的。
您可能不了解原始系統，但有一套玩具可重現的腳本。當檢測到無效案件時，他們會採取救助措施。為什麼會被檢測到？為什麼會這樣？
獎金問題
納斯達克如何解決這個問題？cavirtex 怎麼樣？mtgox 怎麼樣？
tl;博士
共有三個腳本塊。將它們放入 3 個單獨的 SSMS 選項卡並執行它們。第二個和第三個腳本將引發錯誤。幫我弄清楚為什麼會出現錯誤。

預設的READ COMMITTED事務隔離級別保證你的事務不會讀取未送出的數據。它不保證您讀取的任何數據在您再次讀取時將保持不變（可重複讀取）或不會出現新數據（幻像）。
這些相同的考慮適用於同一語句中的多個數據訪問。
您的語句生成了一個多次UPDATE訪問該表的計劃，因此它容易受到不可重複讀取和幻像造成的影響。Transactions
READ COMMITTED該計劃有多種方法可以產生您在孤立情況下不期望的結果。
一個例子
第一個Transactions表訪問查找狀態為的行WaitingList。第二次訪問計算狀態為的條目（針對同一作業）的數量Booked。第一次訪問可能只返回後面的事務（此時是較早的事務Booked）。當第二次（計數）訪問發生時，較早的事務已更改為WaitingList. 因此，後面的行有資格更新到Booked狀態。
解決方案
有幾種方法可以設置隔離語義以獲得您想要的結果，主要是使用正確的隔離級別。
使用READ_COMMITTED_SNAPSHOT不會是一個解決方案。這種讀取送出隔離的實現確實提供了語句級讀取一致性（其中不可重複讀取和幻像是不可能的），但 SQL Server 在定位要更新的行時會使用更新鎖。這意味著用於定位要更新的行的訪問路徑始終讀取最新送出的數據。在同一語句中第二次讀取同一資料結構將讀取版本化行。
有關詳細資訊，請參閱我在 Read Committed Snapshot Isolation 下的文章 Data Modifications。
隔離級別將SNAPSHOT在事務開始時為所有讀取提供一致的快照，但您可能會遇到寫入衝突錯誤。
其他備註
我不得不說，我不會以這種方式設計架構或查詢。所涉及的工作比滿足規定的業務要求所必需的要多。也許這部分是問題簡化的結果，無論如何這是一個單獨的問題。
您看到的行為並不代表任何類型的錯誤。給定請求的隔離語義，腳本會產生正確的結果。像這樣的並發效果也不限於多次訪問數據的計劃。
讀送出隔離級別提供的保證比通常假設的要少得多。例如，跳過行和/或多次讀取同一行是完全可能的。

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

SQL Server 在 UPDATE 期間如何同時返回新值和舊值？

為什麼？

我想可能是因為其他索引

所以我不知道

獎金問題

tl;博士

一個例子

解決方案

其他備註

相關問答

DB Locks - 如何處理長事務並保持高並發？

為什麼 SERIALIZABLE 級別不會為 SQL Server 中的其他會話鎖定表

PostgreSQL 觸發更新函式鎖機制

將 (LEFT) JOIN 與 SELECT FOR UPDATE 組合時數據不一致

鎖定 Postgres 以進行 UPDATE / INSERT 組合

Mysql 鎖定 - 如何為每個 userId 在數據庫中獲取鎖定