文章詳情頁(yè)

詳解SQL Server 中的 ACID 屬性

瀏覽：134日期：2023-03-06 14:25:15

SQL Server 中的事務(wù)是什么？

SQL Server 中的事務(wù)是一組被視為一個(gè)單元的 SQL 語(yǔ)句，它們按照“做所有事或不做任何事”的原則執(zhí)行，成功的事務(wù)必須通過(guò) ACID 測(cè)試。

事務(wù)的 ACID 屬性是什么？

首字母縮寫(xiě)詞 ACID 是指事務(wù)的四個(gè)關(guān)鍵屬性

原子性: Atomicity
一致性: Consistency
隔離性: Isolation
持久性: Durability

為了理解這一點(diǎn)，我們將使用以下兩個(gè)表測(cè)試。

Product (產(chǎn)品表)

ProductIDNamePriceQuantity101Laptop15000100102Desktop20000150104Mobile3000200105Tablet4000250

ProductSales (產(chǎn)品銷(xiāo)售表)

ProductSalesIDProductIDQuantitySold110110210215310430410535

請(qǐng)使用以下 SQL 腳本創(chuàng)建并使用示例數(shù)據(jù)填充 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 中事務(wù)的原子性

SQL Server 中事務(wù)的原子性確保事務(wù)中的所有 DML 語(yǔ)句（即插入、更新、刪除）成功完成或全部回滾。例如，在以下 spSellProduct 存儲(chǔ)過(guò)程中，UPDATE 和 INSERT 語(yǔ)句都應(yīng)該成功。如果 UPDATE 語(yǔ)句成功而 INSERT 語(yǔ)句失敗，數(shù)據(jù)庫(kù)應(yīng)該通過(guò)回滾來(lái)撤消 UPDATE 語(yǔ)句所做的更改。

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

  --如果可用庫(kù)存小于要銷(xiāo)售的數(shù)量,拋出錯(cuò)誤
  IF(@StockAvailable < @QuantityToSell)
  BEGIN
    Raiserror("可用庫(kù)存不足",16,1)
  END

  -- 如果可用庫(kù)存充足
  ELSE
  BEGIN
    BEGIN TRY
      -- 我們需要開(kāi)啟一個(gè)事務(wù)
      BEGIN TRANSACTION

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

      -- 計(jì)算當(dāng)前最大的產(chǎn)品銷(xiāo)售ID,即 MaxProductSalesId
      DECLARE @MaxProductSalesId INT
      SELECT @MaxProductSalesId = CASE 
  WHEN MAX(ProductSalesId) IS NULL THEN 0 
  ELSE MAX(ProductSalesId) 
  END 
      FROM ProductSales

      -- 把 @MaxProductSalesId 加一, 所以我們會(huì)避免主鍵沖突 
      --(解釋下,建表的時(shí)候,沒(méi)有設(shè)置主鍵自增,所以需要人工處理自增)
      Set @MaxProductSalesId = @MaxProductSalesId + 1

      -- 把銷(xiāo)售的產(chǎn)品數(shù)量記錄到ProductSales表中
      INSERT INTO ProductSales VALUES (@MaxProductSalesId, @ProductId, @QuantityToSell)

      -- 最后,提交事務(wù)
      COMMIT TRANSACTION
    END TRY

    BEGIN CATCH
      -- 如果發(fā)生了異常,回滾事務(wù)
      ROLLBACK TRANSACTION
    END CATCH

  End
END

SQL Server 中事務(wù)的一致性

SQL Server 中事務(wù)的一致性確保數(shù)據(jù)庫(kù)數(shù)據(jù)在事務(wù)開(kāi)始之前處于一致?tīng)顟B(tài)，并且在事務(wù)完成后也使數(shù)據(jù)保持一致?tīng)顟B(tài)。如果事務(wù)違反規(guī)則，則應(yīng)回滾。例如，如果可用庫(kù)存從 Product 表中減少，那么 ProductSales 表中必須有一個(gè)相關(guān)條目。

在我們的示例中，假設(shè)事務(wù)更新了 product 表中的可用數(shù)量，突然出現(xiàn)系統(tǒng)故障（就在插入 ProductSales 表之前或中間）。在這種情況下系統(tǒng)會(huì)回滾更新，否則我們無(wú)法追蹤庫(kù)存信息。

SQL Server 中事務(wù)的隔離性

SQL Server 中事務(wù)的隔離性確保事務(wù)的中間狀態(tài)對(duì)其他事務(wù)不可見(jiàn)。一個(gè)事務(wù)所做的數(shù)據(jù)修改必須與所有其他事務(wù)所做的數(shù)據(jù)修改隔離。大多數(shù)數(shù)據(jù)庫(kù)使用鎖定來(lái)維護(hù)事務(wù)隔離。

為了理解事務(wù)的隔離性，我們將使用兩個(gè)獨(dú)立的 SQL Server 事務(wù)。從第一個(gè)事務(wù)開(kāi)始，我們啟動(dòng)了事務(wù)并更新了 Product 表中的記錄，但我們還沒(méi)有提交或回滾事務(wù)。在第二個(gè)事務(wù)中，我們使用 select 語(yǔ)句來(lái)選擇 Product 表中存在的記錄，如下所示。

在sqlserver management studio 或 Navicat 中新建兩個(gè)獨(dú)立的查詢窗口

首先在第1個(gè)窗口運(yùn)行以下事務(wù),更新庫(kù)存(注意事務(wù)沒(méi)有提交或回滾,回滾語(yǔ)句被注釋了)

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

然后在第2個(gè)窗口運(yùn)行以下語(yǔ)句,查詢被更新的產(chǎn)品

select * from dbo.Product where ProductID = 101

你會(huì)發(fā)現(xiàn),第2個(gè)窗口中的查詢語(yǔ)句被阻塞了(一直處于運(yùn)行狀態(tài),沒(méi)有返回?cái)?shù)據(jù))

解決阻塞: 切換到第1個(gè)窗口, (按下鼠標(biāo)左鍵拖動(dòng)選擇 rollback tran ,注意不包含注釋 -- ),
然后單獨(dú)執(zhí)行這個(gè)語(yǔ)句, 在 sqlserver management studio 直接點(diǎn)擊執(zhí)行就行, 在 Navicat 中,點(diǎn)擊運(yùn)行按鈕右邊的下拉箭頭,點(diǎn)擊運(yùn)行已選擇的,好了,再切換到第2個(gè)窗口,你會(huì)發(fā)現(xiàn)結(jié)果出來(lái)了

阻塞的原因: SqlServer默認(rèn)的事務(wù)隔離級(jí)別是 Read Committed,
在上述的Update語(yǔ)句執(zhí)行時(shí)會(huì)在對(duì)應(yīng)的數(shù)據(jù)行上加一個(gè) 排它鎖(X), 直到事務(wù)提交或者回滾才會(huì)釋放,這保證了在此期間,其他任何事務(wù)都不能操作此行數(shù)據(jù)(查詢也不行),因?yàn)榕潘i(也叫獨(dú)占鎖),和其他類(lèi)型的鎖都是不兼容的,這保證了其他事務(wù)看不到另一個(gè)事務(wù)的中間狀態(tài),即避免了臟讀

SQL Server 中事務(wù)的持久性

SQL Server 中事務(wù)的持久性確保一旦事務(wù)成功完成，它對(duì)數(shù)據(jù)庫(kù)所做的更改將是永久性的。即使出現(xiàn)系統(tǒng)故障或電源故障或任何異常變化，它也應(yīng)該保護(hù)已提交的數(shù)據(jù)。

注意：首字母縮寫(xiě)詞 ACID 由 Andreas Reuter 和 Theo Härder 在 1983 年創(chuàng)建，然而，Jim Gray 在 1970 年代后期已經(jīng)定義了這些屬性。大多數(shù)流行的數(shù)據(jù)庫(kù)，如 SQL Server、Oracle、MySQL、Postgre SQL 默認(rèn)都遵循 ACID 屬性。

到此這篇關(guān)于SQL Server 中的 ACID 屬性的文章就介紹到這了,更多相關(guān)SQL Server ACID內(nèi)容請(qǐng)搜索以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持！