（8）MySQL進階篇SQL優化（InnoDB鎖-共享鎖、排他鎖與意向鎖）

2021 年 5 月 7 日
筆記
MySQL進階篇

1.鎖的分類

鎖（Locking）是資料庫在並發訪問時保證數據一致性和完整性的主要機制。之前MyISAM鎖章節已經講過鎖分類，而InnoDB鎖按照粒度分為鎖定整個表的表級鎖（table-level locking）和鎖定數據行的行級鎖（row-level locking）：
●表級鎖：開銷小，加鎖快；不會出現死鎖；鎖定粒度大，發生鎖衝突的概率最高，並發度最低。
●行級鎖：開銷大，加鎖慢；會出現死鎖；鎖定粒度最小，發生鎖衝突的概率最低，並發度最高。

1.1 InnoDB的行鎖概述

InnoDB可以通過SHOW STATUS命令來檢查InnoDB_row_lock狀態變數用作分析系統上的行鎖的爭奪情況：
SHOW STATUS LIKE ‘innodb_row_lock%’;

五個參數說明如下：
●Innodb_row_lock_current_waits：當前正在等待鎖的數量。
●Innodb_row_lock_time：從系統啟動到現在鎖定總時間長度。
●Innodb_row_lock_time_avg：每次等待所花平均時間。
●Innodb_row_lock_time_max：從系統啟動到現在等待最長的一次所花的時間長度。
●Innodb_row_lock_waits：系統啟動到現在總共等待的次數。
其中InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的參數值比較高，那就說明鎖爭用情況比較嚴重，那就要注意下了！

2.共享鎖與排他鎖

InnoDB主要有以下兩種類型的行鎖：
●共享鎖（S）：允許獲得該鎖的事務讀取數據行（讀鎖），同時允許其他事務獲得該數據行上的共享鎖，並且阻止其他事務獲得數據行上的排他鎖。
●排他鎖（X）：允許獲得該鎖的事務更新或刪除數據行（寫鎖），同時阻止其他事務取得該數據行上的共享鎖和排他鎖。

鎖類型	共享鎖（S）	排他鎖（X）
共享鎖（S）	兼容	衝突
排他鎖（X）	衝突	衝突

共享鎖和共享鎖可以兼容，排他鎖和其它鎖都不兼容。例如，事務A獲取了一行數據的共享鎖，事務B可以立即獲得該數據行的共享鎖，也就是鎖兼容；但是此時事務B如果想獲得該數據行的排他鎖，則必須等待事務A釋放數據行上的共享鎖，此種情況存在鎖衝突。請看以下示例：

session_1	session_2
（1）先設置事務T1提交類型為事務非自動提交。	（1）先設置事務T2提交類型為事務非自動提交。
— 事務提交類型:0.事務非自動提交，1.事務自動提交 SET AUTOCOMMIT=0;	— 事務提交類型:0.事務非自動提交，1.事務自動提交 SET AUTOCOMMIT=0;
（2）獲取了商品品牌表數據行ID=1上的共享鎖。	（2）獲取了商品品牌表數據行ID=1上的共享鎖。
— 獲取ID=1品牌行共享鎖 MySQL [(none)]> BEGIN; Query OK, 0 rows affected (0.00 sec) MySQL [(none)]> SELECT * FROM goods.goods_brand WHERE ID=1 FOR SHARE; +—-+——–+ \| ID \| Name \| +—-+——–+ \| 1 \| 榮耀 \| +—-+——–+ 1 row in set (0.00 sec)	— 獲取ID=1品牌行共享鎖 MySQL [(none)]> SELECT * FROM goods.goods_brand WHERE ID=1 FOR SHARE; +—-+——–+ \| ID \| Name \| +—-+——–+ \| 1 \| 榮耀 \| +—-+——–+ 1 row in set (0.00 sec)
	（2）獲取了商品品牌表數據行ID=1上的排他鎖。此時該命令會一直處於等待狀態並且最終超時。也就是說，共享鎖和排他鎖不兼容。
	— 獲取ID=1品牌行排他鎖 MySQL [(none)]> SELECT * FROM goods.goods_brand WHERE ID=1 FOR UPDATE; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
（2）在當前會話提交或者回滾事務。
— 提交事務 MySQL [(none)]> commit; Query OK, 0 rows affected (0.00 sec)
	（3）再獲取了商品品牌表數據行ID=1上的排他鎖。
	— 獲取ID=1品牌行排他鎖 MySQL [(none)]> SELECT * FROM goods.goods_brand WHERE ID=1 FOR UPDATE; +—-+——–+ \| ID \| Name \| +—-+——–+ \| 1 \| 榮耀 \| +—-+——–+ 1 row in set (0.00 sec)

注意：我們在事務中使用select … for share語句獲取了數據行ID= 1上的共享鎖；對於MySQL 8.0之前的版本，可以使用select … lock in share mode命令。select … for update語句是獲取了數據行ID=1上的排他鎖。

3.意向鎖

InnoDB除了支援行級鎖，還支援由MySQL服務層實現的表級鎖（lock tables…write在指定的表加上表級排他鎖）。當這兩種鎖同時存在時，可能導致衝突。例如，事務A獲取了表中一行數據的讀鎖；然後事務B申請該表的寫鎖（例如修改表的結構）。如果事務B加鎖成功，那麼它就應該能修改表中的任意數據行，但是A持有的行鎖不允許修改鎖定的數據行。顯然資料庫需要避免這種問題，B的加鎖申請需要等待A釋放行鎖。
那麼如何判斷事務B是否應該獲取表級鎖呢？首先需要看該表是否已經被其他事務加上了表級鎖，然後依次查看該表中的每一行是否已經被其他事務加上了行級鎖。這種方式需要遍歷整個表中的記錄，效率很低。所以為了解決這個問題，InnoDB引入意向鎖（Intention Locks）：
●意向共享鎖（IS）：事務打算給數據行加行共享鎖，事務在給一個數據行加共享鎖前必須先取得該表的IS鎖。
●意向排他鎖（IX）：事務打算給數據行加行排他鎖，事務在給一個數據行加排他鎖前必須先取得該表的IX鎖。
注意：這兩種意向鎖都是表鎖。
這個時候，事務A必須先申請該表的意向共享鎖，成功後再申請數據行的行鎖。事務B申請表鎖時，資料庫查看該表是否已經被其他事務加上了表級鎖；如果發現該表上存在意向共享鎖，說明表中某些數據行上存在共享鎖，事務B申請的寫鎖會被阻塞。
因此，意向鎖是為了允許行鎖和表鎖能夠共存，從而實現多粒度鎖機制。以下是表鎖（表鎖與行鎖都有共享鎖跟排他鎖）跟意向表鎖兼容性：

鎖類型	共享鎖（S）	排他鎖（X）	意向共享鎖（IS）	意向排他鎖（IX）
共享鎖（S）	兼容	衝突	兼容	衝突
排他鎖（X）	衝突	衝突	衝突	衝突
意向共享鎖（IS）	兼容	衝突	兼容	兼容
意向排他鎖（IX）	衝突	衝突	兼容	兼容

從表格可見表鎖跟意向鎖之間，只有共享鎖兼容，而意向鎖跟意向鎖之間是互相兼容的。
注意：InnoDB表存在兩種表級鎖，一種是lock tables語句手動指定的鎖，另一種是由InnoDB自動添加的意向鎖。對於UPDATE、DELETE和INSERT語句，InnoDB會自動給涉及數據集加排他鎖（X）；對於普通SELECT語句，InnoDB不會加任何鎖。請看以下兩個示例。
示例1：

session_1

session_2

（1）先設置事務T1提交類型為事務非自動提交。

（1）先設置事務T2提交類型為事務非自動提交。

— 事務提交類型:0.事務非自動提交，1.事務自動提交

SET AUTOCOMMIT=0;

— 事務提交類型:0.事務非自動提交，1.事務自動提交

SET AUTOCOMMIT=0;

（2）在當前事務T1中為商品品牌表goods_brand中的數據行ID=1加上排他鎖，同時會為表goods_brand加上意向排他鎖。

— 獲取ID=1的品牌行排他鎖

MySQL [(none)]> BEGIN;

Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> SELECT * FROM goods.goods_brand WHERE ID=1 FOR UPDATE;

+—-+——–+

| ID | Name |

+—-+——–+

| 1 | 榮耀 |

+—-+——–+

1 row in set (14.63 sec)

（2）在當前事務T2中顯式加表級共享鎖或者排他鎖，因為意向排他鎖和表級共享鎖衝突，所以session_2事務T2一直等待session_1事務T1釋放鎖。

— 顯式加表級共享鎖或者排他鎖

LOCK TABLES goods.goods_brand READ;

— 或者

— LOCK TABLES goods.goods_brand WRITE;

阻塞…

（3）T1提交或者回滾事務。

（3）當session_1事務T1提交或者回滾事務釋放鎖，T2自動獲取goods_brand表的共享鎖。

— 回滾

MySQL [(none)]> ROLLBACK;

Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> LOCK TABLES goods.goods_brand READ;

Query OK, 0 rows affected (8.09 sec)

（4）釋放goods_brand表共享鎖。

— 釋放共享鎖

MySQL [(none)]> UNLOCK TABLES;

Query OK, 0 rows affected (0.00 sec)

由此可驗證，意向排他鎖（IX）跟表級共享鎖（S）正如表格中所示是衝突的，其他類型鎖兼容這裡就不一一示範了，大家可自行驗證。
示例2：

session_1	session_2
（1）先設置事務T1提交類型為事務非自動提交。	（1）先設置事務T2提交類型為事務非自動提交。
— 事務提交類型:0.事務非自動提交，1.事務自動提交 SET AUTOCOMMIT=0;	— 事務提交類型:0.事務非自動提交，1.事務自動提交 SET AUTOCOMMIT=0;
（2）在當前事務T1中為商品品牌表goods_brand加上了意向排他鎖和數據行ID=1上的排他鎖。	（2）在當前事務T2中為商品品牌表goods_brand加上了意向排他鎖和數據行ID=2上的排他鎖。
— 獲取ID=1的品牌行排他鎖 MySQL [(none)]> BEGIN; Query OK, 0 rows affected (0.00 sec) MySQL [(none)]> SELECT * FROM goods.goods_brand WHERE ID=1 FOR UPDATE; +—-+——–+ \| ID \| Name \| +—-+——–+ \| 1 \| 榮耀 \| +—-+——–+ 1 row in set (0.00 sec)	— 獲取ID=2的品牌行排他鎖 MySQL [(none)]> SELECT * FROM goods.goods_brand WHERE ID=2 FOR UPDATE; +—-+——–+ \| ID \| Name \| +—-+——–+ \| 2 \| 蘋果 \| +—-+——–+ 1 row in set (0.00 sec)
（3）提交或者回滾事務。
— 提交事務 MySQL [(none)]> COMMIT; Query OK, 0 rows affected (0.00 sec)

由上述示例可以知道，事務T1和T2同時獲得了商品品牌表goods_brand上的意向排他鎖，以及不同數據行上的行級排他鎖，而且這兩種意向鎖並沒有發生衝突，由此可見意向鎖跟意向鎖之間是互相兼容的。InnoDB通過行級鎖，實現了更細粒度的控制，能夠支援更高的並發更新和查詢。還有一點是InnoDB行鎖是通過給索引上的索引項加鎖來實現的，當有明確指定的主鍵或者索引時候，才是行級鎖，否則就是表級鎖！在下一個章節，我將會介紹這個知識點。

參考文獻：
深入淺出MySQL大全
通過各種簡單案例，讓你徹底搞懂MySQL中的鎖機制與MVCC

Tags: MySQL進階篇