MySQL :: MySQL 9.0 參考手冊 :: 17.7.2.1 交易隔離等級

版本 9.0

8.4 目前版本
8.0
5.7

8.0 日文

MySQL 9.0 參考手冊 / ... / 交易隔離等級

17.7.2.1 交易隔離等級

交易隔離是資料庫處理的基礎之一。隔離是縮寫 ACID 中的 I；隔離等級是在多個交易同時進行變更和執行查詢時，微調效能和可靠性、一致性和結果重現性之間平衡的設定。

InnoDB 提供 SQL:1992 標準描述的所有四個交易隔離等級：READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。InnoDB 的預設隔離等級為 REPEATABLE READ。

使用者可以使用 SET TRANSACTION 陳述式變更單一工作階段或所有後續連線的隔離等級。若要設定伺服器所有連線的預設隔離等級，請在命令列或選項檔案中使用 --transaction-isolation 選項。如需有關隔離等級和等級設定語法的詳細資訊，請參閱第 15.3.7 節，「SET TRANSACTION 陳述式」。

InnoDB 使用不同的鎖定策略支援此處描述的每個交易隔離等級。您可以透過預設的 REPEATABLE READ 等級，對 ACID 合規性很重要的關鍵資料操作強制執行高度的一致性。或者，您可以在精確一致性和可重複結果不如盡可能減少鎖定額外負荷重要（例如大量報表）的情況下，使用 READ COMMITTED 甚至 READ UNCOMMITTED 放寬一致性規則。SERIALIZABLE 強制執行的規則甚至比 REPEATABLE READ 更嚴格，主要用於特殊情況，例如 XA 交易以及疑難排解並行性和死鎖的問題。

以下清單說明 MySQL 如何支援不同的交易等級。此清單從最常用的等級到最少使用的等級排序。

REPEATABLE READ
這是 InnoDB 的預設隔離等級。在同一個事務中的一致性讀取會讀取第一次讀取所建立的快照。這表示如果您在同一個事務中發出多個普通的（非鎖定的）SELECT 陳述式，這些 SELECT 陳述式彼此之間也是一致的。請參閱第 17.7.2.3 節，「一致性非鎖定讀取」。
對於鎖定讀取（使用 FOR UPDATE 或 FOR SHARE 的 SELECT）、UPDATE 和 DELETE 陳述式，鎖定取決於該陳述式是否使用具有唯一搜尋條件的唯一索引，或是範圍類型的搜尋條件。
- 對於具有唯一搜尋條件的唯一索引，InnoDB 只會鎖定找到的索引記錄，而不會鎖定它之前的間隙。
- 對於其他搜尋條件，InnoDB 會鎖定掃描的索引範圍，使用間隙鎖定或下鍵鎖定來阻止其他連線將記錄插入到該範圍涵蓋的間隙中。有關間隙鎖定和下鍵鎖定的資訊，請參閱第 17.7.1 節，「InnoDB 鎖定」。
READ COMMITTED
每個一致性讀取，即使在同一個事務中，也會設定並讀取其自己的新快照。有關一致性讀取的資訊，請參閱第 17.7.2.3 節，「一致性非鎖定讀取」。
對於鎖定讀取（使用 FOR UPDATE 或 FOR SHARE 的 SELECT）、UPDATE 陳述式和 DELETE 陳述式，InnoDB 只會鎖定索引記錄，而不會鎖定它們之前的間隙，因此允許將新記錄自由插入到鎖定記錄旁邊。間隙鎖定僅用於外鍵約束檢查和重複鍵檢查。
由於間隙鎖定已停用，因此可能會發生幻影行問題，因為其他連線可以在間隙中插入新行。有關幻影行的資訊，請參閱第 17.7.4 節，「幻影行」。
只有基於列的二進位日誌記錄才支援 READ COMMITTED 隔離等級。如果您將 READ COMMITTED 與 binlog_format=MIXED 搭配使用，伺服器會自動使用基於列的日誌記錄。
使用 READ COMMITTED 還有其他影響
- 對於 UPDATE 或 DELETE 陳述式，InnoDB 只會對其更新或刪除的行持有鎖定。在 MySQL 評估 WHERE 條件之後，會釋放不符合條件的行的記錄鎖定。這大大降低了死鎖的可能性，但它們仍然可能發生。
- 對於 UPDATE 陳述式，如果某行已經被鎖定，InnoDB 會執行一個「半一致性」讀取，將最新的已提交版本傳回給 MySQL，以便 MySQL 可以判斷該行是否符合 UPDATE 的 WHERE 條件。如果該行符合（必須更新），MySQL 會再次讀取該行，而這次 InnoDB 會鎖定該行或等待該行上的鎖定。
考慮以下建立和填入資料的表格
```
CREATE TABLE t (a INT NOT NULL, b INT) ENGINE = InnoDB;
INSERT INTO t VALUES (1,2),(2,3),(3,2),(4,3),(5,2);
COMMIT;
```
在此案例中，表格沒有索引，因此搜尋和索引掃描會使用隱藏的叢集索引進行記錄鎖定（請參閱第 17.6.2.1 節，「叢集索引和次要索引」），而不是索引的欄位。
假設一個連線使用以下陳述式執行 UPDATE
```
# Session A
START TRANSACTION;
UPDATE t SET b = 5 WHERE b = 3;
```
也假設第二個連線在第一個連線執行後，執行以下陳述式來執行 UPDATE
```
# Session B
UPDATE t SET b = 4 WHERE b = 2;
```
當 InnoDB 執行每個 UPDATE 時，它會先取得每一行的獨佔鎖定，然後判斷是否要修改它。如果 InnoDB 不修改該行，它會釋放該鎖定。否則，InnoDB 會保留鎖定，直到事務結束。這會對事務處理產生以下影響。
當使用預設的 REPEATABLE READ 隔離等級時，第一個 UPDATE 會在其讀取的每一行上取得 x 鎖定，且不會釋放任何鎖定
```
x-lock(1,2); retain x-lock
x-lock(2,3); update(2,3) to (2,5); retain x-lock
x-lock(3,2); retain x-lock
x-lock(4,3); update(4,3) to (4,5); retain x-lock
x-lock(5,2); retain x-lock
```
第二個 UPDATE 會在它嘗試取得任何鎖定時立即被封鎖（因為第一個更新已保留所有行的鎖定），並且在第一個 UPDATE 提交或回復之前不會繼續進行
```
x-lock(1,2); block and wait for first UPDATE to commit or roll back
```
如果改用 READ COMMITTED，則第一個 UPDATE 會在其讀取的每一行上取得 x 鎖定，並釋放它不修改的那些行的鎖定
```
x-lock(1,2); unlock(1,2)
x-lock(2,3); update(2,3) to (2,5); retain x-lock
x-lock(3,2); unlock(3,2)
x-lock(4,3); update(4,3) to (4,5); retain x-lock
x-lock(5,2); unlock(5,2)
```
對於第二個 UPDATE，InnoDB 會執行一個「半一致性」讀取，將其讀取的每一行的最新已提交版本傳回給 MySQL，以便 MySQL 可以判斷該行是否符合 UPDATE 的 WHERE 條件
```
x-lock(1,2); update(1,2) to (1,4); retain x-lock
x-lock(2,3); unlock(2,3)
x-lock(3,2); update(3,2) to (3,4); retain x-lock
x-lock(4,3); unlock(4,3)
x-lock(5,2); update(5,2) to (5,4); retain x-lock
```
但是，如果 WHERE 條件包含索引的欄位，並且 InnoDB 使用該索引，則在取得和保留記錄鎖定時，只會考慮索引的欄位。在以下範例中，第一個 UPDATE 會在 b = 2 的每一行上取得並保留 x 鎖定。第二個 UPDATE 會在嘗試在相同的記錄上取得 x 鎖定時被封鎖，因為它也使用了在欄位 b 上定義的索引。
```
CREATE TABLE t (a INT NOT NULL, b INT, c INT, INDEX (b)) ENGINE = InnoDB;
INSERT INTO t VALUES (1,2,3),(2,2,4);
COMMIT;

# Session A
START TRANSACTION;
UPDATE t SET b = 3 WHERE b = 2 AND c = 3;

# Session B
UPDATE t SET b = 4 WHERE b = 2 AND c = 4;
```
READ COMMITTED 隔離等級可以在啟動時設定，也可以在執行時變更。在執行時，它可以針對所有連線全域設定，也可以針對每個連線單獨設定。
READ UNCOMMITTED
SELECT 陳述式以非鎖定的方式執行，但可能會使用較早的行版本。因此，使用此隔離等級，此類讀取不一致。這也稱為髒讀。否則，此隔離等級的工作方式與 READ COMMITTED 類似。
SERIALIZABLE
此等級與 REPEATABLE READ 類似，但是如果 autocommit 已停用，則 InnoDB 會隱式地將所有普通的 SELECT 陳述式轉換為 SELECT ... FOR SHARE。如果 autocommit 已啟用，則 SELECT 本身就是一個事務。因此，已知它是唯讀的，如果以一致性（非鎖定）讀取的方式執行，則可以序列化，並且不需要為其他事務封鎖。（若要強制在其他事務修改選取的行時，讓普通的 SELECT 封鎖，請停用 autocommit。）
從 MySQL 授權表讀取資料（透過聯接清單或子查詢）但不修改它們的 DML 操作，不會在 MySQL 授權表上取得讀取鎖定，無論隔離等級為何。如需更多資訊，請參閱授權表並行。

上一個首頁上一層下一個