背景
mysql中鎖機制核心是保證數據的一致性以及并發控制。鎖機制的實現與存儲引擎有關，本文介紹的是INNODB存儲引擎的鎖機制；其他存儲引擎如myISAM和memory等僅支持表鎖不支持行鎖，不是本文關注的重點。
本文介紹mysql數據庫提供的鎖機制，包括共享鎖和排它鎖、表鎖和行鎖、間隙鎖和next_key鎖。理解本文后，有助于在不同業務場景設計出合理的索引結構。最后介紹死鎖的檢測和處理方式。
本文會結合案例進行介紹，表結構和數據如下所示:
CREATE TABLE `t_student` (`id` INT(10) NOT NULL COMMENT '學號，唯一ID',`name` VARCHAR(50) NOT NULL COMMENT '姓名',`score` INT(10) NOT NULL COMMENT '分數',PRIMARY KEY (`id`) USING BTREE,INDEX `idx_score` (`score`) USING BTREE
)
ENGINE=InnoDB
;
mysql> select * from t_student;
+-----+-----------+-------+
| id | name | score |
+-----+-----------+-------+
| 2 | 測試2 | 2 |
| 10 | 測試10 | 10 |
| 100 | 測試100 | 100 |
| 101 | 測試101 | 101 |
| 200 | 測試200 | 200 |
+-----+-----------+-------+
1.共享鎖和排他鎖
mysql中加鎖需要依次確定鎖類型、鎖粒度，即先確定是加共享鎖還是排它鎖，然后確定使用行鎖還是表鎖。
本章介紹mysql鎖的類型，包括共享鎖和排他鎖, 定義如下:
共享鎖(Share, 也稱為S鎖)，是一種允許多個事務同時對同一數據行或資源進行讀操作的鎖；它保證了多個事務可以并發地讀取數據，但不允許其他事務對數據進行寫操作。
排他鎖(Exclude, 也稱為X鎖)是一種獨占鎖，用于寫操作；當一個事務對某行數據加了排他鎖后，其他事務不能對該行加任何類型的鎖（包括共享鎖和排他鎖）。
mysql引入了MVCC解決了并發讀寫問題，因此普通的select語句不會加鎖，特殊的select語句才會加鎖：
-- 共享鎖
select * from table_name where ... lock in share mode;-- 排它鎖
select * from table_name where ... for update;
delete、update、insert等修改修改語句會添加排它鎖。
2.表鎖和行鎖
行鎖以行為單位進行加鎖；鎖沖突小，并發度較高; 表鎖對整個表進行加鎖；鎖沖突大，并發度較低。
加鎖的范圍由where語句確定, 通過以下三種場景進行介紹。
Note1：行鎖添加在索引上，如果沒有索引，會退化為表鎖
案例：事務A將name='測試2'的列對應的name字段修改為’生產2’; 事務B將name='測試10'的列對應的name字段修改為’生產10’.
案例如上所示, 由于name字段上沒有添加索引，所以where name = '測試2’條件的鎖為表鎖；此時，另一事務修改t_student中的其他記錄時會阻塞。
重置數據庫狀態為初始狀態，對name添加索引(UNIQUE INDEX unique_name(name) USING BTREE)，再次執行上述案例:
由于name字段上添加了唯一索引，所以where name = '測試2’條件的鎖為行鎖；此時，另一事務修改t_student中的其他記錄時不會阻塞。
Note2：列必須是主鍵或者唯一索引，否者(普通索引)加的鎖是next-key鎖
next-key鎖請參考章節3.間隙鎖和next_key鎖
案例：事務A將score=200的列對應的name字段修改為’生產200’, 事務B新增一條記錄(id=150,name=‘測試150’, score=150).
案例如上所示, 由于score字段為普通索引(不是主鍵或者唯一索引)，所以where score= 200 條件的鎖為next-key鎖, 加鎖范圍為(101, 200]；此時，事務B新增的列score值為150在(101, 200]范圍內，因此事務B被阻塞(直到事務A提交后才會執行)。
重置數據庫至初始狀態，如果將 where score = 200 修改為 id = 200(或name=‘測試200’), 結果如下:
id=200只會給當前記錄加鎖，不會獲取id=150的記錄鎖；因此事務B不會被阻塞。
Note3：必須是精確匹配，否者(范圍、模糊查詢)加的鎖是間隙鎖
間隙鎖請參考章節3.間隙鎖和next_key鎖
案例：事務A將滿足id<=100條件的列對應的name字段修改為’生產’, 事務B新增一條記錄(id=50,name=‘測試50’, score=50), 事務C新增一條記錄(id=150,name=‘測試150’, score=150).
案例如上所示, id<=100條件的鎖為間隙鎖, 加鎖范圍為(-無窮, 100]；此時事務B新增的列score值為50在(-無窮, 100]范圍內，因此事務B被阻塞(直到事務A提交后才會執行)；而事務C新增的列score值為150，不在(-無窮, 100]范圍內，因此事務C不被阻塞。
3.間隙鎖和next_key鎖
間隙鎖: 對于一個范圍而不是一條記錄添加索引，當對主鍵或者唯一索引使用范圍查詢時，mysql會對這個范圍加鎖。在章節2的Note3中，id<=100條件的鎖為間隙鎖, 加鎖范圍為(-無窮, 100].
next_key鎖: 對于通索引(非主鍵和唯一索引)，會在一個范圍加鎖，稱為next-key鎖。next-key是一個前開后閉的區間，對于案例數據，如果需要操作score=200的數據，加鎖范圍為:（101,200]; 如果需要操作score=100的數據，加鎖范圍為:（10, 100];
mysql> select * from t_student;
+-----+-----------+-------+
| id | name | score |
+-----+-----------+-------+
| 2 | 測試2 | 2 |
| 10 | 測試10 | 10 |
| 100 | 測試100 | 100 |
| 101 | 測試101 | 101 |
| 200 | 測試200 | 200 |
+-----+-----------+-------+
說明：間隙鎖和next_key鎖通過在一個范圍加鎖，可以有效避免幻讀的發生。
4.意向鎖
略，Note: 意向鎖的引入僅僅是為了提高mysql鎖機制的判斷效率，由Innodb內部使用(添加和釋放)，與前面介紹的鎖無任何沖突，用戶無感知(可以理解為不存在這種鎖)。
5.死鎖問題
mysql事務在執行過程中會根據需要獲取鎖，鎖被其他事務占據時會持續等待(或者超時報錯退出);
獲取的鎖在事務結束的時候才會釋放，因此當事務間鎖相互持有或者循環持有的情況發送時就會導致死鎖：
死鎖檢測
mysql中存在死鎖檢測機制，當檢測到死鎖時，會自動中止其中一個事務并釋放鎖，被中止的事務拋出ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction的異常。
案例如下所示:
| time | 事務A | 事務B |
|---|---|---|
| 1 | START TRANSACTION; | START TRANSACTION; |
| 2 | update t_student set name=‘testA’ where id=2; | |
| 3 | update t_student set name=‘testB’ where id=100; | |
| 4 | update t_student set name=‘testA’ where id=100; | |
| 5 | update t_student set name=‘testB’ where id=2; | |
| 6 | COMMIT; | COMMIT; |
執行過程如下所示:
執行SHOW ENGINE INNODB STATUS;可以在"LATEST DETECTED DEADLOCK"段中查看死鎖信息:
# 剔除了一些不必要信息，突出重點
------------------------
LATEST DETECTED DEADLOCK
------------------------
2025-02-07 16:33:53 139664724940544
*** (1) TRANSACTION:
TRANSACTION 38235789, MySQL thread id 12587, OS thread handle 139658703853312, query id 323778 localhost root updating
update t_student set name='testA' where id=100*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 15112 page no 4 n bits 120 index PRIMARY of table `test`.`t_student` trx id 38235789 lock_mode X locks rec but not gap Record lock,
heap no 48 PHYSICAL RECORD【用鎖X48表示】*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 15112 page no 4 n bits 120 index PRIMARY of table `test`.`t_student` trx id 38235789 lock_mode X locks rec but not gap waiting
Record lock, heap no 51 PHYSICAL RECORD【用鎖X51表示】*** (2) TRANSACTION:
TRANSACTION 38235791, MySQL thread id 12588, OS thread handle 139664290703104, query id 323785 localhost root updating
update t_student set name='testB' where id=2*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 15112 page no 4 n bits 120 index PRIMARY of table `test`.`t_student` trx id 38235791 lock_mode X locks rec but not gap Record lock,
heap no 51 PHYSICAL RECORD【用鎖X51表示】*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 15112 page no 4 n bits 120 index PRIMARY of table `test`.`t_student` trx id 38235791 lock_mode X locks rec but not gap waiting Record lock,
heap no 48 PHYSICAL RECORD【用鎖X48表示】*** WE ROLL BACK TRANSACTION (2)
信息比較清晰:
檢測到死鎖，對應兩個事務標記為事務(1)和事務(2):
事務(1)的事務ID為38235789，已持有了鎖X48; 然后執行update t_student set name=‘testA’ where id=100語句獲取鎖X51失敗阻塞;
事務(2)的事務ID為38235791，已持有了鎖X51; 然后執行update t_student set name=‘testB’ where id=2語句獲取鎖X48失敗阻塞;
mysql選擇回滾事務(2)以解決死鎖問題。
避免死鎖問題的策略
[1] 保持加鎖順序的一致性
上述案例中，如果事務A和事務B以相同的加鎖順序執行SQL語句，不會發送死鎖現象;
[2] 減少事務的顆粒度
事務的鎖在事務提交后才會釋放，事務顆粒度越大，執行的SQL語句越多，獲取的鎖越多，約容易造成死鎖現象;
[3] 設計合理的索引和SQL條件語句
間隙鎖和next-key鎖相對于行鎖更容易發生死鎖現象。
進行多數據源配置及MyBatis Plus集成)
