詳解 SQL 事務(wù)和 ACID 屬性

作者：小技術(shù)君 2024-03-15 11:59:25

事務(wù)是一系列作為單一原子單元執(zhí)行的一個或多個SQL操作。其目的是確保數(shù)據(jù)庫中的數(shù)據(jù)一致性。事務(wù)具有以下屬性，通常被稱為ACID屬性的首字母縮寫。

引言：SQL中的事務(wù)

想象一下一個使用SQL作為數(shù)據(jù)庫的銀行系統(tǒng)。用戶A想將一些錢存入用戶B的賬戶。如果他們發(fā)送了錢，我們從他們的賬戶余額中扣除了這筆錢，并且我們想要將這筆錢存入用戶B的賬戶，但突然間我們的數(shù)據(jù)庫崩潰了。

這是否意味著我們從用戶A的余額中扣除的錢消失了？嗯，并不是在SQL數(shù)據(jù)庫中，因?yàn)樗鼈兪褂肧QL事務(wù)來處理這些情況。

事務(wù)和ACID屬性

事務(wù)是一系列作為單一原子單元執(zhí)行的一個或多個SQL操作。其目的是確保數(shù)據(jù)庫中的數(shù)據(jù)一致性。事務(wù)具有以下屬性，通常被稱為ACID屬性的首字母縮寫：

原子性(Atomicity)：整個事務(wù)被視為單個單元，要么完全成功，要么完全失敗。
一致性(Consistency)：事務(wù)將數(shù)據(jù)庫從一個有效狀態(tài)轉(zhuǎn)換為另一個有效狀態(tài)，保持?jǐn)?shù)據(jù)庫的不變性。
隔離性(Isolation)：并發(fā)事務(wù)的修改在它們提交之前被隔離開來。
持久性(Durability)：一旦事務(wù)已經(jīng)提交，即使系統(tǒng)發(fā)生故障，它也將保持提交狀態(tài)。

SQL事務(wù)中的關(guān)鍵命令

我們使用 BEGIN TRANSACTION 關(guān)鍵字標(biāo)記SQL事務(wù)的開始。

為了保存事務(wù)中進(jìn)行的所有更改，我們將更改 COMMIT 到數(shù)據(jù)庫。

如果在事務(wù)中發(fā)生了錯誤，我們可以使用 ROLLBACK 命令回滾事務(wù)中進(jìn)行的所有更改，將數(shù)據(jù)庫恢復(fù)到事務(wù)開始時的狀態(tài)。

示例：

讓我們回到我們簡單的銀行應(yīng)用程序示例，其中您需要從賬戶A轉(zhuǎn)賬100美元到賬戶B。這涉及兩個步驟：

從賬戶A的余額中扣除金額
將其添加到賬戶B中

為了使事務(wù)成功，必須完成這兩個步驟。下面是如何將其寫為SQL事務(wù)的示例：

BEGIN TRANSACTION;

-- 扣除賬戶A的余額100美元
UPDATE Accounts
SET balance = balance - 100
WHERE account_id = 'A';

-- 檢查賬戶A是否有足夠的余額，如果沒有就回滾
IF @@ROWCOUNT = 0
    ROLLBACK;

-- 將100美元添加到賬戶B的余額
UPDATE Accounts
SET balance = balance + 100
WHERE account_id = 'B';

-- 如果一切正常，則提交事務(wù)
COMMIT;

此事務(wù)執(zhí)行以下操作：

啟動事務(wù) 以確保以下操作是單個原子過程的一部分。
從賬戶A中扣除100美元：假設(shè)有一個名為 accounts 的表，其中包含列 account_id 和 balance。
檢查賬戶A是否有足夠的資金：如果賬戶A沒有足夠的錢，使用 ROLLBACK TRANSACTION 回滾事務(wù)，取消所有更改。
將100美元添加到賬戶B中：如果賬戶A有足夠的錢，就向賬戶B添加100美元。
提交事務(wù)：如果兩次更新都成功，則執(zhí)行 COMMIT TRANSACTION 命令，將在此事務(wù)期間進(jìn)行的更改永久應(yīng)用到數(shù)據(jù)庫。

這確保了要么兩個賬戶都適當(dāng)?shù)馗拢丛谌魏我稽c(diǎn)出現(xiàn)問題時都不應(yīng)用任何更改，從而保持?jǐn)?shù)據(jù)的完整性。

SQL事務(wù)的隔離層級

數(shù)據(jù)庫事務(wù)的隔離級別確定事務(wù)的完整性如何維護(hù)以及在多大程度上每個事務(wù)與其他事務(wù)隔離。

SQL標(biāo)準(zhǔn)定義了四個隔離級別，它們在一致性和性能之間進(jìn)行權(quán)衡。

(1) 讀未提交(Read Uncommitted)

描述：隔離級別最低。事務(wù)甚至可以看到其他事務(wù)尚未提交的更改。
對示例的影響：在轉(zhuǎn)賬過程中，如果另一個事務(wù)正在更新賬戶A或B的余額，這個事務(wù)可能讀取這些尚未提交的值。這可能導(dǎo)致看到一個實(shí)際上并不存在的余額(如果其他事務(wù)失敗并回滾)。

(2) 讀提交(Read Committed)

描述：確保事務(wù)只能讀取已提交的數(shù)據(jù)。
對示例的影響：通過確保只讀取已提交的賬戶A和B余額，避免了“讀未提交”的問題。然而，在事務(wù)內(nèi)多次讀取余額時，如果其他事務(wù)正在修改數(shù)據(jù)，它可能看到不同的值(不可重復(fù)讀)。

(3) 可重復(fù)讀(Repeatable Read)

描述：確保如果事務(wù)第二次讀取數(shù)據(jù)，它將找到相同的數(shù)據(jù)值(避免不可重復(fù)讀)。
對示例的影響：這個級別防止事務(wù)在事務(wù)內(nèi)多次讀取相同數(shù)據(jù)時看到其他事務(wù)所做的更改。在余額檢查和更新操作期間，它有助于保持一致的讀取結(jié)果。然而，它可能不會阻止幻讀(其他事務(wù)添加的新行)。

(4) 可序列化(Serializable)