Una singola istruzione SQL Server è atomica e coerente?

È una dichiarazione in ACID SQL Server?

Cosa intendo con quello

Data una singola istruzione T-SQL, non racchiusa in un BEGIN TRANSACTION / COMMIT TRANSACTION , sono le azioni di tale istruzione:

  • Atomico : vengono eseguite tutte le modifiche ai dati o nessuna di queste viene eseguita.
  • Coerente : una volta completata, una transazione deve lasciare tutti i dati in uno stato coerente.
  • Isolato : le modifiche apportate da transazioni concorrenti devono essere isolate dalle modifiche apportate da qualsiasi altra transazione concorrente.
  • Durevole : dopo che una transazione è stata completata, i suoi effetti sono permanentemente presenti nel sistema.

Il motivo per cui lo chiedo

Ho una singola istruzione in un sistema live che sembra violare le regole della query.

In effetti la mia dichiarazione T-SQL è:

 --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 : una variante concettuale più semplice potrebbe essere:

 --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 entrambe le dichiarazioni, si noti che sono dichiarazioni singole ( UPDATE...SET...WHERE ).

Ci sono casi in cui la transazione sbagliata viene “prenotata” ; in realtà sta selezionando una transazione successiva . Dopo aver osservato questo per 16 ore, sono perplesso. È come se SQL Server stia semplicemente violando le regole.

Mi chiedevo cosa sarebbe successo se i risultati della vista Slots cambiassero prima che si verificasse l’aggiornamento? Cosa succede se SQL Server non tiene blocchi SHARED sulle transazioni in quella data ? È ansible che una singola affermazione possa essere incoerente?

Così ho deciso di provarlo

Ho deciso di verificare se i risultati delle sottoquery o delle operazioni interne sono incoerenti. Ho creato una tabella semplice con una singola colonna int :

 CREATE TABLE CountingNumbers ( Value int PRIMARY KEY NOT NULL ) 

Da più connessioni, in un circuito chiuso, chiamo la singola istruzione T-SQL :

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

In altre parole lo pseudo-codice è:

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

E in pochi secondi ottengo:

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

Le affermazioni sono atomiche?

Il fatto che una singola affermazione non sia atomica mi fa chiedere se le singole affermazioni siano atomiche?

Oppure esiste una definizione più sottile dell’istruzione , che differisce da (ad esempio) ciò che SQL Server considera una dichiarazione:

inserisci la descrizione dell'immagine qui

Questo significa fondamentalmente che all’interno dei confini di una singola istruzione T-SQL, le istruzioni di SQL Server non sono atomiche?

E se una singola affermazione è atomica, quale è la violazione della chiave?

Da all’interno di una stored procedure

Piuttosto che un client remoto che apre n connessioni, l’ho provato con una stored procedure:

 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' 

e ha aperto 5 tabs in SSMS, premuto F5 in ciascuna, e ha guardato come violava anche 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. 

Quindi il fallimento è indipendente da ADO, ADO.net o nessuno dei precedenti.

Per 15 anni ho operato partendo dal presupposto che una singola istruzione in SQL Server è coerente; e l’unico

Che ne dici di TRANSAZIONE ISOLAMENTO LIVELLO xxx?

Per diverse varianti del batch SQL da eseguire:

  • default (read committed) : violazione della chiave

     INSERT INTO CountingNumbers (Value) SELECT ISNULL(MAX(Value), 0)+1 FROM CountingNumbers 
  • default (read committed), transazione esplicita : nessuna violazione della chiave di errore

     BEGIN TRANSACTION INSERT INTO CountingNumbers (Value) SELECT ISNULL(MAX(Value), 0)+1 FROM CountingNumbers COMMIT TRANSACTION 
  • serializzabile : deadlock

     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 (dopo aver modificato il database per abilitare l’isolamento dello snapshot): violazione della chiave

     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 

indennità

  • Microsoft SQL Server 2008 R2 (SP2) – 10.50.4000.0 (X64)
  • Livello di isolamento della transazione predefinito ( READ COMMITTED )

Risulta che ogni query che ho scritto è stata interrotta

Questo certamente cambia le cose. Ogni dichiarazione di aggiornamento che abbia mai scritto è fondamentalmente infranta. Per esempio:

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

Valore sbagliato; perché potrebbe essere inserita un’altra fattura dopo il MAX e prima UPDATE . O un esempio da 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); 

senza holdlock esclusivi, SalesYTD è sbagliato.

Come sono stato in grado di fare qualsiasi cosa in tutti questi anni.

Sono stato operativo supponendo che una singola istruzione in SQL Server sia coerente

Questa supposizione è sbagliata. Le seguenti due transazioni hanno una semantica di blocco identica:

 STATEMENT BEGIN TRAN; STATEMENT; COMMIT 

Nessuna differenza. Dichiarazioni singole e commit automatico non cambiano nulla.

Quindi fondere tutta la logica in un’unica istruzione non aiuta (se lo fa, è stato un caso perché il piano è cambiato).

Risolviamo il problema. SERIALIZABLE risolverà l’incoerenza che stai vedendo perché garantisce che le tue transazioni si comportino come se fossero eseguite con un thread singolo. Equivalentemente, si comportano come se fossero eseguiti all’istante.

Ti sentirai stanco. Se stai bene con un ciclo di tentativi, hai finito a questo punto.

Se si desidera investire più tempo, applicare i suggerimenti di blocco per forzare l’accesso esclusivo ai dati rilevanti:

 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 ) 

Ora vedrai una concorrenza ridotta. Potrebbe essere tutto a posto a seconda del carico.

La natura stessa del tuo problema rende difficile raggiungere la concorrenza. Se hai bisogno di una soluzione, dovremmo applicare tecniche più invasive.

Puoi semplificare un po 'l'AGGIORNAMENTO:

 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 

Questo elimina un join inutile.

Di seguito è riportato un esempio di un’istruzione UPDATE che incrementa atomicamente un valore del contatore

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