Est une seule instruction SQL Server atomique et cohérente?

Est une instruction SQL Server ACID?

Ce que je veux dire par la que

Reçu une seule instruction T-SQL, pas enveloppé dans un BEGIN TRANSACTION /COMMIT TRANSACTION, sont les actions de cette déclaration:

• Atomique: soit l'ensemble de ses données à des modifications sont effectuées, ou aucun d'eux n'est effectuée.
• Cohérente: une fois terminée, une transaction doit laisser toutes les données dans un état cohérent.
• Isolé: les Modifications apportées par les transactions simultanées doivent être isolés à partir des modifications faites par d'autres transactions simultanées.
• Durable: Après qu'une transaction est terminée, ses effets sont en permanence en place dans le système.

La raison pour laquelle je demande

J'ai une seule instruction dans un système qui semble violer les règles de la requête.

En effet mon instruction T-SQL est:

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

Note: Mais un simple conceptuel variante pourrait être:

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

Dans ces deux états, l'avis qu'ils sont de simples déclarations (UPDATE...SET...WHERE).

Il y a des cas où la mauvaise opération est "réservé"; c'est en fait la cueillette d'un plus tard transaction. Après avoir regarder cette pour 16 heures, je suis perplexe. C'est comme si SQL Server est simplement de violation des règles.

Je me demandais ce que si les résultats de l' Slots vue est en train de changer avant la mise à jour se passe? Si SQL Server n'est pas tenue de SHARED de verrous sur les transactions sur que date? Est-il possible qu'une seule instruction peut être incompatible?

J'ai donc décidé de le tester

J'ai décidé de vérifier si les résultats des sous-requêtes, ou des opérations internes, sont incompatibles. J'ai créé un tableau simple avec un seul int colonne:

CREATE TABLE CountingNumbers (
   Value int PRIMARY KEY NOT NULL
)

À partir de plusieurs connexions, dans une boucle serrée, je l'appelle le seule instruction T-SQL:

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

En d'autres termes, le pseudo-code est:

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

Et dans un délai de quelques secondes, j'obtiens:

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

Sont des énoncés atomiques?

Le fait que dans un seul état n'est pas atomique, me fait me demander si les déclarations sont atomiques?

Ou est-il plus subtile définition de déclaration, qui diffère de (par exemple) ce que SQL Server considère un énoncé:

N'a fondamentalement signifie que dans les limites d'une seule instruction T-SQL, SQL Server ne sont pas des comptes atomique?

Et si une seule instruction est atomique, ce que les comptes de la violation de clé?

De l'intérieur d'une procédure stockée

Plutôt que d'une télécommande d'ouverture de client n connexions, j'ai essayé avec une procédure stockée:

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'

et ouvert 5 onglets dans SSMS, appuyé sur la touche F5 dans chaque, et regardé comme ils ont aussi violé l'ACIDE:

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.

Donc la défaillance est indépendant de ADO, ADO.net ou aucun des ci-dessus.

Pendant 15 ans, j'ai été d'exploitation en vertu de l'hypothèse qu'une seule instruction dans SQL Server est compatible, et le seul

Qu'en NIVEAU d'ISOLATION de TRANSACTION xxx?

Pour les différentes variantes du traitement SQL à exécuter:

• par défaut (read committed): violation de clé

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

• par défaut (read committed), transaction explicite: pas d'erreur de violation de clé

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

• serializable: impasse

  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

• instantané (après la modification de la base de données pour permettre l'isolement d'instantané): violation de clé

  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)
• Par défaut du niveau d'isolation de transaction (READ COMMITTED)

S'avère chaque requête que j'ai jamais écrit est cassé

Certes cela change les choses. Chaque instruction de mise à jour que j'ai jamais écrit est fondamentalement brisé. E. g.:

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

Valeur erronée; car un autre facture pourrait être inséré après le MAX et avant la UPDATE. Ou un exemple 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);

sans exclusive holdlocks, le SalesYTD est faux.

Comment ai-je pu faire quelque chose de toutes ces années.