最近，某同事在生產上遇到一個 MySQL 死鎖的問題，于是在幫忙解決問題后，特意花了一周的時間，把 MySQL 所有的鎖都整理了一遍，今天就來一起聊聊 MySQL鎖。
申明：本文基于 MySQL 8.0.30 版本，InnoDB 引擎
MySQL 數據庫鎖設計的初衷是處理并發問題，保證數據安全。MySQL 數據庫鎖可以從下面 3個維度進行劃分：
- 按照鎖的使用方式，MySQL 鎖可以分成共享鎖、排它鎖兩種；
- 根據加鎖的范圍，MySQL 鎖大致可以分成全局鎖、表級鎖和行鎖三類；
- 從思想層面上看，MySQL 鎖可以分為悲觀鎖、樂觀鎖兩種；
我們會先講解共享鎖和排它鎖，然后講解全局鎖、表級鎖和行鎖，因為這三種類別的鎖中，有些是共享鎖，有些是排他鎖，最后，我們再講解 悲觀鎖和樂觀鎖。
為什么要掌握 MySQL 鎖
作為經常和 MySQL 數據庫打交道的程序員來說，編寫業務 SQL 語句是在所難免，編寫高性能的 SQL 語句更是一種技術能力的體現。鎖作為 MySQL 數據庫一個非常重要的知識點，不但是面試中的高頻問題，更是通往技術高P崗位的必備技能。
共享鎖&排他鎖 1.共享鎖
共享鎖，Share lock，也叫讀鎖。它是指當對象被鎖定時，允許其它事務讀取該對象，也允許其它事務從該對象上再次獲取共享鎖，但不能對該對象進行寫入。 加鎖方式是：
# 方式1 select ... lock in share mode; # 方式2 select ... for share;
如果事務T1 在某對象持有共享(S)鎖，則事務T2 需要再次獲取該對象的鎖時，會出現下面兩種情況： - 如果T2 獲取該對象的共享(S)鎖，則可以立即獲取鎖； - 如果T2 獲取該對象的排他(X)鎖，則無法獲取鎖；
為了更好的理解上述兩種情況，可以參照下面的執行順序流和實例圖：
給user表加共享鎖
加鎖線程 sessionA
線程B sessionB
#開啟事務
begin;
#對user整張表加共享鎖
select * from user lock in share mode;
#獲取user表上的共享鎖ok，select操作成功執行
select * from user;
#獲取user表上的排他鎖失敗，操作被堵塞
delete from user where id = 1;
#提交事務
#user表上的共享鎖被釋放
commit;
#獲取user表上的排他鎖成功，delete操作執行ok
delete from user where id = 1;
img.png
給user表id=3的行加共享鎖
加鎖線程 sessionA
線程B sessionB
線程C sessionC
#開啟事務
begin;
#給user表id=3的行加共享鎖
select * from user
where id = 3 lock in share mode;
#獲取user表id=3行上的共享鎖ok
#select操作執行成功
select * from user where id=3;
#獲取user表id=3行上的共享鎖ok
#select操作執行成功
select * from user where id=3;
#獲取user表id=3行上的排它鎖失敗
#delete操作被堵塞
delete from user where id = 3;
#獲取user表id=4行上的排它鎖成功
#delete操作執行成功
delete from user where id = 4;
#提交事務
#user表id=3的行上共享鎖被釋放
commit;
#獲取user表id=3行上的排它鎖成功
#被堵塞的delete操作執行ok
delete from user where id = 3;
img.png
通過上述兩個實例可以看出： - 當共享鎖加在user表上，則其它事務可以再次獲取user表的共享鎖，其它事務再次獲取user表的排他鎖失敗，操作被堵塞； - 當共享鎖加在user表id=3的行上，則其它事務可以再次獲取user表id=3行上的共享鎖，其它事務再次獲取user表id=3行上的排他鎖失敗，操作被堵塞，但是事務可以再次獲取user表id!=3行上的排他鎖；
2. 排它鎖
排它鎖，Exclusive Lock，也叫寫鎖或者獨占鎖，主要是防止其它事務和當前加鎖事務鎖定同一對象。同一對象主要有兩層含義： - 當排他鎖加在表上，則其它事務無法對該表進行insert,update,delete,alter,drop等更新操作； - 當排他鎖加在表的行上，則其它事務無法對該行進行insert,update,delete,alter,drop等更新操作；
排它鎖加鎖方式為：
select ... for update;
為了更好的說明排他鎖，可以參照下面的執行順序流和實例圖：
給user表對象加排他鎖
加鎖線程 sessionA
線程B sessionB
#開啟事務 begin;
#對user整張表加排他鎖
select * from user for update;
#獲取user表上的共享鎖ok，select執行成功
select * from user;
#獲取user表上的排他鎖失敗，操作被堵塞
delete from user where id=3;
#提交事務
#user表上的排他被釋放
commit;
#獲取user表上的排他鎖成功，操作執行ok
delete from user where id = 1;
img.png
給user表id=3的行對象加排他鎖
加鎖線程 sessionA
線程B sessionB
線程C sessionC
#開啟事務
begin;
#給user表id=3的行加排他鎖
select * from user
where id = 3 for update;
#獲取user表id=3行上的共享鎖ok
select * from user where id=3;
#獲取user表id=3行上的共享鎖ok
select * from user where id=3;
#獲取user表id=3行上的排它鎖失敗
delete from user where id = 3;
#獲取user表id=4行上的排它鎖成功
delete from user where id = 4;
#提交事務
#user表id=3的行上排他鎖被釋放
commit;
#獲取user表id=3行上的排它鎖成功
#被堵塞的delete操作執行ok
delete from user where id = 3;
img.png
全局鎖&表級鎖&行鎖 1. 全局鎖 1.1 定義
全局鎖，顧名思義，就是對整個數據庫實例加鎖。它是粒度最大的鎖。
1.2 加鎖
在MySQL中，通過執行 flush tables with read lock 指令加全局鎖：
flush tables with read lock
指令執行完，整個數據庫就處于只讀狀態了，其他線程執行以下操作，都會被阻塞： - 數據更新語句被阻塞，包括 insert, update, delete語句； - 數據定義語句被阻塞，包括建表 create table,alter table、drop table 語句； - 更新操作事務commit語句被阻塞；
1.3 釋放鎖
MySQl釋放鎖有2種方式：
- 執行 unlock tables 指令：unlock tables
- 加鎖的會話斷開，全局鎖也會被自動釋放
為了更好的說明全局鎖，可以參照下面的執行順序流和實例圖：
加鎖線程 sessionA
線程B sessionB
flush tables with read lock; 加全局鎖
select user表ok
select user表ok
insert user表堵塞
insert user表堵塞
delete user表堵塞
delete user表堵塞
drop user 表堵塞
drop user 表堵塞
alter user表 堵塞
alter user表 堵塞
unlock tables； 解鎖
被堵塞的修改操作執行ok
被堵塞的修改操作執行ok
img.png
通過上述的實例可以看出，當加全局鎖時，庫下面所有的表都處于只能狀態，不管是當前事務還是其他事務，對于庫下面所有的表只能讀，不能執行insert，update，delete，alter，drop等更新操作。
1.4 使用場景
全局鎖的典型使用場景是做全庫邏輯備份，在備份過程中整個庫完全處于只讀狀態。如下圖：
img.png
- 假如在主庫上備份，備份期間，業務服務器不能對數據庫執行更新操作，因此涉及到更新操作的業務就癱瘓了；
- 假如在從庫上備份，備份期間，從庫不能執行主庫同步過來的 binlog，會導致主從延遲越來越大，如果做了讀寫分離，那么從庫上獲取數據就會出現延時，影響業務；
從上述分析可以看出，使用全局鎖進行數據備份，不管是在主庫還是在從庫上進行備份操作，對業務總是不太友好。那不加鎖行不行？我們可以通過下面還錢轉賬的例子，看看不加鎖會不會出現問題：
img.png
- 備份前：賬戶A 有1000，賬戶B 有500
- 此時，發起邏輯備份
- 假如數據備份時不加鎖，此時，客戶端A 發起一個還錢轉賬的操作：賬戶A 往賬戶B 轉200
- 當賬戶A 轉出200完成，賬戶B 轉入200 還未完成時，整個數據備份完成
- 如果用該備份數據做恢復，會發現賬戶A 轉出了200，賬戶B 卻沒有對應的轉入記錄，這樣就會產生糾紛：A 說我賬戶少了 200， B 說我沒有收到，最后，A，B誰都不干。
既然不加鎖會產生錯誤，加全局鎖又會影響業務，那么有沒有兩全其美的方式呢？
有，MySQL官方自帶的邏輯備份工具 mysqldump，具體指令如下：
mysqldump –single-transaction
執行該指令，在備份數據之前會先啟動一個事務，來確保拿到一致性視圖， 加上 MVCC 的支持，保證備份過程中數據是可以正常更新。但是，single-transaction方法只適用于庫中所有表都使用了事務引擎，如果有表使用了不支持事務的引擎，備份就只能用 FTWRL 方法。
2. 表級鎖
MySQL 表級鎖有兩種： 表鎖 和 元數據鎖（metadata lock，MDL)
2.1 表鎖
表鎖就是對整張表加鎖，包含讀鎖和寫鎖，由MySQL Server實現，表鎖需要顯示加鎖或釋放鎖，具體指令如下：
# 給表加寫鎖 lock tables tablename write; # 給表加讀鎖 lock tables tablename read; # 釋放鎖 unlock tables;
讀鎖：代表當前表為只讀狀態，讀鎖是一種共享鎖。需要注意的是，讀鎖除了會限制其它線程的操作外，也會限制加鎖線程的行為，具體限制如下： 1. 加鎖線程只能對當前表進行讀操作，不能對當前表進行更新操作，不能對其它表進行所有操作； 2. 其它線程只能對當前表進行讀操作，不能對當前表進行更新操作，可以對其它表進行所有操作；
為了更好的說明讀鎖，可以參照下面的執行順序流和實例圖：
加鎖線程 sessionA
線程B sessionB
#給user表加讀鎖
lock tables user read;
select user表 ok
select user表 ok
insert user表被拒絕
insert user表堵塞
insert address表被拒絕
insert address表ok
select address表被拒絕
alter user表堵塞
unlock tables; 釋放鎖
被堵塞的修改操作執行ok
img.png
寫鎖：寫鎖是一種獨占鎖，需要注意的是，寫鎖除了會限制其它線程的操作外，也會限制加鎖線程的行為，具體限制如下： 1. 加鎖線程對當前表能進行所有操作，不能對其它表進行任何操作； 2. 其它線程不能對當前表進行任何操作，可以對其它表進行任何操作；
為了更好的說明寫鎖，可以參照下面的執行順序流和實例圖：
加鎖線程 sessionA
線程B sessionB
#給user表加寫鎖
lock tables user write;
select user表 ok
select user表 ok
insert user表被拒絕
insert user表堵塞
insert address表被拒絕
insert address表ok
select address表被拒絕
alter user表堵塞
unlock tables; 釋放鎖
堵塞在user表的上更新操作執行ok
img.png
2.2 MDL元數據鎖
元數據鎖：metadata lock，簡稱MDL，它是在MySQL 5.5版本引進的。元數據鎖不用像表鎖那樣顯式的加鎖和釋放鎖，而是在訪問表時被自動加上，以保證讀寫的正確性。加鎖和釋放鎖規則如下：
- MDL讀鎖之間不互斥，也就是說，允許多個線程同時對加了 MDL讀鎖的表進行CRUD(增刪改查)操作；
- MDL寫鎖，它和讀鎖、寫鎖都是互斥的，目的是用來保證變更表結構操作的安全性。也就是說，當對表結構進行變更時，會被默認加 MDL寫鎖，因此，如果有兩個線程要同時給一個表加字段，其中一個要等另一個執行完才能開始執行。
- MDL讀寫鎖是在事務commit之后才會被釋放；
為了更好的說明 MDL讀鎖規則，可以參照下面的順序執行流和實例圖：
加鎖線程 sessionA
其它線程 sessionB
開啟事務
begin;
select user表，user表會默認加上MDL讀鎖
select user表ok
select user表ok
insert user表ok
insert user表ok
update user表ok
update user表ok
delete user表ok
delete user表ok
alter user表，獲取MDL寫鎖失敗，操作被堵塞
commit;提交事務，MDL讀鎖被釋放
被堵塞的修改操作執行ok
img.png
為了更好的說明 MDL寫鎖規則，可以參照下面的順序執行流和實例圖：
加鎖線程 sessionA
線程B sessionB
線程C sessionC
#開啟事務
begin;
#user表會默認加上MDL讀鎖
select user表，
select user表ok
select user表ok
select user表ok
#獲取MDL寫鎖失敗
alter user表操作被堵塞
#獲取MDL讀鎖失敗
select * from user;
提交事務，MDL讀鎖被釋放
#MDL寫鎖被釋放
被堵塞的alter user操作執行ok
#被堵塞的select 操作執行ok
img.png
2.3 意向鎖
由于innodb引擎支持多粒度鎖定，允許行鎖和表鎖共存，為了快速的判斷表中是否存在行鎖，InnoDB推出了意向鎖。
意向鎖，Intention lock，它是一種表鎖，用來標識事務打算在表中的行上獲取什么類型的鎖。 不同的事務可以在同一張表上獲取不同種類的意向鎖，但是第一個獲取表上意向排他(IX) 鎖的事務會阻止其它事務獲取該表上的任何 S鎖 或 X 鎖。反之，第一個獲得表上意向共享鎖(IS) 的事務可防止其它事務獲取該表上的任何 X 鎖。
意向鎖通常有兩種類型： - 意向共享鎖(IS)，表示事務打算在表中的各個行上設置共享鎖。 - 意向排他鎖(IX)，表示事務打算對表中的各個行設置排他鎖。
意向鎖是InnoDB自動加上的，加鎖時遵從下面兩個協議： - 事務在獲取表中行的共享鎖之前，必須先獲取表上的IS鎖或更強的鎖。 - 事務在獲取表中行的排他鎖之前，必須先獲取表上的IX鎖。
為了更好的說明意向共享鎖，可以參照下面的順序執行流和實例圖：
加鎖線程 sessionA
線程B sessionB
#開啟事務
begin;
#user表id=6加共享行鎖 ，默認user表會 加上IS鎖
select * from user where id = 6 for share;
# 觀察IS鎖
select * from performance_schema.data_locksG
img.png
加鎖線程 sessionA
線程B sessionB
#開啟事務
begin;
#user表id=6加排他鎖，默認user表會 加上IX鎖
select * from user where id = 6 for update;
# 觀察IX鎖
select * from performance_schema.data_locksG
img.png
2.4 AUTO-INC鎖
AUTO-INC鎖是一種特殊的表級鎖，當表中有AUTO_INCREMENT的列時，如果向這張表插入數據時，InnoDB會先獲取這張表的AUTO-INC鎖，等插入語句執行完成后，AUTO-INC鎖會被釋放。
AUTO-INC鎖可以使用innodb_autoinc_lock_mode變量來配置自增鎖的算法，innodb_autoinc_lock_mode變量可以選擇三種值如下表：
innodb_autoinc_lock_mode
含義
0
傳統鎖模式，采用 AUTO-INC 鎖
1
連續鎖模式，采用輕量級鎖
2
交錯鎖模式(MySQL8默認)，AUTO-INC和輕量級鎖之間靈活切換
為了更好的說明意AUTO-INC鎖，可以參照下面的順序執行流和實例圖：
2.5 鎖的兼容性
下面的圖表總結了表級鎖類型的兼容性
X
IX
S
IS
X
沖突
沖突
沖突
沖突
IX
沖突
兼容
沖突
兼容
S
沖突
沖突
兼容
兼容
IS
沖突
兼容
兼容
兼容
3. 行鎖
行鎖是針對數據表中行記錄的鎖。MySQL 的行鎖是在引擎層實現的，并不是所有的引擎都支持行鎖，比如，InnoDB引擎支持行鎖而 MyISAM引擎不支持。
InnoDB 引擎的行鎖主要有四類：
- Record Lock： 記錄鎖，是在索引記錄上加鎖；
- Gap Lock：間隙鎖，鎖定一個范圍，但不包含記錄；
- Next-key Lock：Gap Lock + Record Lock，鎖定一個范圍(Gap Lock實現)，并且鎖定記錄本身(Record Lock實現)；
- 插入意向鎖：針對 insert 操作產生的意向鎖；
Record Lock：記錄鎖，是針對索引記錄的鎖，鎖定的總是索引記錄。
例如，select id from user where id = 1 for update; for update 就顯式在索引id上加行鎖(排他鎖)，防止其它任何事務 update或delete id=1 的行，但是對user表的insert、alter、drop操作還是可以正常執行。
為了更好的說明 Record Lock鎖，可以參照下面的執行順序流和實例圖：
加鎖線程 sessionA
線程B sessionB
線程B sessionC
#開啟事務
begin;
給user表id=1加寫鎖
select id from user
where id = 1 for update;
update user set name = 'name121'
where id = 1;
查看 InnoDB監視器中記錄鎖數據
show engine innodb statusG
commit提交事務
record lock 被釋放
被堵塞的update操作執行ok
img.png
3.2 Gap Lock
Gap Lock：間隙鎖，鎖住兩個索引記錄之間的間隙上，由InnoDB隱式添加。比如(1,3) 表示鎖住記錄1和記錄3之間的間隙，這樣記錄2就無法插入，間隙可能跨越單個索引值、多個索引值，甚至是空。
img.png
為了更好的說明 Gap Lock間隙鎖，可以參照下面的順序執行流和實例圖：
加鎖線程 sessionA
線程B sessionB
線程C sessionC
#開啟事務
begin;
加鎖
select * from user
where age = 10 for share;
insert into user(id,age) values(2,20);
#查看 InnoDB監視器中記錄鎖數據
show engine innodb statusG
commit提交事務
Gap Lock被釋放
被堵塞的insert操作執行ok
img.png
上圖中，事務A(sessionA)在加共享鎖的時候產生了間隙鎖(Gap Lock)，事務B(sessionB)對間隙中進行insert/update操作，需要先獲取排他鎖(X)，導致阻塞。事務C(sessionC)通過"show engine innodb statusG" 指令可以查看到間隙鎖的存在。需要說明的，間隙鎖只是鎖住間隙內部的范圍，在間隙外的insert/update操作不會受影響。
Gap Lock鎖，只存在于可重復讀隔離級別，目的是為了解決可重復讀隔離級別下幻讀的現象。
3.3 Next-Key Lock
Next-Key鎖，稱為臨鍵鎖，它是Record Lock + Gap Lock的組合，用來鎖定一個范圍，并且鎖定記錄本身鎖，它是一種左開右閉的范圍，可以用符號表示為：(a,b]。
img.png
為了更好的說明 Next-Key Lock間隙鎖，可以參照下面的順序執行流和實例圖：
加鎖線程 sessionA
線程B sessionB
線程C sessionC
線程D sessionD
#開啟事務
begin;
加鎖
select * from user
where age = 10 for share;
#獲取鎖失敗，insert操作被堵塞
insert into user(id,age)
values(2,20);
update user set name='name1'
where age = 10;
#查看 InnoDB監視器中記錄鎖數據
show engine innodb statusG
提交事務Gap Lock被釋放
commit
被堵塞的insert操作執行ok
被堵塞的update操作執行ok
img.png
上圖中，事務A(sessionA)在加共享鎖的時候產生了間隙鎖(Gap Lock)，事務B(sessionB)對間隙中進行insert操作，需要先獲取排他鎖(X)，導致阻塞。 事務C(sessionC)對間隙中進行update操作，需要先獲取排他鎖(X)，導致阻塞。 事務D(sessionD)通過"show engine innodb statusG" 指令可以查看到間隙鎖的存在。需要說明的，間隙鎖只是鎖住間隙內部的范圍，在間隙外的insert/update操作不會受影響。
3.4 Insert Intention Lock
插入意向鎖，它是一種特殊的間隙鎖，特指插入操作產生的間隙鎖。
為了更好的說明 Insert Intention Lock鎖，可以參照下面的順序執行流和實例圖：
加鎖線程 sessionA
線程B sessionB
線程C sessionC
#開啟事務
begin;
加鎖
select * from user
where age = 10 for share;
#獲取鎖失敗，insert操作被堵塞
insert into user(id,age) values(2,20);
#查看 InnoDB監視器中記錄鎖數據
show engine innodb statusG
commit提交事務
Gap Lock被釋放
#被堵塞的insert操作執行ok
insert into user(id,age) values(2,20);
img.png
樂觀鎖&悲觀鎖
在MySQL中，無論是悲觀鎖還是樂觀鎖，都是人們對概念的一種思想抽象，它們本身還是利用 MySQL提供的鎖機制來實現的。其實，除了在MySQL數據，像 JAVA語言里面也有樂觀鎖和悲觀鎖的概念。
- 悲觀鎖，可以理解成：在對任意記錄進行修改前，先嘗試為該記錄加上排他鎖(exclusive locking)，采用的是先獲取鎖再操作數據的策略，可能會產生死鎖；
- 樂觀鎖，是相對悲觀鎖而言，一般不會利用數據庫的鎖機制，而是采用類似版本號比較之類的操作，因此樂觀鎖不會產生死鎖的問題；
當并發系統中不同線程出現循環資源依賴，涉及的線程都在等待別的線程釋放資源時，就會導致這幾個線程都進入無限等待的狀態，稱為死鎖。可以通過下面的指令查看死鎖
show engine innodb statusG
當出現死鎖以后，有兩種策略：
- 一種策略是，直接進入等待，直到超時。這個超時時間可以通過參數 innodb_lock_wait_timeout 來設置，InnoDB 中 innodb_lock_wait_timeout 的默認值是 50s。
- 另一種策略是，發起死鎖檢測，發現死鎖后，主動回滾死鎖鏈條中的某一個事務，讓其它事務得以繼續執行。將參數 innodb_deadlock_detect 設置為 on，表示開啟死鎖檢測。
