在InnoDB存儲引擎中，行級別鎖有兩種類型：共享鎖（S鎖）和排他鎖（X鎖），理解這 2種鎖的工作機制及其交互關(guān)系對于掌握MySQL的并發(fā)控制和鎖機制非常重要，因此，今天就來一起聊聊MySQL的共享鎖和排他鎖。

申明：本文基于 MySQL 8.0.30 版本，InnoDB引擎。

一、共享鎖

1.什么是共享鎖？

共享鎖（shared lock，S鎖），也叫讀鎖。它是指當對象被鎖定時，允許多個事務(wù)同時讀取該資源，也允許其它事務(wù)從該對象上再次獲取共享鎖，但不能對該對象進行寫操作。

2.加鎖方式

共享鎖一般通過下面 2種方式進行加鎖：

# 方式1
select ... lock in share mode;

# 方式2
select ... for share;

如果事務(wù)T1 持有某對象的共享(S)鎖，則事務(wù)T2 需要再次獲取該對象的鎖時，會出現(xiàn)下面兩種情況：

• 如果T2 獲取該對象的共享(S)鎖，則可以立即獲取鎖；
• 如果T2 獲取該對象的排他(X)鎖，則無法獲取鎖；

二、舉例說明

為了更好地理解上述兩種情況，這里分別以下面的執(zhí)行順序流對InnoDB存儲引擎和MyISAM存儲引擎進行驗證：

1.InnoDB存儲引擎

創(chuàng)建一張用戶user表，表結(jié)構(gòu)如下：

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í)行順序流如下表：

示例執(zhí)行結(jié)果圖如下：

通過上述的示例執(zhí)行結(jié)果可以看出：當事務(wù)A(sessionA)對user中id=3這行添加共享鎖后，事務(wù)B(sessionB)和事務(wù)C(sessionC)都可以獲取user表的共享鎖，也就是select操作能成功執(zhí)行，但是事務(wù)B(SessionB)獲取user表id=3的寫鎖失敗，即delete where id=3操作被阻塞，而事務(wù)C(sessionC)獲取user表id=4的寫鎖成功，即delete where id=4操作成功；

(2) 給表加共享鎖

這里通過lock in share mode方式給user整張表添加共享鎖，執(zhí)行順序流如下表：

示例執(zhí)行結(jié)果圖如下：

通過上述的示例執(zhí)行結(jié)果可以看出：當事務(wù)A(sessionA)對user整張表添加共享鎖后，事務(wù)B(sessionB)可以獲取user表的共享鎖，也就是select操作能成功執(zhí)行，但是事務(wù)B(SessionB)獲取user表的寫鎖失敗，即delete操作被阻塞。

所以，盡管共享鎖（S鎖）是InnoDB存儲引擎的行級別鎖，但是一旦它作用到整張表時，其實是對表中所有的行加共享鎖。

2.MyISAM引擎

創(chuàng)建一張用戶person表，表結(jié)構(gòu)如下：

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í)行順序流如下表：

示例執(zhí)行結(jié)果圖如下：

通過上述的示例執(zhí)行結(jié)果可以看出：當事務(wù)A(sessionA)對person中id=3這行添加共享鎖后，事務(wù)B(sessionB)既能獲取person表的共享鎖，也能獲取person表id=3的寫鎖，即select和update where id=3都操作成功；

因此，在MyISAM引擎中其實不存在共享鎖。

3.總結(jié)

通過上述示例及其運行結(jié)果可以看出：

共享鎖是InnoDB存儲引擎的行級鎖，在MyISAM存儲引擎中不存在；

共享鎖是盡管是行級別鎖，但是當鎖加在整個表時（表中所有的行，一種特殊的行），排他鎖也會在表級別生效；

三、排它鎖

1.什么是排他鎖？

排它鎖（exclusive lock，X鎖），也叫寫鎖或者獨占鎖，主要是防止其它事務(wù)和當前加鎖事務(wù)鎖定同一對象，同一對象主要有兩層含義：

• 當排他鎖加在表上，則其它事務(wù)無法對該表進行insert,update,delete,alter,drop等更新操作；
• 當排他鎖加在行上，則其它事務(wù)無法對該行進行insert,update,delete,alter,drop等更新操作；

2.加鎖方式

排他鎖加鎖的方式一般有 2種：顯式加鎖和隱式加鎖，如下：

-- 顯式加鎖
select ... for update;

-- 隱式加鎖，是 MySQL內(nèi)部自動加鎖

為了更好的說明排他鎖，這里以下面的執(zhí)行順序流來進行驗證，用戶user表的結(jié)構(gòu)還是和上面的一樣：

四、舉例說明

為了更好地理解上述兩種情況，這里分別以下面的執(zhí)行順序流對InnoDB存儲引擎和MyISAM存儲引擎進行驗證：

1.InnoDB存儲引擎

(1) 給行加排他鎖

這里通過for update顯式給user表中id=6行加排他鎖為例，執(zhí)行順序流如下表：

示例執(zhí)行結(jié)果圖如下：

通過上述的示例執(zhí)行結(jié)果可以看出：當事務(wù)A(sessionA)對user中id=6這行添加共享鎖后，事務(wù)B(sessionB)和事務(wù)C(sessionC)都可以獲取user表的共享鎖，也就是select操作能成功執(zhí)行，但是事務(wù)B(SessionB)獲取user表id=6的寫鎖失敗，即delete where id=6操作被阻塞，而事務(wù)C(sessionC)獲取user表id=7的寫鎖成功，即delete where id=7操作成功；

(2) 給表加排他鎖

這里通過for update顯式方式給user整張表添加排他鎖，執(zhí)行順序流如下表：

示例執(zhí)行結(jié)果圖如下：

通過上述的示例執(zhí)行結(jié)果可以看出：當事務(wù)A(sessionA)對user整張表加排他鎖后，事務(wù)B(sessionB)可以獲取user表的共享鎖，也就是select操作能成功執(zhí)行，但是事務(wù)B(SessionB)獲取user表的排他鎖失敗，即delete操作被阻塞；

所以，盡管排他鎖（X鎖）是InnoDB存儲引擎的行級別鎖，但是一旦它作用到整張表時，其實是對表中所有的行加排他鎖。

2.MySQL 隱式加排他鎖

這里通過MySQL隱式給user的id=6行添加排他鎖，執(zhí)行順序流如下表

示例執(zhí)行結(jié)果圖如下：

通過上述的示例執(zhí)行結(jié)果可以看出：當事務(wù)A(sessionA)執(zhí)行update where id=6時，MySQL會隱式加排他鎖，事務(wù)B(sessionB)在lock in share mode模式下獲取user表id=6的共享鎖失敗，也就是select操作能成功被阻塞；

3.MyISAM引擎

MySQL 隱式加排他鎖

這里通過MySQL隱式給person的id=4行添加排他鎖，執(zhí)行順序流如下表：

示例執(zhí)行結(jié)果圖如下：

通過上述的示例執(zhí)行結(jié)果可以看出：當事務(wù)A(sessionA)執(zhí)行update where id=6時，MySQL不會隱式加排他鎖，事務(wù)B(sessionB)既能獲取id=4的共享鎖，也能獲取id=4的排他鎖；

因此，在MyISAM引擎中其實不存在排他鎖。

4.總結(jié)

通過上述 3個示例及其運行結(jié)果可以看出：排他鎖有表級別共享鎖和行級別共享鎖和自動鎖機制 3種 表級別共享鎖：

• 鎖定整個表，排他鎖也會在表級別生效；
• 行級別共享鎖：鎖定特定行，排他鎖也會在行級別生效；
• 自動鎖機制：根據(jù)操作是表級別還行級別自動加對應(yīng)的鎖；

五、共享鎖和排他鎖的兼容性矩陣

為了更好地理解共享鎖和排他鎖的互斥關(guān)系，可以參考以下兼容性矩陣：

從上述矩陣可以看出：

• 無鎖狀態(tài)下可以獲取任何類型的鎖
• 共享鎖狀態(tài)下可以繼續(xù)獲取共享鎖，但不能獲取排他鎖
• 排他鎖狀態(tài)下不能獲取任何其他鎖

六、總結(jié)

• 共享鎖（S鎖）和排他鎖（X鎖）是InnoDB存儲引擎中的 2種行級別鎖，MyISAM存儲引擎不存在。
• 盡管共享鎖（S 鎖）和排他鎖（X 鎖）是行級鎖，但是當他們加到表級別時，對表所有行都生效，這樣看上去等同表級鎖
• 共享鎖（S 鎖）允許多個事務(wù)同時讀取數(shù)據(jù)，但不允許修改數(shù)據(jù)。多個事務(wù)可以同時持有共享鎖
• 排他鎖（X 鎖）允許一個事務(wù)修改數(shù)據(jù)。只有一個事務(wù)可以持有排他鎖，并且在它釋放鎖之前，其他事務(wù)不能獲得任何類型的鎖