单个 SQL Server 语句是原子的且一致的吗?

2022-01-16 00:00:00 sql-server sql-server-2008-r2

SQL Server ACID中是否有语句?

Is a statement in SQL Server ACID?

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

Given a single T-SQL statement, not wrapped in a BEGIN TRANSACTION / COMMIT TRANSACTION, are the actions of that statement:

  • 原子:要么执行所有数据修改，要么不执行任何修改.
  • 一致:完成后，事务必须使所有数据保持一致状态.
  • 隔离:并发事务所做的修改必须与任何其他并发事务所做的修改隔离.
  • 持久性:交易完成后，其效果将永久存在于系统中.
  • Atomic: either all of its data modifications are performed, or none of them is performed.
  • Consistent: When completed, a transaction must leave all data in a consistent state.
  • Isolated: Modifications made by concurrent transactions must be isolated from the modifications made by any other concurrent transactions.
  • Durable: After a transaction has completed, its effects are permanently in place in the system.

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

I have a single statement in a live system that appears to be violating the rules of the query.

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

In effect my T-SQL statement is:

--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: But a simpler conceptual variant might be:

--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).

In both of these statements, notice that they are single statements (UPDATE...SET...WHERE).

有些情况是错误的交易被预定"；它实际上是在选择稍后的交易.在盯着这个 16 个小时后，我被难住了.就好像 SQL Server 只是在违反规则.

There are cases where the wrong transaction is being "booked"; it's actually picking a later transaction. After staring at this for 16 hours, I'm stumped. It's as though SQL Server is simply violating the rules.

我想知道如果 Slots 视图的结果在更新发生之前发生了变化怎么办?如果 SQL Server 在那个 date 上没有对 transactions 持有 SHARED 锁怎么办?有没有可能单个语句不一致?

I wondered what if the results of the Slots view is changing before the update happens? What if SQL Server is not holding SHARED locks on the transactions on that date? Is it possible that a single statement can be inconsistent?

我决定检查子查询或内部操作的结果是否不一致.我用一个 int 列创建了一个简单的表:

I decided to check if the results of sub-queries, or inner operations, are inconsistent. I created a simple table with a single int column:

CREATE TABLE CountingNumbers (
   Value int PRIMARY KEY NOT NULL
)

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

From multiple connections, in a tight loop, I call the single T-SQL statement:

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)

语句是原子的吗?

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

Are statements atomic?

The fact that a single statement wasn't atomic makes me wonder if single statements are atomic?

或者是否有一个更微妙的语句定义，它不同于(例如)SQL Server 认为的语句:

Or is there a more subtle definition of statement, that differs from (for example) what SQL Server considers a statement:

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

Does this fundamentally means that within the confines of a single T-SQL statement, SQL Server statements are not atomic?

如果单个语句是原子的，那么密钥违规的原因是什么?

And if a single statement is atomic, what accounts for the key violation?

而不是远程客户端打开 n 个连接，我尝试使用存储过程:

Rather than a remote client opening n connections, I tried it with a stored procedure:

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:

and opened 5 tabs in SSMS, pressed F5 in each, and watched as they too violated 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 或以上都无关.

So the failure is independent of ADO, ADO.net, or none of the above.

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

For 15 years i've been operating under the assumption that a single statement in SQL Server is consistent; and the only

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

For different variants of the SQL batch to execute:

  • 默认(已提交读取):密钥违规

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 (after altering database to enable snapshot isolation): key violation

    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

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

    这肯定会改变一些事情.我写过的每一个更新声明都从根本上被破坏了.例如:

    This certainly changes things. Every update statement I've ever written is fundamentally broken. E.g.:

    --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 的示例:

    Wrong value; because another invoice could be inserted after the MAX and before the UPDATE. Or an example from 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 是错误的.

    without exclusive holdlocks, the SalesYTD is wrong.

    这些年来我是怎么做到的.

    How have I been able to do anything all these years.

    推荐答案

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

    I've been operating under the assumption that a single statement in SQL Server is consistent

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

    That assumption is wrong. The following two transactions have identical locking semantics:

    STATEMENT

BEGIN TRAN; STATEMENT; COMMIT

    完全没有区别.单个语句和自动提交不会改变任何东西.

    No difference at all. Single statements and auto-commits do not change anything.

    因此将所有逻辑合并到一个语句中并没有帮助(如果有，那是因为计划改变了，这是偶然的).

    So merging all logic into one statement does not help (if it does, it was by accident because the plan changed).

    让我们解决手头的问题.SERIALIZABLE 将解决您看到的不一致问题，因为它保证您的事务的行为就像它们以单线程执行一样.同样，它们的行为就像是立即执行一样.

    Let's fix the problem at hand. SERIALIZABLE will fix the inconsistency you are seeing because it guarantees that your transactions behave as if they executed single-threadedly. Equivalently, they behave as if they executed instantly.

    你会遇到死锁.如果您对重试循环没问题，那么此时您就完成了.

    You will be getting deadlocks. If you are ok with a retry loop, you're done at this point.

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

    If you want to invest more time, apply locking hints to force exclusive access to the relevant data:

    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
)

    您现在将看到并发性降低.根据您的负载，这可能完全没问题.

    You will now see reduced concurrency. That might be totally fine depending on your load.

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

    The very nature of your problem makes achieving concurrency hard. If you require a solution for that we'd need to apply more invasive techniques.

    你可以稍微简化一下更新:

    You can simplify the UPDATE a bit:

    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

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

    This gets rid of one unnecessary join.

    相关文章