是SQL Server中的一条语句ACID?

我的意思是

给定单个 T-SQL 语句，未包装在BEGIN TRANSACTION / COMMIT TRANSACTION，是该语句的操作：

• Atomic：要么执行所有数据修改，要么不执行任何数据修改。
• 持续的：事务完成后，必须使所有数据保持一致状态。
• Isolated：并发事务所做的修改必须与任何其他并发事务所做的修改隔离。
• Durable：交易完成后，其影响将永久保留在系统中。

我问的原因

我在实时系统中有一条语句似乎违反了查询规则。

实际上我的 T-SQL 语句是：

--If there are any slots available, 
--then find the earliest unbooked transaction and mark it booked
UPDATE Transactions
SET Booked = 1
WHERE TransactionID = (
   SELECT TOP 1 TransactionID
   FROM Slots
      INNER JOIN Transactions t2
      ON Slots.SlotDate = t2.TransactionDate
   WHERE t2.Booked = 0 --only book it if it's currently unbooked
   AND Slots.Available > 0 --only book it if there's empty slots
   ORDER BY t2.CreatedDate)

Note：但一个更简单的概念变体可能是：

--Give away one gift, as long as we haven't given away five
UPDATE Gifts
SET GivenAway = 1
WHERE GiftID = (
   SELECT TOP 1 GiftID
   FROM Gifts
   WHERE g2.GivenAway = 0
   AND (SELECT COUNT(*) FROM Gifts g2 WHERE g2.GivenAway = 1) < 5
   ORDER BY g2.GiftValue DESC
)

在这两个语句中，请注意它们是单个语句 (UPDATE...SET...WHERE).

存在错误交易的情况"booked";它实际上是在选择一个later交易。盯着这个看了16个小时后，我被难住了。就好像 SQL Server 完全违反了规则一样。

我想知道如果结果如何Slots视图在更新发生之前发生变化？如果 SQL Server 没有持有怎么办SHARED锁定在交易在那date？是否有可能单个语句不一致？

所以我决定测试一下

我决定检查子查询或内部操作的结果是否不一致。我创建了一个简单的表，其中包含一个int column:

CREATE TABLE CountingNumbers (
   Value int PRIMARY KEY NOT NULL
)

从多个连接，在一个紧密的循环中，我称之为单个 T-SQL 语句:

INSERT INTO CountingNumbers (Value)
SELECT ISNULL(MAX(Value), 0)+1 FROM CountingNumbers

换句话说，伪代码是：

while (true)
{
    ADOConnection.Execute(sql);
}

几秒钟之内我得到：

Violation of PRIMARY KEY constraint 'PK__Counting__07D9BBC343D61337'. 
Cannot insert duplicate key in object 'dbo.CountingNumbers'. 
The duplicate value is (1332)

语句是原子的吗？

单个语句不是原子的这一事实让我想知道单个语句是否是原子的？

或者还有更多subtle的定义陈述，这与（例如）SQL Server 认为的语句不同：

这是否从根本上意味着在单个 T-SQL 语句的范围内，SQL Server 语句不是原子的？

如果单个语句是原子的，那么是什么导致了密钥违规？

从存储过程中

而不是远程客户端打开n连接，我用存​​储过程尝试了它：

CREATE procedure [dbo].[DoCountNumbers] AS

SET NOCOUNT ON;

DECLARE @bumpedCount int
SET @bumpedCount = 0

WHILE (@bumpedCount < 500) --safety valve
BEGIN
SET @bumpedCount = @bumpedCount+1;

PRINT 'Running bump '+CAST(@bumpedCount AS varchar(50))

INSERT INTO CountingNumbers (Value)
SELECT ISNULL(MAX(Value), 0)+1 FROM CountingNumbers

IF (@bumpedCount >= 500)
BEGIN
    PRINT 'WARNING: Bumping safety limit of 500 bumps reached'
END
END

PRINT 'Done bumping process'

在 SSMS 中打开 5 个选项卡，在每个选项卡中按 F5，然后观察它们是否也违反了 ACID：

Running bump 414
Msg 2627, Level 14, State 1, Procedure DoCountNumbers, Line 14
Violation of PRIMARY KEY constraint 'PK_CountingNumbers'. 
Cannot insert duplicate key in object 'dbo.CountingNumbers'. 
The duplicate key value is (4414).
The statement has been terminated.

因此，该故障与 ADO、ADO.net 无关，或者与以上都无关。

15 年来，我一直假设 SQL Server 中的单个语句是一致的；以及唯一的

事务隔离级别 xxx 怎么样？

对于要执行的 SQL 批处理的不同变体：

• 默认（读已提交）：关键违规

  INSERT INTO CountingNumbers (Value)
  SELECT ISNULL(MAX(Value), 0)+1 FROM CountingNumbers
  

• 默认（读已提交），显式事务：无错误按键违规

  BEGIN TRANSACTION
  INSERT INTO CountingNumbers (Value)
  SELECT ISNULL(MAX(Value), 0)+1 FROM CountingNumbers
  COMMIT TRANSACTION
  

• 可序列化: 僵局

  SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
  BEGIN TRANSACTION
  INSERT INTO CountingNumbers (Value)
  SELECT ISNULL(MAX(Value), 0)+1 FROM CountingNumbers
  COMMIT TRANSACTION
  SET TRANSACTION ISOLATION LEVEL READ COMMITTED
  

• snapshot（更改数据库以启用快照隔离后）：密钥违规

  SET TRANSACTION ISOLATION LEVEL SNAPSHOT
  BEGIN TRANSACTION
  INSERT INTO CountingNumbers (Value)
  SELECT ISNULL(MAX(Value), 0)+1 FROM CountingNumbers
  COMMIT TRANSACTION
  SET TRANSACTION ISOLATION LEVEL READ COMMITTED
  

Bonus

• 微软 SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64)
• 默认事务隔离级别（READ COMMITTED)

结果我写的每一个查询都被破坏了

这肯定会改变事情。我写过的每一个更新语句都从根本上被破坏了。例如。：

--Update the user with their last invoice date
UPDATE Users 
SET LastInvoiceDate = (SELECT MAX(InvoiceDate) FROM Invoices WHERE Invoices.uid = Users.uid)

值错误；因为可以在之后插入另一张发票MAX和之前UPDATE。或者来自 BOL 的示例：

UPDATE Sales.SalesPerson
SET SalesYTD = SalesYTD + 
    (SELECT SUM(so.SubTotal) 
     FROM Sales.SalesOrderHeader AS so
     WHERE so.OrderDate = (SELECT MAX(OrderDate)
                           FROM Sales.SalesOrderHeader AS so2
                           WHERE so2.SalesPersonID = so.SalesPersonID)
     AND Sales.SalesPerson.BusinessEntityID = so.SalesPersonID
     GROUP BY so.SalesPersonID);

没有独占锁，SalesYTD是错的。

这些年我怎么能做点什么。

我一直在假设 SQL Server 中的单个语句是一致的情况下进行操作

这个假设是错误的。以下两个事务具有相同的锁定语义：

STATEMENT

BEGIN TRAN; STATEMENT; COMMIT

完全没有区别。单个语句和自动提交不会改变任何内容。

因此，将所有逻辑合并到一个语句中并没有帮助（如果有的话，那也是偶然的，因为计划发生了变化）。

让我们解决手头的问题。SERIALIZABLE将修复您所看到的不一致，因为它保证您的事务的行为就像单线程执行一样。同样，他们的行为就好像他们立即执行了一样。

你将会陷入僵局。如果您同意重试循环，那么此时您就完成了。

如果您想投入更多时间，请应用锁定提示来强制对相关数据进行独占访问：

UPDATE Gifts  -- U-locked anyway
SET GivenAway = 1
WHERE GiftID = (
   SELECT TOP 1 GiftID
   FROM Gifts WITH (UPDLOCK, HOLDLOCK) --this normally just S-locks.
   WHERE g2.GivenAway = 0
    AND (SELECT COUNT(*) FROM Gifts g2 WITH (UPDLOCK, HOLDLOCK) WHERE g2.GivenAway = 1) < 5
   ORDER BY g2.GiftValue DESC
)

您现在将看到并发性减少。根据您的负载，这可能完全没问题。

您的问题的本质使得实现并发变得困难。如果您需要解决方案，我们需要应用更具侵入性的技术。

您可以稍微简化一下更新：

WITH g AS (
   SELECT TOP 1 Gifts.*
   FROM Gifts
   WHERE g2.GivenAway = 0
    AND (SELECT COUNT(*) FROM Gifts g2 WITH (UPDLOCK, HOLDLOCK) WHERE g2.GivenAway = 1) < 5
   ORDER BY g2.GiftValue DESC
)
UPDATE g  -- U-locked anyway
SET GivenAway = 1

这消除了一个不必要的连接。