質問

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

間違ったトランザクションが "book"です。 が選ばれている場合があります。 後 トランザクションを選んでいるのです。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'

で、SSMS で 5 つのタブを開き、それぞれで 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 の単一のステートメントが一貫しているという仮定のもとで活動してきました。

TRANSACTION ISOLATION LEVEL 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 ステートメントは、根本的に壊れています。例えば

--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 は間違いです。

どうして今まで何もできなかったのか。

どうすれば解決するのか？

<ブロッククオート

SQL Server の 1 つのステートメントが一貫しているという前提で操作してきました。

その仮定は間違っています。次の 2 つのトランザクションは、同一のロック セマンティクスを持っています。

STATEMENT

BEGIN TRAN; STATEMENT; COMMIT

全く変わりません。シングルステートメントと自動コミットでは何も変わりません。

ですから、すべてのロジックを1つのステートメントにマージしても、何の役にも立ちません（もしそうなったとしても、計画が変更されたので偶然そうなったのです）。

目の前の問題を解決しましょう。 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
)

これで同時実行性が低下していることがわかります。負荷によっては全く問題ないでしょう。

あなたの問題の本質が、同時実行を達成することを難しくしています。そのためのソリューションを必要とするならば、私たちはより侵襲的な技術を適用する必要があります。

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

これで不要な結合を1つ取り除くことができます。