SQL Server 中的 ACID 屬性

Broadm發表於2022-07-13
SQLServer

SQL Server 中的事務是什麼？

SQL Server 中的事務是一組被視為一個單元的 SQL 語句，它們按照“做所有事或不做任何事”的原則執行，成功的事務必須通過 ACID 測試。

事務的 ACID 屬性是什麼？

首字母縮寫詞 ACID 是指事務的四個關鍵屬性

  • 原子性: Atomicity
  • 一致性: Consistency
  • 隔離性: Isolation
  • 永續性: Durability

為了理解這一點，我們將使用以下兩個表測試。

Product (產品表)

ProductID Name Price Quantity
101 Laptop 15000 100
102 Desktop 20000 150
104 Mobile 3000 200
105 Tablet 4000 250

ProductSales (產品銷售表)

ProductSalesID ProductID QuantitySold
1 101 10
2 102 15
3 104 30
4 105 35

請使用以下 SQL 指令碼建立並使用示例資料填充 Product 和 ProductSales 表。

IF OBJECT_ID('dbo.Product','U') IS NOT NULL
    DROP TABLE dbo.Product
IF OBJECT_ID('dbo.ProductSales','U') IS NOT NULL
    DROP TABLE dbo.ProductSales
GO
CREATE TABLE Product
(
  ProductID INT PRIMARY KEY, 
  Name VARCHAR(40), 
  Price INT,
  Quantity INT
 )
GO
INSERT INTO Product VALUES(101, 'Laptop', 15000, 100)
INSERT INTO Product VALUES(102, 'Desktop', 20000, 150)
INSERT INTO Product VALUES(103, 'Mobile', 3000, 200)
INSERT INTO Product VALUES(104, 'Tablet', 4000, 250)
GO
CREATE TABLE ProductSales
(
  ProductSalesId INT PRIMARY KEY,
  ProductId INT,
  QuantitySold INT
) 
GO
INSERT INTO ProductSales VALUES(1, 101, 10)
INSERT INTO ProductSales VALUES(2, 102, 15)
INSERT INTO ProductSales VALUES(3, 103, 30)
INSERT INTO ProductSales VALUES(4, 104, 35)
GO

SQL Server 中事務的原子性

SQL Server 中事務的原子性確保事務中的所有 DML 語句（即插入、更新、刪除）成功完成或全部回滾。例如，在以下 spSellProduct 儲存過程中，UPDATE 和 INSERT 語句都應該成功。如果 UPDATE 語句成功而 INSERT 語句失敗，資料庫應該通過回滾來撤消 UPDATE 語句所做的更改。

IF OBJECT_ID('spSellProduct','P') IS NOT NULL
    DROP PROCEDURE spSellProduct
GO
CREATE PROCEDURE spSellProduct
@ProductID INT,
@QuantityToSell INT
AS
BEGIN
  
  -- 首先我們需要檢查待銷售產品的可用庫存
  DECLARE @StockAvailable INT
  SELECT @StockAvailable = Quantity FROM Product WHERE ProductId = @ProductId

  --如果可用庫存小於要銷售的數量,丟擲錯誤
  IF(@StockAvailable < @QuantityToSell)
  BEGIN
    Raiserror('可用庫存不足',16,1)
  END

  -- 如果可用庫存充足
  ELSE
  BEGIN
    BEGIN TRY
      -- 我們需要開啟一個事務
      BEGIN TRANSACTION

      -- 首先做減庫存操作
      UPDATE Product SET Quantity = (Quantity - @QuantityToSell) WHERE ProductID = @ProductID

      -- 計算當前最大的產品銷售ID,即 MaxProductSalesId
      DECLARE @MaxProductSalesId INT
      SELECT @MaxProductSalesId = CASE 
          WHEN MAX(ProductSalesId) IS NULL THEN 0 
          ELSE MAX(ProductSalesId) 
          END 
      FROM ProductSales

      -- 把 @MaxProductSalesId 加一, 所以我們會避免主鍵衝突 
      --(解釋下,建表的時候,沒有設定主鍵自增,所以需要人工處理自增)
      Set @MaxProductSalesId = @MaxProductSalesId + 1

      -- 把銷售的產品數量記錄到ProductSales表中
      INSERT INTO ProductSales VALUES (@MaxProductSalesId, @ProductId, @QuantityToSell)

      -- 最後,提交事務
      COMMIT TRANSACTION
    END TRY

    BEGIN CATCH
      -- 如果發生了異常,回滾事務
      ROLLBACK TRANSACTION
    END CATCH

  End
END

SQL Server 中事務的一致性

SQL Server 中事務的一致性確保資料庫資料在事務開始之前處於一致狀態，並且在事務完成後也使資料保持一致狀態。如果事務違反規則，則應回滾。例如，如果可用庫存從 Product 表中減少，那麼 ProductSales 表中必須有一個相關條目。

在我們的示例中，假設事務更新了 product 表中的可用數量，突然出現系統故障（就在插入 ProductSales 表之前或中間）。在這種情況下系統會回滾更新，否則我們無法追蹤庫存資訊。

SQL Server 中事務的隔離性

SQL Server 中事務的隔離性確保事務的中間狀態對其他事務不可見。一個事務所做的資料修改必須與所有其他事務所做的資料修改隔離。大多數資料庫使用鎖定來維護事務隔離。

為了理解事務的隔離性，我們將使用兩個獨立的 SQL Server 事務。從第一個事務開始，我們啟動了事務並更新了 Product 表中的記錄，但我們還沒有提交或回滾事務。在第二個事務中，我們使用 select 語句來選擇 Product 表中存在的記錄，如下所示。

在sqlserver management studio 或 Navicat 中新建兩個獨立的查詢視窗

首先在第1個視窗執行以下事務,更新庫存(注意事務沒有提交或回滾,回滾語句被註釋了)

begin tran
update dbo.Product set Quantity = 150 where ProductID = 101
--rollback tran

然後在第2個視窗執行以下語句,查詢被更新的產品

select * from dbo.Product where ProductID = 101

你會發現,第2個視窗中的查詢語句被阻塞了(一直處於執行狀態,沒有返回資料)

解決阻塞: 切換到第1個視窗, (按下滑鼠左鍵拖動選擇 rollback tran ,注意不包含註釋 -- ),
然後單獨執行這個語句, 在 sqlserver management studio 直接點選執行就行, 在 Navicat 中,點選執行按鈕右邊的下拉箭頭,點選執行已選擇的,好了,再切換到第2個視窗,你會發現結果出來了

阻塞的原因: SqlServer預設的事務隔離級別是 Read Committed,
在上述的Update語句執行時會在對應的資料行上加一個 排它鎖(X), 直到事務提交或者回滾才會釋放,這保證了在此期間,其他任何事務都不能操作此行資料(查詢也不行),因為排它鎖(也叫獨佔鎖),和其他型別的鎖都是不相容的,這保證了其他事務看不到另一個事務的中間狀態,即避免了髒讀

SQL Server 中事務的永續性

SQL Server 中事務的永續性確保一旦事務成功完成，它對資料庫所做的更改將是永久性的。即使出現系統故障或電源故障或任何異常變化，它也應該保護已提交的資料。

注意：首字母縮寫詞 ACID 由 Andreas Reuter 和 Theo Härder 在 1983 年建立，然而，Jim Gray 在 1970 年代後期已經定義了這些屬性。大多數流行的資料庫，如 SQL Server、Oracle、MySQL、Postgre SQL 預設都遵循 ACID 屬性。

相關文章