É uma declaração no SQL ServerACID?

O que eu quero dizer com isso

Dada uma única instrução T-SQL, não envolvida em umBEGIN TRANSACTION / COMMIT TRANSACTION, são as ações dessa declaração:

Atomic: todas as suas modificações de dados são executadas ou nenhuma delas é executada.Consistente: Quando concluída, uma transação deve deixar todos os dados em um estado consistente.Isolado: As modificações feitas por transações simultâneas devem ser isoladas das modificações feitas por quaisquer outras transações simultâneas.Durável: Após a conclusão de uma transação, seus efeitos permanecem em vigor no sistema.A razão pela qual pergunto

Eu tenho uma única instrução em um sistema ativo que parece estar violando as regras da consulta.

Com efeito, minha instrução 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: Mas uma variante conceitual mais simples pode 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
)

Nas duas declarações, observe que são declarações únicas (UPDATE...SET...WHERE)

Há casos em que a transação errada está sendo"reservado"; na verdade, está escolhendo ummais tarde transação. Depois de encarar isso por 16 horas, estou perplexo. É como se o SQL Server estivesse simplesmente violando as regras.

Eu me perguntava e se os resultados doSlots a visualização está mudando antes que a atualização aconteça? E se o SQL Server não estiver segurandoSHARED bloqueios notransações naquiloencontro? É possível que uma única declaração possa ser inconsistente?

Então eu decidi testá-lo

Decidi verificar se os resultados de subconsultas ou operações internas são inconsistentes. Criei uma tabela simples com uma únicaint coluna:

CREATE TABLE CountingNumbers (
   Value int PRIMARY KEY NOT NULL
)

Em várias conexões, em um circuito fechado, eu chamo oinstrução T-SQL única:

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

Em outras palavras, o pseudo-código é:

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

E em alguns segundos eu recebo:

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

As declarações são atômicas?

O fato de uma única declaração não ser atômica me faz pensar se as declarações únicas são atômicas?

Ou existe maissutil definição dedeclaração, que difere (por exemplo) do que o SQL Server considera uma declaração:

Isso significa fundamentalmente que, dentro dos limites de uma única instrução T-SQL, as instruções do SQL Server não são atômicas?

E se uma única declaração é atômica, o que explica a violação da chave?

De dentro de um procedimento armazenado

Em vez de abrir um cliente remoton conexões, tentei com um procedimento armazenado:

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'

e abriu 5 guias no SSMS, pressionou F5 em cada uma delas e observou como elas também violavam o 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.

Portanto, a falha é independente do ADO, ADO.net ou de nenhum dos itens acima.

Por 15 anos, estou operando sob o pressuposto de que uma única instrução no SQL Server é consistente; e o único

E o NÍVEL DE ISOLAMENTO DE TRANSAÇÃO xxx?

Para diferentes variantes do lote SQL a serem executadas:

padrão (leitura confirmada): violação de chave

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

padrão (leitura confirmada), transação explícita: sem erro violação de chave

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

serializável: 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

instantâneo (após alterar o banco de dados para ativar o isolamento de captura instantânea): violação de chave

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

BônusMicrosoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64)Nível de isolamento de transação padrão (READ COMMITTED)Acontece que todas as consultas que eu já escrevi estão quebradas

Isso certamente muda as coisas. Toda declaração de atualização que eu já escrevi é fundamentalmente quebrada. Por exemplo.:

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

Valor errado; porque outra fatura pode ser inserida após oMAX e antes doUPDATE. Ou um exemplo 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);

sem fechaduras exclusivas, oSalesYTD está errado.

Como pude fazer algo durante todos esses anos.