1 MySQL鎖介紹
2 MySQL表級鎖
2.1 表級鎖介紹
? 表級鎖由SQL layer實現(xiàn)蛋辈。MySQL的表級鎖有兩種：一種是表鎖擒贸；一種是元數(shù)據(jù)鎖（meta data lock弄喘，MDL)。
? MySQL 實現(xiàn)的表級鎖的爭用狀態(tài)變量：
? -table_locks_immediate：產(chǎn)生表級鎖的次數(shù)；
? -table_locks_waited：出現(xiàn)表級鎖爭用而發(fā)生等待的次數(shù)；
2.1.1 表鎖介紹
表鎖有兩種表現(xiàn)形式：表共享讀鎖（Table Read Lock）蟹演、表獨占寫鎖（Table Write Lock）
手動增加表鎖
lock table 表名稱 read(write),表名稱2 read(write)，其他;
查看表鎖情況
show open tables;
刪除表鎖
unlock tables;
2.2.2 表鎖演示
2.2.2.1 環(huán)境準備
-- 新建表
CREATE TABLE mylock (
id int(11) NOT NULL AUTO_INCREMENT, NAME varchar(20) DEFAULT NULL, PRIMARY KEY (id)
);
INSERT INTO mylock (id,NAME) VALUES (1, 'a');
INSERT INTO mylock (id,NAME) VALUES (2, 'b');
INSERT INTO mylock (id,NAME) VALUES (3, 'c');
INSERT INTO mylock (id,NAME) VALUES (4, 'd');
2.2.2.2 表讀鎖
session1：lock table mylock read; -- 給mylock表加讀鎖
session1：select * from mylock; -- 可以查詢
session1：select * from tdep; -- 不能訪問非鎖定表
session2：select * from mylock; -- 可以查詢 沒有鎖
session2：update mylock set name='x' where id=2; -- 修改阻塞,自動加行寫鎖
session1：unlock tables; -- 釋放表鎖
session2：Rows matched: 1 Changed: 1 Warnings: 0 -- 修改執(zhí)行完成
session1：select * from tdep; -- 可以訪問
2.2.2.3 表寫鎖
session1: lock table mylock write; -- 給mylock表加寫鎖
session1: select * from mylock; -- 可以查詢
session1：select * from tdep; -- 不能訪問非鎖定表
session1：update mylock set name='y' where id=2; -- 可以執(zhí)行
session2：select * from mylock; -- 查詢阻塞
session1：unlock tables; -- 釋放表鎖
session2：4 rows in set (22.57 sec) -- 查詢執(zhí)行完成
session1：select * from tdep; -- 可以訪問
2.2 元數(shù)據(jù)鎖
2.2.1 元數(shù)據(jù)鎖介紹
? 在 MySQL 5.5 版本中引入了 MDL(metaDataLock 元數(shù)據(jù)鎖) 顷蟀，當對表做增刪改查操作的時候酒请，加 MDL 讀鎖；當要對表做結(jié)構(gòu)變更操作的時候鸣个，加 MDL 寫鎖羞反。
2.2.2 元數(shù)據(jù)鎖演示
session1: begin;-- 開啟事務
session1: select * from mylock;-- 加MDL讀鎖
session2: alter table mylock add f int; -- 修改阻塞
session1：commit; -- 提交事務或者rollback釋放讀鎖
session2：Query OK, 0 rows affected (38.67 sec) --修改完成Records: 0 Duplicates: 0 Warnings: 0
3 MySQL行級鎖
3.1 行級鎖介紹
行級鎖由InnoDB存儲引擎實現(xiàn)
3.2 行級鎖分類
3.2.1 按照鎖定范圍分類
? ①記錄鎖（Record Locks）: 單個行記錄上的鎖。
? ②間隙鎖（Gap Locks）: 間隙鎖囤萤，鎖定一個范圍昼窗，但不包含記錄本身。
? ③臨鍵鎖（Next-Key Locks） : 記錄鎖 + 間隙鎖涛舍，鎖定一個范圍澄惊，并且鎖定記錄本身。
記錄鎖(Record Lock)：
? 事務加鎖后鎖住的只是表的某一條記錄富雅。
記錄鎖出現(xiàn)條件：
?精準條件命中掸驱，并且命中的條件字段是唯一索引；
例如：update user_info set name=’張三’ where id=1 ,這里的id是唯一索引吹榴。
?Record Lock總是會去鎖住索引記錄亭敢，如果InnoDB存儲引擎表在建立的時候沒有設置任何一個索引，那么這時InnoDB存儲引擎會使用隱式的主鍵來進行鎖定图筹。
記錄鎖的作用：
?加了記錄鎖之后可以避免數(shù)據(jù)在查詢的時候被修改的重復讀問題帅刀，也避免了在修改的事務未提交前被其他事務讀取的臟讀問題。
間隙鎖(gap lcok )：
? 當我們用范圍條件而不是相等條件檢索數(shù)據(jù)远剩，并請求共享或排他鎖時扣溺，InnoDB會給符合條件的已有數(shù)據(jù)記錄的索引項加鎖；對于鍵值在條件范圍內(nèi)但并不存在的記錄瓜晤，叫做“間隙（GAP)”锥余，InnoDB也會對這個“間隙”加鎖，這種鎖機制就是所謂的間隙鎖痢掠。
? 很顯然驱犹，在使用范圍條件檢索并鎖定記錄時，InnoDB這種加鎖機制會阻塞符合條件范圍內(nèi)鍵值的并發(fā)插入足画，這往往會造成嚴重的鎖等待雄驹。因此，在實際應用開發(fā)中淹辞，尤其是并發(fā)插入比較多的應用医舆，我們要盡量優(yōu)化業(yè)務邏輯，盡量使用相等條件來訪問更新數(shù)據(jù)，避免使用范圍條件蔬将。
間隙鎖的出現(xiàn)條件（RR事務隔離級別下）：
? 使用普通索引鎖定爷速；
? 使用多列唯一索引；
? 使用唯一索引鎖定多行記錄霞怀。
間隙鎖的作用：
? 防止幻讀惫东，以滿足相關(guān)隔離級別的要求；
? 滿足恢復和復制的需要：
臨鍵鎖(Next-Key Lock)
? 臨鍵鎖是INNODB的行鎖默認算法毙石，它是記錄鎖和間隙鎖的組合凿蒜，臨鍵鎖會把查詢出來的記錄鎖住，同時也會把該范圍查詢內(nèi)的所有間隙空間也會鎖住胁黑，再之它會把相鄰的下一個區(qū)間也會鎖住废封。
臨鍵鎖出現(xiàn)條件：
? 范圍查詢并命中，查詢命中了索引丧蘸。
臨鍵鎖的作用：
? 結(jié)合記錄鎖和間隙鎖的特性漂洋，臨鍵鎖避免了在范圍查詢時出現(xiàn)臟讀、重復讀力喷、幻讀問題刽漂。加了臨鍵鎖之后，在范圍區(qū)間內(nèi)數(shù)據(jù)不允許被修改和插入弟孟。
? Next-Key Lock是結(jié)合了Gap Lock和Record Lock的一種鎖定算法贝咙，在Next-Key Lock算法下，InnoDB對于行的查詢都是采用這種鎖定算法拂募。
? 除了Next-Key Locking庭猩，還有Previous-Key Locking技術(shù)。
3.2.2 按照功能分類
①共享讀鎖（S）：允許一個事務去讀一行陈症，阻止其他事務獲得相同數(shù)據(jù)集的排他鎖蔼水。
select * from table_name WHERE ... lock in share mode -- 共享讀鎖 手動添加
select * from table_name -- 無鎖
②排他寫鎖（X）：允許獲得排他寫鎖的事務更新數(shù)據(jù)，阻止其他事務取得相同數(shù)據(jù)集的共享讀鎖和排他寫鎖录肯。
a.自動加
對于UPDATE趴腋、DELETE和INSERT的DML語句，InnoDB會自動給涉及數(shù)據(jù)集加排他鎖（X)论咏；
? b.手動加
select * from table_name where ... for update
? InnoDB也實現(xiàn)了表級鎖优炬，也就是意向鎖，意向鎖是mysql內(nèi)部使用的厅贪，不需要用戶干預蠢护。
意向共享鎖（IS）：事務打算給數(shù)據(jù)行加行共享鎖，事務在給一個數(shù)據(jù)行加共享鎖前必須先取得該表的IS鎖卦溢。
意向排他鎖（IX）：事務打算給數(shù)據(jù)行加行排他鎖糊余，事務在給一個數(shù)據(jù)行加排他鎖前必須先取得該表的IX鎖。
? 由于InnoDB存儲引擎支持的是行級別的鎖单寂，因此意向鎖其實不會阻塞除全表掃以外的任何請求贬芥。故表級意向鎖與行級鎖的兼容性如下所示：
| 共享鎖（S） | 排他鎖（X） | 意向共享鎖（IS） | 意向排他鎖（IX） | |
|---|---|---|---|---|
| 共享鎖（S） | 兼容 | 沖突 | 兼容 | 沖突 |
| 排他鎖（X） | 沖突 | 沖突 | 沖突 | 沖突 |
| 意向共享鎖（IS） | 兼容 | 沖突 | 兼容 | 兼容 |
| 意向排他鎖（IX） | 沖突 | 沖突 | 兼容 | 兼容 |
如果一個事務請求的鎖模式與當前的鎖兼容， InnoDB 就將請求的鎖授予該事務宣决； 反之蘸劈，如果兩者不兼容，該事務就要等待鎖釋放
意向鎖到底有什么作用尊沸？
意向鎖的主要作用是為了【全表更新數(shù)據(jù)】時的性能提升威沫。否則在全表更新數(shù)據(jù)時，需要先檢索該表某些記錄上面是否有行鎖洼专。
innodb的意向鎖主要用于多粒度的鎖并存的情況棒掠。比如事務A要在一個表上加S鎖，如果表中的一行已被事務B加了X鎖屁商，那么該鎖的申請也應被阻塞烟很。如果表中的數(shù)據(jù)很多，逐行檢查鎖標志的開銷將很大蜡镶，系統(tǒng)的性能將會受到影響雾袱。為了解決這個問題，可以在表級上引入新的鎖類型來表示其所屬行的加鎖情況官还，這就引出了“意向鎖”的概念芹橡。
?舉個例子，如果表中記錄1億望伦，事務A把其中有幾條記錄上了行鎖了林说，這時事務B需要給這個表加表級鎖，如果沒有意向鎖的話屯伞，那就要去表中查找這一億條記錄是否上鎖了述么。如果存在意向鎖，那么假如事務Ａ在更新一條記錄之前愕掏，先加意向鎖度秘，再加Ｘ鎖，事務B先檢查該表上是否存在意向鎖饵撑，存在的意向鎖是否與自己準備加的鎖沖突剑梳，如果有沖突，則等待直到事務Ａ釋放滑潘，而無須逐條記錄去檢測垢乙。事務Ｂ更新表時，其實無須知道到底哪一行被鎖了语卤，它只要知道反正有一行被鎖了就行了追逮。
?主要作用是處理行鎖和表鎖之間的矛盾酪刀，能夠顯示“某個事務正在某一行上持有了鎖，或者準備去持有鎖”
3.3 兩階段鎖（2PL）
鎖操作分為兩個階段钮孵，加鎖階段與解鎖階段骂倘， 加鎖階段與解鎖階段不相交。加鎖階段只加鎖不放鎖巴席；解鎖階段只放鎖不加鎖历涝。
3.4 行鎖演示
?InnoDB行鎖是通過給索引上的索引項加鎖來實現(xiàn)的。InnoDB這種行鎖實現(xiàn)特點意味著只有通過索引條件檢索的數(shù)據(jù)漾唉，InnoDB才使用行級鎖荧库，否則，InnoDB將使用表鎖赵刑。
-- 查看行鎖狀態(tài)
show STATUS like 'innodb_row_lock%';
Innodb_row_lock_current_waits： 當 前 正 在 等 待 鎖 定 的 數(shù) 量 分衫；
?Innodb_row_lock_time： 從 系 統(tǒng) 啟 動 到 現(xiàn) 在 鎖 定 總 時 間 長 度 ；
?Innodb_row_lock_time_avg： 每 次 等 待 所 花 平 均 時 間 般此；
?Innodb_row_lock_time_max：從系統(tǒng)啟動到現(xiàn)在等待最常的一次所花的時間丐箩；
?Innodb_row_lock_waits：系統(tǒng)啟動后到現(xiàn)在總共等待的次數(shù)；
3.4.1 行讀鎖
-- session1:
begin; -- 開啟事務未提交
select * from mylock where ID=1 lock in share mode; -- 手動加id=1的行讀鎖,使用索引
-- session2:
update mylock set name='y' where id=2; -- 未鎖定該行可以修改
update mylock set name='y' where id=1; -- 鎖定該行修改阻塞
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction -- 鎖定超時
-- session1:
commit; -- 提交事務或者rollback釋放讀鎖
-- session2:
update mylock set name='y' where id=1; -- 修改成功
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
-- 注：使用索引加行鎖 恤煞，未鎖定的行可以訪問
3.4.2 行讀鎖升級為表鎖
-- session1:
begin; -- 開啟事務未提交
select * from mylock where name='c' lock in share mode; -- 手動加name='c'的行讀鎖,未使用索引
-- session2：
update mylock set name='y' where id=2; -- 修改阻塞 未用索引行鎖升級為表鎖
-- session1:
commit; -- 提交事務或者rollback釋放讀鎖
-- session2：
update mylock set name='y' where id=2; -- 修改成功
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
-- 注：未使用索引行鎖升級為表鎖
3.4.3 行寫鎖
-- session1:
begin;--開啟事務未提交
select * from mylock where id=1 for update; -- 手動加id=1的行寫鎖,
-- session2：
select * from mylock where id=2; -- 可以訪問
-- session2:
select * from mylock where id=1 ; -- 可以讀 不加鎖
-- session2:
select * from mylock where id=1 lock in share mode ; -- 加讀鎖被阻塞
-- session1：
commit; -- 提交事務或者rollback釋放寫鎖
-- session2： -- 執(zhí)行成功
-- 注：主鍵索引產(chǎn)生記錄鎖
3.4.4 間隙鎖
環(huán)境準備：
mysql> create table news (id int, number int,primary key (id));
mysql> insert into news values(1,2);
......
-- 加非唯一索引
mysql> alter table news add index idx_num(number);
3.4.4.1 非唯一索引的等值
-- 非唯一索引的等值
-- session1:
start transaction ;
update news set number=3 where number=4;
-- session2:
start transaction ;
insert into news value(2,3);#（均在間隙內(nèi)屎勘，阻塞）
insert into news value(7,8);#（均在間隙外，成功）
insert into news value(2,8);#（id在間隙內(nèi)居扒，number在間隙外概漱，成功）
insert into news value(4,8);#（id在間隙內(nèi)，number在間隙外喜喂，成功）
insert into news value(7,3);#（id在間隙外瓤摧，number在間隙內(nèi)，阻塞）
insert into news value(7,2);# (id在間隙外玉吁，number為上邊緣數(shù)據(jù)照弥，阻塞)
insert into news value(2,2);#（id在間隙內(nèi)，number為上邊緣數(shù)據(jù)进副，阻塞）
insert into news value(7,5);#（id在間隙外这揣，number為下邊緣數(shù)據(jù)，成功）
insert into news value(4,5);#（id在間隙內(nèi)影斑，number為下邊緣數(shù)據(jù)给赞，阻塞）
結(jié)論：只要number（where后面的）在間隙里（2 3 4），不包含最后一個數(shù)（5）矫户，則不管id是多少都會阻塞片迅。
3.4.4.2 主鍵索引范圍
-- 主鍵索引范圍
-- session1:
start transaction ;
update news set number=3 where id>1 and id <6;
-- session2:
start transaction ;
insert into news value(2,3);#（均在間隙內(nèi)，阻塞）
insert into news value(7,8);#（均在間隙外皆辽，成功）
insert into news value(2,8);#（id在間隙內(nèi)柑蛇，number在間隙外芥挣，阻塞）
insert into news value(4,8);#（id在間隙內(nèi)，number在間隙外耻台，阻塞）
insert into news value(7,3);#（id在間隙外空免，number在間隙內(nèi)，成功）
-- id無邊緣數(shù)據(jù)粘我，因為主鍵不能重復
結(jié)論：只要id（在where后面的）在間隙里(2 4 5)，則不管number是多少都會阻塞痹换。
3.4.4.3 非唯一索引無窮大
-- 無窮大
-- session1:
start transaction ;
update news set number=3 where number=13 ;
-- session2:
start transaction ;
insert into news value(11,5);#(執(zhí)行成功)
insert into news value(12,11);#(執(zhí)行成功)
insert into news value(14,11);#( 阻 塞 )
insert into news value(15,12);#(阻塞)
-- 檢索條件number=13,向左取得最靠近的值11作為左區(qū)間征字，向右取得無窮大作為右區(qū)間（由于沒有記錄），因此session1的間隙鎖的范圍（11娇豫，無窮大）
-- 注：非主鍵索引產(chǎn)生間隙鎖匙姜，主鍵范圍產(chǎn)生間隙鎖
結(jié)論：id和number都在間隙里會阻塞。
3.4.5 死鎖
? 兩個 session 互相等待對方的資源釋放之后能釋放自己的資源冯痢，造成了死鎖氮昧。
-- session1:
begin; -- 開啟事務未提交
update mylock set name='m' where id=1; -- 手動加行寫鎖 id=1 ，使用索引
-- session2：
begin; -- 開啟事務未提交
update mylock set name='m' where id=2; -- 手動加行寫鎖 id=2 浦楣，使用索引
-- session1:
update mylock set name='nn' where id=2; -- 加寫鎖被阻塞
-- session2：
update mylock set name='nn' where id=1; -- 加寫鎖會死鎖袖肥，不允許操作
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
3.5 行鎖原理分析
3.5.1 簡單SQL的加鎖分析
3.5.1.1 下面SQL語句加鎖么，加的是什么鎖振劳？
? SQL1：select * from t1 where t1.id = 1;
? SQL2：delete from t1 where id = 10;
3.5.1.2 談鎖前提
? 前提一：當前系統(tǒng)的隔離級別是什么椎组？
? 前提二：id列是不是主鍵？
? 前提三：id列如果不是主鍵历恐，那么id列上有索引嗎寸癌？
? 前提四：id列上如果有二級索引，那么這個索引是唯一索引嗎
3.5.1.3 簡單SQL1的加鎖分析
? 分析加鎖需要考慮當前的隔離級別弱贼。在串行化隔離級別下蒸苇，MVCC會降級成Lock-Based CC，是加鎖的吮旅，加的是讀鎖溪烤。在其他三種隔離級別下，由于MVCC的快照讀庇勃，所以是不加鎖的氛什。
3.5.1.4 簡單SQL2的加鎖分析
組合一：id主鍵+RC
如下圖所示：
id是主鍵時，此SQL只需要在id=10這條記錄上加寫鎖即可匪凉。
組合二：id唯一索引+RC
如下圖所示：
過程如下：
由于id是唯一索引時枪眉，where會走id列的索引進行過濾，在找到id=10的記錄后對唯一索引id=10的記錄加X鎖再层。
同時會回表查詢主鍵索引name=d的數(shù)據(jù)贸铜，并對name=d的數(shù)據(jù)也加上X鎖
為什么唯一索引與主鍵索引都要加上鎖堡纬？
試想一下，如果在刪除的過程中并發(fā)了一條更新語句蒿秦，where用的是name作為條件烤镐，那么如果刪除操作沒有對主鍵索引加鎖，那么更新語句是感知不到刪除語句的存在從而進行更新棍鳖，這違背了同一記錄上的寫操作需要串行化執(zhí)行的原則
組合三：id非唯一索引+RC
如下圖所示：
過程如下：
對ID索引中符合條件的數(shù)據(jù)加上X鎖炮叶。
再把對應的主鍵索引上的數(shù)據(jù)也加上X鎖。
與組合二的區(qū)別在于ID是非唯一索引會對滿足條件的多條數(shù)據(jù)都加上X鎖渡处。而組合二只會對一條數(shù)據(jù)加上X鎖
組合四：id無索引+RC
如下圖所示：
過程如下：
由于沒有索引镜悉，所以走的是全表掃描，所以會對主鍵索引上每一條記錄施加X鎖医瘫。
為什么會對所有記錄施加X鎖侣肄，而不是表鎖或者說符合條件的數(shù)據(jù)加X鎖呢？
這是由于InnoDB的實現(xiàn)決定的醇份。由于沒有索引稼锅，無法在存儲引擎層過濾（執(zhí)行計劃里的Using Where），所以存儲引擎對每一條數(shù)據(jù)加鎖后返回給SqlServer進行過濾僚纷。Sql Server在進行過濾的過程中對不滿足條件的數(shù)據(jù)會立即執(zhí)行unlock_row方法矩距，把不滿足條件的記錄放鎖（違背2PL的約束），這樣做怖竭，保證了最后只有滿足條件的數(shù)據(jù)持有X鎖剩晴。但是對每條數(shù)據(jù)加鎖的步驟是沒法省略的。
組合五：id主鍵+RR
與組合一是相同的侵状。
主鍵范圍產(chǎn)生Gap
組合六：id唯一索引+RR
與組合二是相同的赞弥。
組合七：id非唯一索引+RR
如下圖所示：
這一組合與RC級別區(qū)別就很大了。
RC級別下是允許幻讀的出現(xiàn)趣兄。
而MySql在RR級別下是不允許幻讀的出現(xiàn)的绽左。
所以為了解決幻讀的問題，InnoDB會施加間隙鎖來解決這一問題（為防止幻讀 （RR）艇潭，不能在間隙insert）拼窥。
過程如下：
首先對ID索引中符合條件的數(shù)據(jù)施加X鎖。
對符合條件施加X鎖的數(shù)據(jù)前后間隙施加間隙鎖蹋凝。
對ID索引對應的主鍵索引的數(shù)據(jù)施加X鎖鲁纠。
? 施加了間隙鎖以后，數(shù)據(jù)的前后都不會插入新的數(shù)據(jù)鳍寂，就可以保證兩次當前讀的結(jié)果完全一致改含。
組合八：id無索引+RR
如下圖所示：
? 這一點就非常恐怖了迄汛，由于沒有索引所以會對全表加鎖捍壤，還會對所有的間隙加鎖骤视。這個時候?qū)τ谠摫沓瞬患渔i的快照讀，其他所有的并發(fā)操作全部鎖死鹃觉。
? 當然對于這一點與組合四類似专酗，MySql也有自己的優(yōu)化。與組合四：id無索引, RC類似盗扇，就是所謂的semi-consistent read祷肯。semi-consistent read開啟的情況下，對于不滿足查詢條件的記錄疗隶，MySQL會提前放鎖佑笋。針對上面的這個用例，就是除了記錄[d,10]抽减，[g,10]之外允青，所有的記錄鎖都會被釋放橄碾，同時不加間隙鎖卵沉。
? semi-consistent read如何觸發(fā)：要么是read committed隔離級別；要么是Repeatable Read隔離級別法牲，同時設置了 innodb_locks_unsafe_for_binlog 參數(shù)史汗。
3.5.2 復雜SQL語句的行鎖分析
SQL：delete from t1 where pubtime>1 and pubtime<20 and userid='hdc' and comment is not NULL;
在詳細分析這條SQL的加鎖情況前，還需要有一個知識儲備拒垃，那就是一個SQL中的where條件如何拆分停撞？在這里直接給出分析后的結(jié)果：
index key：puptime > 1 and puptime < 20。此條件確定了idx_t1_pu上面的值悼瓮。
index filter：userid = 'hdc' 戈毒。此條件，可以在idx_t1_pu索引上進行過濾横堡，但不屬于index key埋市。
table filter：comment is not NULL。此條件命贴，無法在idx_t1_pu索引上進行過濾道宅，只能在聚簇索引上過濾。
? 在where條件過濾時胸蛛，先過濾index key（索引列為范圍查詢污茵，起始條件為index First Key，截止條件為index Last key）葬项，再過濾index filter（索引列）泞当，最后過濾table filter（非索引列）。在ICP過程中民珍，下推index filter零蓉。
分析完這些后我們看一下加鎖情況（假定在Repeatable Read隔離級別下）：
從圖中可以看出笤受，在RR隔離級別下，
? 由Index Key所確定的范圍敌蜂，被加上了間隙鎖箩兽；
? Index Filter鎖給定的條件 (userid = ‘hdc’)何時過濾，視MySQL的版本而定章喉。
? 在MySQL 5.6版本之前汗贫，不支持Index Condition Pushdown(ICP)，因此Index Filter在MySQL Server層 過濾秸脱，
? 在MySQL 5.6版本之后落包，支持了Index Condition Pushdown(ICP)，則在index key上過濾摊唇。
? 若不支持ICP咐蝇，不滿足Index Filter的記錄，也需要加上記錄X鎖巷查；
? 若支持ICP有序，則不滿足Index Filter的記錄，無需加記錄X鎖 岛请；
? 圖中旭寿，用紅色箭頭標出的X鎖，是否要加崇败，視是否支持ICP而定盅称。
? 而Table Filter對應的過濾條件，則在聚簇索引中讀取后后室，在MySQL Server層面過濾缩膝，因此聚簇索引上也需要X鎖。
? 最后岸霹，選取出了一條滿足條件的記錄[8,hdc,d,5,good]疾层，但是加鎖的數(shù)量，要遠遠大于滿足條件的記錄數(shù)量松申。
結(jié)論：
? 在RR隔離級別下云芦，針對一個復雜的SQL，首先需要提取其where條件贸桶。
? Index Key確定的范圍舅逸，需要加上GAP鎖；
? Index Filter過濾條件皇筛，視MySQL版本是否支持ICP而定琉历；若支持ICP，則不滿足Index Filter的記錄，不加X鎖旗笔，否則需要X鎖彪置；
? Table Filter過濾條件，無論是否滿足蝇恶，都需要加X鎖拳魁，加鎖的數(shù)量，要遠遠大于滿足條件的記錄數(shù)量撮弧。
3.6 死鎖原理分析
本文前面的部分潘懊，基本上已經(jīng)涵蓋了MySQL/InnoDB所有的加鎖規(guī)則。深入理解MySQL如何加鎖贿衍，有兩個比較重要的作用：
可以根據(jù)MySQL的加鎖規(guī)則授舟，寫出不會發(fā)生死鎖的SQL；
可以根據(jù)MySQL的加鎖規(guī)則贸辈，定位出線上產(chǎn)生死鎖的原因释树；
3.6.1 死鎖是怎么被發(fā)現(xiàn)的？
3.6.1.1 死鎖成因&&檢測方法
左圖那兩輛車造成死鎖了嗎擎淤？不是奢啥！右圖四輛車造成死鎖了嗎？是揉燃！
我們mysql用的存儲引擎是innodb扫尺，從日志來看，innodb主動探知到死鎖，并回滾了某一苦苦等待的事務明也。問題來了涩哟，innodb是怎么探知死鎖的？
直觀方法是在兩個事務相互等待時樟凄，當一個等待時間超過設置的某一閥值時，對其中一個事務進行回滾，另一個事務就能繼續(xù)執(zhí)行迈倍。這種方法簡單有效，在innodb中捣域，參數(shù)innodb_lock_wait_timeout用來設置超時時間啼染。
僅用上述方法來檢測死鎖太過被動，innodb還提供了wait-for graph算法來主動進行死鎖檢測焕梅，每當加鎖請求無法立即滿足需要并進入等待時迹鹅，wait-for graph算法都會被觸發(fā)
3.6.1.2 wait-for graph原理
我們怎么知道上圖中四輛車是死鎖的？他們相互等待對方的資源贞言，而且形成環(huán)路斜棚！我們將每輛車看為一個節(jié)點，當節(jié)點1需要等待節(jié)點2的資源時，就生成一條有向邊指向節(jié)點2弟蚀，最后形成一個有向圖蚤霞。我們只要檢測這個有向圖是否出現(xiàn)環(huán)路即可，出現(xiàn)環(huán)路就是死鎖义钉！這就是wait-for graph算法昧绣。
innodb將各個事務看為一個個節(jié)點，資源就是各個事務占用的鎖捶闸，當事務1需要等待事務2的鎖時滞乙，就生成一條有向邊從1指向2，最后行成一個有向圖鉴嗤。
3.6.2 innodb隔離級別斩启、索引與鎖
? 死鎖檢測是死鎖發(fā)生時innodb給我們的救命稻草，我們需要它醉锅，但我們更需要的是避免死鎖發(fā)生的能力兔簇，如何盡可能避免？這需要了解innodb中的鎖硬耍。
3.6.2.1 鎖與索引的關(guān)系
? 假設我們有一張消息表（msg）垄琐，里面有3個字段。假設id是主鍵经柴，token是非唯一索引狸窘，message沒有索引。
| id | token | message |
|---|---|---|
| bigint | varchar(30) | varchar(4096) |
? innodb對于主鍵使用了聚簇索引坯认，這是一種數(shù)據(jù)存儲方式翻擒，表數(shù)據(jù)是和主鍵一起存儲，主鍵索引的葉結(jié)點存儲行數(shù)據(jù)牛哺。對于普通索引陋气，其葉子節(jié)點存儲的是主鍵值。
下面分析下索引和鎖的關(guān)系引润。
1）delete from msg where id=2巩趁；
由于id是主鍵，因此直接鎖住整行記錄即可淳附。
2）delete from msg where token=’ cvs’;
由于token是二級索引议慰，因此首先鎖住二級索引（兩行），接著會鎖住相應主鍵所對應的記錄奴曙；
3）delete from msg where message=訂單號是多少’别凹；
message沒有索引，所以走的是全表掃描過濾缆毁。這時表上的各個記錄都將添加上X鎖番川。
3.6.2.2 鎖與隔離級別的關(guān)系
? 我們在3.6.2.1節(jié)談論的其實是RC隔離級別下的鎖，它可以防止不同事務版本的數(shù)據(jù)修改提交時造成數(shù)據(jù)沖突的情況，但當別的事務插入數(shù)據(jù)時可能會出現(xiàn)問題颁督。
? 如下圖所示践啄，事務A在第一次查詢時得到1條記錄，在第二次執(zhí)行相同查詢時卻得到兩條記錄沉御。從事務A角度上看是見鬼了屿讽！這就是幻讀，RC級別下盡管加了行鎖吠裆，但還是避免不了幻讀伐谈。
? innodb的RR隔離級別可以避免幻讀發(fā)生，怎么實現(xiàn)试疙？當然需要借助于鎖了诵棵！
? 為了解決幻讀問題，innodb引入了gap鎖祝旷。
? 在事務A執(zhí)行：update msg set message=‘訂單’ where token=‘a(chǎn)sd’;
? innodb首先會和RC級別一樣履澳，給索引上的記錄添加上X鎖，此外怀跛，還在非唯一索引’asd’與相鄰兩個索引的區(qū)間加上鎖距贷。
? 這樣，當事務B在執(zhí)行insert into msg values (null,‘a(chǎn)sd',’hello’); commit;時吻谋，會首先檢查這個區(qū)間是否被鎖上忠蝗，如果被鎖上，則不能立即執(zhí)行漓拾，需要等待該gap鎖被釋放阁最。這樣就能避免幻讀問題。
3.6.3 死鎖成因
? 了解了innodb鎖的基本原理后晦攒，下面分析下死鎖的成因闽撤。如前面所說得哆，死鎖一般是事務相互等待對方資源脯颜，最后形成環(huán)路造成的。下面簡單講下造成相互等待最后形成環(huán)路的例子贩据。
3.6.3.1不同表相同記錄行鎖沖突
? 這種情況很好理解栋操，事務A和事務B操作兩張表，但出現(xiàn)循環(huán)等待鎖情況饱亮。
3.6.3.2相同表記錄行鎖沖突
? 這種情況比較常見矾芙，之前遇到兩個job在執(zhí)行數(shù)據(jù)批量更新時，jobA處理的的id列表為[1,2,3,4]近上，而job處理的id列表為[8,9,10,4,2]剔宪，這樣就造成了死鎖。
3.6.3.3不同索引鎖沖突
? 這種情況比較隱晦，事務A在執(zhí)行時葱绒，除了在二級索引加鎖外感帅，還會在聚簇索引上加鎖，在聚簇索引上加鎖的順序是[1,4,2,3,5]地淀，而事務B執(zhí)行時失球，只在聚簇索引上加鎖，加鎖順序是[1,2,3,4,5]帮毁，這樣就造成了死鎖的可能性实苞。
3.6.3.4 gap鎖沖突
? innodb在RR級別下，如下的情況也會產(chǎn)生死鎖烈疚，比較隱晦黔牵。不清楚的同學可以自行根據(jù)上節(jié)的gap鎖原理分析下。
3.6.4 如何盡可能避免死鎖
? 1）以固定的順序訪問表和行爷肝。比如對于第3.6.3.2節(jié)兩個job批量更新的情形荧止，簡單方法是對id列表先排序，后執(zhí)行阶剑，這樣就避免了交叉等待鎖的情形跃巡；又比如對于3.6.3.1節(jié)的情形，將兩個事務的sql順序調(diào)整為一致牧愁，也能避免死鎖素邪。
? 2）大事務拆小。大事務更傾向于死鎖猪半，如果業(yè)務允許兔朦，將大事務拆小。
? 3）在同一個事務中磨确，盡可能做到一次鎖定所需要的所有資源沽甥，減少死鎖概率。
? 4）降低隔離級別乏奥。如果業(yè)務允許摆舟，將隔離級別調(diào)低也是較好的選擇，比如將隔離級別從RR調(diào)整為RC邓了，可以避免掉很多因為gap鎖造成的死鎖恨诱。
? 5）為表添加合理的索引∑可以看到如果不走索引將會為表的每一行記錄添加上鎖照宝，死鎖的概率大大增大。
3.6.5 如何定位死鎖成因
? 1）通過應用業(yè)務日志定位到問題代碼句葵，找到相應的事務對應的sql厕鹃；
? 因為死鎖被檢測到后會回滾兢仰，這些信息都會以異常反應在應用的業(yè)務日志中，通過這些日志我們可以定位到相應的代碼剂碴，并把事務的sql給梳理出來旨别。
? 2）確定數(shù)據(jù)庫隔離級別。
? 執(zhí)行select @@global.tx_isolation汗茄，可以確定數(shù)據(jù)庫的隔離級別秸弛。如果數(shù)據(jù)庫的隔離級別是RC，這樣可以很大概率排除gap鎖造成死鎖的嫌疑;
? 3）找DBA執(zhí)行下show InnoDB STATUS看看最近死鎖的日志洪碳。
? 這個步驟非常關(guān)鍵递览。通過DBA的幫忙，我們可以有更為詳細的死鎖信息瞳腌。
