Является ли один оператор SQL Server атомарным и последовательным?

Является ли выражение в SQL Server ACID?

Что я подразумеваю под этим

Учитывая один оператор T-SQL, не заключенный в BEGIN TRANSACTION/COMMIT TRANSACTION, являются действиями этого оператора:

Atomic: либо выполняются все его модификации данных, либо ни один из них не выполняется.
Согласовано. По завершении транзакция должна оставить все данные в согласованном состоянии.
Изолированные. Модификации, выполняемые параллельными транзакциями, должны быть изолированы от изменений, сделанных любыми другими параллельными транзакциями.
Прочный: после завершения транзакции его эффекты постоянно установлены в системе.

Причина, по которой я прошу

У меня есть один оператор в живой системе, который, по-видимому, нарушает правила запроса.

Фактически мой оператор 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 currently unbooked
   AND Slots.Available > 0 --only book it if there empty slots
   ORDER BY t2.CreatedDate)

Примечание. Но более простой концептуальный вариант может быть:

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

В обоих этих утверждениях обратите внимание, что они являются одиночными операторами (UPDATE...SET...WHERE).

Есть случаи, когда неправильная транзакция "забронирована"; он фактически выбирает более позднюю транзакцию. Посмотрев на это в течение 16 часов, я в тупике. Как будто SQL Server просто нарушает правила.

Интересно, что, если результаты представления Slots меняются до того, как произойдет обновление? Что делать, если SQL Server не удерживает блокировки SHARED в транзакциях на этой дате? Возможно ли, что один оператор может быть непоследовательным?

Поэтому я решил проверить его

Я решил проверить, не соответствуют ли результаты подзапросов или внутренних операций. Я создал простую таблицу с одним столбцом int:

CREATE TABLE CountingNumbers (
   Value int PRIMARY KEY NOT NULL
)

Из нескольких соединений в замкнутом цикле я вызываю одно выражение T-SQL:

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

Другими словами, псевдокод:

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

И через несколько секунд я получаю:

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

Являются ли утверждения атомарными?

Тот факт, что одно утверждение не было атомом, заставляет меня задаться вопросом, являются ли одиночные утверждения атомарными?

Или есть более тонкое определение оператора, которое отличается от (например) тем, что SQL Server считает выражением:

Это в основном означает, что в рамках одного оператора T-SQL заявления SQL Server не являются атомарными?

И если один оператор является атомарным, что объясняет нарушение ключа?

Из хранимой процедуры

Вместо удаленных клиентов, открывающих n подключений, я попробовал его с хранимой процедурой:

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'

и открыл 5 вкладок в SSMS, нажал F5 в каждом и наблюдал за тем, как они слишком нарушили 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.

Таким образом, отказ не зависит от ADO, ADO.net или ни от одного из вышеперечисленных.

В течение 15 лет я работал в предположении, что один оператор в SQL Server согласован; и только

Что относительно УРОВНЯ ИЗОЛЯЦИИ ОПЕРАЦИИ xxx?

Для разных вариантов исполняемого пакета SQL:

по умолчанию (чтение прочитано): нарушение ключа

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

значение по умолчанию (чтение прочитано), явная транзакция: ~~отсутствие ошибки~~
```
BEGIN TRANSACTION
INSERT INTO CountingNumbers (Value)
SELECT ISNULL(MAX(Value), 0)+1 FROM CountingNumbers
COMMIT TRANSACTION
```

сериализуемый: тупик

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

моментальный снимок (после изменения базы данных для включения выделения моментального снимка): нарушение ключа

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)
Уровень изоляции транзакции по умолчанию (READ COMMITTED)

Вызывается каждый запрос, который я когда-либо писал, сломан

Это, безусловно, меняет ситуацию. Каждое обновление, которое я когда-либо писал, принципиально нарушено. Например:.

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

Неверное значение; потому что после MAX и перед UPDATE может быть вставлен другой счет-фактура. Или пример из 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);

без эксклюзивных holdlocks, SalesYTD неверно.

Как я мог делать все эти годы.

Ответ 1

Я работал в предположении, что один оператор в SQL Server согласован

Это предположение неверно. Следующие две транзакции имеют идентичную семантику блокировки:

STATEMENT

BEGIN TRAN; STATEMENT; COMMIT

Никакой разницы. Одиночные утверждения и автокоманды ничего не меняют.

Таким образом, объединение всей логики в один оператор не помогает (если это так, это было случайно, потому что план изменился).

Позвольте исправить эту проблему. SERIALIZABLE фиксирует несогласованность, которую вы видите, потому что она гарантирует, что ваши транзакции ведут себя так, как если бы они выполнялись однопоточно. Эквивалентно, они ведут себя так, как будто они выполняются мгновенно.

Вы будете получать тупики. Если вы в порядке с циклом повтора, вы закончите в этот момент.

Если вы хотите инвестировать больше времени, примените блокирующие подсказки для принудительного эксклюзивного доступа к соответствующим данным:

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
)

Теперь вы увидите уменьшенный concurrency. Это может быть совершенно нормально в зависимости от вашей нагрузки.

Сама природа вашей проблемы делает достижение concurrency трудным. Если вам требуется решение для этого, нам нужно применять более инвазивные методы.

Вы можете немного упростить UPDATE:

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

Это избавляет от ненужного соединения.

Ответ 2

Ниже приведен пример инструкции UPDATE, которая увеличивает значение счетчика атомарно

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