Is a single SQL Server statement atomic and consistent?

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.

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

Below is an example of an UPDATE statement that does increment a counter value atomically

-- Do this once for test setup
CREATE TABLE CountingNumbers (Value int PRIMARY KEY NOT NULL)
INSERT INTO CountingNumbers VALUES(1) 

-- Run this in parallel: start it in two tabs on SQL Server Management Studio
-- You will see each connection generating new numbers without duplicates and without timeouts
while (1=1)
BEGIN
  declare @nextNumber int
  -- Taking the Update lock is only relevant in case this statement is part of a larger transaction
  -- to prevent deadlock
  -- When executing without a transaction, the statement will itself be atomic
  UPDATE CountingNumbers WITH (UPDLOCK, ROWLOCK) SET @nextNumber=Value=Value+1
  print @nextNumber
END

Select does not lock exclusively, even serializable does, but only for the time the select is executed! Once the select is over, the select lock is gone. Then, update locks take on as they now know what to lock as Select has return results. Meanwhile, anyone else can Select again!

The only sure way to safely read and lock a row is:

begin transaction

--lock what i need to read
update mytable set col1=col1 where mykey=@key

--now read what i need
select @d1=col1,@d2=col2 from mytable where mykey=@key

--now do here calculations checks whatever i need from the row i read to decide my update
if @d1<@d2 set @d1=@d2 else set @d1=@d2 * 2 --just an example calc

--now do the actual update on what i read and the logic
update mytable set col1=@d1,col2=@d2 where mykey=@key

commit transaction

This way any other connection running the same statement for the same data it will surely wait at the first (fake) update statement until the previous is done. This ensures that when lock is released only one connection will granted permission to lock request to 'update' and this one will surely read committed finalized data to make calculations and decide if and what to actually update at the second 'real' update.

In other words, when you need to select information to decide if/how to update, you need a begin/commit transaction block plus you need to start with a fake update of what you need to select - before you select it(update output will also do).