MySQL 交易、隔離與並行

InnoDB 交易透過以原子方式提交相關變更，並控制並行執行的陳述式如何觀察這些變更，來保護資料庫不變量。隔離性不能取代條件約束：對於結構描述能夠表達的不變量，請使用主鍵、唯一條件約束、外部索引鍵條件約束和檢查條件約束，然後針對多陳述式決策選擇交易與鎖定模式。

交易邊界與自動提交 #

MySQL 預設啟用自動提交，因此明確交易之外的每個陳述式都會作為一個交易提交或回復。請明確啟動多陳述式單位，並保持其簡短：

sql

START TRANSACTION;

UPDATE account
SET balance = balance - 100
WHERE id = 42 AND balance >= 100;

UPDATE account
SET balance = balance + 100
WHERE id = 84;

COMMIT;

在為目的地入帳之前，先檢查第一個更新所影響的資料列數。任何不變量或陳述式失敗時都使用 ROLLBACK。連線集區在歸還連線之前，必須還原自動提交、隔離層級、存取模式及其他工作階段狀態，因為這些屬性會在交易之間持續存在。

許多資料定義陳述式和帳戶管理陳述式會隱含提交。原子 DDL 可使受支援的結構描述陳述式具備當機安全性，但不會使該陳述式成為周圍應用程式交易的一部分。

隔離設定 #

InnoDB 在 MySQL 8.4 中的預設值是 REPEATABLE READ。使用 --transaction-isolation 或 transaction_isolation 設定伺服器預設值，並且只在明確指定的範圍內覆寫：

sql

SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT @@GLOBAL.transaction_isolation, @@SESSION.transaction_isolation;

GLOBAL 會為新工作階段建立預設值。SESSION 會套用到該連線上的後續交易。不含限定詞的 SET TRANSACTION 會套用到下一個交易。也可以在相同陳述式中選取 READ ONLY 和 READ WRITE。

MVCC 與一致性讀取 #

只要作用中的快照仍可能需要，InnoDB 就會在 undo 資料中保留較早的資料列版本。一般 SELECT 通常會執行非鎖定一致性讀取：

READ UNCOMMITTED 可能會公開未提交的版本。
READ COMMITTED 會為每次一致性讀取建立新的快照。
REPEATABLE READ 會在交易中的第一次一致性讀取時建立快照，並在後續一致性讀取中重複使用該快照。
SERIALIZABLE 會在停用自動提交時，將一般 SELECT 陳述式轉換為共用鎖定讀取。

寫入與明確鎖定讀取檢查的是目前已提交的狀態，而不是交易較早的一致性讀取快照。混合使用快照讀取與目前讀取是有效的，但應用程式必須處理兩者之間的差異。長時間存在的快照也會保留 undo 歷程，並可能增加清除壓力。

InnoDB 鎖定類型 #

InnoDB 會鎖定陳述式選取的索引記錄和索引範圍：

記錄鎖定會保護索引項目。
間隙鎖定會保護索引項目前方的區間，防止插入，但不會鎖定項目本身。
下一鍵鎖定會將記錄鎖定與其前方的間隙結合。
意向鎖定會在資料表層級宣告交易持有或計畫持有資料列層級的共用鎖定或獨佔鎖定，使資料表鎖定相容性檢查更有效率。

在 REPEATABLE READ 下，鎖定範圍掃描會使用下一鍵鎖定，以防止幻影插入。找到單一資料列的唯一索引等值搜尋可以只使用記錄鎖定。READ COMMITTED 會對大多數搜尋停用間隙鎖定，但 MySQL 為外部索引鍵和重複鍵檢查所需的情況除外。

存取路徑決定 InnoDB 檢查並可能鎖定哪些項目。因此，缺少索引或索引選擇性不足會同時擴大工作量和競爭範圍。請檢查每個鎖定述詞的執行計畫。

MySQL 也會在陳述式使用結構描述物件時取得中繼資料鎖定。已存取某個資料表的開啟交易，即使不存在資料列衝突，也可能使 ALTER TABLE 等待。Performance Schema 提供中繼資料鎖定表和資料鎖定表，可用於診斷這些等待。

樂觀 Compare-and-Swap #

樂觀並行控制會在一次寫入中執行業務條件與版本比較：

sql

UPDATE account
SET balance = balance - 100,
    version = version + 1
WHERE id = 42
  AND version = 17
  AND balance >= 100;

影響一個資料列表示狀態轉換成功。影響零個資料列表示資料列、版本或業務述詞已不再相符。只有在作業仍然有效時，才重新載入目前狀態並重試；不要將每次衝突都轉換成無限制的重試。

悲觀鎖定讀取 #

當交易必須在決定寫入內容之前檢查目前狀態時，請使用鎖定讀取：

sql

START TRANSACTION;

SELECT available
FROM inventory
WHERE sku = 'A-42'
FOR UPDATE;

UPDATE inventory
SET available = available - 1
WHERE sku = 'A-42' AND available > 0;

COMMIT;

FOR SHARE 會取得共用鎖定，FOR UPDATE 會取得獨佔鎖定。NOWAIT 會直接返回，而不是等待衝突鎖定。SKIP LOCKED 會省略已鎖定的資料列，可支援佇列取用者，但它會呈現刻意不一致的檢視，因此不適合一般業務查詢。請將網路呼叫、遠端服務和使用者輸入放在鎖定區間之外。

諮詢鎖定與資料表鎖定 #

GET_LOCK(name, timeout) 會取得由連線擁有的伺服器範圍具名諮詢鎖定。提交不會釋放它；RELEASE_LOCK、連線終止或伺服器關閉會釋放它。諮詢鎖定不會保護資料表資料列，因此所有參與者都必須遵守相同的命名慣例：

sql

SELECT GET_LOCK('schema-migration', 10);
-- 執行有界限且固定連線的工作。
SELECT RELEASE_LOCK('schema-migration');

請將諮詢鎖定用於協作式單例工作，不要將其作為資料庫條件約束或資料列鎖定的替代方案。連線集區不得歸還仍擁有具名鎖定的連線。

LOCK TABLES 會建立獨立的工作階段層級明確資料表鎖定邊界，並可能隱含提交。它會降低並行性，且與交易資料表的互動方式不同於一般 InnoDB 資料列鎖定。請將其保留給有文件記錄的管理或相容性工作流程，並一律以 UNLOCK TABLES 終止該邊界。

死結、鎖定等待與重試 #

在並行環境中，應預期會發生死結。InnoDB 會偵測等待循環，並以錯誤 1213 回復其中一個交易。超過 innodb_lock_wait_timeout 的鎖定等待會返回錯誤 1205；根據設定，可能回復失敗的陳述式，而不是整個交易。應用程式應明確回復，且任何重試都應從乾淨的交易邊界開始。

透過依一致順序鎖定資料列、使用選擇性高的索引，以及限制交易持續時間，來減少可避免的衝突。使用 SHOW ENGINE INNODB STATUS 以及包括 data_locks、data_lock_waits 和 metadata_locks 在內的 Performance Schema 資料表，診斷反覆發生的案例。

重試可能會在原始結果變得不確定後，再次執行資料庫外部的工作。為對外可見的作業提供冪等金鑰或持久化作業識別碼，透過退避和抖動限制重試次數，並回報最終衝突，而不是無限重試。