[TOC] # MySQL行鎖、表鎖、間隙鎖詳解 ## 前言 我們前幾篇講了索引是什么，如何使用`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：系統啟動后到現在總共等待鎖的次數。