騰訊一面：MySQL的共享鎖和獨占鎖
在InnoDB存儲引擎中，行級別鎖有兩種類型：共享鎖（S鎖）和排他鎖（X鎖），理解這 2種鎖的工作機制及其交互關系對于掌握MySQL的并發(fā)控制和鎖機制非常重要，因此，今天就來一起聊聊MySQL的共享鎖和排他鎖。
申明：本文基于 MySQL 8.0.30 版本，InnoDB引擎。
一、共享鎖
1.什么是共享鎖？
共享鎖（shared lock，S鎖），也叫讀鎖。它是指當對象被鎖定時，允許多個事務同時讀取該資源，也允許其它事務從該對象上再次獲取共享鎖，但不能對該對象進行寫操作。
2.加鎖方式
共享鎖一般通過下面 2種方式進行加鎖：
# 方式1
select ... lock in share mode;
# 方式2
select ... for share;如果事務T1 持有某對象的共享(S)鎖，則事務T2 需要再次獲取該對象的鎖時，會出現(xiàn)下面兩種情況：
- 如果T2 獲取該對象的共享(S)鎖，則可以立即獲取鎖；
- 如果T2 獲取該對象的排他(X)鎖，則無法獲取鎖；
二、舉例說明
為了更好地理解上述兩種情況，這里分別以下面的執(zhí)行順序流對InnoDB存儲引擎和MyISAM存儲引擎進行驗證：
1.InnoDB存儲引擎
創(chuàng)建一張用戶user表，表結構如下：
CREATE TABLE `user` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`age` int DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci(1) 給行加共享鎖
這里給user表中id=3行加共享鎖為例，執(zhí)行順序流如下表：
加鎖線程 sessionA | 線程B sessionB | 線程C sessionC |
#開啟事務 begin; | ||
#給 select * from user where id = 3 lock in share mode; | ||
#獲取 #select操作執(zhí)行成功 select * from user where id=3; | #獲取 #select操作執(zhí)行成功 select * from user where id=3; | |
#獲取 #delete操作被堵塞 delete from user where id = 3; | #獲取 #delete操作執(zhí)行成功 delete from user where id = 4; | |
#提交事務 # commit; | ||
#獲取 #被堵塞的delete操作執(zhí)行成功 delete from user where id = 3; |
示例執(zhí)行結果圖如下：
通過上述的示例執(zhí)行結果可以看出：當事務A(sessionA)對user中id=3這行添加共享鎖后，事務B(sessionB)和事務C(sessionC)都可以獲取user表的共享鎖，也就是select操作能成功執(zhí)行，但是事務B(SessionB)獲取user表id=3的寫鎖失敗，即delete where id=3操作被阻塞，而事務C(sessionC)獲取user表id=4的寫鎖成功，即delete where id=4操作成功；
(2) 給表加共享鎖
這里通過lock in share mode方式給user整張表添加共享鎖，執(zhí)行順序流如下表：
加鎖線程 sessionA | 線程B sessionB |
#開啟事務 begin; | |
#對 select * from user lock in share mode; | |
#成功獲取 select * from user; | |
#獲取 delete from user where id = 1; | |
#提交事務 # commit; | |
#獲取 delete from user where id = 1; |
示例執(zhí)行結果圖如下：
通過上述的示例執(zhí)行結果可以看出：當事務A(sessionA)對user整張表添加共享鎖后，事務B(sessionB)可以獲取user表的共享鎖，也就是select操作能成功執(zhí)行，但是事務B(SessionB)獲取user表的寫鎖失敗，即delete操作被阻塞。
所以，盡管共享鎖（S鎖）是InnoDB存儲引擎的行級別鎖，但是一旦它作用到整張表時，其實是對表中所有的行加共享鎖。
2.MyISAM引擎
創(chuàng)建一張用戶person表，表結構如下：
CREATE TABLE `person` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(25) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `index_name` (`name`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_給行加共享鎖
這里給person表的id=3行加共享鎖為例，執(zhí)行順序流如下表：
加鎖線程 sessionA | 線程B sessionB |
#開啟事務 begin; | |
#給 select * from person where id = 3 lock in share mode; | |
#獲取 #select操作成功 select * from person where id=3; | |
#獲取 #update操作成功 update person set name='name3xx' user where id = 3; | |
select * from person where id=3; | |
#提交事務 # commit; |
示例執(zhí)行結果圖如下：
通過上述的示例執(zhí)行結果可以看出：當事務A(sessionA)對person中id=3這行添加共享鎖后，事務B(sessionB)既能獲取person表的共享鎖，也能獲取person表id=3的寫鎖，即select和update where id=3都操作成功；
因此，在MyISAM引擎中其實不存在共享鎖。
3.總結
通過上述示例及其運行結果可以看出：
共享鎖是InnoDB存儲引擎的行級鎖，在MyISAM存儲引擎中不存在；
共享鎖是盡管是行級別鎖，但是當鎖加在整個表時（表中所有的行，一種特殊的行），排他鎖也會在表級別生效；
三、排它鎖
1.什么是排他鎖？
排它鎖（exclusive lock，X鎖），也叫寫鎖或者獨占鎖，主要是防止其它事務和當前加鎖事務鎖定同一對象，同一對象主要有兩層含義：
- 當排他鎖加在表上，則其它事務無法對該表進行insert,update,delete,alter,drop等更新操作；
- 當排他鎖加在行上，則其它事務無法對該行進行insert,update,delete,alter,drop等更新操作；
2.加鎖方式
排他鎖加鎖的方式一般有 2種：顯式加鎖和隱式加鎖，如下：
-- 顯式加鎖
select ... for update;
-- 隱式加鎖，是 MySQL內部自動加鎖為了更好的說明排他鎖，這里以下面的執(zhí)行順序流來進行驗證，用戶user表的結構還是和上面的一樣：
四、舉例說明
為了更好地理解上述兩種情況，這里分別以下面的執(zhí)行順序流對InnoDB存儲引擎和MyISAM存儲引擎進行驗證：
1.InnoDB存儲引擎
(1) 給行加排他鎖
這里通過for update顯式給user表中id=6行加排他鎖為例，執(zhí)行順序流如下表：
加鎖線程 sessionA | 線程B sessionB | 線程C sessionC |
#開啟事務 begin; | ||
#給 select * from user where id = 6 for update; | ||
#獲取 select * from user where id=6; | #獲取 select * from user where id=6; | |
#獲取 delete from user where id = 6; | #獲取 delete from user where id = 7; | |
#提交事務 # commit; | ||
#獲取 #被堵塞的delete操作執(zhí)行成功 delete from user where id = 6; |
示例執(zhí)行結果圖如下：
通過上述的示例執(zhí)行結果可以看出：當事務A(sessionA)對user中id=6這行添加共享鎖后，事務B(sessionB)和事務C(sessionC)都可以獲取user表的共享鎖，也就是select操作能成功執(zhí)行，但是事務B(SessionB)獲取user表id=6的寫鎖失敗，即delete where id=6操作被阻塞，而事務C(sessionC)獲取user表id=7的寫鎖成功，即delete where id=7操作成功；
(2) 給表加排他鎖
這里通過for update顯式方式給user整張表添加排他鎖，執(zhí)行順序流如下表：
加鎖線程 sessionA | 線程B sessionB |
#開啟事務 begin; | |
#對 select * from user for update; | |
#獲取 select * from user; | |
#獲取 delete from user where id=3; | |
#提交事務 # commit; | |
#獲取 delete from user where id = 3; |
示例執(zhí)行結果圖如下：
通過上述的示例執(zhí)行結果可以看出：當事務A(sessionA)對user整張表加排他鎖后，事務B(sessionB)可以獲取user表的共享鎖，也就是select操作能成功執(zhí)行，但是事務B(SessionB)獲取user表的排他鎖失敗，即delete操作被阻塞；
所以，盡管排他鎖（X鎖）是InnoDB存儲引擎的行級別鎖，但是一旦它作用到整張表時，其實是對表中所有的行加排他鎖。
2.MySQL 隱式加排他鎖
這里通過MySQL隱式給user的id=6行添加排他鎖，執(zhí)行順序流如下表
加鎖線程 sessionA | 線程B sessionB |
#開啟事務 begin; | |
#MySQL隱式給 update user set name = 'name6' where id =6; | |
#獲取 select * from user where id = 6 lock in share mode; | |
#提交事務 # commit; | |
#獲取 |
示例執(zhí)行結果圖如下：
通過上述的示例執(zhí)行結果可以看出：當事務A(sessionA)執(zhí)行update where id=6時，MySQL會隱式加排他鎖，事務B(sessionB)在lock in share mode模式下獲取user表id=6的共享鎖失敗，也就是select操作能成功被阻塞；
3.MyISAM引擎
MySQL 隱式加排他鎖
這里通過MySQL隱式給person的id=4行添加排他鎖，執(zhí)行順序流如下表：
加鎖線程 sessionA | 線程B sessionB |
#開啟事務 begin; | |
#MySQL不會隱式給 update person set name = 'name4' where id =4; | |
#獲取 select * from user where id=4 lock in share mode; | |
#獲取 | |
#提交事務 commit; |
示例執(zhí)行結果圖如下：
通過上述的示例執(zhí)行結果可以看出：當事務A(sessionA)執(zhí)行update where id=6時，MySQL不會隱式加排他鎖，事務B(sessionB)既能獲取id=4的共享鎖，也能獲取id=4的排他鎖；
因此，在MyISAM引擎中其實不存在排他鎖。
4.總結
通過上述 3個示例及其運行結果可以看出：排他鎖有表級別共享鎖和行級別共享鎖和自動鎖機制 3種 表級別共享鎖：
- 鎖定整個表，排他鎖也會在表級別生效；
- 行級別共享鎖：鎖定特定行，排他鎖也會在行級別生效；
- 自動鎖機制：根據(jù)操作是表級別還行級別自動加對應的鎖；
五、共享鎖和排他鎖的兼容性矩陣
為了更好地理解共享鎖和排他鎖的互斥關系，可以參考以下兼容性矩陣：
無鎖 | 共享鎖 | 排他鎖 | |
無鎖 | 允許 | 允許 | 允許 |
共享鎖 | 允許 | 允許 | 阻塞 |
排他鎖 | 允許 | 阻塞 | 阻塞 |
從上述矩陣可以看出：
- 無鎖狀態(tài)下可以獲取任何類型的鎖
- 共享鎖狀態(tài)下可以繼續(xù)獲取共享鎖，但不能獲取排他鎖
- 排他鎖狀態(tài)下不能獲取任何其他鎖
六、總結
- 共享鎖（S鎖）和排他鎖（X鎖）是InnoDB存儲引擎中的 2種行級別鎖，MyISAM存儲引擎不存在。
- 盡管共享鎖（S 鎖）和排他鎖（X 鎖）是行級鎖，但是當他們加到表級別時，對表所有行都生效，這樣看上去等同表級鎖
- 共享鎖（S 鎖）允許多個事務同時讀取數(shù)據(jù)，但不允許修改數(shù)據(jù)。多個事務可以同時持有共享鎖
- 排他鎖（X 鎖）允許一個事務修改數(shù)據(jù)。只有一個事務可以持有排他鎖，并且在它釋放鎖之前，其他事務不能獲得任何類型的鎖
