单个SQL Server语句是原子的还是一致的？

是SQL Server中的声明ACID吗？

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

原子:要么执行所有数据修改,要么不执行任何数据修改.

一致:完成后,事务必须使所有数据保持一致状态.

隔离:并发事务所做的修改必须与任何其他并发事务所做的修改隔离.

持久:交易完成后,其效果将永久存在于系统中.

我问的原因

我在实时系统中有一个声明似乎违反了查询规则.

实际上我的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)

注意:但更简单的概念变体可能是:

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

有些情况下错误的交易被"预订" ; 它实际上是在选择一个以后的交易.盯着这个16个小时后,我很难过.就好像SQL Server只是违反规则一样.

我想知道Slots在更新发生之前视图的结果是否会发生变化？如果SQL Server 在该日期没有SHARED对事务进行锁定怎么办？单个语句是否可能不一致？

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

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)

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

或者是否有一个更微妙的语句定义,它与(例如)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中的单个语句是一致的情况下运行; 而唯一的

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

default(读已提交):密钥违规

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

default(读提交),显式事务:没有错误密钥违规

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

快照(在更改数据库以启用快照隔离后):密钥违规

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)

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

这无疑会改变一切.我写过的每一个更新声明都从根本上被打破了.例如:

--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是错误的.

这些年来我怎么能做任何事情.