Is a single SQL Server statement atomic and consistent?

Question

Is a statement in SQL Server ACID?

What I mean by that

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.

The reason I ask

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

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 ) 

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

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.

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?

So I decided to test it

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 ) 

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 

In other words the pseudo-code is:

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

And within a few seconds I get:

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?

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

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?

From within a stored procedure

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' 

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. 

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

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

What about TRANSACTION ISOLATION LEVEL xxx?

For different variants of the SQL batch to execute:

• default (read committed): key violation

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

• default (read committed), explicit transaction: no error key violation

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

• serializable: deadlock

  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 

Bonus

• Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64)
• Default transaction isolation level (READ COMMITTED)

Turns out every query I've ever written is broken

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) 

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

without exclusive holdlocks, the SalesYTD is wrong.

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

Answer 1

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

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.