Is a single SQL Server statement atomic and consistent?

sql-server, sql-server-2008-r2

Solution

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.

Problem

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.

Original source