Is the expression in SQL Server ACID ?
What do I mean by this
Given one T-SQL statement not enclosed in BEGIN TRANSACTION / COMMIT TRANSACTION , the actions of this statement are:
- Atomic : either all of its data modifications are executed, or none of them are executed.
- Agreed . Upon completion, the transaction should leave all data in a consistent state.
- Isolated . Modifications performed by concurrent transactions must be isolated from changes made by any other concurrent transactions.
- Durable : after a transaction is completed, its effects are permanently installed in the system.
The reason I ask
I have one statement in a live system that apparently violates the query rules.
Actually my T-SQL statement:
--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 currently unbooked AND Slots.Available > 0 --only book it if there empty slots ORDER BY t2.CreatedDate)
Note But a simpler conceptual option 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, note that they are single statements ( UPDATE...SET...WHERE ).
There are cases when the wrong transaction is “booked”; he actually picks a later transaction. After looking at it for 16 hours, I'm at a standstill. As if SQL Server was just breaking the rules.
Interestingly, if the results of the Slots view change before the update occurs? What if SQL Server does not hold SHARED in transactions on this date ? Is it possible that one operator may be inconsistent?
So I decided to check it out
I decided to check if the results of the subqueries or internal operations are not consistent. I created a simple table with one int column:
CREATE TABLE CountingNumbers ( Value int PRIMARY KEY NOT NULL )
Out of several closed loop connections, I call one T-SQL expression :
INSERT INTO CountingNumbers (Value) SELECT ISNULL(MAX(Value), 0)+1 FROM CountingNumbers
In other words, pseudo code:
while (true) { ADOConnection.Execute(sql); }
And after 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 one statement was not an atom makes me wonder if single statements are atomic.
Or there is a more subtle statement definition that differs from (for example) in what SQL Server considers an expression:
Does this basically mean that SQL Server statements are not atomic in a single T-SQL statement?
And if one operator is atomic, what explains the key violation?
From stored procedure
Instead of remote clients 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 how they too violated the 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.
Thus, the refusal does not depend on ADO, ADO.net or on any of the above.
For 15 years, I have been working on the assumption that one statement in SQL Server is consistent; only
What about the INSULATION LEVEL of OPERATION xxx?
For different variants of an executable SQL package:
default (read read) : key violation
INSERT INTO CountingNumbers (Value) SELECT ISNULL(MAX(Value), 0)+1 FROM CountingNumbers
default value (read read), explicit transaction : no error
BEGIN TRANSACTION INSERT INTO CountingNumbers (Value) SELECT ISNULL(MAX(Value), 0)+1 FROM CountingNumbers COMMIT TRANSACTION
serializable : dead end
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 changing the database to enable highlighting): 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 )
Every request I ever wrote is broken
This, of course, changes the situation. Every update I've ever written is fundamentally broken. For example:.
Incorrect value; because after MAX and before UPDATE another invoice can be inserted. 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, SalesYTD is incorrect.
How could I do all these years.