title: Is a single SQL Server statement atomic and consistent?
body: <p>Is a statement in SQL Server <code>ACID</code>?</p>
  
  <h1>What I mean by that</h1>
  
  <p>Given a single T-SQL statement, not wrapped in a <code>BEGIN TRANSACTION</code> / <code>COMMIT TRANSACTION</code>, are the actions of that statement:</p>
  
  <ul>
  <li><strong>Atomic</strong>: either all of its data modifications are performed, or none of them is performed.</li>
  <li><strong>Consistent</strong>: When completed, a transaction must leave all data in a consistent state.</li>
  <li><strong>Isolated</strong>: Modifications made by concurrent transactions must be isolated from the modifications made by any other concurrent transactions.</li>
  <li><strong>Durable</strong>: After a transaction has completed, its effects are permanently in place in the system.</li>
  </ul>
  
  <h1>The reason I ask</h1>
  
  <p>I have a single statement in a live system that appears to be violating the rules of the query.</p>
  
  <p>In effect my T-SQL statement is:</p>
  
  <pre><code>--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 &gt; 0 --only book it if there's empty slots
     ORDER BY t2.CreatedDate)
  </code></pre>
  
  <p><strong>Note</strong>: But a simpler conceptual variant might be:</p>
  
  <pre><code>--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) &lt; 5
     ORDER BY g2.GiftValue DESC
  )
  </code></pre>
  
  <p>In both of these statements, notice that they are single statements (<code>UPDATE...SET...WHERE</code>).</p>
  
  <p>There are cases where the wrong transaction is being <em>"booked"</em>; it's actually picking a <em>later</em> transaction. After staring at this for 16 hours, I'm stumped. It's as though SQL Server is simply violating the rules.</p>
  
  <p>I wondered what if the results of the <code>Slots</code> view is changing before the update happens? What if SQL Server is not holding <code>SHARED</code> locks on the <strong>transactions</strong> on that <strong>date</strong>? Is it possible that a single statement can be inconsistent?</p>
  
  <h1>So I decided to test it</h1>
  
  <p>I decided to check if the results of sub-queries, or inner operations, are inconsistent. I created a simple table with a single <strong><code>int</code></strong> column:</p>
  
  <pre><code>CREATE TABLE CountingNumbers (
     Value int PRIMARY KEY NOT NULL
  )
  </code></pre>
  
  <p>From multiple connections, in a tight loop, I call the <strong>single T-SQL statement</strong>:</p>
  
  <pre><code>INSERT INTO CountingNumbers (Value)
  SELECT ISNULL(MAX(Value), 0)+1 FROM CountingNumbers
  </code></pre>
  
  <p>In other words the pseudo-code is:</p>
  
  <pre><code>while (true)
  {
      ADOConnection.Execute(sql);
  }
  </code></pre>
  
  <p>And within a few seconds I get: </p>
  
  <pre><code>Violation of PRIMARY KEY constraint 'PK__Counting__07D9BBC343D61337'. 
  Cannot insert duplicate key in object 'dbo.CountingNumbers'. 
  The duplicate value is (1332)
  </code></pre>
  
  <h1>Are statements atomic?</h1>
  
  <p>The fact that a single statement wasn't atomic makes me wonder if single statements are atomic?</p>
  
  <p>Or is there a more <em>subtle</em> definition of <em>statement</em>, that differs from (for example) what SQL Server considers a statement:</p>
  
  <p><img src="https://i.stack.imgur.com/mnnI6.png" alt="enter image description here"></p>
  
  <p>Does this fundamentally means that within the confines of a single T-SQL statement, SQL Server statements are not atomic?</p>
  
  <p>And if a single statement is atomic, what accounts for the key violation?</p>
  
  <h1>From within a stored procedure</h1>
  
  <p>Rather than a remote client opening <em>n</em> connections, I tried it with a stored procedure:</p>
  
  <pre><code>CREATE procedure [dbo].[DoCountNumbers] AS
  
  SET NOCOUNT ON;
  
  DECLARE @bumpedCount int
  SET @bumpedCount = 0
  
  WHILE (@bumpedCount &lt; 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 &gt;= 500)
  BEGIN
      PRINT 'WARNING: Bumping safety limit of 500 bumps reached'
  END
  END
  
  PRINT 'Done bumping process'
  </code></pre>
  
  <p>and opened 5 tabs in SSMS, pressed F5 in each, and watched as they too violated ACID:</p>
  
  <pre><code>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.
  </code></pre>
  
  <p>So the failure is independent of ADO, ADO.net, or none of the above.</p>
  
  <p>For 15 years i've been operating under the assumption that a single statement in SQL Server is consistent; and the only </p>
  
  <h1>What about TRANSACTION ISOLATION LEVEL xxx?</h1>
  
  <p>For different variants of the SQL batch to execute:</p>
  
  <ul>
  <li><p><strong>default (read committed)</strong>: key violation</p>
  
  <pre><code>INSERT INTO CountingNumbers (Value)
  SELECT ISNULL(MAX(Value), 0)+1 FROM CountingNumbers
  </code></pre></li>
  <li><p><strong>default (read committed), explicit transaction</strong>: <strike>no error</strike> key violation</p>
  
  <pre><code>BEGIN TRANSACTION
  INSERT INTO CountingNumbers (Value)
  SELECT ISNULL(MAX(Value), 0)+1 FROM CountingNumbers
  COMMIT TRANSACTION
  </code></pre></li>
  <li><p><strong>serializable</strong>: deadlock</p>
  
  <pre><code>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
  </code></pre></li>
  <li><p><strong>snapshot</strong> (after altering database to enable snapshot isolation): key violation</p>
  
  <pre><code>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
  </code></pre></li>
  </ul>
  
  <h1>Bonus</h1>
  
  <ul>
  <li>Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64)</li>
  <li>Default transaction isolation level (<code>READ COMMITTED</code>)</li>
  </ul>
  
  <h1>Turns out every query I've ever written is broken</h1>
  
  <p>This certainly changes things. Every update statement I've ever written is fundamentally broken. E.g.:</p>
  
  <pre><code>--Update the user with their last invoice date
  UPDATE Users 
  SET LastInvoiceDate = (SELECT MAX(InvoiceDate) FROM Invoices WHERE Invoices.uid = Users.uid)
  </code></pre>
  
  <p>Wrong value; because another invoice could be inserted after the <code>MAX</code> and before the <code>UPDATE</code>. Or an example from BOL:</p>
  
  <pre><code>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);
  </code></pre>
  
  <p>without exclusive holdlocks, the <code>SalesYTD</code> is wrong.</p>
  
  <p>How have I been able to do anything all these years.</p>