Es una declaración en SQL ServerACID?

Lo que quiero decir con eso

Dada una sola instrucción T-SQL, no envuelta en unBEGIN TRANSACTION / COMMIT TRANSACTION, son las acciones de esa afirmación:

Atómico: se realizan todas las modificaciones de los datos o no se realiza ninguna de ellas.Consistente: Cuando se completa, una transacción debe dejar todos los datos en un estado consistente.Aislado: Las modificaciones realizadas por transacciones concurrentes deben estar aisladas de las modificaciones realizadas por cualquier otra transacción concurrente.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 declaración en un sistema en vivo que parece estar violando las reglas de la consulta.

En efecto, mi declaración 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 de estas declaraciones, observe que son declaraciones individuales (UPDATE...SET...WHERE).

Hay casos en los que se está realizando la transacción incorrecta."reservado"; en realidad está escogiendo unluego transacción. Después de mirar esto durante 16 horas, estoy perplejo. Es como si SQL Server simplemente estuviera violando las reglas.

Me pregunté qué pasaría si los resultados de laSlots vista está cambiando antes de que ocurra la actualización? ¿Qué pasa si SQL Server no está sosteniendoSHARED cerraduras en elactas en esefecha? ¿Es posible que una sola declaración pueda ser inconsistente?

Así que decidí probarlo

Decidí verificar si los resultados de las subconsultas o las operaciones internas son inconsistentes. He creado una tabla simple con una solaint columna:

CREATE TABLE CountingNumbers (
   Value int PRIMARY KEY NOT NULL
)

Desde conexiones múltiples, en un circuito cerrado, llamo alsentencia T-SQL única:

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

En otras palabras, el pseudocódigo es:

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

Y dentro de unos segundos me sale:

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

¿Son las declaraciones atómicas?

¿El hecho de que una sola declaración no fuera atómica me hace preguntarme si las declaraciones simples son atómicas?

O hay massutil definicion dedeclaración, que difiere de (por ejemplo) lo que SQL Server considera una declaración:

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

Y si una sola declaración es atómica, ¿qué explica la violación clave?

Desde dentro de un procedimiento almacenado

En lugar de abrir un cliente remoton Conexiones, lo probé con un procedimiento almacenado:

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'

y abrí 5 pestañas en SSMS, presioné F5 en cada una, y observé que también violaban 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.

Así que el fallo es independiente de ADO, ADO.net o ninguno de los anteriores.

Durante 15 años he estado operando bajo el supuesto de que una sola declaración en SQL Server es consistente; y el único

¿Qué pasa con el nivel de aislamiento de transacciones xxx?

Para diferentes variantes del lote SQL a ejecutar:

por defecto (leer confirmado): violación clave

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

predeterminado (lectura confirmada), transacción explícita: No hay error violación clave

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

PrimaMicrosoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64)Nivel de aislamiento de transacción predeterminado (READ COMMITTED)Resulta que cada consulta que he escrito está rota

Esto ciertamente cambia las cosas. Todas las declaraciones de actualización que he escrito están fundamentalmente dañadas. 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 otra factura podría ser insertada después de laMAX y antes de laUPDATE. 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 candados exclusivos, elSalesYTD Está Mal.

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