InnoDB鎖和事務模型

InnoDB鎖和事務模型 | Tony's toy blog

前言

本篇譯自Mysql官方參考手冊（5.7）的鎖和事務章節，個人認為翻譯價值還可以，是以翻譯如下。閱讀需要有一些InnoDB的基本概念，如有需要可以先閱讀前面的章節。翻譯水準有限，有不當之處還請指出。

InnoDB Locking and Transaction Model

要實作大規模、高并發或高可靠的資料庫應用，或者要從不同的資料庫系統移植大量的代碼，又或者是要優化MySQL的性能，了解InnoDB的鎖和事務的模型是非常重要的。

本章節讨論了幾個您應該需要熟悉的、與InnoDB鎖和InnoDB事務模型相關的幾個話題。

• 14.7.1 “InnoDB鎖” 描述了InnoDB所使用的各種鎖的類型。
• 14.7.2 “InnoDB事務模型” 描述了各個事務隔離級别的性質以及各個級别所使用的鎖政策。同時讨論了autocommit、一緻性非鎖定讀取（consistent non-locking reads）和鎖定讀取（Locking reads）的應用。
• 14.7.3 “InnoDB中由不同的SQL語句設定的鎖” 讨論了InnoDB中各種不同語句設定的鎖的類型。
• 14.7.4 “幻讀行” 描述了InnoDB如何采用next-key鎖來避免幻讀。
• 14.7.5 “InnoDB中的死鎖” 提供一個死鎖示例，讨論了死鎖的發現和復原，同時提供了一些在InnoDB中減少和處理死鎖的tips。

InnoDB Locking

本章節描述了InnoDB所使用的鎖的類型。

• Shared and Exclusive Locks（共享鎖和排他鎖）
• Intention Locks（意向鎖）
• Record Locks（單個記錄鎖）
• Gap Locks（間隙鎖）
• Next-Key Locks
• Insert Intention Locks（插入意向鎖）
• AUTO-INC Locks
• Predicate Locks for Spatial Indexes

Shared and Exclusive Locks

InnoDB實作了标準行級鎖，有兩種類型，分别是共享鎖（S鎖）和排他鎖（X鎖）。

• 一把共享（S）鎖允許持有鎖的事務讀取一行。
• 一把排他（X）鎖允許持有鎖的事務更新或删除一行。

如果事務T1在行r上持有一把共享鎖，那麼來自其他事務T2的對行r的獲鎖請求将以如下方式處理：

• 對擷取行r共享鎖的請求可以被立即通過。這樣的結果就是T1和T2在行r上同時各自持有一把共享鎖。
• 對擷取行r排他鎖的請求無法被立即通過。

如果事務T1在行r上持有一把排他鎖，來自其他事務T2對行r的擷取任意一種鎖請求都無法被立即通過。事務T2必須等待事務T1釋放它在行r上的鎖。

Intention Locks

InnoDB支援多顆粒度的鎖定，允許行鎖和表鎖共存。舉例來說，類似LOCK TABLES ... WRITE的語句将在指定的表上擷取一把排他鎖（X鎖）。為了能夠在多顆粒度上實作鎖定，InnoDB使用意向鎖。意向鎖是表級鎖，用于申明事務稍後需要對表中的一行擷取的哪種類型的鎖（共享或排他）。有兩種意向鎖：

• 一把意向共享（IS）鎖表明一個事務試圖對表中的某行設定共享鎖。
• 一把意向排他（IX）鎖表明一個事務試圖對表中的某行設定排他鎖。

例如，SELECT ... LOCK IN SHARE MODE設定了一把IS鎖，SELECT ... FOR UPDATE設定了一把IX鎖。

意向鎖協定如下：

• 在事務能夠擷取表中某一行的共享鎖之前，它必須在相應的表上擷取一把IS鎖或者更強的鎖。
• 在事務能夠擷取表中某一行的排他鎖之前，它必須在相應的表上擷取一把IX鎖。

表級鎖類型的相容性總結如下表：

X	IX	S	IS
X	不相容	不相容	不相容	不相容
IX	不相容	相容	不相容	相容
S	不相容	不相容	相容	相容
IS	不相容	相容	相容	相容

如果一個事務請求的鎖與現有的鎖相容，那麼請求将被允許，如果沖突則将被拒絕。事務将等待直到已經存在的不相容的鎖被釋放。如果一個獲鎖請求與已存在的鎖沖突，且可能會引發死鎖，将會出現一個異常。

意向鎖不會阻塞除了全表請求（例如LOCK TABLES ... WRITE）之外的操作。意向鎖主要的目的是表明某個事務正在或正将要鎖定表中的某一行。

一個意向鎖的事務資料以類似如下的形式出現在顯示InnoDB狀态指令（SHOW ENGINE engine_name {STATUS | MUTEX}）和InnoDB監控的輸出中：

TABLE LOCK table `test`.`t` trx id 10080 lock mode IX           

Record Locks

記錄鎖是索引記錄上的鎖。例如SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE;将阻止任何其他事務插入、更新或者删除t.c1的值是10的行。

記錄鎖鎖定的一定是索引記錄，即使在沒有定義索引的表上也是如此。在這種情況下，InnoDB建立了一個隐藏的聚集索引，并使用這個索引來鎖定記錄。見章節 14.6.2.1 “Clustered and Secondary Indexes”。

一個記錄鎖的事務資料以類似如下的形式出現在顯示InnoDB狀态指令和InnoDB監控的輸出中：

RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t`
trx id 10078 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 8000000a; asc ;;
1: len 6; hex 00000000274f; asc 'O;;
2: len 7; hex b60000019d0110; asc ;;           

Gap Locks

間隙鎖是指索引記錄之間的間隙上的鎖，或者是在第一條索引記錄之前或最後一條索引記錄之後的間隙上的鎖。例如，SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE;阻止任何其他事務插入t表c1列值為15的資料，無論是否已經存在此類值的記錄，因為所有現有值之間的間隙範圍已經被鎖定。

間隙可能跨越單個索引值、多個索引值，甚至是空的。

間隙鎖是性能和并發性之間權衡的一部分，隻用于個别事務隔離級别。

對于使用唯一索引鎖定行以搜尋唯一行的語句，不需要間隙鎖。（這不包括搜尋條件隻包含多列唯一索引的某幾列的情況；在這種情況下會使用間隙鎖。）例如，如果id列有一個唯一索引，下面的語句隻會對id值為100的行使用一個索引記錄鎖，而不會影響别的會話在前面的間隙中插入行：

SELECT * FROM child WHERE id = 100;           

如果id列沒有被索引或者有唯一索引，上述語句将會鎖定間隙。值得注意的是，間隙上可以保持不同僚務相沖突的鎖。例如，事務A可以在某個間隙上持有共享間隙鎖（gap S-lock），同時事務B在同一個間隙上持有排他間隙鎖（gap X-lock）。沖突的間隙鎖可以共存的原因是如果從索引中清除記錄，則必須合并記錄上由不同僚務持有的間隙鎖。

在InnoDB中間隙鎖隻用于抑制操作，這意味着它們的唯一目标就是阻止其他事務在間隙中插入内容。間隙鎖可以共存。一個事務持有的間隙鎖不會阻止任何其他事務在相同的間隙上擷取間隙鎖。共享間隙鎖和排他間隙鎖并沒有差別。它們之間不會互相沖突，執行相同的功能。

間隙鎖可以被顯式禁用。當您将隔離級别調整為READ COMMITTED或者啟用了innodb_locks_unsafe_for_binlog系統變量（現在已經過時）時将會禁用間隙鎖。在這些情況下，在搜尋和索引掃描時将不會使用間隙鎖，間隙鎖僅用于外鍵限制檢查和重複鍵檢查。

使用READ COMMITTED隔離級别或者啟用innodb_locks_unsafe_for_binlog還有一些其他影響。在MySQL執行WHERE條件後，會釋放不比對行的記錄鎖。對于更新語句，InnoDB将執行半一緻性讀來傳回最新送出的版本給MySQL，MySQL以此決定行是否滿足更新語句的WHERE條件。

Next-key Locks

next-key鎖是索引上的記錄鎖和索引記錄之前的間隙上的間隙鎖的組合。

InnoDB執行行級鎖定的方式是，當它搜尋或掃描表索引時，它會在遇到的索引記錄上設定共享鎖或排他鎖。是以，行級鎖實際上是索引記錄鎖。索引記錄上的next-key鎖同時會影響在該索引記錄之前的“間隙”。也就是說，next-key鎖是一個索引記錄鎖加上索引記錄之前的間隙上的間隙鎖。如果一個會話擁有索引中記錄R的共享鎖或排他鎖，其他的會話無法按索引順序在R之前的間隙中插入新的索引記錄。

假設一個索引包含了值10、11、13和20。該索引可能包含的next-key鎖包含以下區間，其中圓括号表示對區間端點的排除，方括号表示包含端點：

(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)           

對于最後一個區間，next-key鎖鎖定了索引中最大值以上的間隙以及包含了大于索引中任何值的“上界”僞記錄。上确界（ supremum）不是真正的索引記錄，是以，實際上next-key鎖隻鎖定了最大索引值之後的間隙。

預設情況下，InnoDB在REPEATABLE READ事務隔離級别下運作。在這種情況下，InnoDB使用next-key鎖進行搜尋和索引掃描，進而防止幻讀（見章節14.7.4 “Phantom Rows”）。

一個next-key鎖的事務資料以類似如下的形式出現在顯示InnoDB狀态指令和InnoDB監控的輸出中：

RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t`
trx id 10080 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 8000000a; asc ;;
1: len 6; hex 00000000274f; asc 'O;;
2: len 7; hex b60000019d0110; asc ;;           

Insert Intention Locks

插入意向鎖是插入操作在插入行之前設定的一種間隙鎖。這種鎖訓示多個事務插入相同的索引間隙時，如果它們不在間隙中相同的位置插入則不需要互相等待對方。假設有值為4和7的索引記錄。不同的事務分别嘗試插入值為5和6的的記錄，每個事務在擷取插入行的排他鎖之前使用插入意向鎖鎖定4和7之前的間隙，但它們不會互相阻塞，因為要插入的行是不沖突的。

下面的示例示範了事務在擷取插入記錄的排他鎖之前使用插入意向鎖。這個例子涉及A和B兩個客戶。

客戶A建立了包含兩個索引記錄（90和102）的表，然後開始一個事務，對ID大于100的索引記錄設定排他鎖。排他鎖包含了一個102記錄之前的間隙鎖：

mysql> CREATE TABLE child (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
mysql> INSERT INTO child (id) values (90),(102);
mysql> START TRANSACTION;
mysql> SELECT * FROM child WHERE id > 100 FOR UPDATE;
+-----+
| id |
+-----+
| 102 |
+-----+           

客戶B開始一個事務來向間隙中插入一條記錄。當事務等待擷取排他鎖時使用了一個插入意向鎖。

mysql> START TRANSACTION;
mysql> INSERT INTO child (id) VALUES (101);           

一個插入意向鎖的事務資料以類似如下的形式出現在顯示InnoDB狀态指令和InnoDB監控的輸出中：

RECORD LOCKS space id 31 page no 3 n bits 72 index `PRIMARY` of table `test`.`child`
trx id 8731 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000066; asc f;;
1: len 6; hex 000000002215; asc " ;;
2: len 7; hex 9000000172011c; asc r ;;...           

AUTO-INC Locks

AUTO-INC鎖是一種特殊的表級鎖，由插入具有 AUTO_INCREMENT屬性列的表的事務使用。在最簡單的情況下，如果一個事務正在向表中插入值，則任何其他事務都必須等待對該表執行自己的插入操作，以便第一個事務插入的揭露接收連續的主鍵值。

配置項innodb_autoinc_lock_mode控制了自動遞增鎖定所使用的算法。它允許您選擇如何在可預測的自動增量值序列和插入操作的最大并發性之間進行權衡。

詳情請見章節14.6.1.4 “AUTO_INCREMENT Handling in InnoDB”。

Predicate Locks for Spatial Indexes

InnoDB支援包含空間列的空間索引（見章節 11.5.8 “Optimizing Spatial Analysis”）。

要處理涉及空間索引鎖定的操作，next-key鎖并不能很好地支援REPEATABLE READ或SERIALIZABLE事務隔離級别。多元資料中沒有絕對順序的概念，是以無法明确哪一個是“下一個”鍵。

為了支援具有空間索引的表的事物隔離級别，InnoDB使用predicate鎖。空間索引包含了最小矩形邊界(MBR)值，是以InnoDB通過對用于查詢的MBR值設定predicate鎖來強制對索引進行一緻的讀取。其他事務不能插入或修改與查詢條件比對的行。

InnoDB Transaction Model

InnoDB事務模型的目标是将多版本資料庫的優點與傳統的二階段鎖結合起來。InnoDB在行級别執行鎖定，同時在預設情況下以Oracle風格的非鎖定一緻性讀（nonlocking consistent reads）的形式來運作查詢。InnoDB中鎖資訊存儲的空間使用率很高，是以擴大鎖不是必要的（這句翻的可能有問題）。通常情況下，允許多個使用者鎖定InnoDB表中的任何一行，或者是所有行的任意子集，而不會造成InnoDB記憶體耗盡。

Transaction Isolation Levels

事務隔離是資料庫運作的基礎之一。隔離是縮寫ACID中的 I；隔離級别是在多個事務同時進行修改和執行查詢時對性能和結果的可靠性、一緻性和可重制性之間平衡的的設定。

InnoDB提供了SQL：1992标準所規定的所有4種隔離級别：READ UNCOMMITTED（未送出讀），READ COMMITTED（送出讀），REPEATABLE READ（可重複讀），和SERIALIZABLE（序列化）。InnoDB預設的隔離級别是REPEATABLE READ。

使用者可以更改單個會話的隔離級别，或者通過SET TRANSACTION指令修改所有後續連接配接的隔離級别。如果需要為所有連接配接設定服務的預設隔離級别，可以在指令行或者配置檔案中更改--transaction-isolation選項值。詳細的隔離級别資訊和隔離級别設定的文法請參考章節13.3.6，“SET TRANSACTION Syntax”。

InnoDB通過使用不同的鎖政策來支援列出的這幾個事務隔離級别。當處理關鍵的資料、必須遵從ACID特性時，你可使用預設的REPEATABLE READ級别來強化高度的一緻性。在精準一緻性和可重複的結果沒有減小鎖開銷重要的情況下，比如大批量的報告處理，你也可以不嚴格遵從一緻性規則，采用READ COMMITTED甚至是READ UNCOMMITTED的隔離級别。SERIALIZABLE采用了比REPEATABLE READ更加嚴格的規則，通常在特定的情況下使用，例如XA分布式事務以及發現處理并發和死鎖的問題。

下面的清單說明了MySQL是如何支援不同的事務級别的。清單根據常用性從高到低排列。

REPEATABLE READ

• 這是InnoDB預設的隔離級别。在同一事務中執行一緻性讀取由第一次讀取建立的快照。這意味着如果你在同一個事務中發起多次簡單（非鎖）SELECT語句，這些語句互相之間是一緻的。詳見章節14.7.2.3 “Consistent Nonlocking Reads”。
• 對于鎖定讀取（SELECT時帶上FOR UPDATE或LOCK IN SHARE MODE）、更新和删除語句，加鎖取決于語句是否是在唯一查詢條件或範圍查詢條件下使用了唯一索引。對于使用唯一索引的單一查詢條件，InnoDB隻鎖定找到的索引記錄，而不鎖定它們之間的gap。對于其他的查詢條件，InnoDB将使用gap locks或next-key locks來鎖定掃描過的範圍，阻止其他會話在覆寫的範圍内進行插入。對于gap locks和next-key locks的資訊，詳見章節14.7.1 “InnoDB Locking”。

READ COMMITTED

• 每一次一緻性讀取，包括同一次事務中，将會設定并讀取它自身的最新快照。對于一緻性讀的詳細資訊，見章節14.7.2.3 “Consistent Nonlocking Reads”。
• 對于鎖定讀取（SELECT時帶上FOR UPDATE或LOCK IN SHARE MODE）、更新和删除語句，InnoDB隻鎖定索引記錄，不鎖定它們之間的gap，是以允許在鎖定記錄旁邊自由插入新的記錄。Gap Locking隻用于外鍵限制檢查和重複主鍵檢查。
• 因為不使用gap locking，别的會話可以在間隙間插入新的記錄，可能出現幻讀的問題。關于幻讀的詳細資訊，見章節14.7.4 “Phantom Rows”。
• READ COMMITTED隔離級别隻支援基于行的日志記錄。如果你使用READ COMMITTED和選項binlog_format=MIXED，服務将自動使用基于行的日志記錄。
• 使用READ COMMITTED的其他效果：對于UPDATE或DELETE語句，InnoDB隻持有更新、删除的行的鎖。MySQL在執行WHERE條件後，不符合條件的行的Record locks将會被釋放。這将極大減少死鎖的可能性，但是仍有可能發生。對于UPDATE語句，如果行已經被鎖，InnoDB将執行一次半一緻性（semi-consistent）讀取，傳回最新送出的版本給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;           

在這個示例中，給定的表沒有索引，是以搜尋和索引掃描使用隐藏的聚集索引來鎖定記錄（見章節14.6.2.1， “Clustered and Secondary Indexes”）而不是索引列。

假設某一會話使用下面的語句執行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語句時，它首先為讀取的每一行擷取獨占鎖（X鎖），然後決定是否修改它。如果InnoDB不修改行，它将釋放鎖。否則将保持鎖直到事務結束。這将影響事務的處理，如下所示。

當使用預設的REPEATABLE READ隔離級别時，第一個UPDATE語句擷取讀取的每一行的獨占鎖，并且全部不釋放：

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-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)           

但是，如果WHERE條件語句包含了索引列，同時InnoDB使用了索引，那麼擷取和釋放記錄鎖隻和索引列相關。在下面的例子中，第一個UPDATE語句擷取并保持b=2的行的獨占鎖。第二個UPDATE将在它擷取相同記錄的獨占鎖時阻塞，因為它也使用了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隔離級别造成的影響與啟用不推薦的設定選項innodb_locks_unsafe_for_binlog相同，除了以下幾點：啟用innodb_locks_unsafe_for_binlog是一個全局設定，影響所有會話，而隔離級别可以在全局設定或者對每個會話單獨設定。innodb_locks_unsafe_for_binlog選項隻能在服務啟動時設定，而隔離級别可以在啟動時設定或者運作時更改。

是以READ COMMITTED相比于innodb_locks_unsafe_for_binlog提供了更好、更彈性的控制。

READ UNCOMMITTED

• SELECT語句以非鎖定方式執行，但可能傳回行的早期版本。是以，使用這個隔離級别，這種讀取是不一緻的。這也被稱為髒讀（dirty read）。在其他的方面，這個隔離級别的運作與READ COMMITTED相似。

SERIALIZABLE

• 這個隔離級别與REPEATABLE READ相似，但是當autocommit關閉時InnoDB隐式地将所有普通SELECT語句轉換為SELECT … LOCK IN SHARE MODE。如果autocommit開啟，SELECT就是在其自身的事務内。是以可認為是隻讀的而且以一緻讀（非鎖定）執行，并且不需要阻塞其他事務。（如果當其他事務已經修改了選擇的行時需要強制SELECT語句阻塞，關閉autocommit）

autocommit, Commit, and Rollback

在InnoDB中，所有的使用者行為發生在單個事務之中。如果autocommit選項開啟，每一個SQL語句将以自身組成一個事務。預設情況下，MySQL對于每個新的連接配接開始的會話都會開啟autocommit，是以MySQL将在每一個沒有傳回錯誤的SQL語句執行之後進行送出。如果語句傳回了一個錯誤，送出或是復原的行為取将取決于錯誤。見章節 14.21.4 ，“InnoDB Error Handling”。

一個開啟了autocommit的會話可以通過開始前顯式指定START TRANSACTIOn或者BEGIN語句來執行一個多語句事務，并且以COMMIT或者ROLLBACK語句結束事務。見章節 13.3.1，“START TRANSACTION, COMMIT, and ROLLBACK Syntax”。

如果在一個會話中使用SET autocommit = 0關閉自動送出模式，會話将總是保持開啟事務。一個COMMIT或者ROLLBACK語句将結束目前事務并且開啟一個新的事務。

如果一個關閉autocommit的會話結束時沒有顯式送出最後的事務，MySQL将復原該事務。

有些語句隐式地結束了事務，就好像在執行語句之前已經完成了送出一樣。詳情見章節13.3.3，“Statements That Cause an Implicit Commit”。

COMMIT意味着目前事務所做的更改将持久化并且對其他會話可見。在另一方面，ROLLBACK語句表示取消目前事務所做的所有更改。COMMIT和ROLLBACK都會釋放目前事務期間設定的所有InnoDB鎖。

Grouping DML Operations with Transactions

預設情況下，MySQL服務的連接配接開始時autocommit都是開啟的，這将會自動送出您執行的每一個SQL語句。如果您有處理其他資料庫系統的經驗，這種操作模式可能會不太适應，通常标準做法是發起一些列DML語句然後同時送出或者復原它們。

要使用多語句事務，可以使用SQL語句SET autocommit = 0并且以COMMIT或者ROLLBACK結束每個事務。保持autocommit開啟的情況下，以START TRANSACTION開啟事務并且以COMMIT或者ROLLBACK結束。下面的例子顯示了兩個事務。第一個送出，而第二個復原。

shell> mysql test
mysql> CREATE TABLE customer (a INT, b CHAR (20), INDEX (a));
Query OK, 0 rows affected (0.00 sec)
mysql> -- Do a transaction with autocommit turned on.
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO customer VALUES (10, 'Heikki');
Query OK, 1 row affected (0.00 sec)
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
mysql> -- Do another transaction with autocommit turned off.
mysql> SET autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO customer VALUES (15, 'John');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO customer VALUES (20, 'Paul');
Query OK, 1 row affected (0.00 sec)
mysql> DELETE FROM customer WHERE b = 'Heikki';
Query OK, 1 row affected (0.00 sec)
mysql> -- Now we undo those last 2 inserts and the delete.
mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM customer;
+------+--------+
| a | b |
+------+--------+
| 10 | Heikki |
+------+--------+
1 row in set (0.00 sec)
mysql>           

Transactions in Client-Side Languages

在MySQL的API中，例如PHP、Perl DBI、JDBC、ODBC或者是标準C調用接口，你可以以字元串發送類似COMMIT的事務控制語句，就如同其他所有SQL語句一樣，比如SELECT或者INSERT。某些API還提供了其他特殊的送出或復原事務的功能和方法。

Consistent Nonlocking Reads

一緻性讀取意味着InnoDB使用多版本控制将某個時間點的資料庫的快照提供給一個查詢。在此之前送出的事務所做的改變對查詢是可見的，而不會看到之後送出的事務和未送出的事務所做的更改。這條規則的例外是查詢可以看到同一事務中之前的語句所做的更改。這個例外将導緻以下異常：如果你更新了表中的某幾行，SELECT語句将看到更新的這幾行的最新版本，但同時也可能會看到其他行的舊版本。如果其他會話同時更新了同一張表，異常意味着您可能會看到該表處于資料庫中不存在的狀态。

如果隔離級别為REPEATABLE READ（預設級别），同一事務内的所有一緻性讀将讀取該事務中第一次讀取所建立的快照。你可以通過送出事務，并在此之後發起新的查詢來擷取一個更加新的快照。

在READ COMMITTED隔離級别下，同一事務内的各個一緻性讀将建立和讀取它自身的最新快照。

一緻性讀（consistent read）是InnoDB在READ COMMITTED和REPEATABLE READ隔離級别下執行SELECT語句的預設模式。一次一緻性讀取對它所處理的表不會設定任何鎖，是以其他會話在表進行一緻性讀的時候可以同時任意修改這些表。

假設您正運作預設的REPEATABLE READ隔離級别。當您發起一次一緻性讀（普通的SELECT語句），InnoDB将根據查詢資料的時刻給予事務一個時間點。如果其他事務删除了某一行，并且在這個時間點之後送出，您将不會看到行已經被删除。插入和更新基本上也是如此。

注意：

在同一事務中應用于SELECT語句的資料庫快照，對于DML語句（INSERT、UPDATE、DELETE）不是必要的。如果您插入或修改了某些行并且送出了事務，從并發的其他REPEATABLE READ事務中發起的DELETE和UPDATE語句能夠影響這些剛送出的行，即使會話無法查詢它們。如果一個事務嘗試更新或删除被不同的事務更改送出的記錄，那麼這些更改将對目前事務可見。例如，您可能會遇到類似下面的情況：

SELECT COUNT(c1) FROM t1 WHERE c1 = 'xyz';
-- Returns 0: no rows match.
DELETE FROM t1 WHERE c1 = 'xyz';
-- Deletes several rows recently committed by other transaction.
SELECT COUNT(c2) FROM t1 WHERE c2 = 'abc';
-- Returns 0: no rows match.
UPDATE t1 SET c2 = 'cba' WHERE c2 = 'abc';
-- Affects 10 rows: another txn just committed 10 rows with 'abc' values.
SELECT COUNT(c2) FROM t1 WHERE c2 = 'cba';
-- Returns 10: this txn can now see the rows it just updated.           

您可以通過送出事務然後發起其他的SELECT或者開啟事務來增加時間點。

這被稱為多版本并發控制（MVCC）。

在下面的的例子中，會話A隻有在B送出插入且A也送出了事務的情況下才能夠看到B所插入的記錄，是以（可以說明）時間點在B送出後增加。

Session A 				Session B
		SET autocommit=0;		SET autocommit=0;
time
|		SELECT * FROM t;
| 		empty set
| 								INSERT INTO t VALUES (1, 2);
|
v 		SELECT * FROM t;
		empty set
								COMMIT;
		SELECT * FROM t;
		empty set
		COMMIT;
		SELECT * FROM t;
		---------------------
		| 1 | 2 |
		---------------------           

如果您希望看到資料庫的“最新”狀态，使用READ COMMITTED隔離級别或者鎖定讀：

SELECT * FROM t FOR SHARE;           

在READ COMMITTED隔離級别下，事務内的每次一緻性讀将建立并讀取它自身的最新快照。在LOCK IN SHARE模式下，取而代之發生的是鎖定讀：SELECT将會阻塞直到包含最新行的事務結束（見章節14.7.2.4 “Locking Reads”）。

一緻性讀不适用于某些DDL語句：

• 一緻性讀不作用于DROP TABLE，因為MySQL不能使用一個已經删除的表并且InnoDB破壞了表。
• 一緻性讀不作用于ALTER TABLE，因為這個語句對原始表做了一次臨時拷貝并且在臨時拷貝建立後删除原表。當您在一個事務中重新發起一次一緻性讀，新表中的行将不可見，因為這些行在事物的快照建立時并不存在。在這個情況下，事務将傳回一個錯誤：ER_TABLE_DEF_CHANGED，“Table definition has changed, please retry transaction”。

在子句中類似INSERT INTO ... SELECT，UPDATE ... (SELECT)和CREATE TABLE ... SELECT這樣并沒有指定FOR UPDATE或者LOCK IN SHARE MODE的選擇，讀取類型将會發生變化：

• 預設情況下，InnoDB使用更強的鎖，SELECT部分表現的行為類似READ COMMITTED，包括同一事務中的每個一緻性讀設定并讀取自身最新的快照。
• 為了在這種情況下使用一緻性讀，可以開啟innodb_locks_unsafe_for_binlog選項并且将事務的隔離級别設定為READ UNCOMMITTE、READ COMMITTED或者REPEATABLE READ（除了 SERIALIZABLE）。這種情況下，從選擇的表中讀取的行将不會加鎖。

Locking Reads

如果您在同一個事務中查詢然後插入或修改相關的資料，正常的SELECT語句無法提供足夠的保護。其他的事務可以更新或删除您剛剛查詢的行。InnoDB支援兩種提供了額外的安全性的鎖定讀：

• SELECT … LOCK IN SHARE MODE
• 對于正在讀取的所有行設定共享形式的鎖。其他會話可以讀取行，但是在您的事務送出前無法修改他們。如果這些行中的任意一行被其他尚未送出的事務更改，則您的事務将一直等待直到該事務結束，然後使用最新的值做處理。
• SELECT … FOR UPDATE
• 對于查詢中遇到的索引記錄，鎖定行和任何相關的索引項，就如同您對這些行發起了UPDATE語句一樣。其他事務被阻止更新這些行、執行SELECT ... LOCK IN SHARE MODE或在某些隔離級别下讀取資料。一緻性讀取忽略在讀取視圖中存在的在記錄上設定的任何鎖。（記錄的舊版本無法被鎖定；它們通過對記錄在記憶體中的備份應用undo log來重構）

這些子句主要在處理樹結構或圖結構的資料時非常有用，無論是在單個表中還是拆分在多個表中。您從一個位置到另一個位置周遊邊（圖結構的edge）或樹的分支時，同時保留傳回和更改這些“指針”的值的權利。

當事務送出或復原時，所有由LOCK IN SHARE MODE和FOR UPDATE查詢所設定的鎖都會被釋放。

注意：

鎖定讀隻在不啟用自動送出的情況下生效（使用START TRANSACTION開始事務或設定autocommit為0）。

除非子查詢中同時也指定了鎖讀子句，否則外部語句中的鎖定讀不會鎖定嵌套子查詢中表的行。例如，下面的語句不會鎖定t2表中的行。

SELECT * FROM t1 WHERE c1 = (SELECT c1 FROM t2) FOR UPDATE;           

如果要鎖定t2表中的行，需要給子查詢添加鎖定字尾：

SELECT * FROM t1 WHERE c1 = (SELECT c1 FROM t2 FOR UPDATE) FOR UPDATE;           

Locking Read Examples

假設您想要向child表中插入新的一行，同時保證該行在parent表中有一個父級行。您的應用程式代碼需要保證整個操作序列的引用完整性。

首先，使用一緻性讀查詢parent表确認父級行存在。您能否安全地在child表中插入子行？并不能，因為其他會話可能在您SELECT和INSERT之間的時間内在您不知情的情況下删除父級行。

為了避免這種可能的情況，使用LOCK IN SHARE MODE來執行SELECT：

SELECT * FROM parent WHERE NAME = 'Jones' LOCK IN SHARE MODE;           

在LOCK IN SHARE MODE查詢傳回父級‘Jones’之後，您可以安全地向child表中插入子記錄，然後送出事務。任何試圖在parent表的相應行中擷取排他鎖的的事務都将等待直到您（的事務）完成，或者說是等待直到所有表中的資料處于一緻狀态。

另外一個例子，考慮在CHILD_CODES表中有個整形統計字段，該字段用于為添加到CHILD表中的每個子節點配置設定唯一辨別。不要使用一緻性讀或共享模式讀取計數器的目前值，因為資料庫的兩個使用者在可以看到計數器相同的值，如果兩個事務試圖向CHILD表中添加具有相同辨別的行，将會出現鍵值重複的錯誤。

在這種情況下，LOCK IN SHARE MODE并不是一個好的解決方案，因為兩個使用者同時讀取計數器，至少其中一個将在其嘗試更新計數器時以死鎖終止。

為了實作讀取并增加計數器，首先使用FOR UPDATE來對計數器執行鎖定讀，然後增加計數器。例如：

SELECT counter_field FROM child_codes FOR UPDATE;
UPDATE child_codes SET counter_field = counter_field + 1;           

SELECT ... FOR UPDATE讀取最新可用的資料，為它讀取的每一行設定排他鎖。是以，它為一個搜尋SQL設定了與UPDATE将在行上設定的相同的鎖。

上面的描述僅僅是SELECT ... FOR UPDATE如何工作的一個例子。在MySQL中，生成唯一辨別的特定任務隻需要通路表一次就可以完成：

UPDATE child_codes SET counter_field = LAST_INSERT_ID(counter_field + 1);
SELECT LAST_INSERT_ID();           

SELECT語句僅檢索辨別資訊（特定于目前連結）。它不通路任何表。（注：這裡翻的可能有問題）

Locks Set by Different SQL Statements in InnoDB

鎖定讀取、更新或删除通常在SQL語句執行的時候對其掃描過的所有索引記錄設定記錄鎖。語句中是否存在排除行的條件并不重要。InnoDB不記錄确切的WHERE條件，隻記錄被掃描的索引範圍。這些鎖通常是同時阻止插入記錄之前“間隙”的next-key鎖。但是，間隙鎖可以顯式禁用，這将導緻不使用next-key鎖定。詳情見章節14.7.1 “InnoDB Locking”。事務隔離級别也會影響設定的鎖的類型，見章節14.7.2.1 “Transaction Isolation Levels”。

如果在搜尋中使用了輔助索引，而要設定的索引記錄鎖是獨占的，則InnoDB将同時檢索相應的聚集索引記錄并在其上設定鎖。

如果您的語句沒有适用的索引，MySQL必須掃描整個表以處理該語句，則表的每一行都會被鎖定，這将阻止其他使用者對該表的所有插入操作。建立好的索引非常重要，這樣您的查詢就不會不必要地掃描過多的行。

InnoDB設定的鎖的類型如下所示：

• SELECT ... FROM采用一緻性讀，讀取資料庫的快照，除非隔離級别設定為SERIALIZABLE，不會設定任何鎖。在SERIALIZABLE級别下，搜尋将在它遇到的索引記錄上設定共享的next-key鎖。但是，對于使用唯一索引鎖定行以搜尋唯一行的語句，隻設定索引記錄鎖。
• 對于SELECT ... FOR UPDATE或SELECT ... IN SHARE MODE，将在掃描的行擷取鎖，對于不符合條件不包含在結果集中的行，它們的鎖将被釋放（例如，它們不符合在WHERE語句中的條件）。但是，在某些情況下，行可能不會立即被解鎖，因為結果行與它原始資料之間的關系在查詢執行過程中丢失。例如，在UNION語句中，表中被掃描（同時被鎖定）的行可能會被插入臨時表中，然後再計算它們是否符合結果集的條件。在這種情況下，臨時表中的行與原始表中的行之間的關系将丢失，而原始表中的行直到查詢執行結束時才會被解鎖。
• SELECT ... LOCK IN SHARE MODE為所有搜尋過程中遇到的索引記錄設定共享的next-key鎖。但是，對于使用唯一索引鎖定行以搜尋唯一行的語句，隻設定索引記錄鎖。
• SELECT ... FOR UPDATE為所有搜尋過程中遇到的索引記錄設定獨占的next-key鎖。但是，對于使用唯一索引鎖定行以搜尋唯一行的語句，隻設定索引記錄鎖。
• 對于在搜尋中遇到的索引記錄，SELECT ... FOR UPDATE阻止其他會話進行SELECT ... LOCK IN SHARE MODE以及在某些事務隔離級别下的讀取操作。一緻性讀取将忽略讀取視圖中存在的記錄上的鎖。
• UPDATE ... WHERE ...為搜尋時遇到的所有記錄設定獨占的next-key鎖。但是，對于使用唯一索引鎖定行以搜尋唯一行的語句，隻設定索引記錄鎖。
• 當UPDATE修改聚集索引記錄時，将對受影響的次要索引記錄進行隐式鎖定。在插入新的輔助索引記錄和插入新的輔助索引記錄之前執行的重複檢查掃描時，UPDATE操作還會對受影響的輔助索引記錄設定共享鎖。
• DELETE FROM ... WHERE ...為搜尋時遇到的所有記錄設定獨占的next-key鎖。但是，對于使用唯一索引鎖定行以搜尋唯一行的語句，隻設定索引記錄鎖。
• INSERT對于插入行設定排他鎖。這個鎖是一個索引記錄鎖，而不是next-key鎖（即沒有間隙鎖），不會阻止其他會話在插入行之前的間隙進行插入。

在插入行之前，将設定一種稱為插入意向間隙鎖的間隙鎖。這種鎖訓示多個事務插入相同的索引間隙時，如果它們不在間隙中相同的位置插入則不需要互相等待對方。假設有值為4和7的索引記錄。不同的事務分别嘗試插入值為5和6的的記錄，每個事務在擷取插入行的排他鎖之前使用插入意向鎖鎖定4和7之間的間隙，但它們不會互相阻塞，因為要插入的行是不沖突的。

如果發生鍵值重複的錯誤，則在重複索引記錄上設定共享鎖。如果有多個會話試圖插入同一行(如果另一個會話已經具有獨占鎖)，此時共享鎖的這種使用可能會導緻死鎖。如果另一個會話删除該行，可能發生上述這種情況。假設InnoDB表t1具有如下結構：

CREATE TABLE t1 (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;           

現在假設3個會話按順序執行下面的操作：

# Session 1:
START TRANSACTION;
INSERT INTO t1 VALUES(1);
# Session 2:
START TRANSACTION;
INSERT INTO t1 VALUES(1);
# Session 3:
START TRANSACTION;
INSERT INTO t1 VALUES(1);
# Session 1:
ROLLBACK;           

會話1的第一個操作擷取行的獨占鎖。會話2和會話3的操作都會導緻重複鍵錯誤，它們都請求行的共享鎖。當會話1復原時，它會釋放該行上的獨占鎖，并允許會話2和3的排隊執行共享鎖請求。此時，會話2和3死鎖：由于互相持有共享鎖，這兩個會話都不能擷取該行的排他鎖。

如果表已經包含了鍵值為1的行且3個會話按順序執行如下操作時，會發生相似的情況：

# Session 1:
START TRANSACTION;
DELETE FROM t1 WHERE i = 1;
# Session 2:
START TRANSACTION;
INSERT INTO t1 VALUES(1);
# Session 3:
START TRANSACTION;
INSERT INTO t1 VALUES(1);
# Session 1:
COMMIT;           

會話1的第一個操作擷取行的獨占鎖。會話2和會話3的操作都會導緻重複鍵錯誤，它們都請求行的共享鎖。當會話1送出時，它會釋放該行上的獨占鎖，并允許會話2和3的排隊執行共享鎖請求。此時，會話2和3死鎖：由于互相持有共享鎖，這兩個會話都不能擷取該行的排他鎖。

• INSERT ... ON DUPLICATE KEY UPDATE與簡單的INSERT不同，當發生鍵值重複的錯誤時，将對要更新的行設定排他鎖而不是共享鎖。對于主鍵重複，采用獨占索引記錄鎖。對于唯一鍵值重複，采用排他的next-key鎖。
• 如果在唯一鍵上沒有沖突，REPLACE會像INSERT一樣處理。除此之外，在被替換的行上将設定排他的next-key鎖。（注：REPLACE是MySQL對SQL的擴充）
• INSERT INTO T SELECT ... FROM S WHERE ...對于插入T表的每一行設定排他索引記錄鎖（沒有間隙鎖）。如果事務隔離級别為READ COMMITTED或者啟用了innodb_locks_unsafe_for_binlog選項同時隔離級别不為SERIALIZABLE，InnoDB以一緻性讀的方式進行搜尋（無鎖）。否則，InnoDB在S表中的行設定共享next-key鎖。InnoDB必須在後一種情況下設定鎖：在使用基于語句的二進制日志進行前滾恢複期間，每個SQL語句必須以與最初執行的方式完全相同的方式執行。
• CREATE TABLE ... SELECT ...執行SELECT時設定共享next-key鎖或者一緻性讀，與INSERT ... SELECT相同。
• 當在結構REPLACE INTO t SELECT ... FROM s WHERE ...或UPDATE t ... WHERE col IN (SELECT ... FROM s ...)中使用SELECT時，InnoDB對s表中的行設定共享next-key鎖。
• 當初始化表中預先定義為AUTO_INCREMENT的列時，InnoDB将在AUTO_INCREMENT列關聯的索引尾部設定排他鎖。在通路自動遞增計數器時，InnoDB使用特定的AUTO-INC表級鎖模式，鎖隻持續到目前SQL語句結束，而不是整個事務結束。持有AUTO_INC表鎖時，其他會話無法向表中插入；見章節14.7.2 “InnoDB Transaction Model”。
• InnoDB在擷取之前已經初始化的AUTO_INCREMENT列的值時不設定任何鎖。
• 如果一個表上定義了一個外鍵限制，任何需要檢查限制條件的插入、更新或删除操作都會在要檢查限制的記錄上設定共享的、記錄級别的鎖。InnoDB在限制失敗的情況下也會設定這些鎖。
• LOCK TABLE設定表鎖，但這是在InnoDB層級之上的MySQL層級來設定這些鎖。如果選項innodb_table_locks值為1(預設)并且autocommit = 0，InnoDB可以感覺表鎖，而且InnoDB之上的的MySQL層能感覺行級鎖的狀況。
• 否則，InnoDB的自動死鎖檢測無法檢測涉及此類表鎖的死鎖。另外，因為在較進階别的MySQL層不知道行級鎖的情況，是以有可能在另一個會話目前已經具有行級鎖的表上獲得表鎖。但是這并不會危及事務的完整性，在章節14.7.5.2 “Deadlock Detection and Rollback”中進行了讨論。另見章節14.6.1.6 “Limits on InnoDB Tables”。

Phantom Rows

在同一個事務中，相同的查詢在不同的時間産生不同的結果集，這就是所謂的幻讀問題。例如，如果一個SELECT（語句）被執行兩次，但是第二次傳回了第一次沒有傳回的行，這行就被稱為“幻影”行。

假設child表的id列上有一個索引，您希望讀取和鎖定辨別符值大于100的表中的所有行，以便在之後更新這些選擇的行中的某些列：

SELECT * FROM child WHERE id > 100 FOR UPDATE;           

查詢從id大于100的第一個記錄開始掃描索引。假設表包含id值為90和102的行。如果在掃描範圍内的索引記錄上設定的鎖沒有對在間隙中進行插入的操作進行鎖定(在本例中，90和102之間的間隙)，則另一個會話可以将一個新的id為101的行插入到表中。如果您在同一事務中執行相同的SELECT，您将在查詢傳回的結果集中看到一個新的id為101的行(“幻影”)。如果我們将一組行視為資料項，則新的幻影子項将違反事務的隔離原則，即事務運作時，它讀取的資料在事務處理期間不會發生更改。

為了防止幻讀，InnoDB使用了組合了索引記錄鎖和間隙鎖的稱為next-key鎖的算法。InnoDB執行級鎖定的方式是，當它搜尋或掃描表索引時，它會在遇到的索引記錄上設定共享鎖或排他鎖。是以，行級鎖實際上是索引記錄鎖。此外，索引記錄上的next-key鎖也會影響該索引記錄之前的“間隙”。也就是說，next-key鎖是索引記錄鎖加上索引記錄之前的間隙上的間隙鎖。如果一個會話擁有索引中的記錄R的共享或獨占鎖，則另一個會話不能按索引順序在R之前的間隙中插入新的索引記錄。

當InnoDB掃描索引時，它還可以鎖定索引中最後一個記錄之後的間隙。在前面的示例中：為了防止任何id大于100的資料插入表中，InnoDB設定的鎖包含了id值102之後的間隙上的鎖。

您可以在應用程式中使用next-key鎖定來實作唯一性檢查：如果您在共享模式下讀取資料，而沒有看到将要插入的行有重複，則可以安全地插入行，并知道在讀取期間在讀取行之後(的間隙)設定的next-key鎖可以防止任何人同時插入與您的行相同的内容。是以，next-key鎖使您能夠“鎖定”表中不存在的東西。

間隙鎖可以被顯式禁用，這一點在章節14.7.1 “InnoDB Locking”中已經說明。這可能導緻幻讀問題，因為當間隙鎖被禁用時其他會話可以在間隙中插入新的行。

Deadlocks in InnoDB

死鎖是描述了這樣一種情況，不同的事務因為各自持有另一個事務需要的鎖而全部無法繼續執行。因為這兩個事務都在等待資源變為可用狀态，而他們同時不釋放自身所持有的鎖。

當多個事務以不同的順序鎖定多個表中的行時（通過如UPDATE或SELECT ... FOR UPDATE之類的語句），可能會發生死鎖。當這樣的語句鎖定索引記錄和間隙時，每個事務可能會因為時間問題隻獲得一部分鎖，這樣也可能會出現死鎖。對于死鎖的樣例，見章節14.7.5.1 “An InnoDB Deadlock Example”。

為了減少死鎖的可能性，應該多使用事務而不是鎖表語句；盡量減少插入或更新資料事務的規模，使它們不會持續很長時間；當不同的事務更新多個表或大範圍的行時，在每個事務中使用相同的操作順序執行語句（例如SELET ... FOR UPDATE）；在SELECT ... FOR UPDATE和UPDATE ... WHERE語句中可能使用到的列上建立索引；死鎖的可能性不受隔離級别的影響，因為隔離級别改變了讀操作的行為，而死鎖則是由寫操作引起的。對于更多避免死鎖、從死鎖狀态中恢複的資訊，見章節14.7.5.3 “How to Minimize and Handle Deadlocks”。

當啟用死鎖檢測（預設）并發生死鎖時，InnoDB将檢測到該情況并復原其中一個事務（受害者）。如果使用 innodb_deadlock_detect配置項關閉死鎖檢測，在死鎖的情況下InnoDB通過innodb_lock_wait_timeout設定選項來復原事務。是以，即使您的應用程式邏輯是正确的，您仍需要處理事務復原重試的情況。若要檢視InnoDB使用者事務中最後的死鎖情況，請使用SHOW ENGINE INNODB STATUS指令。如果事務結構或應用程式錯誤處理頻繁引發死鎖，則應在啟用innodb_print_all_deadlocks選項的情況下運作，以便将所有死鎖相關的資訊列印到mysqld的錯誤日志。對于更多死鎖是如何被自動發現并處理的資訊，見章節14.7.5.2 “Deadlock Detection and Rollback”。

An InnoDB Deadlock Example

下面的示例說明了當鎖請求引發死鎖時錯誤是如何發生的。這個例子涉及兩個使用者，A和B。

首先，使用者A建立了包含1行記錄的表，然後開啟事務。在事務執行過程中，A通過共享模式選擇該行擷取了該行上的一把S鎖：

mysql> CREATE TABLE t (i INT) ENGINE = InnoDB;
Query OK, 0 rows affected (1.07 sec)
mysql> INSERT INTO t (i) VALUES(1);
Query OK, 1 row affected (0.09 sec)
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM t WHERE i = 1 LOCK IN SHARE MODE;
+------+
| i |
+------+
| 1 |
+------+           

然後，使用者B開啟了一個事務并嘗試删除表中的該行：

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> DELETE FROM t WHERE i = 1;           

删除操作請求X鎖。因為與使用者A持有的S鎖不相容，獲鎖無法被準許，是以該請求進入鎖請求隊列同時使用者B被阻塞。

最後，使用者A也嘗試删除表中的該行：

mysql> DELETE FROM t WHERE i = 1;
ERROR 1213 (40001): Deadlock found when trying to get lock;
try restarting transaction           

這裡會發生死鎖，因為使用者A需要一個X鎖來删除該行。但是，這個鎖請求無法被準許，因為用戶端B已經有了對X鎖的請求，并且正在等待使用者A釋放其S鎖。也不能将A持有的S鎖更新為X鎖，因為B事先請求X鎖。是以，InnoDB為其中一個使用者生成一個錯誤并釋放其鎖。用戶端傳回錯誤：

ERROR 1213 (40001): Deadlock found when trying to get lock;
try restarting transaction           

此時，可以準許另一個使用者的鎖請求，并将該行從表中删除。

Deadlock Detection and Rollback

啟用死鎖檢測時（預設），InnoDB将自動檢測事務死鎖并復原一個或多個事務以打破死鎖。InnoDB嘗試選擇相對較小的事務進行復原，而事務的大小取決于插入、更新或删除的行數。

當innodb_table_locks = 1（預設值）并且autocommit = 0，InnoDB可以感覺到表鎖，并且在它之上的MySQL層了解行級鎖的資訊。否則，InnoDB無法檢測到由MySQL鎖表語句設定的表鎖或由InnoDB以外的存儲引擎設定的鎖引發的死鎖。通過設定innodb_lock_wait_timeout系統變量的值來解決這些情況。

當InnoDB對一個事物執行完全復原時，該事務設定的所有鎖都将被釋放。但是，如果僅單個SQL語句因為錯誤而引發復原，則可能會保留該語句設定的一些鎖。發生這種情況是因為根據InnoDB存儲行鎖的結構，在（錯誤發生）之後無法知道哪個鎖是由哪個語句設定的。

如果在事務中使用SELECT調用存儲的函數，而函數過程中的某個語句失敗，則該語句復原。此外，如果在此之後執行ROLLBACK，則整個事務将復原。

如果InnoDB監控輸出的“LATEST DETECTED DEADLOCK”部分包含了以下面内容為開頭的資訊，TOO DEEP OR LONG SEARCH IN THE LOCK TABLE WAITS-FOR GRAPH, WE WILL ROLL BACK FOLLOWING TRANSACTION,，就表明在等待隊列中的事務超過了上限200。一個等待隊列中的事務數量超過200将以死鎖處理，所有嘗試檢查進入隊列的事務都将復原。當一個請求鎖的線程必須檢查等待隊列上的事務所擁有的超過1000000個鎖時，也可能發生相同的錯誤。

對于組織資料庫操作避免死鎖的技巧，詳見章節14.7.5 “Deadlocks in InnoDB”。

Disabling Deadlock Detection

在高并發系統中，當多個線程等待同一鎖時，死鎖檢測會減慢處理速度。在這種情況下，禁用死鎖檢測并依靠事務復原中的innodb_lock_wait_timeout設定來處理發生的死鎖或許會更加高效。死鎖檢測可以通過innodb_deadlock_detect配置項來禁用。

How to Minimize and Handle Deadlocks

本節基于章節14.7.5.2 “Deadlock Detection and Rollback”中有關死鎖的概念資訊。它解釋了如何組織資料庫操作以最小化死鎖和應用程式中所需的後續錯誤處理。解釋了如何組織資料庫操作以減少死鎖以及後續應用程式中的錯誤處理。

死鎖在事務性資料庫中是一個典型的問題，但它們并不危險，除非它們太頻繁，以至于您根本無法運作某些事務。通常情況下您需要編寫應用程式來保持當事務因為死鎖而復原時重新發起事務。

InnoDB使用自動行級鎖。即使是隻插入或删除一行的事務，也可能發生死鎖。這是因為這些操作并不是真正的“原子”操作；它們會自動設定插入或删除行的（可能多個）索引記錄的鎖。

您可以使用以下技巧處理死鎖并減少其發生的可能性：

• 任何時候都可以使用SHOW ENGINE INNODB STATUS指令來确定最近的死鎖的原因。這可以幫助您優化應用程式以避免死鎖。
• 如果頻繁發現死鎖警告，可以通過啟用innodb_print_all_deadlocks配置選項擷取更詳細的調試資訊。有關每個死鎖的資訊，不僅僅是最近的死鎖，都記錄在MySQL錯誤日志中。調試完成後，請禁用此選項。
• 總是預先準備在事務因死鎖而失敗時重新發起事務。死鎖并不危險，重試即可。
• 保持事務的簡短，以減小其發生沖突的可能性。
• 在進行一組相關更改之後立即送出事務，使它們不容易發生沖突。特别是不要讓互動式MySQL會話在有未送出事務的情況下打開很長時間。
• 如果您需要使用鎖定讀（SELECT … FOR UPDATE或SELECT … LOCK IN SHARE MODE），嘗試使用較低的事務隔離級别，例如READ COMMITTED。
• 當修改事務中的多個表或同一表中不同的行集時，每次都以相同的順序執行這些操作。這樣事務可以形成定義良好的隊列，不會發生死鎖。例如，将資料庫操作組織到應用程式中的函數中，或者調用存儲過程，而不是在不同位置編寫多個相似的插入、更新和删除語句序列。
• 盡可能較少使用鎖。如果您能夠容許SELECT從舊快照中傳回資料，就不要為該語句添加FOR UPDATE或LOCK IN SHARE MODE字尾。這裡使用READ COMMITTED隔離級别會比較好，因為同一事務中的每個一緻性讀取都是從自己最新的快照中讀取的。
• 如果沒有其他更好的辦法，請使用表級鎖序列化您的事務。對于具有事務性的表，如InnoDB表，使用LOCK TABLES的正确方式是以SET autocommit = 0（而不是START TRANSACTION）開始事務，接下來緊跟着LOCK TABLES語句，直到您顯式送出事務後再調用UNLOCK TABLES。例如，如果您需要寫入t1表并且從t2表讀取資料，您可以這麼做：

SET autocommit=0;
LOCK TABLES t1 WRITE, t2 READ, ...;
... do something with tables t1 and t2 here ...
COMMIT;
UNLOCK TABLES;           

• 表級鎖阻止對表的并發更新，以減少系統響應能力的代價來避免忙碌系統中的死鎖。
• 序列化事務的另一種方法是建立一個僅包含一行的輔助“信号量”表。讓每個事務在通路其他表之前先更新該行。這樣，所有事務都以串行方式進行。注意，InnoDB實時死鎖檢測算法在這種情況下也是有效的，因為序列化鎖是行級鎖。對于MySQL表級鎖，必須使用方法逾時來解決死鎖。