前言
我們前幾篇講了索引是什麼，如何使用explain分析索引使用情況，如何去優化索引，以及show profiles分析SQL語句執行資源消耗的學習。今天我們來講講MySQL的各種鎖，這裡儲存引擎我們使用InnoDB
準備工作
建立表 tb_innodb_lock
drop table if exists test_innodb_lock;
CREATE TABLE test_innodb_lock (
a INT (11),
b VARCHAR (20)
) ENGINE INNODB DEFAULT charset = utf8;
insert into test_innodb_lock values (1,'a');
insert into test_innodb_lock values (2,'b');
insert into test_innodb_lock values (3,'c');
insert into test_innodb_lock values (4,'d');
insert into test_innodb_lock values (5,'e');建立索引
create index idx_lock_a on test_innodb_lock(a);
create index idx_lock_b on test_innodb_lock(b);MySQL 各種鎖演示
- 先將自動提交事務改成手動提交：
set autocommit=0; - 我們啟動兩個會話視窗 A 和 B，模擬一個搶到鎖，一個沒搶到被阻塞住了。
行鎖（寫&讀）
- A 視窗執行
update test_innodb_lock set b='a1' where a=1;SELECT * from test_innodb_lock;
我們可以看到 A 視窗可以看到更新後的結果
- B 視窗執行
SELECT * from test_innodb_lock;
我們可以看到 B 視窗不能看到更新後的結果，看到的還是老資料，這是因為 a = 1 的這行記錄被 A 視窗執行的 SQL 語句搶到了鎖，並且沒有執行 commit 提交操作。所以視窗 B 看到的還是老資料。這就是 MySQL 隔離級別中的"讀已提交"。
- 視窗 A 執行 commit 操作
COMMIT;- 視窗 B 查詢
SELECT * from test_innodb_lock;
這個時候我們發現視窗 B 已經讀取到最新資料了
行鎖（寫&寫）
- 視窗 A 執行更新 a = 1 的記錄
update test_innodb_lock set b='a2' where a=1;這時候並沒有 commit 提交，鎖是視窗 A 持有。
- 視窗 B 也執行更新 a = 1 的記錄
update test_innodb_lock set b='a3' where a=1;
可以看到，視窗 B 一直處於阻塞狀態，因為視窗 A 還沒有執行 commit，還持有鎖。視窗 B 搶不到 a = 1 這行記錄的鎖，所以一直阻塞等待。
- 視窗 A 執行 commit 操作
COMMIT;- 視窗 B 的變化
可以看到這個時候視窗 B 已經執行成功了
表鎖
當索引失效的時候，行鎖會升級成表鎖，索引失效的其中一個方法是對索引自動 or 手動的換型。a 欄位本身是 integer，我們加上引號，就變成了 String，這個時候索引就會失效了。
- 視窗 A 更新 a = 1 的記錄
update test_innodb_lock set b='a4' where a=1 or a=2;- 視窗 B 更新 a = 2 的記錄
update test_innodb_lock set b='b1' where a=3;
這個時候發現，雖然視窗 A 和 B 更新的行不一樣，但是視窗 B 還是被阻塞住了，就是因為視窗 A 的索引失效，導致行鎖升級成了表鎖，把整個表鎖住了，索引視窗 B 被阻塞了。
- 視窗 A 執行 commit 操作
COMMIT;- 視窗 B 的變化
可以看到這個時候視窗 B 已經執行成功了
間隙鎖
- 什麼是間隙鎖
當我們採用範圍條件查詢資料時，InnoDB 會對這個範圍內的資料進行加鎖。比如有 id 為：1、3、5、7 的 4 條資料，我們查詢 1-7 範圍的資料。那麼 1-7 都會被加上鎖。2、4、6 也在 1-7 的範圍中，但是不存在這些資料記錄，這些 2、4、6 就被稱為間隙。
- 間隙鎖的危害
範圍查詢時，會把整個範圍的資料全部鎖定住，即便這個範圍內不存在的一些資料，也會被無辜的鎖定住，比如我要在 1、3、5、7 中插入 2，這個時候 1-7 都被鎖定住了，根本無法插入 2。在某些場景下會對效能產生很大的影響
- 間隙鎖演示
我們先把欄位 a 的值修改成 1、3、5、7、9
- 視窗 A 更新 a = 1~7 範圍的資料
update test_innodb_lock set b='b5' where a>1 and a<7;- 視窗 B 在 a = 2 的位置插入資料
insert into test_innodb_lock values(2, "b6");
這個時候發現視窗 B 更新 a = 2 的操作一直在等待，因為 1~7 範圍的資料被間隙鎖，鎖住了。只有等視窗 A 執行 commit，視窗 B 的 a = 2 才能更新成功
行鎖分析
- 執行 SQL 分析命令
show status like 'innodb_row_lock%';
Variable_name 說明
- Innodb_row_lock_current_waits：當前正在等待鎖定的數量。
- Innodb_row_lock_time：從系統啟動到現在鎖定的時長。
- Innodb_row_lock_time_avg：每次等待鎖所花平均時間。
- Innodb_row_lock_time_max：從系統啟動到現在鎖等待最長的一次所花的時間。
- Innodb_row_lock_waits：系統啟動後到現在總共等待鎖的次數。
結語
大家可以根據 Variable_name 這幾個引數考慮是否要進行優化，如果鎖定時間，鎖定次數過大，那就該考慮優化了。優化手段可以參考之前索引優化的文章。