Mysql之鎖機制

2021 年 3 月 30 日
筆記
MySQL, 搞懂Mysql

全局鎖

全局鎖就是對整個資料庫實例加鎖。MySQL 提供了一個加全局讀鎖的方法FTWRL

Flush tables with read lock

全局鎖的典型使用場景是，做全庫邏輯備份，也就是把整庫每個表都 select 出來存成文本。在備份過程中整個庫完全處於只讀狀態，存在以下問題：

如果你在主庫上備份，那麼在備份期間都不能執行更新，業務基本上就得停擺
如果你在從庫上備份，那麼備份期間從庫不能執行主庫同步過來的 binlog，會導致主從延遲

可使用官方自帶的邏輯備份工具mysqldump，配合參數–single-transaction，導數據之前啟動一個事務，來確保拿到一致性視圖。由於 MVCC 的支援，這個過程中數據是可以正常更新的。但需要注意的是：single-transaction 方法只適用於所有的表使用事務引擎的庫(InnoDB )

表級鎖

表鎖

-- 給指定表加上表級讀鎖或寫鎖
lock tables … read/write

-- 查看錶鎖定情況
-- In_use：表上鎖及請求鎖的數量(表鎖時其他會話寫請求堵塞)
-- Name_locked：表名是否被鎖定，用於刪除表和表重命名
show open tables where in_use >=1;
| Database | Table | In_use | Name_locked |
+----------+-------+--------+-------------+
| test     | t     |      1 |           0 |

-- 釋放被當前會話持有的任何鎖
unlock tables

元數據鎖

MDL（metadata lock)，在 MySQL 5.5 版本中引入了 MDL，當對一個表做增刪改查操作的時候，加 MDL 讀鎖；當要對錶做結構變更操作的時候，加 MDL 寫鎖。

上圖中如果session A事務未及時提交，就會一直佔用MDL鎖，session C中MDL寫鎖堵塞，後續的讀請求因為MDL讀鎖堵塞，造成整個表不可讀寫。如果剛好是一張熱點表，就有可能造成資料庫執行緒爆滿，從而整個庫不可用。因此，對於長事務或者熱點表的結構調整要慎重。

意向鎖

意向鎖是一種不與行級鎖衝突的表級鎖，分為兩種：
- 意向共享鎖（intention shared lock, IS）：事務有意向對表中的某些行加共享鎖（S鎖）
```
-- 事務要獲取某些行的 S 鎖，必須先獲得表的 IS 鎖。
SELECT column FROM table ... LOCK IN SHARE MODE;
```
- 意向排他鎖（intention exclusive lock, IX）：事務有意向對表中的某些行加排他鎖（X鎖）
```
-- 事務要獲取某些行的 X 鎖，必須先獲得表的 IX 鎖。
SELECT column FROM table ... FOR UPDATE;
```
意向鎖是由數據引擎自己維護的，用戶無法手動操作意向鎖，在為數據行加共享 / 排他鎖之前，InooDB 會先獲取該數據行所在數據表對應意向鎖。

其存在的意義在於：對同一張表加表鎖時，只需要檢測是否存在意向排他鎖即可，不用檢測表中行上的排他鎖存在。

意向共享鎖（IS）意向排他鎖（IX）

共享鎖（S）兼容互斥

排他鎖（X）互斥互斥

注意：這裡的排他 / 共享鎖指的都是表鎖！！！意向鎖不會與行級的共享 / 排他鎖互斥！！！意向鎖之間是互相兼容的！！！

	意向共享鎖（IS）	意向排他鎖（IX）
共享鎖（S）	兼容	互斥
排他鎖（X）	互斥	互斥

行鎖

行鎖又稱記錄鎖，記為LOCK_REC_NOT_GAP

-- 加共享鎖(Shared Locks：S鎖)
select…lock in share mode

-- 加排他鎖(Exclusive Locks：X鎖)
select…for update

兩階段鎖協議

在 InnoDB 事務中，行鎖是在需要的時候才加上的，但並不是不需要了就立刻釋放，而是要等到事務結束時才釋放。因此，如果事務中涉及多個行鎖，要把最可能造成鎖衝突、最可能影響並發度的鎖盡量往後放。

在讀已提交隔離級別下有一個優化，即：語句執行過程中加上的行鎖，在語句執行完成後，就要把「不滿足條件的行」上的行鎖直接釋放了，不需要等到事務提交。也就是說，讀提交隔離級別下，鎖的範圍更小，鎖的時間更短，這也是不少業務都默認使用讀已提交隔離級別的原因。

死鎖檢測

直接進入等待，直到超時，可以通過參數 innodb_lock_wait_timeout 來設置，默認50s
發起死鎖檢測，發現死鎖後，主動回滾死鎖鏈條中的某一個事務，讓其他事務得以繼續執行。將參數 innodb_deadlock_detect 設置為 on，表示開啟這個邏輯

由於第一種策略，時間無法預知，太短可能誤傷。正常情況下採用第二種策略，即主動死鎖檢測，但又會消耗CPU資源。對於熱點行的更新可能導致性能問題，解決思路：

對於不會出現死鎖的業務，可以關掉死鎖檢測，存在風險
控制並發度
- 客戶端並發控制，但需要考慮分散式問題
- 資料庫端並發控制：資料庫中間件實現或者修改Mysql源碼(大神玩家)
業務設計上拆分，單行數據拆分為多行，減小並發度，例如：1一個賬戶拆分為多個子賬戶

思考題

如果刪除一個表裡面的前 10000 行數據，有以下三種方法可以做到：

第一種，直接執行 delete from T limit 10000;
第二種，在一個連接中循環執行 20 次 delete from T limit 500;
第三種，在 20 個連接中同時執行 delete from T limit 500。

哪一種方法更好？為什麼？

長事務、鎖衝突

next-key lock

next-key lock由間隙鎖(Gap Lock)和行鎖組成，每個 next-key lock 是前開後閉區間，解決了幻讀的問題。鎖類型記為：LOCK_ORDINARY

間隙鎖之間不存在衝突關係，跟間隙鎖存在衝突關係的，是「往這個間隙中插入一個記錄」這個操作。

舉例說明

CREATE TABLE `t` (
`id` int(11) NOT NULL,
`c` int(11) DEFAULT NULL,
`d` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `c` (`c`)
) ENGINE=InnoDB;

insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);

session A	session B
begin;
select * from t where id=9 for update;
	begin;
	select * from t where id=9 for update;
	insert into t values(9,9,9);
insert into t values(9,9,9);

上述語句執行結果如何？出現了死鎖，為什麼呢？

session A、session B中select for update由於id=9不存在，均加上了(5,10)的間隙鎖，這也證明了間隙鎖之間不存在衝突。接下來A、B都向這個間隙里插入數據，互相和對方持有的間隙鎖衝突，相互等待形成死鎖。如果開啟了死鎖檢測，InnoDB會馬上發現死鎖關係，讓A中插入報錯返回。

從以上例子也可以看出，由於間隙鎖的引入，雖然解決了幻讀，可也影響了資料庫的並發度。如果實際業務場景不需要保證可重複讀，就可以考慮使用讀已提交，同時binlog_format=row，保證主從同步的一致性。

加鎖規則：兩個原則、兩個優化、一個bug

原則 1：加鎖的基本單位是 next-key lock，前開後閉區間
原則 2：查找過程中訪問到的對象才會加鎖
優化 1：索引上的等值查詢，給唯一索引加鎖的時候，匹配上數據，next-key lock 退化為行鎖
優化 2：索引上的等值查詢，向右遍歷時且最後一個值不滿足等值條件的時候，next-key lock 退化為間隙鎖
一個 bug：唯一索引上的範圍查詢會訪問到不滿足條件的第一個值為止

以上規則，其實可以理解為數據查找過程中，掃描到的對象應該加鎖，排除邏輯上明顯不需要加鎖的對象，即為加鎖範圍

重點：

加鎖是分步進行的，例如：c>=10 and c<=11，分解為c=10、c>10 and c<11、c=11依次進行鎖申請
間隙由右邊的間隙記錄，這也導致了不同方向上掃描加鎖範圍不一樣
從掃描到的第一個記錄上作為起點，例如：普通索引c取值為[0,5,10,15,20]，c>10和c>=10其分別第一個掃描到的數為15、10，因此第一個間隙鎖為(10,15]、(5,10]

讀已提交下的應用

在外鍵場景下有間隙鎖，場景待確認

insert intention lock

插入意向鎖，僅用於insert語句，表明將在某間隙插入記錄，與間隙鎖互斥關係如下：

	X,GAP	S,GAP	intention-insert
X,GAP	兼容	兼容	互斥
S,GAP	兼容	兼容	互斥
intention-insert	兼容	兼容	唯一鍵衝突可能互斥

間隙鎖之間不存在互斥關係（X、S表示是什麼語句導致的間隙鎖）
間隙鎖可以堵塞區間內的插入意向鎖，但插入意向鎖不會堵塞後續的間隙鎖
唯一鍵衝突，如果是主鍵加記錄鎖，如果是唯一索引加next-key lock

插入意向鎖實驗驗證

mysql> CREATE TABLE `t` (
`id` int(11) NOT NULL,
`c` int(11) DEFAULT NULL,
`d` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `c` (`c`)
) ENGINE=InnoDB;

mysql> insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);

-- 開啟事務A
mysql> begin;

/**
在事務A中執行修改語句，id=7不存在，添加(5,10)的間隙鎖，LOCK_MODE=X,GAP LOCK_DATA=10可以驗證兩個觀點：
1、間隙鎖是加在右邊間隙上的
2、此處X並不代表10上加行鎖，僅代表什麼語句造成的，若改為select * from t where id=7 lock in share mode，LOCK_MODE就變為S,GAP
**/
mysql> update t set d=d+1 where id=7;

-- 在事務B中插入id=6的數據，需要申請插入意向鎖，進入堵塞狀態
mysql> insert into t values(6,6,6);

/**
事務A中已經添加了間隙鎖，相同間隙的插入意向鎖堵塞，LOCK_MODE=X,GAP,INSERT_INTENTION，LOCK_STATUS=WAITING
v8.0.11時，LOCK_MODE=X,GAP，INSERT_INTENTION標識是高版本新加的(此處使用的是8.0.21)，插入意向鎖是一種特殊的間隙鎖
**/
mysql> select THREAD_ID,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA 
from performance_schema.data_locks;
+-----------+---------------+-------------+------------+-----------+------------------------+-------------+-----------+
| THREAD_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE              | LOCK_STATUS | LOCK_DATA |
+-----------+---------------+-------------+------------+-----------+------------------------+-------------+-----------+
|        54 | demo          | t           | NULL       | TABLE     | IX                     | GRANTED     | NULL      |
|        54 | demo          | t           | PRIMARY    | RECORD    | X,GAP                  | GRANTED     | 10        |
|        53 | demo          | t           | NULL       | TABLE     | IX                     | GRANTED     | NULL      |
|        53 | demo          | t           | PRIMARY    | RECORD    | X,GAP,INSERT_INTENTION | WAITING     | 10        |
+-----------+---------------+-------------+------------+-----------+------------------------+-------------+-----------+

-- 開啟事務C
mysql> begin;

-- 在事務C中插入id=16的數據，由於該間隙上沒有間隙鎖，申請插入意向鎖成功
mysql> insert into t values(16,16,16);

/** 
查詢當前加鎖情況，並沒有發現插入意向鎖，為什麼？
插入意向鎖是為了配合間隙鎖解決幻讀問題，在有間隙鎖的情況下進行堵塞。此時沒有間隙鎖，不需要堵塞，所以就不用加插入意向鎖嗎？
但其他事務中相同行插入會產生衝突，說明這裡還是有其他約束的，只是不用堵塞的插入意向鎖轉換成另外一種約束了
**/
mysql> select THREAD_ID,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA 
from performance_schema.data_locks;
+-----------+---------------+-------------+------------+-----------+-----------+-------------+-----------+
| THREAD_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-----------+---------------+-------------+------------+-----------+-----------+-------------+-----------+
|        53 | demo          | t           | NULL       | TABLE     | IX        | GRANTED     | NULL      |
+-----------+---------------+-------------+------------+-----------+-----------+-------------+-----------+
/**
證明其他約束的存在，新啟一個事務，同樣執行insert into t values(16,16,16)，可以看到申請S鎖堵塞，正常上一個事務中的插入有其他約束
這裡需要進行唯一約束驗證，獲取id=16的讀鎖
**/
mysql> select THREAD_ID,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA 
from performance_schema.data_locks;
+-----------+---------------+-------------+------------+-----------+---------------+-------------+-----------+
| THREAD_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA |
+-----------+---------------+-------------+------------+-----------+---------------+-------------+-----------+
|        53 | demo          | t           | NULL       | TABLE     | IX            | GRANTED     | NULL      |
|        56 | demo          | t           | NULL       | TABLE     | IX            | GRANTED     | NULL      |
|        56 | demo          | t           | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 16        |
|        56 | demo          | t           | PRIMARY    | RECORD    | S,REC_NOT_GAP | WAITING     | 16        |
+-----------+---------------+-------------+------------+-----------+---------------+-------------+-----------+


-- 開啟事務D
mysql> begin;

-- 在事務D中插入id=10，
mysql> insert into t values(10,10,10);
1062 - Duplicate entry '10' for key 'PRIMARY'

-- 在事務E中插入id=9
mysql> insert into t values(9,9,9); 
(blocked)

/**V8.0.11
查看當前加鎖情況，事務D插入語句檢測到唯一衝突後在id=10上加了一個S鎖
事務E中插入id=9，等待插入意向鎖，沒有間隙鎖衝突，為什麼會堵塞呢？
唯一鍵衝突加的應該不是一個記錄S鎖，應該是一個next-key lock (5,10]，因為已經存在間隙鎖，所以插入意向鎖才會堵塞
這是MySQL的一個bug，在V8.0.16已經修復，事務E中插入不會堵塞（主鍵唯一衝突就是一個單純的記錄鎖）
//bugs.mysql.com/bug.php?id=93806
**/
mysql> select THREAD_ID,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA 
from performance_schema.data_locks;
+--------+-----------+---------------+-------------+------------+-----------+-----------+-------------+-----------+
| ENGINE | THREAD_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+-----------+---------------+-------------+------------+-----------+-----------+-------------+-----------+
| INNODB |       109 | demo          | t           | NULL       | TABLE     | IX        | GRANTED     | NULL      |
| INNODB |       109 | demo          | t           | PRIMARY    | RECORD    | S         | GRANTED     | 10        |
| INNODB |       108 | demo          | t           | NULL       | TABLE     | IX        | GRANTED     | NULL      |
| INNODB |       108 | demo          | t           | PRIMARY    | RECORD    | X,GAP     | WAITING     | 10        |
+--------+-----------+---------------+-------------+------------+-----------+-----------+-------------+-----------+

加鎖檢測

等MDL鎖

-- 事務A
lock table test_data write;

-- 由於事務A加了表鎖，事務B堵塞
select * from test_data;

等flush

-- 關閉表t
flush tables t with read lock; 
-- 關閉所有打開的表
flush tables with read lock;

-- 事務A
select sleep(1) from t;

-- 事務B：事務A中表t已打開，需要等待其結束
flush tables t;

-- 事務C：等待事務B中flush結束
select * from t where id=1;

等行鎖

-- 事務C
begin
update t set a=1 where id=1;

-- 由於事務C行鎖未提交，事務D相同行被堵塞
update t set a=1 where id=1;

鎖及堵塞查詢

-- 查看錶阻塞的process id(MySQL啟動時需要設置performance_schema=on，相比於設置為off會有10%左右的性能損失)
select blocking_pid from sys.schema_table_lock_waits;

-- 查看行鎖等待情況
select * from sys.innodb_lock_waits;

-- MySQL5.7及之前查看事務鎖情況
select * from performance_schema.innodb_locks;
-- MySQL8.0及之後查看事務鎖情況
select * from performance_schema.data_locks;
-- 查看元數據加鎖情況
select * from performance_schema.metadata_locks;

-- 查看當前進程及狀態
show processlist;

-- 查看innodb引擎狀態，可以獲取一些關鍵資訊點，例如：最近事務及加鎖情況，對分析定位問題有幫助
show engine innodb status;

Tags: MySQL 搞懂Mysql