最近，我受命调试电子商务应用程序中的一个奇怪问题。应用程序升级后，该站点开始不时挂起，我被送去调试。检查事件日志后，我发现SQL服务器在几分钟内写入了约200 000个事件，并显示一条消息，说明约束失败。经过大量调试和跟踪，我找到了罪魁祸首。我删除了一些不必要的代码并对其进行了一些清理，但实际上就是这样

WHILE EXISTS (SELECT * FROM ShoppingCartItem WHERE ShoppingCartItem.PurchID = @PurchID)
BEGIN
    SELECT TOP 1 
        @TmpGFSID = ShoppingCartItem.GFSID, 
        @TmpQuantity = ShoppingCartItem.Quantity,
        @TmpShoppingCartItemID = ShoppingCartItem.ShoppingCartItemID,
    FROM
        ShoppingCartItem INNER JOIN GoodsForSale on ShoppingCartItem.GFSID = GoodsForSale.GFSID
    WHERE ShoppingCartItem.PurchID = @PurchID

    EXEC @ErrorCode = spGoodsForSale_ReverseReservations @TmpGFSID, @TmpQuantity
    IF @ErrorCode <> 0
    BEGIN
        Goto Cleanup    
    END

    DELETE FROM ShoppingCartItem WHERE ShoppingCartItem.ShoppingCartItemID = @TmpShoppingCartItemID
    -- @@ROWCOUNT is 1 after this
END

事实：

• 只有一个或者两个记录与第一个选择子句匹配
• DELETE语句中的RowCount表示已将其删除
• WHILE条款将永远循环

该过程已被重写，以选择应删除到临时内存表中的行，从而解决了眼前的问题，但这确实激发了我的好奇心。

为什么会永远循环？

说明：删除不会失败(调试后，删除stmt之后@@ rowcount为1)
澄清2：SELECT TOP ...子句是否由任何特定字段排序都没有关系，因为具有返回ID的记录将被删除，因此在下一个循环中它将获得另一个记录。

更新：检查了Subversion日志后，我发现了导致该存储过程陷入混乱的罪魁祸首。我能找到的唯一真正的区别是，以前在SELECT TOP 1语句中没有联接，即没有联接的情况下，删除周围没有任何事务语句就可以工作。似乎是由于引入了联接，使SQL Server变得更加挑剔。

更新说明：brien指出不需要联接，但实际上我们确实使用了GoodsForSale表中的某些字段，但我删除了它们只是为了保留代码，以便我们专注于手头的问题。

解决方案

显然，某些事情并没有在应该删除或者修改的地方进行。如果条件在下一次迭代中仍然相同，那么它将继续下去。

另外，我们正在比较@TmpShoppingCartItemID，而不是@PurchID。我可以看到它们之间可能有什么不同，我们可以删除与while语句中要检查的行不同的行。

我不确定我是否理解问题，但是在select子句中它正在与另一个表进行内部联接。该联接可能导致没有记录，然后删除失败。尝试使用左联接。

在ShoppingCartItem中是否有带有@PurchID的记录，而GFSID不在GoodsForSale表中？这将解释为什么EXISTS返回true，但是没有更多记录可删除。

我们是在显式还是隐式事务模式下操作？

由于我们处于显式模式，因此我认为我们需要用BEGIN TRANSACTION和COMMIT TRANSACTION语句来包围DELETE操作。

WHILE EXISTS (SELECT * FROM ShoppingCartItem WHERE ShoppingCartItem.PurchID = @PurchID)
BEGIN
    SELECT TOP 1 
            @TmpGFSID = ShoppingCartItem.GFSID, 
            @TmpQuantity = ShoppingCartItem.Quantity,
            @TmpShoppingCartItemID = ShoppingCartItem.ShoppingCartItemID,
    FROM
            ShoppingCartItem INNER JOIN GoodsForSale on ShoppingCartItem.GFSID = GoodsForSale.GFSID
    WHERE ShoppingCartItem.PurchID = @PurchID

    EXEC @ErrorCode = spGoodsForSale_ReverseReservations @TmpGFSID, @TmpQuantity
    IF @ErrorCode <> 0
    BEGIN
            Goto Cleanup    
    END

    BEGIN TRANSACTION delete

        DELETE FROM ShoppingCartItem WHERE ShoppingCartItem.ShoppingCartItemID = @TmpShoppingCartItemID
        -- @@ROWCOUNT is 1 after this

    COMMIT TRANSACTION delete
END

澄清：我们需要使用事务的原因是，在我们执行COMMIT操作之前，删除实际上不会在数据库中进行。通常在原子事务中有多个写操作时使用。基本上，仅当所有操作成功后，我们才希望对数据库进行更改。

在情况下，只有1个操作，但是由于我们处于显式事务模式，因此需要告诉SQL Server真正进行更改。

如果以上评论到目前为止对我们没有帮助，我建议添加/替换：

DECLARE Old@ShoppingCartItemID INT

SET @OldShoppingCartItemID = 0

WHILE EXISTS (SELECT ... WHERE ShoppingCartItemID > @ShoppingCartItemID)

SELECT TOP 1 WHERE ShoppingCartItemID > @OldShoppingCartItemID ORDER BY ShoppingCartItemID 

SET @OldShoppingCartItemID = @TmpShoppingCartItemID

FROM
  ShoppingCartItem
    INNER JOIN
  GoodsForSale
    on ShoppingCartItem.GFSID = GoodsForSale.GFSID

糟糕，加入使结果集减少到零行。

SELECT TOP 1
    @TmpGFSID = ShoppingCartItem.GFSID,
    @TmpQuantity = ShoppingCartItem.Quantity,
    @TmpShoppingCartItemID =
      ShoppingCartItem.ShoppingCartItemID

糟糕，我们对没有行的集合使用了多重分配。这将导致变量保持不变(它们将具有与上次遍历循环时相同的值)。在这种情况下，变量不会分配为null。

如果将这段代码放在循环的开始，它将(正确地)更快地失败：

SELECT
    @TmpGFSID = null,
    @TmpQuantity = null,
    @TmpShoppingCartItemID = null

如果我们更改代码以获取键(不进行联接)，然后在第二个查询中通过键获取相关数据，那么我们将获胜。

如果GoodsForSale表中不存在任何购物车项目，则这将陷入无限循环。

尝试更改存在声明以考虑到这一点

(SELECT * FROM ShoppingCartItem WHERE  JOIN GoodsForSale on ShoppingCartItem.GFSID = GoodsForSale.GFSID where ShoppingCartItem.PurchID = @PurchID)

或者更妙的是，重写它，这样就不需要循环。这样的循环是一个无限的循环，等待发生。我们应该替换为基于集合的操作和事务。