sql-server - support - windows server 2016 sql server 2008




單個SQL Server語句是原子的還是一致的? (2)

我一直在假設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
)

您現在將看到減少的並發性。 根據您的負載,這可能完全沒問題。

問題的本質使得實現並髮變得困難。 如果您需要解決方案,我們需要應用更具侵入性的技術。

您可以稍微簡化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

這擺脫了一個不必要的連接。

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

並在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批處理的不同變體:

  • default(讀已提交) :密鑰違規

    INSERT INTO CountingNumbers (Value)
    SELECT ISNULL(MAX(Value), 0)+1 FROM CountingNumbers
  • default(讀提交),顯式事務沒有錯誤密鑰違規

    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)

錯誤的價值; 因為可以在MAXUPDATE之前插入另一張發票。 或者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是錯誤的。

這些年來我怎麼能做任何事情。


下面是一個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




sql-server-2008-r2