在高并发期间，我们遇到了查询返回无意义结果的问题——结果违反了发出查询的逻辑。重现该问题需要一段时间。我已经设法将可重现的问题提炼为少量的 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 < 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 > 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 > 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 < 100000)
BEGIN
    SET @attempts = @attempts+1

    /*Flip the earlier transaction from Booked back to WaitingList
        Because it's now on the waiting list -> there is a free slot.
        Because there is a free slot -> a transaction can be booked.
        Because this is the earlier transaction -> 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 选项卡并运行它们。第二个和第三个脚本将引发错误。帮我弄清楚为什么会出现错误。