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

Question

Это заявление в SQL Server ACID?

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

Учитывая один оператор T-SQL, не обернутый в BEGIN TRANSACTION / COMMIT TRANSACTION действия этого утверждения:

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

Причина я спрашиваю

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

По сути, мое утверждение 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)

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

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

бонус

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

без эксклюзивных замков, SalesYTD неправильно.

Как я смог сделать что-нибудь все эти годы.

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

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

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

Вы можете немного упростить ОБНОВЛЕНИЕ:

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

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

Answer 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

Answer 3

Select не блокируется исключительно, даже сериализуемый блокирует, а только на время выполнения select!По окончании выбора блокировка выбора снимается. Затем вступают в действие блокировки обновления, поскольку теперь они знают, что блокировать, поскольку Select возвращает результаты. Между тем, любой другой может снова выбрать!

Единственный надежный способ безопасно прочитать и заблокировать строку:

      begin transaction

--lock what i need to read
update mytable set col1=col1 where mykey=@key

--now read what i need
select @d1=col1,@d2=col2 from mytable where mykey=@key

--now do here calculations checks whatever i need from the row i read to decide my update
if @d1<@d2 set @d1=@d2 else set @d1=@d2 * 2 --just an example calc

--now do the actual update on what i read and the logic
update mytable set col1=@d1,col2=@d2 where mykey=@key

commit transaction

Таким образом, любое другое соединение, выполняющее тот же оператор для тех же данных, обязательно будет ждать первого (фальшивого) оператора обновления, пока не будет выполнено предыдущее. Это гарантирует, что при снятии блокировки только одному соединению будет предоставлено разрешение на блокировку запроса на «обновление», и это одно обязательно будет читать зафиксированные завершенные данные, чтобы производить вычисления и решать, нужно ли и что на самом деле обновлять при втором «реальном» обновлении.

Другими словами, когда вам нужно выбрать информацию, чтобы решить, нужно ли / как обновлять, вам нужен блок транзакции начала / фиксации, плюс вам нужно начать с поддельного обновления того, что вам нужно выбрать, - прежде чем вы его выберете (вывод обновления будет тоже делаю).