sql-server - hacia - sql server split string into columns

¿Es una declaración única de SQL Server atómica y consistente?

Question

Es una declaración en SQL Server ACID ?

Lo que quiero decir con eso

Dada una sola instrucción T-SQL, no envuelta en una BEGIN TRANSACTION COMMIT TRANSACTION / COMMIT TRANSACTION , están las acciones de esa declaració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 aislarse de las modificaciones realizadas por cualquier otra transacción concurrente.
• Duradero : una vez que se ha completado una transacción, sus efectos permanecen 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 que la transacción incorrecta se está "reservando" ; En realidad está escogiendo una transacción posterior . 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 la vista de Slots están cambiando antes de que ocurra la actualización. ¿Qué sucede si SQL Server no mantiene bloqueos SHARED en las transacciones en esa fecha ? ¿Es posible que una sola declaración sea inconsistente?

Así que decidí probarlo

Decidí verificar si los resultados de las subconsultas o las operaciones internas son inconsistentes. Creé una tabla simple con una sola columna int :

CREATE TABLE CountingNumbers (
   Value int PRIMARY KEY NOT NULL
)

Desde varias conexiones, en un bucle cerrado, llamo a la única instrucción T-SQL :

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 una definición más sutil de declaració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 declaraciones 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 un cliente remoto abriendo 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) --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:

• predeterminado (lectura confirmada) : violación de clave

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

• predeterminado (lectura confirmada), transacción explícita : sin violación de clave de error

  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ánea): violación de la 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 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 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 SalesYTD exclusivo, el SalesYTD está equivocado.

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

Answer 1

He estado operando bajo el supuesto de que una sola declaración en SQL Server es consistente

Esa suposición es errónea. Las dos transacciones siguientes tienen semánticas de bloqueo idénticas:

STATEMENT

BEGIN TRAN; STATEMENT; COMMIT

No hay diferencia en absoluto. Las declaraciones individuales y las auto-confirmaciones no cambian nada.

Por lo tanto, combinar toda la lógica en una sola declaración no ayuda (si lo hace, fue por accidente porque el plan cambió).

Vamos a solucionar el problema en cuestión. SERIALIZABLE solucionará la incoherencia que está viendo porque garantiza que sus transacciones se comportarán como si se ejecutaran en un solo hilo. Equivalentemente, se comportan como si se ejecutaran instantáneamente.

Obtendrás puntos muertos. Si está bien con un bucle de reintento, ha terminado en este punto.

Si desea invertir más tiempo, aplique sugerencias de bloqueo 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á reducida concurrencia. Eso podría estar totalmente bien dependiendo de su carga.

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

Puedes simplificar un poco la ACTUALIZACIÓN:

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 se deshace de una unión innecesaria.

Answer 2

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