大綱
1.鎖概述
2.鎖分類
3.鎖實戰之全域性鎖
4.鎖實戰之表級鎖(偏讀)
5.鎖實戰之行級鎖(偏寫)—行級鎖升級表級鎖
6.鎖實戰之行級鎖(偏寫)—間隙鎖
7.鎖實戰之行級鎖(偏寫)—臨鍵鎖
8.鎖實戰之行級鎖(偏寫)—幻讀演示和解決
9.鎖實戰之行級鎖(偏寫)—最佳化建議
10.鎖實戰之樂觀鎖
11.行鎖原理
12.死鎖與解決方案
1.鎖概述
undo log版本鏈 + Read View機制實現的MVCC多版本併發控制，可以防止事務併發讀寫同一資料時出現的髒讀+不可重複讀+幻讀問題。但除髒讀+不可重複讀+幻讀問題外，併發讀寫同一資料還有髒寫問題。就是當多個事務併發更新同一條資料時，此時就可能會出現髒寫問題，如下圖示：
一.事務A對資料進行操作，將值修改為A；
二.事務B也對該資料進行操作，將值修改為B；
三.接著事務A進行了回滾，將值恢復成NULL；
四.事務B發現資料值B沒有了，出現資料不一致；
髒寫：一個事務修改了另一個沒提交的事務修改過的值，導致資料可能不一致。因為這個沒提交的事務有可能會回滾。
2.鎖分類
(1)從操作的粒度可分為表級鎖、行級鎖和頁級鎖
(2)從操作的型別可分為讀鎖和寫鎖
(3)從操作的效能可分為樂觀鎖和悲觀鎖
(1)從操作的粒度可分為表級鎖、行級鎖和頁級鎖
一.表級鎖：每次操作鎖住整張表
鎖定粒度最大，發生鎖衝突機率最高，併發度最低，應用在MyISAM、InnoDB、BDB儲存引擎中。
二.行級鎖：每次操作鎖住一行資料
鎖定粒度最小，發生鎖衝突的機率最低，併發度最高，應用在InnoDB儲存引擎中。
三.頁級鎖：每次鎖定相鄰的一組記錄
鎖定粒度界於表鎖和行鎖間，開銷和加鎖時間界於表鎖和行鎖間。併發度一般，應用在BDB儲存引擎中。
(2)從操作的型別可分為讀鎖和寫鎖
一.讀鎖(S鎖)：共享鎖，行級鎖
針對同一份資料，多個讀操作可以同時進行而不會互相影響。事務A對記錄新增了S鎖，可以對記錄進行讀操作，不能做修改。其他事務可以對該記錄追加S鎖，但是不能追加X鎖。要追加X鎖，需要等記錄的S鎖全部釋放。
二.寫鎖(X鎖)：排它鎖，行級鎖
當前寫操作沒有完成前，它會阻斷其他事務的寫鎖和讀鎖請求。事務A對記錄新增了X鎖，可以對記錄進行讀和修改，其他事務不能對記錄進行讀和修改。
三.IS鎖：意向共享鎖，表級鎖
已加S鎖的表肯定會有IS鎖，反過來，有IS鎖的表不一定會有S鎖。
四.IX鎖：意向排它鎖，表級鎖
已加X鎖的表肯定會有IX鎖，反過來，有IX鎖的表不一定會有X鎖。
(3)從操作的效能可分為樂觀鎖和悲觀鎖
一.樂觀鎖
一般的實現方式是對記錄資料版本進行對比。在資料庫表中有一個version欄位，根據該欄位進行衝突檢測。如果有衝突就提示錯誤資訊，所以樂觀鎖屬於程式碼層面的鎖控制。
二.悲觀鎖
修改一條資料時，為避免被其他事務同時修改，在修改前先鎖定。共享鎖和排它鎖都是悲觀鎖的不同實現，屬於資料庫提供的鎖機制。
根據加鎖的範圍，MySQL的鎖可以分為：全域性鎖、表級鎖和行級鎖三類。
3.鎖實戰之全域性鎖
(1)什麼是全域性鎖
(2)全域性鎖使用場景
(3)資料庫全域性鎖的兩種方法
(4)全域性鎖示例
(1)什麼是全域性鎖
全域性鎖是對整個資料庫例項加鎖，新增全域性鎖後，以下語句會被阻塞：
一.資料更新語句(增刪改)
二.資料定義語句(建表、修改表結構等)
三.更新類事務的提交語句
全域性鎖命令(FTWRL)：
mysql> flush tables with read lock;(2)全域性鎖使用場景
全域性鎖的典型使用場景是：全庫邏輯備份(mysqldump)。透過全域性鎖保證不會有其他執行緒對資料庫進行更新，然後對整個庫備份，在備份過程中整個庫完全處於只讀狀態。
值得注意的是，當使用MySQL的邏輯備份工具mysqldump進行備份時：
一.新增--single-transaction引數時可正常更新
可以在導資料之前就啟動一個事務，來確保拿到一致性快照檢視。由於MVCC的支援，這個過程中資料是可以正常更新的。
二.--single-transaction引數只適用於所有表都使用InnoDB引擎的情況
如果有表沒有使用事務引擎，那麼備份就需要使用FTWRL命令。
(3)資料庫全域性鎖的兩種方法
-- 方式一
mysql> flush tables with read lock;
-- 方式二(不建議)：
mysql> set global readonly = true;執行FTWRL命令後由於客戶端發生異常斷開，那麼MySQL會自動釋放這個全域性鎖，整個庫回到可以正常更新的狀態。
將整個庫設定為readonly後，如果客戶端發生異常，那麼資料庫就會一直保持readonly狀態，導致整個庫長時間不可寫。
(4)全域性鎖示例
一.使用資料庫test_lock建立test1表
create table test1(
id int primary key,
name varchar(32)
);二.插入資料
insert into test1 values(1,'A'),(2,'B'),(3,'C');三.檢視錶資料
mysql> select * from test1;
+----+------+
| id | name |
+----+------+
| 1 | A |
| 2 | B |
| 3 | C |
+----+------+四.加全域性鎖
mysql> flush tables with read lock;五.執行insert操作報錯
mysql> insert into test1 values(4,'D');
ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock六.執行查詢可以正常返回結果
mysql> select * from test1;
+----+------+
| id | name |
+----+------+
| 1 | A |
| 2 | B |
| 3 | C |
+----+------+七.釋放鎖，新增成功
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test1 values(4,'D');
Query OK, 1 row affected (0.10 sec)
4.鎖實戰之表級鎖(偏讀)
(1)資料準備
(2)加鎖語法
(3)加讀鎖測試
(4)加寫鎖測試
表級鎖是MySQL中鎖定粒度最大的鎖，表示對當前操作的整張表加鎖。它實現簡單，資源消耗較少，被大部分MySQL引擎支援，最常使用的MyISAM與InnoDB都支援表級鎖定。
表級鎖特點：開銷小，加鎖快，出現死鎖的機率比較低。鎖定的粒度比較大，發生鎖衝突的機率比較高，併發度比較低。MyISAM引擎預設使用表級鎖。
表級鎖分類：表共享讀鎖(共享鎖)與表獨佔寫鎖(排他鎖)。
(1)資料準備
-- 建立表選擇MyISAM儲存引擎
create table mylock01(
id int primary key auto_increment,
title varchar(20)
) engine myisam;
create table mylock02(
id int primary key auto_increment,
title varchar(20)
) engine myisam;
insert into mylock01(title) values('a1'),('b1'),('c1'),('d1'),('e1');
insert into mylock02(title) values('a2'),('b2'),('c2'),('d2'),('e2');(2)加鎖語法
一.查詢表中加過的鎖
-- 0表示沒有加鎖，當前的所有資料庫表都沒有加鎖
mysql> show open tables;
+----------+----------+--------+-------------+
| Database | Table | In_use | Name_locked |
+----------+----------+--------+-------------+
| test | mylock02 | 0 | 0 |
| test | test1 | 0 | 0 |
| test | mylock01 | 0 | 0 |
+----------+----------+--------+-------------+
-- 查詢加鎖的表，條件In_use大於0
mysql> show open tables where In_use > 0;
Empty set (0.00 sec)二.手動加表鎖
語法格式：LOCK TABLE 表名 READ(WRITE), 表名2 READ(WRITE) ...;
-- 為mylock01加讀鎖(共享鎖)，給mylock02加寫鎖(排它鎖)
mysql> lock table mylock01 read, mylock02 write;
Query OK, 0 rows affected (0.00 sec)
mysql> show open tables where In_use > 0;
+-----------+----------+--------+-------------+
| Database | Table | In_use | Name_locked |
+-----------+----------+--------+-------------+
| test_lock | mylock01 | 1 | 0 |
| test_lock | mylock02 | 1 | 0 |
+-----------+----------+--------+-------------+三.釋放鎖，解除鎖定
mysql> unlock tables;
mysql> show open tables where In_use > 0;
Empty set (0.00 sec)(3)加讀鎖測試
開啟session1和session2兩個會話視窗：
一.在session1中對mylock01表加讀鎖
mysql> lock table mylock01 read;二.對mylock01進行讀操作，兩個視窗都可以讀
mysql> select * from mylock01;三.在session1進行寫操作失敗
mysql> update mylock set title='a123' where id=1;
ERROR 1100 (HY000): Table 'mylock' was not locked with LOCK TABLES四.在session1中讀取其他表不允許，比如讀取mylock02表，讀取失敗，不能讀取未鎖定的表
mysql> select * from mylock02;
ERROR 1100 (HY000): Table 'mylock02' was not locked with LOCK TABLES五.在session2中對mylock01表進行寫操，執行後一直阻塞
mysql> update mylock01 set title='a123' where id = 1;六.session1解除mylock01的鎖定，session2的修改執行成功
mysql> unlock tables;
mysql> update mylock01 set title='a123' where id = 1;
Query OK, 1 row affected (47.83 sec)結論：對MyISAM表的讀寫操作加讀鎖，不會阻塞其他程序對同一表的讀請求。但會阻塞其他程序對同一表的寫請求，只有當讀鎖釋放後才執行寫操作。
(4)加寫鎖測試
一.在session1中對mylock01表加寫
mysql> lock table mylock01 write;二.在session1中，對mylock01進行讀寫操作，都是可以進行的
mysql> select * from mylock01 where id = 1;
mysql> update mylock01 set title = 'a123' where id = 1;三.在session1中讀其他表還是不允許
mysql> select * from mylock02 where id = 1;
ERROR 1100 (HY000): Table 'mylock02' was not locked with LOCK TABLES四.在session2中讀mylock01表，讀操作被阻塞
mysql> select * from mylock01;五.在session2中對mylock01表進行寫操作，仍然被阻塞
mysql> update mylock01 set title = 'a456' where id = 1;六.session1釋放鎖，session2操作執行成功
mysql> unlock tables;結論：對MyISAM表加寫鎖，會阻塞其他程序對同一表的讀和寫操作。只有當寫鎖釋放後，才會執行其他程序的讀和寫操作。
5.鎖實戰之行級鎖(偏寫)—行級鎖升級表級鎖
(1)行級鎖介紹
(2)行鎖測試
(3)InnoDB行級鎖升級為表級鎖
(4)查詢SQL的鎖測試
(5)行鎖的三種加鎖模式
(1)行級鎖介紹
行鎖是MySQL鎖中粒度最小的一種鎖。因為鎖的粒度很小，所以發生資源爭搶機率最小，併發效能最大。但是也會造成死鎖，每次加鎖和釋放鎖的開銷也是比較大的。
一.使用MySQL行級鎖的三個前提
前提一：使用InnoDB引擎
前提二：開啟事務，保證隔離級別是RR
前提三：行級鎖要使用到索引才能生效
二.InnoDB行級鎖的型別
型別一：共享鎖(S鎖)
當事務對資料加上共享鎖後，其他使用者可以併發讀取資料。但任何事務都不能對資料進行修改，直到已釋放所有共享鎖。
型別二：排它鎖(X鎖)
事務T對資料A加上排它鎖後，其他事務不能再對資料A加任何型別的鎖。獲取到排它鎖的事務既能讀資料，又能修改資料。
三.加鎖的方式
InnoDB引擎預設會為增刪改SQL語句涉及到的資料，自動加上排他鎖。select查詢語句預設不加任何鎖，如果要加也可以使用下面的方式。
方式一：查詢加共享鎖(S鎖)
mysql> select * from table_name where ... lock in share mode;方式二：查詢加排他鎖(x鎖)
mysql> select * from table_name where ... for update;四.鎖相容
共享鎖只能相容共享鎖，不相容排它鎖，排它鎖互斥共享鎖和其它排它鎖。
(2)行鎖測試
一.資料準備
create table innodb_lock(
id int primary key auto_increment,
name varchar(20),
age int,
index idx_name(name)
);
insert into innodb_lock values(null,'a',13);
insert into innodb_lock values(null,'a',23);
insert into innodb_lock values(null,'a',33);
insert into innodb_lock values(null,'a',43);
insert into innodb_lock values(null,'a',43);
insert into innodb_lock values(null,'b',53);
insert into innodb_lock values(null,'c',63);
insert into innodb_lock values(null,'d',73);
mysql> select * from innodb_lock;
+----+------+------+
| id | name | age |
+----+------+------+
| 1 | a | 13 |
| 2 | a | 23 |
| 3 | a | 33 |
| 4 | a | 43 |
| 5 | a | 43 |
| 6 | b | 53 |
| 7 | c | 63 |
| 8 | d | 73 |
+----+------+------+
8 rows in set (0.00 sec)二.開啟兩個視窗，開啟手動提交事務(提交或者回滾事務就會釋放鎖)
#開啟MySQL資料庫手動提交
SET autocommit = 0;三.視窗1中對id為5的資料進行更新操作，但是不commit
執行之後，在當前視窗檢視錶資料，發現被修改了。
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> update innodb_lock set name = 'aaa' where id = 5;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from innodb_lock;
+----+------+------+
| id | name | age |
+----+------+------+
| 1 | a | 13 |
| 2 | a | 23 |
| 3 | a | 33 |
| 4 | a | 43 |
| 5 | aaa | 43 |
| 6 | b | 53 |
| 7 | c | 63 |
| 8 | d | 73 |
+----+------+------+
8 rows in set (0.00 sec)四.在視窗2檢視錶資訊，無法看到更新的內容
在排它鎖(寫鎖)的情況下，一個事務不允許讀取另一個事務沒有提交的內容，避免了髒讀。但後來的事務可操作其他資料行，解決了表鎖的併發效能比較低的問題。
mysql> select * from innodb_lock;
+----+------+------+
| id | name | age |
+----+------+------+
| 1 | a | 13 |
| 2 | a | 23 |
| 3 | a | 33 |
| 4 | a | 43 |
| 5 | a | 43 |
| 6 | b | 53 |
| 7 | c | 63 |
| 8 | d | 73 |
+----+------+------+
8 rows in set (0.00 sec)五.在視窗2中繼續嘗試更新id=5的資料，結果發現被阻塞了
因為視窗1還沒提交對id=5的資料的更新，但在視窗2中可以修改id不是5的資料。
mysql> update innodb_lock set name = 'aaa' where id = 5;
-- 阻塞
mysql> update innodb_lock set name = 'a' where id = 6;
Query OK, 1 row affected (0.01 sec)
mysql> select * from innodb_lock;
+----+------+------+
| id | name | age |
+----+------+------+
| 1 | a | 13 |
| 2 | a | 23 |
| 3 | a | 33 |
| 4 | a | 43 |
| 5 | a | 43 |
| 6 | a | 53 |
| 7 | c | 63 |
| 8 | d | 73 |
+----+------+------+
8 rows in set (0.00 sec)六.視窗1commit後，重新開啟事務讀取innodb_lock表id=5的這行資料
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from innodb_lock where id = 5;
+----+------+------+
| id | name | age |
+----+------+------+
| 5 | aaa | 43 |
+----+------+------+
1 row in set (0.00 sec)七.視窗2開啟事務，對id=5的資料進行修改，然後提交事務
mysql> begin;
mysql> update innodb_lock set name = 'a' where id = 5;
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from innodb_lock;
+----+------+------+
| id | name | age |
+----+------+------+
| 1 | a | 13 |
| 2 | a | 23 |
| 3 | a | 33 |
| 4 | a | 43 |
| 5 | a | 43 |
| 6 | a | 53 |
| 7 | c | 63 |
| 8 | d | 73 |
+----+------+------+
8 rows in set (0.00 sec)八.視窗2提交事務後，視窗1再次查詢，還是之前的查詢結果
mysql> select * from innodb_lock where id = 5;
+----+------+------+
| id | name | age |
+----+------+------+
| 5 | aaa | 43 |
+----+------+------+
1 row in set (0.00 sec)結論：在有排它鎖的情況下，一個事務內多次讀取同一資料的結果始終保持一致，避免了不可重複讀。
(3)InnoDB行級鎖升級為表級鎖
InnoDB中的行級鎖是對索引加的鎖，而不是對資料記錄加的鎖。在不透過索引查詢並更新資料時，InnoDB就會使用表級鎖。
但是是否透過索引查詢並更新資料，還要看MySQL的執行計劃，MySQL的最佳化器會判斷是一條SQL執行的最佳策略。
若MySQL覺得執行索引查詢還不如全表掃描速度快，那麼MySQL就會使用全表掃描來查詢。即使SQL中使用了索引，最後還是執行為全表掃描，因為加的是表鎖。
下面是行級鎖升級為表級鎖的原因：
原因一：更新操作未使用到索引
原因二：更新操作時索引失效
原因三：更新的索引欄位重複率過高(一般重複率超過30%)
接下來對上面的幾種情況進行一下演示：
一.更新操作未使用索引導致行級鎖升級為表級鎖
mysql> show index from innodb_lock;
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| innodb_lock | 0 | PRIMARY | 1 | id | A | 8 | NULL | NULL | | BTREE | | |
| innodb_lock | 1 | idx_name | 1 | name | A | 4 | NULL | NULL | YES | BTREE | | |
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
-- 視窗1中，設定手動提交，更新資料成功，但是不提交
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> update innodb_lock set name = 'lisi' where age = 63; -- 更新操作未使用上索引
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
-- 視窗2中，開啟事務，然後對ID為6的資料進行修改，但是發生阻塞
-- 視窗1中修改的age=63的資料的id是7，與視窗2中修改的不是同一條資料
-- 如果行級鎖生效，那麼視窗2是不會被阻塞正常操作的，所以可知行級鎖升級為了表級鎖
mysql> update innodb_lock set name = 'wangwu' where id = 6;
-- 阻塞二.更新操作索引失效導致行級鎖升級為表級鎖
mysql> show index from innodb_lock;
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| innodb_lock | 0 | PRIMARY | 1 | id | A | 8 | NULL | NULL | | BTREE | | |
| innodb_lock | 1 | idx_name | 1 | name | A | 4 | NULL | NULL | YES | BTREE | | |
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
-- 視窗1中，設定手動提交，更新資料成功，但是不提交；或者直接在上述情況下執行rollback；
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> update innodb_lock set name = 'lisi' where name like '%c';
-- 視窗2中，開啟事務，然後對ID為6的資料進行修改，但是發生阻塞
mysql> update innodb_lock set name = 'wangwu' where id = 6;
-- 阻塞三.更新的索引欄位重複率過高，導致索引失效(一般重複率超過30%)
-- 視窗1，執行查詢並新增排它鎖
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from innodb_lock where name = 'a' for update;
+----+------+------+
| id | name | age |
+----+------+------+
| 1 | a | 13 |
| 2 | a | 23 |
| 3 | a | 33 |
| 4 | a | 43 |
| 5 | a | 43 |
| 6 | a | 53 |
+----+------+------+
6 rows in set (0.00 sec)
-- 視窗2
mysql> update innodb_lock set name = 'wangwu' where id = 7;
-- 發生阻塞，原因是name欄位雖然有索引，但是欄位值重複率太高，MySQL放棄了該索引
-- 視窗1，開啟事務，查詢name = b的資料，並加入排它鎖
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from innodb_lock where name = 'b' for update;
Empty set (0.00 sec)
-- 視窗2，開啟事務，查詢name = d的資料，並加入排它鎖，查詢到了結果
-- 沒有阻塞的原因是name欄位的索引生效了，還是行級鎖，鎖沒有升級
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from innodb_lock where name = 'd' for update;
+----+------+------+
| id | name | age |
+----+------+------+
| 8 | d | 73 |
+----+------+------+
1 row in set (0.00 sec)
-- 透過Explain進行分析，可以看到兩條SQL的索引使用情況
mysql> explain select * from innodb_lock where name = 'b';
+----+-------------+-------------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| 1 | SIMPLE | innodb_lock | NULL | ref | idx_name | idx_name | 63 | const | 1 | 100.00 | NULL |
+----+-------------+-------------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from innodb_lock where name = 'a';
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | innodb_lock | NULL | ALL | idx_name | NULL | NULL | NULL | 8 | 75.00 | Using where |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)(4)查詢SQL的鎖測試
一.查詢時的排它鎖測試
select語句加排它鎖方式：
mysql> select * from table where ... for update;for update的作用：for update是在資料庫中上鎖用的，可以為資料庫中的行新增一個排它鎖。存在高併發且對資料的準確性有要求的場景，可以選擇使用for update，for update = 當前讀。
for update的注意點：for update僅適用於InnoDB，for update必須開啟事務，在begin與commit之間執行才生效。
在視窗1中，首先開啟事務，然後對id為1的資料進行排他查詢。
-- 視窗1
mysql> begin;
mysql> select * from innodb_lock where id = 1 for update;
+----+------+------+
| id | name | age |
+----+------+------+
| 1 | a | 13 |
+----+------+------+
1 row in set (0.00 sec)在視窗2中，對同一資料分別使用"排它鎖"和"共享鎖"兩種方式查詢。
-- 視窗2
-- 嘗試更新
mysql> update innodb_lock set name = 'a' where id = 1;
-- 阻塞
-- 排他鎖查詢
mysql> select * from innodb_lock where id = 1 for update;
-- 阻塞
-- 共享鎖查詢
mysql> select * from innodb_lock where id = 1 lock in share mode;
-- 阻塞我們看到開了視窗2的排它鎖查詢和共享鎖查詢都會處於阻塞狀態。因為id=1的資料已經被加上了排他鎖，此處阻塞是等待排他鎖釋放。如果只是使用普通查詢，我們發現是可以的。
mysql> select * from innodb_lock where id = 1;二.查詢時的共享鎖測試
新增共享鎖：
mysql> select * from table_name where ... lock in share mode;事務獲取了共享鎖，在其他查詢中也只能加共享鎖，但不能加排它鎖。lock in share mode = 當前讀。
視窗1開啟事務，使用共享鎖查詢id=2的資料，但是不要提交事務。
-- 視窗1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from innodb_lock where id = 2 lock in share mode;
+----+------+------+
| id | name | age |
+----+------+------+
| 2 | a | 23 |
+----+------+------+
1 row in set (0.00 sec)視窗2開啟事務，使用普通查詢和共享鎖查詢id=2的資料是可以的。
-- 視窗2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from innodb_lock where id = 2 lock in share mode;
+----+------+------+
| id | name | age |
+----+------+------+
| 2 | a | 23 |
+----+------+------+
1 row in set (0.00 sec)加排它鎖就查不到，因為排它鎖與共享鎖不能存在同一資料上。
-- 視窗2
-- 排它查同一條資料阻塞
mysql> select * from innodb_lock where id = 2 for update;
-- 阻塞
-- 排它更新同一條資料阻塞
mysql> update innodb_lock set name = 'b' where id = 2;
-- 阻塞(5)行鎖的三種加鎖模式
在使用行鎖時，有三種不同的加鎖模式：記錄鎖、間隙鎖、臨鍵鎖。
一.記錄鎖(Record Lock)
記錄鎖就是為某行記錄加鎖(也就是行鎖)。
特點一：列必須為唯一索引列或主鍵列，否則加的鎖就會變成臨鍵鎖。
特點二：查詢語句為精準匹配= ，不能為>、<、like等，否則會退化成臨鍵鎖。
總結：使用記錄鎖的條件是使用主鍵索引或唯一索引，而且是等值查詢。
比如下面的兩條SQL就是新增了記錄鎖：
-- 在id=1的資料上新增記錄鎖，阻止其他事務對其進行更新操作
select * from test where id = 1 for update;
-- 透過主鍵索引和唯一索引對資料進行update操作時，也會對該行資料加記錄鎖
update test set age = 50 where id = 1; -- id是主鍵記錄鎖也是排它鎖(X鎖)，所以會阻塞其他事務對同一資料插入、更新、刪除。
二.間隙鎖(Gap Lock)
間隙鎖就是封鎖索引記錄中的間隔。這裡的間隔可能是兩個索引記錄之間，也可能是第一個索引記錄之前或最後一個索引記錄之後的空間。
select * from table where id between 1 and 10 for update;即所有在(1，10)區間內的記錄行都會被鎖住，所有id為2、3、4、5、6、7、8、9的資料行的插入會被阻塞，但是1和10兩條記錄行並不會被鎖住。
三.臨鍵鎖(Next-Key Lock)
Next-Key鎖就是記錄鎖 + 間隙鎖，既鎖定一個範圍，還鎖定記錄本身。InnoDB預設加鎖方式是Next-Key鎖，透過臨建鎖可以解決當前讀情況下的幻讀問題。
每個資料行上的非唯一索引列(普通索引列)上都會存在一把臨鍵鎖。當某個事務持有該資料行的臨鍵鎖時，會鎖住一段左開右閉區間的資料。
6.鎖實戰之行級鎖(偏寫)—間隙鎖
(1)間隙鎖的產生
(2)檢視間隙鎖設定
(3)主鍵索引的間隙鎖
(4)普通索引的間隙鎖
(1)間隙鎖的產生
間隙鎖是用來封鎖索引記錄中的間隔的。
產生間隙鎖的情況(RR事務隔離級別)
情況一：使用普通索引鎖定；
情況二：使用多列唯一索引；
情況三：使用主鍵索引鎖定多行記錄；
(2)檢視間隙鎖設定
innodb_locks_unsafe_for_binlog參數列示是否禁用間隙鎖。
mysql> show variables like 'innodb_locks_unsafe_for_binlog';
+--------------------------------+-------+
| Variable_name | Value |
+--------------------------------+-------+
| innodb_locks_unsafe_for_binlog | OFF |
+--------------------------------+-------+innodb_locks_unsafe_for_binlog預設值為OFF，即啟用間隙鎖。此引數是隻讀模式，如果想要禁用間隙鎖，需修改my.cnf並重啟才行。
# 在my.cnf裡面的[mysqld]新增
[mysqld]
innodb_locks_unsafe_for_binlog = 1(3)主鍵索引的間隙鎖
準備資料：
create table test_Gaplock (
id int primary key auto_increment,
name varchar(32) default null
);
insert into test_Gaplock values(1, '路飛'),(5, '喬巴'),(7, '娜美'),(11, '烏索普');在進行測試前，先來看test表中存在的隱藏間隙：
mysql> select * from test_Gaplock;
+----+-----------+
| id | name |
+----+-----------+
| 1 | 路飛 |
| 5 | 喬巴 |
| 7 | 娜美 |
| 11 | 烏索普 |
+----+-----------+
4 rows in set (0.00 sec)
---- (無窮小, 1]
---- (1, 5]
---- (5, 7]
---- (7, 11]
---- (11, 無窮大]測試1：只使用記錄鎖，不會產生間隙鎖。
使用記錄鎖的條件：主鍵索引或唯一索引，而且where條件是等值查詢。
-- 視窗1，開啟事務1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
-- 視窗1，查詢id = 5的資料並加記錄鎖
-- 當然也可以透過"update test_Gaplock set name = test where id = 5"來加記錄鎖
mysql> select * from test_Gaplock where id = 5 for update;
+----+--------+
| id | name |
+----+--------+
| 5 | 喬巴 |
+----+--------+
-- 視窗1，延時30秒執行，防止鎖釋放
mysql> select sleep(30);
+-----------+
| sleep(30) |
+-----------+
| 0 |
+-----------+
-- 視窗2，開啟事務2，插入一條資料，成功
mysql> insert into test_Gaplock(id, name) values(4, '索隆');
Query OK, 1 row affected (0.12 sec)
-- 視窗2，事務3，插入一條資料，成功
mysql> insert into test_Gaplock(id, name) values(8, '羅賓');
Query OK, 1 row affected (0.12 sec)
-- 視窗1，提交事務1，釋放事務1的鎖
commit;結論：由於是主鍵索引，並且使用等值查詢。所以事務1只會對id = 5的資料加上記錄鎖，而不會產生間隙鎖。
測試2：產生間隙鎖的情況
清空表資料，重新插入資料繼續透過id進行測試。
mysql> truncate test_Gaplock;
mysql> insert into test_Gaplock values(1, '路飛'),(5, '喬巴'),(7, '娜美'),(11, '烏索普');
mysql> select * from test_Gaplock;
+----+-----------+
| id | name |
+----+-----------+
| 1 | 路飛 |
| 5 | 喬巴 |
| 7 | 娜美 |
| 11 | 烏索普 |
+----+-----------+
-- 視窗1，開啟事務
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
-- 視窗1，查詢id在7~11範圍的資料並加間隙鎖
-- 當然也可以透過"update test_Gaplock set name = test where id between 5 and 7"來加間隙鎖
mysql> select * from test_Gaplock where id between 5 and 7 for update;
+----+--------+
| id | name |
+----+--------+
| 5 | 喬巴 |
| 7 | 娜美 |
+----+--------+
2 rows in set (0.00 sec)
-- 視窗1，延時30秒執行，防止鎖釋放
mysql> select sleep(30);
+-----------+
| sleep(30) |
+-----------+
| 0 |
+-----------+
1 row in set (30.00 sec)
-- 視窗2，開啟一個插入事務，正常執行
mysql> insert into test_Gaplock(id, name) values(3, '大和'); -- 正常執行
Query OK, 1 row affected (0.10 sec)
-- 視窗2，開啟一個插入事務，正常執行
mysql> insert into test_Gaplock(id, name) values(4, '凱多'); -- 正常執行
Query OK, 1 row affected (0.00 sec)
-- 視窗2，開啟一個插入事務，阻塞
mysql> insert into test_Gaplock(id, name) values(6, '大媽'); -- 阻塞
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
-- 視窗2，開啟一個插入事務，阻塞
mysql> insert into test_Gaplock(id, name) values(8, '山治'); -- 阻塞
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
-- 視窗2，開啟一個插入事務，阻塞
mysql> insert into test_Gaplock(id, name) values(9, '艾斯'); -- 阻塞
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
-- 視窗2，開啟一個插入事務，阻塞
mysql> insert into test_Gaplock(id, name) values(11, '白鬍子'); -- 阻塞
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
-- 視窗2，開啟一個插入事務，正常執行
mysql> insert into test_Gaplock(id, name) values(12, '黑鬍子');
Query OK, 1 row affected (0.00 sec)
-- 視窗1，事務1提交事務
commit;結論：事務1執行的是："select * from test_Gaplock where id between 5 and 7 for update"，其中(5, 7]和(7, 11]這兩個區間，是不允許插入資料的，其他區間可插入。也就是給(5, 7]這個區間加鎖時，會鎖住(5, 7]和(7, 11]這兩個區間。
where id between 5 and 7在範圍查詢時間隙鎖的新增方式是：過濾條件的最左側值作為左區間，也就是記錄值id = 5。向右尋找最靠近檢索條件的記錄值id = 11，那麼鎖定的區間就是(5, 11)。
測試3：清空表資料，重新插入原始資料，對不存在的資料進行加鎖
mysql> truncate test_Gaplock;
mysql> insert into test_Gaplock values(1, '路飛'),(5, '喬巴'),(7, '娜美'),(11, '烏索普');
mysql> select * from test_Gaplock;
+----+-----------+
| id | name |
+----+-----------+
| 1 | 路飛 |
| 5 | 喬巴 |
| 7 | 娜美 |
| 11 | 烏索普 |
+----+-----------+
-- 視窗1，開啟事務1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
-- 視窗1，查詢不存在id = 3的資料
mysql> select * from test_Gaplock where id = 3 for update;
Empty set (0.00 sec)
-- 視窗1，延時30秒執行，防止鎖釋放
mysql> select sleep(30);
-- 視窗2，開啟一個獨立的事務，執行插入操作
mysql> insert into test_Gaplock(id, name) values(2, '大和'); -- 阻塞
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
-- 視窗2，開啟一個獨立的事務，執行插入操作
mysql> insert into test_Gaplock(id, name) values(4, '凱多'); -- 阻塞
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
-- 視窗2，開啟一個獨立的事務，執行插入操作
mysql> insert into test_Gaplock(id, name) values(6, '大媽'); -- 正常執行
Query OK, 1 row affected (0.00 sec)
-- 視窗2，開啟一個獨立的事務，執行插入操作
mysql> insert into test_Gaplock(id, name) values(8, '山治'); -- 正常執行
Query OK, 1 row affected (0.00 sec)結論：對於指定查詢某一條記錄的加鎖語句。如果記錄不存在，會產生記錄鎖和間隙鎖，封鎖該記錄附近的範圍區間。如果記錄存在，則只會產生記錄鎖，只封鎖該記錄。上述例子中，"select * from test_Gaplock where id = 3 for update;"產生的間隙鎖範圍是(1, 5)，這個區間的起始位置是離id=3最近的記錄值。
(4)普通索引的間隙鎖
一.資料準備
-- 建立表
create table test_Gaplock2(
id int primary key auto_increment,
number int,
index idx_n(number)
);
-- 插入資料
insert into test_Gaplock2 values(1, 1), (5, 3), (7, 8), (11, 12);二.分析test_Gaplock2表中，number2索引存在的隱藏間隙
mysql> select * from test_Gaplock2;
+----+--------+
| id | number |
+----+--------+
| 1 | 1 |
| 5 | 3 |
| 7 | 8 |
| 11 | 12 |
+----+--------+
4 rows in set (0.00 sec)
--- (無窮小, 1]
--- (1, 3]
--- (3, 8]
--- (8, 12]
--- (12, 無窮大]三.案例測試
-- 視窗1，開啟事務
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
-- 視窗1，查詢number = 3的資料新增間隙鎖
mysql> select * from test_Gaplock2 where number = 3 for update;
+----+--------+
| id | number |
+----+--------+
| 5 | 3 |
+----+--------+
1 row in set (0.00 sec)
-- 視窗1，延時30秒執行，防止鎖釋放
mysql> select sleep(30);
+-----------+
| sleep(30) |
+-----------+
| 0 |
+-----------+
-- 視窗2，開啟一個獨立的事務，執行插入操作
mysql> insert into test_Gaplock2 (number) values(0); -- 執行成功
Query OK, 1 row affected (0.00 sec)
-- 視窗2，開啟一個獨立的事務，執行插入操作
mysql> insert into test_Gaplock2 (number) values(1); -- 阻塞
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
-- 視窗2，開啟一個獨立的事務，執行插入操作
mysql> insert into test_Gaplock2 (number) values(2); -- 阻塞
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
-- 視窗2，開啟一個獨立的事務，執行插入操作
mysql> insert into test_Gaplock2 (number) values(4); -- 阻塞
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
-- 視窗2，開啟一個獨立的事務，執行插入操作
mysql> insert into test_Gaplock2 (number) values(8); -- 執行成功
Query OK, 1 row affected (0.01 sec)
-- 視窗2，開啟一個獨立的事務，執行插入操作
mysql> insert into test_Gaplock2 (number) values(9); -- 執行成功
Query OK, 1 row affected (0.00 sec)
-- 視窗2，開啟一個獨立的事務，執行插入操作
mysql> insert into test_Gaplock2 (number) values(10); -- 執行成功
Query OK, 1 row affected (0.01 sec)
-- 視窗1，提交事務1
commit;結論：使用普通索引：number = 3 for update時，產生了間隙鎖。因為產生了間隙鎖，number的區間在(1, 8)的間隙中，插入語句都被阻塞了。而不在這個範圍內的語句，都是正常執行。
+----+--------+
| id | number |
+----+--------+
| 12 | 0 | --執行成功
| 1 | 1 |
| 5 | 3 |
| 7 | 8 |
| 16 | 8 | --執行成功
| 17 | 9 | --執行成功
| 18 | 10 | --執行成功
| 11 | 12 |
+----+--------+四.接著進行測試(首先將資料還原)
mysql> truncate test_Gaplock2;
mysql> insert into test_Gaplock2 values(1, 1), (5, 3), (7, 8), (11, 12);
mysql> select * from test_Gaplock2;
+----+--------+
| id | number |
+----+--------+
| 1 | 1 |
| 5 | 3 |
| 7 | 8 |
| 11 | 12 |
+----+--------+
-- 視窗1，開啟事務
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
-- 視窗1，查詢number = 3的資料新增間隙鎖
mysql> select * from test_Gaplock2 where number = 3 for update;
+----+--------+
| id | number |
+----+--------+
| 5 | 3 |
+----+--------+
1 row in set (0.00 sec)
-- 視窗1，延時30秒執行，防止鎖釋放
mysql> select sleep(30);
+-----------+
| sleep(30) |
+-----------+
| 0 |
+-----------+
-- 視窗2，開啟一個獨立的事務1，執行插入操作
mysql> insert into test_Gaplock2(id, number) values(2, 1); -- 阻塞
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
-- 視窗2，開啟一個獨立的事務2，執行插入操作
mysql> insert into test_Gaplock2(id, number) values(3, 2);-- 阻塞
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
-- 視窗2，開啟一個獨立的事務3，執行插入操作
mysql> insert into test_Gaplock2(id, number) values(6, 8);-- 阻塞
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
-- 視窗2，開啟一個獨立的事務4，執行插入操作
mysql> insert into test_Gaplock2(id, number) values(8, 8);-- 正常執行
Query OK, 1 row affected (0.00 sec)
-- 視窗2，開啟一個獨立的事務5，執行插入操作
mysql> insert into test_Gaplock2(id, number) values(9, 9);-- 正常執行
Query OK, 1 row affected (0.00 sec)
-- 視窗2，開啟一個獨立的事務6，執行插入操作
mysql> insert into test_Gaplock2(id, number) values(10, 12);-- 正常執行
Query OK, 1 row affected (0.00 sec)
-- 視窗2，開啟一個獨立的事務7，執行插入操作
mysql> update test_Gaplock2 set number = 5 where id = 11 and number = 12; -- 阻塞
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
-- 視窗1，提交事務1
commit;可以透過按順序列出原始資料和要插入的資料，判斷是否在間隙範圍來分析阻塞的原因：
+----+--------+
| id | number |
+----+--------+
| 1 | 1 | --原始資料
| 2 | 1 | --間隙資料，阻塞
| 3 | 2 | --間隙資料，阻塞
| 4 | 2 | --間隙資料，阻塞
| 5 | 3 | --原始資料，加鎖
| 6 | 8 | --間隙資料，阻塞
| 7 | 8 | --原始資料
| 8 | 8 | --間隙資料，正常
| 9 | 9 | --間隙資料，正常
| 10 | 12 | --間隙資料，正常
| 11 | 12 | --原始資料
| ...| ...| --間隙資料，正常時間點1：原始資料Id值為：1、5、7、11，原始資料number值為：1、3、8、12。
時間點2：事務3新增資料(id = 6，number = 8)是在(3, 8)的區間裡，所以被阻塞。
時間點3：當加鎖欄位number相同時，會根據主鍵id進行排序。事務4新增資料(id = 8，number = 8)，這條資料的id在(8, 12)的區間裡，不在滿足間隙的原始資料範圍內了，所以不會被阻塞。
時間點4：事務7要設定number = 5，修改後的值在(3, 8)的區間裡，所以被阻塞。
結論：在普通索引列上，不管是何種查詢，只要加鎖，都會產生間隙鎖。
7.鎖實戰之行級鎖(偏寫)—臨鍵鎖
(1)加鎖原則
(2)Next-Key Lock案例演示
Next-Key鎖 = 記錄鎖 + 間隙鎖，可以理解為一種特殊的間隙鎖。透過臨建鎖可以解決幻讀的問題。
每個資料行上的非主鍵索引列(普通索引列)上會存在一把臨鍵鎖，當某個事務持有該資料行的臨鍵鎖時，會鎖住一段左開右閉區間的資料。
當InnoDB搜尋或掃描索引時，在它遇到的索引記錄上所設定的鎖就是Next-Key Lock，它會鎖定索引記錄本身以及該索引記錄前面的Gap。
(1)加鎖原則
原則一：加鎖的基本單位是Next-Key Lock，Next-Key Lock是左開右閉區間。
原則二：查詢過程中訪問到的物件才會加鎖。
原則三：主鍵索引或唯一索引上的等值查詢，Next-Key Lock退化為記錄鎖(行鎖)。
原則四：非主鍵索引或非唯一索引上的等值查詢，向右遍歷且最後一個值不滿足等值條件時，Next-Key Lock退化為間隙鎖。
原則五：唯一索引上的範圍查詢會訪問到不滿足條件的第一個值為止。
一.lock in share mode與for update的區別
lock in share mode加讀鎖(共享鎖)，lock in share mode只鎖覆蓋索引。lock in share mode的加鎖內容是：非主鍵索引樹中符合條件的索引項。
for update加的是寫鎖(排它鎖)，for update的加鎖內容是：非主鍵索引樹中符合條件的索引項，以及其對應主鍵索引樹中的索引項，所以for update在兩個索引樹上都加了鎖。
二.lock in share mode與for update的共同點
兩者都屬於當前讀範圍。
(2)Next-Key Lock案例演示
一.資料準備
create table test_NK(
id int primary key,
num1 int,
num2 int,
key idx_num1(num1)
);
insert into test_NK values(5, 5, 5),(10, 10, 10),(20, 20, 20),(25, 25, 25);二.在更新時，不僅對下面的5條資料加行鎖，還會對中間的取值範圍增加6個臨鍵鎖，該索引可能被鎖住的範圍如下：
-- 加行鎖的5條資料
| id (主鍵) | num1(普通索引) | num2(無索引) |
| --------- | -------------- | ------------ |
| 5 | 5 | 5 |
| 10 | 10 | 10 |
| 15 | 15 | 15 |
| 20 | 20 | 20 |
| 25 | 25 | 25 |
-- 中間的取值範圍
(-∞, 5], (5, 10], (10, 15], (15, 20], (20, 25], (25, +∞)測試1：等值查詢普通索引
-- 視窗1，開啟事務1，查詢num1 = 5的記錄，並新增共享鎖
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
-- 視窗1，查詢num1 = 5的記錄，並新增共享鎖
mysql> select id from test_NK where num1 = 5 lock in share mode;
+----+
| id |
+----+
| 5 |
+----+
1 row in set (0.01 sec)
-- 視窗2，開啟事務2，執行插入操作
mysql> update test_NK set num2 = num2 + 1 where id = 5; -- 執行成功
Query OK, 1 row affected (0.01 sec)
-- 視窗2，開啟事務，執行插入操作
mysql> insert into test_NK values(7, 7, 7); --阻塞
mysql> insert into test_NK values(4, 4, 4); --阻塞
mysql> insert into test_NK values(10, 10, 10); -- 主鍵已存在
ERROR 1062 (23000): Duplicate entry '10' for key 'PRIMARY'
mysql> insert into test_NK values(12, 12, 12); --執行成功分析上面部分SQL語句發生阻塞的原因：
原因一：初步加鎖，加鎖的範圍是(0, 5]，(5, 10]的臨鍵鎖。
原因二：根據原則4，由於查詢是等值查詢且最後一個值不滿足查詢要求，鎖Next-Key退化為間隙鎖，最終鎖定的範圍是(0, 10)。
原因三：因為加鎖範圍是(0, 10)，所以insert id = 4和 id=7都阻塞了。
原因四：因為num1是普通索引列，lock in share mode只對非主鍵索引加鎖，沒有對主鍵索引加鎖，所以對主鍵id = 5的update操作正常執行。
測試2：範圍查詢主鍵索引
mysql> truncate test_NK;
mysql> insert into test_NK values(5, 5, 5),(10, 10, 10),(20, 20, 20),(25, 25, 25);
mysql> select * from test_NK;
+----+------+------+
| id | num1 | num2 |
+----+------+------+
| 5 | 5 | 5 |
| 10 | 10 | 10 |
| 20 | 20 | 20 |
| 25 | 25 | 25 |
+----+------+------+
-- 視窗1，開啟事務1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
-- 視窗1，執行一個條件是id的範圍查詢
mysql> select * from test_NK where id > 10 and id < 15 for update;
Empty set (0.00 sec)
-- 視窗2, 每一條SQL都是一個獨立事務
-- 間隙範圍: (-∞, 5]、(5, 10]、(10, 15]、(15, 20]、(20, 25]、(25, +∞)
mysql> insert into test_NK values(9, 9, 9); -- 成功
mysql> insert into test_NK values(12, 12, 12); -- 阻塞
mysql> insert into test_NK values(14, 14, 14); -- 阻塞
mysql> insert into test_NK values(16, 16, 16); -- 阻塞
mysql> insert into test_NK values(17, 17, 17); -- 阻塞
mysql> insert into test_NK values(18, 18, 18); -- 阻塞
mysql> insert into test_NK values(19, 19, 19); -- 阻塞
mysql> insert into test_NK values(20, 20, 20); -- 阻塞
mysql> insert into test_NK values(21, 21, 21); -- 成功
Query OK, 1 row affected (0.00 sec)分析上面部分SQL語句發生阻塞的原因：根據原則5，唯一索引上的範圍查詢會訪問到不滿足條件的第一個值為止。所以鎖定範圍是(10, 15]、(15, 20]，也就是(10, 20)。
測試3：範圍查詢普通索引
-- 視窗1，開啟事務1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
-- 視窗1，透過索引欄位num1進行範圍查詢，並加排它鎖
mysql> select * from test_NK where num1 >= 10 and num1 < 15 for update;
+----+------+------+
| id | num1 | num2 |
+----+------+------+
| 10 | 10 | 10 |
+----+------+------+
-- 視窗2，每一條SQL都是一個獨立事務
-- 間隙範圍: (-∞, 5]、(5, 10]、(10, 15]、(15, 20]、(20, 25]、(25, +∞)
mysql> insert into test_NK values(9, 9, 9); -- 阻塞
mysql> insert into test_NK values(13, 13, 13); -- 阻塞
mysql> insert into test_NK values(16, 16, 16); -- 阻塞
mysql> insert into test_NK values(19, 19, 19); -- 阻塞
mysql> insert into test_NK values(21, 21, 21); -- 正常
Query OK, 1 row affected (0.00 sec)分析上面部分SQL語句發生阻塞的原因：InnoDB儲存引擎會對普通索引的下一個鍵值加上Gap Lock。所以原本臨鍵鎖鎖定是(5, 10]、(10, 15]，而15下一個鍵值是(15, 20]，所以插入5 ~ 20之間的值的時候都會被鎖定，要求等待。
8.鎖實戰之行級鎖(偏寫)—幻讀演示和解決
(1)什麼是幻讀
(2)什麼時候產生幻讀
(3)幻讀問題演示與解決
(4)MySQL如何解決幻讀
(1)什麼是幻讀
幻讀指的是同一事務下不同的時間點，同樣的範圍查詢得到不同的結果。比如一個事務中同樣的查詢執行了兩次，第二次比第一次多了一行記錄。
(2)什麼時候產生幻讀
一.快照讀—查詢的都是快照版本
因為基於MVCC來查詢快照的某個版本，所以不會存在幻讀的問題。MVCC下的查詢其實就是快照讀。
對於RC級別來說，因為每次查詢都新生成一個Read View，即查詢的都是最新的快照。所以每次查詢可能會得到不一樣的資料，造成不可重複讀 + 幻讀。
對於RR級別來說，因為只有第一次查詢時生成Read View，即查詢的是事務開始時的快照。所以不存在不可重複讀問題，當然就更不可能有幻讀的問題。
二.當前讀—可能會出現幻讀
在RR級別下，如果是快照讀，當前事務不會看到別的事務插入的資料，因此幻讀問題只會在當前讀的情況下才會出現。
當前讀指的是SQL讀取的資料是最新的版本(修改並且已經提交)。當前讀包括：lock in share mode、for update、insert、update、delete。
(3)幻讀問題演示與解決
下面是一張user表：
| id | username | age |
| ---- | -------- | ---- |
| 1 | Jack | 20 |
| 2 | Tom | 18 |一.接下來對user表進行一些事務性操作
如下圖示：
說明一：事務1中執行三次查詢，都是要查出name = "Jack"的記錄行，這裡假設name = "Jack"的這條資料行上加了行鎖。
說明二：事務1第一次查詢只返回id = 1這一行。
說明三：事務1第二次查詢前，事務2把id = 2這一行的name改成了Jack。因此事務1第二次查詢的結果是id = 1和id = 2這兩行。
說明四：事務1第三次查詢前，事務3插入一行name = "Jack"的新資料。因此事務1第三次查詢的結果是id = 1 、id = 2、id = 3這三行。
說明五：事務1第三次查詢時，查詢出了不存在的行，這就發生了幻讀。
二.繼續分析下面這個場景
如下圖示：
說明一：在事務2中新增一條SQL語句，把id = 2的這行資料的age欄位改為40。即事務2對id = 2的行記錄會先修改name = "Jack"，再修改age = 40。
說明二：在這之前，事務1只給id = 1的這一行加鎖，沒有對id = 2這一行加鎖。所以事務2可以執行這條update語句。
說明三：但是事務2的修改操作先修改name = "Jack"，再修改age = 40，其實是破壞了事務1要把所有的name = "Jack"的行鎖住的宣告語義。
三.接下來再給事務1加上一條SQL語句
如下圖示：
在上圖的四個時刻中：
說明一：經過T1時刻，id = 1這一行變成了(1, "Tom", 20)。
說明二：經過T2時刻，id = 2這一行變成了(2, "Jack", 18)。
說明三：經過T3時刻，多了一行(3, "Jack", 30)。
再看一下對應的binlog日誌中記錄的操作內容：
T2時刻，事務2提交，寫入了2條update語句到binlog：
update user set name = "Jack" where id = 2; -- (2,"Jack",18)T3時刻，事務3提交，寫入了1條insert語句到binlog：
insert into user values(3, "Jack", 30) -- (3,"Jack",30)T4時刻，事務1提交，binlog中寫入了：
update user set name = "Tom" where name = "Jack";加鎖的目的是為了保證資料的一致性。一致性不單單是指資料的一致性，還包括了日誌的一致性。T4時刻的操作，把所有的name = "Jack"的行都改成了name = "Tom"。基於binlog進行主從複製時，id = 2和id = 3這兩行的name都變成了Tom。也就是說id = 2和id = 3這兩行，出現了主從資料不一致。主庫的這兩行資料是Jack，從庫的這兩行資料卻變成了Tom。出現不一致的原因是：T1時刻只假設事務1只在id = 1的這一行加行鎖；
四.接下來再假設事務1加鎖的範圍是所有行
這樣事務2的更新就會阻塞，直到T4時刻事務1提交commit釋放鎖。
事務1將所有的行都加了寫鎖，所以事務2在執行第一個update語句時就被鎖住了，需要等到T4時刻事務1提交後，事務2才能繼續執行。再次檢視binlog的寫入情況，此時的執行順序是這樣的：
-- 事務3
insert into user values(3,"Jack",30); -- (3,"Jack",30)
-- 事務1
update user set name = "Tom" where name = "Jack";
-- 事務2
update user set name = "Jack" where id = 2; -- (2,"Jack",18)這樣看事務2的問題解決了，但是需要注意的是事務3在資料庫裡面的結果是(3, "Jack", 30)。而根據binlog的執行結果則是(3, "Tom", 30)，對所有行加鎖還是阻止不了id = 3這一行的插入和更新，還是出現了幻讀。原因是給所有行加鎖時，id = 3這一行還不存在，掃描不到而加不上鎖。
(4)MySQL如何解決幻讀
產生幻讀的原因：行鎖只能鎖住行，但新插入的記錄操作的是行間隙。因此為了解決幻讀問題，InnoDB引入了間隙鎖。
這樣在執行select * from user where name = "Jack" for update時，不僅僅給資料庫表中已有的n個記錄加上了行鎖，同時還增加了N+1個間隙鎖，保護這些間隙，不允許插入值。
行鎖 + 間隙鎖解決了幻讀問題，這種鎖叫做臨鍵鎖Next-Key Lock。
9.鎖實戰之行級鎖(偏寫)—最佳化建議
InnoDB儲存引擎實現的行級鎖定，雖然在鎖定機制實現方面帶來的效能損耗可能比表級鎖定要更高一些，但是在整體併發處理能力方面要遠遠優於MyISAM的表級鎖定的。當系統併發量較高時，InnoDB的整體效能就會比MyISAM更有明顯優勢。
但是，InnoDB的行級鎖定同樣也有其脆弱的一面。如果使用不當，那InnoDB的整體效能可能會比MyISAM更差。要想合理利用InnoDB的行級鎖定，做到揚長避短，有如下建議：
建議一：儘可能讓所有的資料檢索都透過索引來完成，從而避免InnoDB因為無法透過索引鍵加鎖而升級為表級鎖定。
建議二：合理設計索引，讓InnoDB在索引鍵上加鎖時儘可能準確，儘可能的縮小鎖定範圍，避免造成不必要的鎖定而影響其他SQL查詢的執行。
建議三：儘可能減少基於範圍的資料檢索過濾條件，避免因為間隙鎖帶來的負面影響而鎖定了不該鎖定的記錄。
建議四：儘量控制事務的大小，減少鎖定的資源量和鎖定時間長度。
建議五：在業務環境允許的情況下，儘量使用較低階別的事務隔離，以減少MySQL因為實現事務隔離級別所帶來的附加成本。
可以透過檢查InnoDB_row_lock狀態變數來分析系統上的行鎖的爭奪情況：
mysql> show status like 'InnoDB_row_lock%';
+-------------------------------+--------+
| Variable_name | Value |
+-------------------------------+--------+
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 522443 |
| Innodb_row_lock_time_avg | 18658 |
| Innodb_row_lock_time_max | 51068 |
| Innodb_row_lock_waits | 28 |
+-------------------------------+--------+
5 rows in set (0.00 sec)InnoDB的行級鎖定狀態變數不僅記錄了鎖定等待次數，還記錄了鎖定總時長、每次平均時長、以及最大時長，此外還有一個非累積狀態量顯示了當前正在等待鎖定的等待數量。
對各個狀態量的說明如下：
一.InnoDB_row_lock_current_waits：當前正在等待鎖定的數量。
二.InnoDB_row_lock_time：從系統啟動到現在鎖定總時間長度。
三.InnoDB_row_lock_time_avg：每次等待所花平均時間。
四.InnoDB_row_lock_time_max：系統啟動後等待最長的一次所花的時間。
五.InnoDB_row_lock_waits：系統啟動後到現在總共等待的次數。
對於這5個狀態變數，比較重要的主要是：
一.InnoDB_row_lock_time_avg(等待平均時長)
二.InnoDB_row_lock_waits(等待總次數)
三.InnoDB_row_lock_time(等待總時長)
尤其是當等待次數很高，而且每次等待時長也不小的時候，就要分析為何有如此多的等待，然後根據分析結果著手指定最佳化計劃。
10.鎖實戰之樂觀鎖
(1)什麼是樂觀鎖
(2)樂觀鎖實現方式
(3)CAS導致的ABA問題
(4)解決ABA問題的方法
行鎖、表鎖、讀鎖、寫鎖、共享鎖、排他鎖其實都屬於悲觀鎖。
(1)什麼是樂觀鎖
樂觀鎖是相對於悲觀鎖而言的，樂觀鎖不是資料庫提供的功能，需要開發者自己去實現。
在運算元據庫時，想法很樂觀，認為這次的操作不會導致衝突。因此在資料庫操作時並不做任何的特殊處理，而是在進行事務提交時再去判斷是否有衝突；
(2)樂觀鎖實現方式
樂觀鎖的概念中其實已經闡述了它的具體實現細節，主要就是兩個步驟：衝突檢測和資料更新。
比較典型的一種實現方式就是CAS(Compare and Swap)。CAS是一種常見的降低讀寫鎖衝突，保證資料一致性的樂觀鎖機制。當多個執行緒使用CAS同時更新某變數時，只有其中一個執行緒能更新成功。而其它執行緒都失敗，失敗的執行緒並不會被掛起，而是可以再次嘗試。
比如一個扣減庫存操作，可透過樂觀鎖實現：
-- 查詢商品庫存資訊，查詢結果為: quantity = 3
mysql> select quantity from items where id = 1;
-- 修改商品庫存為2
mysql> update items set quantity = 2 where id = 1 and quantity = 3;上述SQL在更新前，會先查詢一下庫存表中當前庫存數(quantity)，然後在update時，會把庫存數作為一個修改條件。
提交更新時，首先獲取當前庫存數，然後對比第一次取出來的庫存數。如果當前庫存數與第一次取出來的庫存數相等，那麼才允許更新。
(3)CAS導致的ABA問題
CAS樂觀鎖機制確實能夠提升吞吐，並保證一致性，但在極端情況下可能會出現ABA問題。
時間點一：併發執行緒1獲取出資料的初始值是A，後續計劃實施CAS樂觀鎖，期望資料仍是A時，修改才能成功。
時間點二：併發執行緒2將資料修改成B。
時間點三：併發執行緒3將資料修改回A。
時間點四：併發執行緒1使用CAS檢測發現資料值還是A，於是進行修改。
但是併發執行緒1在修改資料時，雖然還是A，但已經不是初始條件的A了。中間發生了A變B，B又變A的變化。此A已經非彼A，資料卻成功修改，可能導致錯誤。這就是CAS引發的所謂的ABA問題。
(4)解決ABA問題的方法
設計一個單獨的可以順序遞增的version欄位，每操作一次就將相應記錄的version版本號加1。version是用來檢視被讀的記錄有無變化，version的作用是防止記錄在業務處理期間被其他事務修改。
以下單操作為例：
-- 1.查詢出商品資訊 version = 1
mysql> select (quantity, version) from items where id = 1;
-- 2.根據商品生成訂單
mysql> insert into orders ...
mysql> insert into items ...
-- 3.修改商品庫存，同時遞增版本欄位值
mysql> update products set quantity=quantity-1, version=version+1 where id=1 and version=#{version};除了手動實現樂觀鎖外，許多資料庫訪問框架也封裝了樂觀鎖的實現，比如MyBatis框架可以使用OptimisticLocker外掛來擴充套件樂觀鎖。
11.行鎖原理
(1)SQL語句背後的鎖實現原理
(2)InnoDB引擎加鎖原理分析
(3)複雜SQL的加鎖分析
(4)在RR隔離級別下如何判斷一個複雜SQL應該加什麼樣的鎖
(1)SQL語句背後的鎖實現原理
一.InnoDB儲存引擎3種行鎖演算法
二.MVCC中的快照讀和當前讀
三.插入、更新、刪除操作都歸為當前讀
四.MySQL兩階段封鎖協議
一.InnoDB儲存引擎3種行鎖演算法
InnoDB引擎的行鎖是透過對索引頁和資料頁上的記錄加鎖實現的，主要實現演算法有3種：Record Lock、Gap Lock和Next-key Lock。
演算法一：Record Lock鎖(記錄鎖，RC、RR隔離級別都支援)
記錄鎖，鎖定單個行記錄的鎖。
演算法二：Gap Lock鎖(間隙鎖，RR隔離級別支援)
間隙鎖，鎖定索引記錄間隙(不包括記錄本身)，確保索引記錄間隙不變。
演算法三：Next-Key Lock鎖(記錄鎖 + 間隙鎖，RR隔離級別支援)
記錄鎖和間隙鎖組合，同時鎖住資料，並且鎖住資料前後範圍。
二.MVCC中的快照讀和當前讀
MySQL支援MVCC多版本併發控制機制，MVCC實現了資料庫的讀不加鎖，讀寫不衝突，提高了系統的併發效能。
普通的select操作屬於快照讀，不加鎖；
mysql> select * from table where ...;特殊的讀操作、插入、更新、刪除操作，都屬於當前讀，需要加鎖；
mysql> select * from table where ... lock in share mode;
mysql> select * from table where ... for update;
mysql> insert into table values (...);
mysql> update table set ... where ...;
mysql> delete from table where ...;當前讀會讀取記錄的最新版本，並且讀取後還要對讀取記錄加鎖，保證其他併發事務不能修改當前記錄。其中除了lock in share mode對讀取記錄加S鎖 (共享鎖)外，其他操作如for update、insert、update、delete都是加X鎖 (排它鎖)。
三.插入、更新、刪除操作都歸為當前讀
下面是一個Update操作的具體流程：
時間點1：當更新語句傳送給MySQL後，MySQL Server會根據where條件，讀取第一個滿足條件的記錄。InnoDB引擎會將查詢到的第一條記錄返回，並加上鎖(當前讀)。
時間點2：MySQL Server收到這條加鎖的記錄後，再發起一個更新請求去更新記錄。
時間點3：一條記錄操作完成再讀取下一條記錄，直到沒有滿足條件的記錄為止。
總結：透過上面的分析，可以知道Update操作內部就包含了一個當前讀，同理Delete操作內部也一樣包含一個當前讀。Insert操作觸發唯一鍵檢查時，也會有一個當前讀，判斷主鍵是否已存在。
四.MySQL兩階段封鎖協議
兩階段封鎖協議主要用於保證單機事務中的一致性與隔離性，該協議要求每個事務分兩個階段提出加鎖和解鎖申請。注意：保證分散式事務中的一致性與隔離性可用兩階段提交協議。
階段一：增長階段
事務可以獲得鎖，但不能釋放鎖。即對任何資料進行讀、寫操作之前，首先要申請並獲得對該資料的封鎖。
階段二：縮減階段
事務可以釋放鎖，但不能獲得鎖。即釋放一個封鎖之後，事務不再申請和獲得其它任何封鎖。
從上圖可以看出：兩階段封鎖協議2PL就是將加鎖/解鎖分為兩個完全不相交的階段。
加鎖階段：只加鎖，不解鎖。
解鎖階段：只解鎖，不加鎖。
兩階段封鎖協議實現了事務集的序列化排程。但同時，一個事務的失敗可能會引起一連串事務的回滾(可能級聯回滾)，並且兩階段封鎖協議並不保證不會發生死鎖(可能發生死鎖)，資料庫系統必須採取其他的措施，預防和解決死鎖問題。
(2)InnoDB引擎加鎖原理分析
SQL1：下面的查詢語句加鎖了嗎？加的是什麼鎖？
mysql> select * from v1 where t1.id = 1;分析查詢語句的加鎖需要考慮當前的隔離級別。該語句在序列化下MVCC會降級成Lock-Based CC，會加讀鎖。在其他三種隔離級別下，由於MVCC快照讀，所以會不加鎖。
SQL2：下面的這條SQL又是怎麼加鎖的？
mysql> delete from v1 where id = 10;分析增刪改語句的加鎖情況時，需要注意如下問題：
問題1：id列是不是主鍵？
問題2：當前系統的隔離級別是什麼？
問題3：id列如果不是主鍵，那麼id列上有索引嗎？
問題4：id列上如果有二級索引，那麼這個索引是唯一索引嗎？
問題5：兩個SQL的執行計劃是什麼？索引掃描？全表掃描？
一.RC級別加鎖機制
組合一：id主鍵 + RC級別
組合二：id不是主鍵但是唯一索引 + RC級別
組合三：id非唯一索引 + RC級別
組合四：id無索引 + RC級別
組合一：id主鍵 + RC級別
當id是主鍵時，只需要在該條SQL操作的資料記錄上加寫鎖即可。
組合二：id不是主鍵但是唯一索引 + RC級別
因為id是唯一索引，where會透過id列的索引進行過濾。在找到id = 10的記錄後，會對非聚簇索引樹上id = 10的記錄加X鎖。同時回表查詢主鍵索引name = d的記錄，並對name = d的記錄也加X鎖。
注意下圖中：id是唯一索引(非聚簇索引)，name是主鍵索引(聚簇索引)。
唯一索引與主鍵索引都需要加鎖。
因為在執行這條刪除語句時：如果併發出現了一條更新語句，而更新語句的where條件是name欄位。此時如果根據id對資料進行的刪除操作，沒有對相應主鍵索引記錄加鎖，那麼更新語句在更新同樣記錄時就不知有刪除操作的存在從而進行更新。刪除非聚簇索引上的某一行記錄時，也會刪除對應的聚簇索引上的記錄。這樣就違反了同一資料行上的寫操作需要序列化執行的原則。
組合三：id非唯一索引 + RC級別
對id索引(非聚簇索引)中符合條件的多條記錄都加上X鎖，再把對應的name索引(主鍵索引)上的多條記錄也加上X鎖。
與組合二的區別在於：
在id是非唯一索引列的情況下，會對滿足條件的多條資料都加上X鎖。而組合二因為id是唯一索引列，所以只會對一條資料加上X鎖。
組合四：id無索引 + RC級別
由於沒有索引，所以只需刪除聚簇索引上的記錄即可。由於沒有索引，所以執行計劃使用的是全表掃描。既然是全表掃描，所以就會對主鍵索引上每一條記錄施加X鎖。
為什麼會對所有記錄加X鎖，而不是表鎖或對符合條件的資料加X鎖呢？這是由於InnoDB的實現決定的。由於沒有索引，無法在儲存引擎層過濾(執行計劃裡的Using Where)，所以儲存引擎只能對每一條資料加鎖，並返回給Server層進行過濾。
實際上InnoDB在實現中會有一些改進：Server層在過濾時對不滿足條件的記錄會立即執行unlock_row方法，對不滿足條件的記錄解鎖(違背2PL)，保證只有滿足條件的記錄持有X鎖，但是對每條資料加鎖的步驟是沒法省略的。
二.RR級別加鎖機制
組合五：id主鍵 + RR，與組合一相同
組合六：id唯一索引 + RR，與組合二相同
組合七：id非唯一索引 + RR
組合八：id無索引 + RR級別
組合九：Serializable
組合五：id主鍵 + RR，與組合一相同
當id是主鍵時，只需要在該條SQL操作的資料記錄上加寫鎖即可。
組合六：id唯一索引 + RR，與組合二相同
因為id是唯一索引，where會透過id列的索引進行過濾。在找到id = 10的記錄後，會對非聚簇索引樹上id = 10的記錄加X鎖。同時回表查詢主鍵索引name = d的記錄，並對name = d的記錄也加X鎖。
注意下圖中：id是唯一索引(非聚簇索引)，name是主鍵索引(聚簇索引)。
唯一索引與主鍵索引都需要加鎖。
因為在執行這條刪除語句時：如果併發出現了一條更新語句，而更新語句的where條件是name欄位。此時如果根據id對資料進行的刪除操作，沒有對相應主鍵索引記錄加鎖，那麼更新語句在更新同樣記錄時就不知有刪除操作的存在從而進行更新。刪除非聚簇索引上的某一行記錄時，也會刪除對應的聚簇索引上的記錄。這樣就違反了同一資料行上的寫操作需要序列化執行的原則。
組合七：id非唯一索引 + RR
RC隔離級別下允許幻讀的出現，RR隔離級別下不允許幻讀的出現。InnoDB在預設RR隔離級別下，透過增加間隙鎖來解決當前讀的幻讀問題。
上圖中name是主鍵索引，id是普通非唯一索引。
步驟一：首先對id索引(非聚簇索引)中符合條件的記錄施加X鎖。
步驟二：對非聚簇索引中施加了X鎖的記錄前後間隙施加間隙鎖。
步驟三：對id索引(非聚簇索引)對應的主鍵索引的記錄施加X鎖。
施加了間隙鎖後，資料的前後都不會插入新的資料，可以保證兩次當前讀的結果完全一致。
注意：對非聚簇索引中符合條件的記錄新增間隙鎖和X鎖，聚簇索引中相對應的記錄不需要加間隙鎖，只加X鎖。
組合八：id無索引 + RR級別
RR級別下如果進行全表掃描的當前讀，那麼會鎖上聚簇索引的所有記錄。同時鎖上聚簇索引內的所有間隙，杜絕所有併發更新、刪除、插入操作。當然也可以觸發Semi-Consistent Read，來緩解加鎖開銷與併發影響。但是Semi-Consistent Read本身也會帶來其他問題，不建議使用。
組合九：Serializable
mysql> select * from t1 where id = 10;這條SQL在RC、RR隔離級別下，都是快照讀，不加鎖。但是在Serializable隔離級別，SQL1會加讀鎖。也就是說快照讀不復存在，MVCC併發控制降級為Lock-Based CC。
InnoDB中讀不加鎖，並不適用於所有情況，而是與隔離級別有關的。Serializable隔離級別，讀不加鎖就不成立，所有讀操作，都是當前讀。
(3)複雜SQL的加鎖分析
資料庫中有一張T1表，欄位資訊如下：
Table: t1(id primary key, userid, blogid, pubtime, comment)
-- id為主鍵, userid與comment欄位建立了聯合索引
Index: idx_pu(pubtime, user_id)在RR級別下，分析如下SQL會加什麼鎖，假設SQL走的是idx_pu索引。
mysql> delete from t1
where pubtime > 1 and pubtime < 20 and userid = 'hdc' and comment is not null;分析SQL中的條件構成：
一.Index key是：pubtime > 1 and puptime < 20，此條件用於確定在idx_pu索引上的查詢範圍。
二.Index Filter是：userid = "hdc"，此條件可以在idx_pu索引上進行過濾，但不屬於Index Key。
三.Table Filter是：comment is not NULL，此條件無法在idx_pu索引上進行過濾，只能在聚簇索引上進行過濾。
SQL中的where條件提取：
所有SQL的where條件可歸納為3大類：
一.Index Key
用於確定SQL查詢在索引中的連續範圍(起始 + 結束)的查詢條件。由於一個範圍，至少包含一個起始與一個終止，因此Index Key也被拆分為Index First Key和Index Last Key，分別用於定位索引查詢的起始，以及索引查詢的終止條件。
二.Index Filter
Index Filter用於過濾索引查詢範圍中不滿足查詢條件的記錄。因此對於索引範圍中的每一條記錄，均需要與Index Filter進行對比。若不滿足Index Filter則直接丟棄，繼續讀取索引下一條記錄。
三.Table Filter
所有不屬於索引列的查詢條件都在Table Filter中。
假設現已滿足Index key和Index Filter的條件，並回表讀取了完整記錄。接下來就是要判斷完整記錄是否滿足Table Filter中的查詢條件：如果不滿足，則跳過當前記錄，繼續讀取索引的下一條記錄；如果滿足，則返回記錄給前端使用者。
在RR隔離級別下，根據上圖和下面的分析，就是這條SQL的加鎖情況：
第一：由Index Key所確定的範圍被加上了間隙鎖
第二：Index Filter鎖給定的條件(userid = "hdc")，在index上過濾
Index Filter鎖給定的條件(userid = "hdc")何時過濾，視MySQL版本而定。在5.6版本前，不支援ICP，因此Index Filter在Server層過濾。在5.6版本後，支援Index Condition Pushdown(ICP)，則在index上過濾。若不支援ICP，不滿足Index Filter的記錄，也需要對記錄加上X鎖。若支援ICP，則不滿足Index Filter的記錄，無需對記錄加上X鎖。圖中，用紅色箭頭標出的X鎖，是否要加，視是否支援ICP而定。
ICP是MySQL使用索引從表中檢索行資料的一種最佳化方式。
禁用ICP時，儲存引擎會透過遍歷索引定位基表中的行。然後返回給Server層，再去為這些資料行進行where條件的過濾。
啟用ICP時，如果where條件可使用索引，會把過濾操作放到儲存引擎層。儲存引擎透過索引過濾，把滿足的行從表中讀取出。
ICP可減少儲存引擎必須訪問基表的次數，ICP也可減少Server層必須訪問儲存引擎的次數。
第三：Table Filter的過濾條件，會從聚簇索引中讀取到Server層過濾
因此聚簇索引上也需要X鎖。
第四：最後選取出一條滿足條件的記錄
這條記錄就是[8, hdc, d, 5, good]。但是加鎖的數量，要遠遠大於滿足條件的記錄數量。
(4)在RR隔離級別下如何判斷一個複雜SQL應該加什麼樣的鎖
首先需要提取其where條件。
Index Key確定的範圍，需要加上間隙鎖。
Index Filter過濾條件，滿足Index Filter的記錄加X鎖，否則不加X鎖。
Table Filter過濾條件，無論是否滿足都需要加X鎖。
12.死鎖與解決方案
(1)表的死鎖
(2)行級鎖死鎖
(3)死鎖案例演示
(4)死鎖總結
(1)表的死鎖
一.產生原因
使用者A訪問表A鎖住了表A，然後又訪問表B。另一個使用者B訪問表B鎖住了表B，然後企圖訪問表A。這時使用者A由於使用者B已經鎖住表B，它要等使用者B釋放表B才能繼續。同樣使用者B要等使用者A釋放表A才能繼續，這就死鎖就產生了。
使用者A --> A表(表鎖) --> B表(表鎖)
使用者B --> B表(表鎖) --> A表(表鎖)
二.解決方案
這種死鎖比較常見，是由於程式的BUG產生的，除了調整的程式的邏輯沒有其它的辦法。仔細分析程式的邏輯，對於資料庫的多表操作時：儘量按照相同的順序進行處理，儘量避免同時鎖定兩個資源。如操作A和B兩張表時，總是按先A後B的順序處理。兩執行緒必須同時鎖定兩個資源時，要保證按照相同的順序來鎖定資源。
(2)行級鎖死鎖
一.產生原因1
如果在事務中執行了一條沒有索引條件的查詢，引發全表掃描，把行級鎖上升為全表記錄鎖定(等價於表級鎖)。多個這樣的事務執行後，就容易產生死鎖和阻塞。最終應用系統會越來越慢，發生阻塞或死鎖。
解決方案1：
SQL語句中不要使用太複雜的關聯多表的查詢。使用explain對SQL進行分析，對於有全表掃描和全表鎖定的SQL，建立相應的索引進行最佳化。
二.產生原因2
兩個事務分別想拿到對方持有的鎖，互相等待，於是產生死鎖。
三.產生原因3
每個事務只有一個SQL，但有時還是會發生死鎖。
下圖中：id是主鍵索引，name是普通索引，pubtime也是普通索引，箭頭上的數字1和2表示的是加鎖的順序。
事務1從name索引出發，讀到的[hdc, 1], [hdc, 6]均滿足條件，不僅會加name索引上的記錄X鎖，而且會加聚簇索引上的記錄X鎖，加鎖順序為先[1, hdc, 100]後[6, hdc, 10]。
事務2從pubtime索引出發，[10, 6], [100, 1]均滿足過濾條件，同樣也會加聚簇索引上的記錄X鎖，加鎖順序為先[6, hdc, 10]後[1, hdc, 100]，但是加鎖時發現跟事務1的加鎖順序正好相反。
兩個事務恰好都持有了第一把鎖，請求加第二把鎖，死鎖就發生了。
解決方案：上面出現死鎖的原因是對索引行加鎖順序的不一致而導致的。所以如果可以，儘量以相同的順序來訪問索引記錄和表(針對原因2)。比如在程式以批次方式處理資料時，如果事先對資料排序(針對原因3)，就可以保證每個執行緒按固定的順序來處理記錄，從而能大大減少出現死鎖的情況。
(3)死鎖案例演示
接下來演示一下對於發生死鎖的分析過程：
一.資料準備
create table test_deadLock(
id int primary key,
name varchar(50),
age int
);
insert into test_deadLock values(1, 'lisi', 11),(2, 'zhangsan', 22),(3, 'wangwu', 33);二.資料庫隔離級別檢視
select @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+三.檢視加鎖資訊
-- information_schema.innodb_trx: 當前出現的鎖
mysql> select * from information_schema.innodb_locks;
-- information_schema.innodb_trx: 當前執行的所有事務
mysql> select * from information_schema.innodb_trx;
-- information_schema.innodb_lock_waits: 鎖等待的對應關係
mysql> select * from information_schema.innodb_lock_waits;四.檢視InnoDB狀態
InnoDB狀態會包含最近的死鎖日誌資訊。
mysql> show engine innodb status;五.案例分析
作為第一個示例，這裡進行細緻的分析。兩個事務每執行一條SQL，可以檢視下innodb鎖狀態、鎖等待資訊及當前innodb事務列表資訊，最後可以透過"show engine innodb status;"檢視最近的死鎖日誌資訊。
第一步：事務1執行begin開始事務執行一條SQL，查詢id=1的資料。
-- 視窗1，開啟事務
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
-- 視窗1，查詢id=1的資料
mysql> select * from test_deadLock where id = 1 for update;
+----+------+------+
| id | name | age |
+----+------+------+
| 1 | lisi | 11 |
+----+------+------+
1 row in set (0.00 sec)分析加鎖過程：
過程1：事務1進行首先申請IX鎖(意向排它鎖，因為是for update)。
過程2：然後申請X鎖，查詢是否存在id=1的記錄。
過程3：存在該記錄，因為id欄位是唯一索引，所以新增的是記錄鎖。
第二步：檢視information_schema.innodb_trx表，發現存在事務1的資訊。
mysql> select
trx_id '事務id',
trx_state '事務狀態',
trx_started '事務開始時間',
trx_weight '事務權重',
trx_mysql_thread_id '事務執行緒ID',
trx_tables_locked '事務擁有鎖個數',
trx_lock_memory_bytes '事務鎖住記憶體大小',
trx_rows_locked '事務鎖住行數',
trx_rows_modified '事務更改行數'
from information_schema.innodb_trx;第三步：執行事務2的delete語句，刪除成功；因為id=3的資料沒被加鎖。
-- 視窗2
mysql> begin;
mysql> delete from test_deadLock where id = 3; -- 刪除成功檢視事務資訊，innodb_trx已經有T1、T2兩個事務資訊。
-- 視窗1
mysql> select
trx_id '事務id',
trx_state '事務狀態',
trx_weight '事務權重',
trx_mysql_thread_id '事務執行緒ID',
trx_tables_locked '事務擁有多少個鎖',
trx_lock_memory_bytes '事務鎖住的記憶體大小',
trx_rows_locked '事務鎖住的行數',
trx_rows_modified '事務更改的行數'
from information_schema.innodb_trx;第四步：事務1對id = 3的記錄進行修改，發生阻塞
因為id = 3的資料的X鎖已經被事務2拿到，其他事務的操作只能被阻塞。
-- 視窗1
mysql> update test_deadLock set name = 'aaa' where id = 3;
-- 阻塞第五步：此時檢視當前鎖資訊
-- 視窗2，在事務1還在更新阻塞時去視窗2檢視當前鎖資訊
select
lock_id '鎖ID',
lock_trx_id '擁有鎖的事務ID',
lock_mode '鎖模式',
lock_type '鎖型別',
lock_table '被鎖的索引',
lock_space '被鎖的表空間號',
lock_page '被鎖的頁號',
lock_rec '被鎖的記錄號',
lock_data '被鎖的資料'
from information_schema.innodb_locks;lock_rec = 4表示是對唯一索引進行的加鎖，lock_mode = X表示這裡加的是X鎖。
-- 視窗2，在事務1還在更新阻塞時去視窗2檢視鎖等待的對應關係
select
requesting_trx_id '請求鎖的事務ID',
requested_lock_id '請求鎖的鎖ID',
blocking_trx_id '當前擁有鎖的事務ID',
blocking_lock_id '當前擁有鎖的鎖ID'
from information_schema.innodb_lock_waits;第六步：在事務2執行刪除操作，刪除id = 1的資料成功。
-- 視窗2，執行刪除 id = 1的資料成功
mysql> delete from test_deadLock where id = 1;
Query OK, 1 row affected (0.00 sec)第七步：但是發現事務1已經檢測到了死鎖的發生。
-- 視窗1
mysql> update test_deadLock set name = 'aaa' where id = 3;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
-- 視窗1，commit事務1，更新操作失敗
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test_deadLock;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | lisi | 11 |
| 2 | zhangsan | 22 |
+----+----------+------+
2 rows in set (0.00 sec)
-- 事務2 commit，刪除操作成功
mysql> commit;
mysql> select * from test_deadLock;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | lisi | 11 |
| 2 | zhangsan | 22 |
+----+----------+------+
2 rows in set (0.00 sec)事務1和事務2的整個死鎖過程操作梳理如下：
-- 事務1
mysql> begin;
-- 查詢id=1的資料
mysql> select * from test_deadLock where id = 1 for update;
mysql> update test_deadLock set name = 'aaa' where id = 3;
-- 阻塞
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
-- 事務2
mysql> begin;
mysql> delete from test_deadLock where id = 3; -- 刪除成功
mysql> delete from test_deadLock where id = 1; -- 刪除成功第八步：檢視死鎖日誌
ACTIVE 309 sec：表示事務活動時間；
starting index read：表示讀取索引；
tables in use 1：表示有一張表被使用了；
LOCK WAIT 3 lock struct(s)：表示該事務的鎖連結串列的長度為3，每個連結串列節點代表該事務持有的一個鎖結構，如表鎖、記錄鎖等；
heap size 1136：為事務分配的鎖堆記憶體大小；
3 row lock(s)：表示當前事務持有的行鎖個數/Gap鎖的個數；
LATEST DETECTED DEADLOCK
------------------------
2021-04-04 06:22:01 0x7fa66b39d700
*** (1) TRANSACTION: 事務1
TRANSACTION 7439917, ACTIVE 309 sec starting index read
-- 事務編號7439917，活躍秒數309，starting index read表示事務狀態為根據索引讀取資料
mysql tables in use 1, locked 1
-- 表示有一張表被使用了，locked 1表示表上有一個表鎖，對於DML語句為LOCK_IX
LOCK WAIT 3 lock struct(s), heap size 1136, 3 row lock(s)
MySQL thread id 20, OS thread handle 140352739985152, query id 837 localhost root updating
update test_deadLock set name = 'aaa' where id = 3
--當前正在等待鎖的SQL語句
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 248 page no 3 n bits 72 index PRIMARY of table `test`.`test_deadLock` trx id 7439917 lock_mode X locks rec but not gap waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
0: len 4; hex 80000003; asc ;;
1: len 6; hex 000000004059; asc @Y;;
2: len 7; hex 4100000193256b; asc A %k;;
3: len 6; hex 77616e677775; asc wangwu;;
4: len 4; hex 80000021; asc !;;
*** (2) TRANSACTION:
TRANSACTION 7439918, ACTIVE 300 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 19, OS thread handle 140352740251392, query id 838 localhost root updating
delete from test_deadLock where id = 1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 248 page no 3 n bits 72 index PRIMARY of table `test`.`test_deadLock` trx id 7439918 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
0: len 4; hex 80000003; asc ;;
1: len 6; hex 000000004059; asc @Y;;
2: len 7; hex 4100000193256b; asc A %k;;
3: len 6; hex 77616e677775; asc wangwu;;
4: len 4; hex 80000021; asc !;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 248 page no 3 n bits 72 index PRIMARY of table `test`.`test_deadLock` trx id 7439918 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 6; hex 00000000403d; asc @=;;
2: len 7; hex b0000001240110; asc $ ;;
3: len 4; hex 6c697369; asc lisi;;
4: len 4; hex 8000000b; asc ;;(4)死鎖總結
一.對索引加鎖順序的不一致很可能會導致死鎖
所以儘量以相同的順序來訪問索引記錄和表，在程式以批次方式處理資料時，如果事先對資料排序，保證每個執行緒按固定的順序來處理資料，可大大減少出現死鎖的情況。
二.間隙鎖往往是程式中導致死鎖的真兇
由於預設隔離級別是RR，所以是要使用間隙鎖的。但是如果能確定幻讀和不可重複讀對應用的影響不大，可以考慮降低隔離級別，改成RC可以避免間隙鎖導致的死鎖。
三.為表新增合理的索引
如果不走索引將會為表的每一行記錄加鎖，死鎖的機率就會增大。
四.避免大事務，儘量將大事務拆成多個小事務
因為大事務佔用資源多、耗時長，與其他事務衝突的機率也會變高。
五.避免在同時執行多個對同一表進行讀寫的指令碼
要特別注意加鎖且運算元據量比較大的SQL語句。
六.設定鎖等待超時引數
這個引數就是innodb_lock_wait_timeout。在併發高的情況下，如果大量事務因無法立即獲得所需的鎖而掛起，會佔用大量計算機資源，造成嚴重效能問題，甚至拖跨資料庫。透過設定合適的鎖等待超時閾值，可以避免這種情況發生。