本文介绍了单个 SQL Server 语句是否具有原子性和一致性?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

是 SQL Server ACID 中的语句吗?

Is a statement in SQL Server ACID?

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

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

Atomic:要么执行其所有数据修改，要么不执行任何修改.
一致:交易完成后，所有数据必须保持一致状态.
隔离:并发事务所做的修改必须与任何其他并发事务所做的修改相隔离.
持久:交易完成后，其影响将永久存在于系统中.

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 在那个日期没有对事务持有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.

推荐答案

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

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.

这篇关于单个 SQL Server 语句是否具有原子性和一致性?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！