¿Es atómica y consistente una sola statement SQL Server?

Es una statement en SQL Server ACID ?

Lo que quiero decir con eso

Dado un solo enunciado T-SQL, no incluido en BEGIN TRANSACTION / COMMIT TRANSACTION , son las acciones de ese enunciado:

  • Atómico : se realizan todas sus modificaciones de datos o no se realiza ninguna de ellas.
  • Constante : cuando se completa, una transacción debe dejar todos los datos en un estado consistente.
  • Aislado : las modificaciones hechas por transacciones concurrentes deben aislarse de las modificaciones realizadas por cualquier otra transacción simultánea.
  • Durable : una vez que se ha completado una transacción, sus efectos están permanentemente en su lugar en el sistema.

La razón por la que pregunto

Tengo una sola statement en un sistema en vivo que parece estar violando las reglas de la consulta.

En efecto, mi statement de T-SQL es:

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

Nota : Pero una variante conceptual más simple podría ser:

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

En ambas declaraciones, observe que son declaraciones únicas ( UPDATE...SET...WHERE ).

Hay casos en los que se “reserva” la transacción incorrecta; en realidad está escogiendo una transacción posterior . Después de mirar esto durante 16 horas, estoy perplejo. Es como si SQL Server simplemente violara las reglas.

Me pregunté qué pasaría si los resultados de la vista de Slots cambian antes de que ocurra la actualización. ¿Qué pasa si SQL Server no mantiene lockings SHARED en las transacciones en esa fecha ? ¿Es posible que una sola statement pueda ser inconsistente?

Así que decidí probarlo

Decidí verificar si los resultados de las sub-consultas u operaciones internas son inconsistentes. Creé una tabla simple con una sola columna int :

 CREATE TABLE CountingNumbers ( Value int PRIMARY KEY NOT NULL ) 

Desde múltiples conexiones, en un ciclo cerrado, llamo a la sola instrucción T-SQL :

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

En otras palabras, el pseudo-código es:

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

Y en unos pocos segundos obtengo:

 Violation of PRIMARY KEY constraint 'PK__Counting__07D9BBC343D61337'. Cannot insert duplicate key in object 'dbo.CountingNumbers'. The duplicate value is (1332) 

¿Las declaraciones son atómicas?

El hecho de que una sola afirmación no fuera atómica me hace preguntarme si las declaraciones individuales son atómicas.

¿O hay una definición más sutil de enunciado , que difiere de (por ejemplo) lo que SQL Server considera una statement?

enter image description here

¿Esto significa fundamentalmente que, dentro de los límites de una sola instrucción T-SQL, las declaraciones de SQL Server no son atómicas?

Y si una sola statement es atómica, ¿a qué se debe la violación de la clave?

Desde dentro de un procedimiento almacenado

En lugar de un cliente remoto que abre n conexiones, lo intenté con un procedimiento almacenado:

 CREATE procedure [dbo].[DoCountNumbers] AS SET NOCOUNT ON; DECLARE @bumpedCount int SET @bumpedCount = 0 WHILE (@bumpedCount = 500) BEGIN PRINT 'WARNING: Bumping safety limit of 500 bumps reached' END END PRINT 'Done bumping process' 

y abrió 5 tabs en SSMS, presionó F5 en cada una, y vio que también violaban a 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. 

Entonces la falla es independiente de ADO, ADO.net, o ninguno de los anteriores.

Durante 15 años he estado operando bajo la suposición de que una única statement en SQL Server es consistente; y el único

¿Qué pasa con TRANSACTION ISOLATION LEVEL xxx?

Para diferentes variantes del lote SQL para ejecutar:

  • predeterminado (leer comprometido) : violación de clave

     INSERT INTO CountingNumbers (Value) SELECT ISNULL(MAX(Value), 0)+1 FROM CountingNumbers 
  • transacción predeterminada (lectura confirmada), explícita : sin error de clave de violación

     BEGIN TRANSACTION INSERT INTO CountingNumbers (Value) SELECT ISNULL(MAX(Value), 0)+1 FROM CountingNumbers COMMIT TRANSACTION 
  • serializable : punto muerto

     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 
  • instantánea (después de alterar la base de datos para habilitar el aislamiento de instantáneas): violación de clave

     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 

Prima

  • Microsoft SQL Server 2008 R2 (SP2) – 10.50.4000.0 (X64)
  • Nivel de aislamiento de transacción predeterminado ( READ COMMITTED )

Resulta que todas las consultas que he escrito están rotas

Esto sin duda cambia las cosas. Cada statement de actualización que he escrito está fundamentalmente rota. P.ej:

 --Update the user with their last invoice date UPDATE Users SET LastInvoiceDate = (SELECT MAX(InvoiceDate) FROM Invoices WHERE Invoices.uid = Users.uid) 

Valor incorrecto; porque se podría insertar otra factura después del MAX y antes de la UPDATE . O un ejemplo de 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); 

sin holdlocks exclusivos, SalesYTD está mal.

¿Cómo he podido hacer algo todos estos años?

He estado operando bajo la suposición de que una única statement en SQL Server es consistente

Esa suposición es incorrecta. Las dos transacciones siguientes tienen una semántica de locking idéntica:

 STATEMENT BEGIN TRAN; STATEMENT; COMMIT 

No hay diferencia en absoluto. Las declaraciones individuales y las confirmaciones automáticas no cambian nada.

Así que fusionar toda la lógica en una statement no ayuda (si lo hace, fue por accidente porque el plan cambió).

Arreglemos el problema que tenemos entre manos. SERIALIZABLE corregirá la incoherencia que está viendo porque garantiza que sus transacciones se comporten como si se ejecutaran de manera única. De forma equivalente, se comportan como si se ejecutaran al instante.

Obtendrás puntos muertos. Si estás bien con un ciclo de rebash, ya terminaste en este punto.

Si desea invertir más tiempo, aplique sugerencias de locking para forzar el acceso exclusivo a los datos relevantes:

 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 ) 

Ahora verá concurrencia reducida. Eso podría ser totalmente correcto dependiendo de tu carga.

La naturaleza misma de su problema dificulta el logro de la concurrencia. Si necesita una solución para eso, tendríamos que aplicar más técnicas invasivas.

Puede simplificar la ACTUALIZACIÓN un poco:

 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 

Esto elimina una unión innecesaria.

A continuación se muestra un ejemplo de una instrucción UPDATE que incrementa un valor de contador de forma atómica

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