前言

大家，我是田螺。

最近在開發(fā)需求的時(shí)候，用到了select...for update。在代碼評審的時(shí)候，一位同事說 ，唯一索引+一個(gè)非索引字段，是否可能會(huì)鎖全表呢？本文田螺哥將通過9個(gè)實(shí)驗(yàn)操作的例子，給大家驗(yàn)證select...for update到底加了什么鎖，是表鎖還是行鎖。

這是本文的提綱哈：

因?yàn)榧渔i是跟數(shù)據(jù)庫的隔離級別息息相關(guān)的。而常用的數(shù)據(jù)庫隔離級別也就RC(讀已提交)和RR(可重復(fù)讀)，所以本文分別根據(jù)RC(讀已提交) 和 RR(可重復(fù)讀)隔離級別展開講述。

1. 環(huán)境準(zhǔn)備

設(shè)置數(shù)據(jù)庫隔隔離級別

mysql> set global TRANSACTION ISOLATION level read COMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-COMMITTED          |
+-------------------------+
1 row in set (0.00 sec)

自動(dòng)提交關(guān)閉

mysql> set @@autocommit=0;  //設(shè)置自動(dòng)提交關(guān)閉
Query OK, 0 rows affected (0.00 sec)

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0 |
+--------------+
1 row in set (0.00 sec)

建表語句

CREATE TABLE `user_info_tab` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_name` varchar(255) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `city` varchar(255) DEFAULT NULL,
  `status` varchar(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_user_name` (`user_name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1570072 DEFAULT CHARSET=utf8mb3;

初始化數(shù)據(jù)(接下來的實(shí)驗(yàn)證明，都是基于這幾條初始數(shù)據(jù))

insert into user_info_tab(`user_name`,`age`,`city`,`status`) values('杰倫',18,'深圳','1');
insert into user_info_tab(`user_name`,`age`,`city`,`status`) values('奕迅',26,'湛江','0');
insert into user_info_tab(`user_name`,`age`,`city`,`status`) values('俊杰',28,'廣州','1');

MYSQL 版本

mysql> select @@version;  
+-----------+
| @@version |
+-----------+
| 8.0.31    |
+-----------+
1 row in set (0.00 sec)

2.RC 隔離級別

2.1 RC隔離級別 + 唯一索引

先把隔離級別設(shè)置為RC，因?yàn)閡ser_name為唯一索引，我們使用user_name為條件去執(zhí)行select......for update語句，然后開啟另外一個(gè)事務(wù)去更新數(shù)據(jù)同一條數(shù)據(jù)，發(fā)現(xiàn)被阻塞了。如下圖：

事務(wù)二的更新語句為什么會(huì)阻塞呢？

因?yàn)槭聞?wù)一的select......for update已經(jīng)加了鎖。那加的是行鎖還是表鎖呢？如果加的是表鎖的話，我們更新其他行的記錄的話，應(yīng)該是也會(huì)阻塞的，如果是行鎖的話，更新其他記錄是可以順利執(zhí)行的。

大家可以再看下這個(gè)圖：

通過實(shí)驗(yàn)，可以發(fā)現(xiàn):如果事務(wù)中是更新其他行記錄的話，是可以順利執(zhí)行的。因此在RC隔離級別下，如果條件是唯一索引，那么select...for update加的應(yīng)該是行鎖。

有些小伙伴會(huì)很好奇，到底加了什么鎖呢？ 接下來帶大家看看，具體加的是什么鎖。

我用的MySQL版本是8.0+,用這個(gè)語句查看：

SELECT * FROM performance_schema.data_locks\G;

如下圖，select * from user_info_tab where user_name ='杰倫' for update語句一共加了三把鎖，分別是 IX意向排他鎖（表級別的鎖，不影響插入）、兩把X排他鎖（行鎖，分別對應(yīng)唯一索引，主鍵索引）

有些讀者朋友說，這里不是加了IX表鎖嘛？為什么不阻塞其他行的更新？ 其實(shí)這個(gè)是意向排他鎖。

意向排他鎖：簡稱IX鎖，當(dāng)事務(wù)準(zhǔn)備在某條記錄上加上X鎖時(shí)，需要在表級別加一個(gè)IX鎖。如select ... for update，要給表設(shè)置IX鎖;

那既然有表鎖，為啥事務(wù)二在執(zhí)行其他行的更新語句時(shí)，并不會(huì)阻塞，這是因?yàn)椋阂庀蜴i僅僅表明意向的鎖，意向鎖之間不會(huì)互斥，是可以并行的。，鎖的兼容性如下：

有些小伙伴可能還有疑問，為啥會(huì)有兩把的X鎖呢？ 不是都鎖住的是同一行嘛？其實(shí)RC隔離級別下，唯一索引的加鎖是這樣的：

為什么不是唯一索引上加X鎖就可以了呢？為什么主鍵索引上的記錄也要加鎖呢？

如果并發(fā)的一個(gè)SQL，通過唯一索引條件，來更新主鍵索引：update user_info_tab set user_name = '學(xué)友' where id = '1570068';此時(shí)，如果select...for update語句沒有將主鍵索引上的記錄加鎖，那么并發(fā)的update就會(huì)感知不到select...for update語句的存在，違背了同一記錄上的更新/刪除需要串行執(zhí)行的約束。

大家如果對于鎖這塊基礎(chǔ)知識，還是有疑問的話，可以看下我之前的這篇文章哈：兩萬字詳解！InnoDB鎖專題！

2.2 RC 隔離級別 + 主鍵

在RC 隔離級別下，如果select...for update的查詢條件是主鍵id，加的又是什么鎖呢？

我們執(zhí)行語句：select * from user_info_tab where id ='1570070' for update;然后開啟另外一個(gè)事務(wù)去更新數(shù)據(jù)同一條數(shù)據(jù)，發(fā)現(xiàn)被阻塞了。如下圖：

事務(wù)二更新的是其他行的記錄，則是可以順利執(zhí)行的，如下圖：

通過實(shí)驗(yàn)，可以發(fā)現(xiàn):

如果事務(wù)中是更新其他行記錄，是可以順利執(zhí)行的話。在RC隔離級別下，如果條件是主鍵，那么select...for update鎖的也是行。

根據(jù)2.1小節(jié)的結(jié)論，select...for update都會(huì)加個(gè)表級別的IX意向排他鎖。所以，查詢條件是id的話，select...for update會(huì)加兩把鎖，分表是IX意向排他鎖（表鎖，不影響插入）、一把X排他鎖（行鎖，對于主鍵索引）

我們執(zhí)行語句，查詢一下到底加的是什么鎖。

begin;
select * from user_info_tab where id ='1570070' for update;
SELECT * FROM performance_schema.data_locks\G;

因此在RC隔離級別下，如果條件是主鍵，那么select......for update加的就是兩把鎖，一把IX意向排他鎖（不影響插入），一把對應(yīng)主鍵的X排他鎖（行鎖，會(huì)鎖住主鍵索引那一行）。

2.3 RC 隔離級別 + 普通索引

在RC 隔離級別下，如果select......for update的查詢條件是普通索引，加的又是什么鎖呢？

我們這里先給原來表加上普通索引：

alter table user_info_tab add index idx_city (city);

我們執(zhí)行語句：select * from user_info_tab where city ='廣州' for update;然后開啟另外一個(gè)事務(wù)去更新同一條數(shù)據(jù)，發(fā)現(xiàn)被阻塞了。如下圖：

如果事務(wù)二更新的是其他行的記錄，還是可以順利執(zhí)行的，如下圖：

我們看一下select * from user_info_tab where city ='廣州' for update;到底加了什么鎖，如下圖：

發(fā)現(xiàn)一共加了三把鎖，分別是：IX意向排他鎖（表鎖）、兩把X排他鎖（行鎖，分別對應(yīng)普通索引的X鎖，對應(yīng)主鍵的X鎖）。

如果查詢條件，沒有命中數(shù)據(jù)庫表的記錄，又加什么鎖呢？

我們把查詢條件改一下：select * from user_info_tab where city ='廣州' and status='0' for update;

發(fā)現(xiàn)只加了一把鎖，即IX意向排他鎖（表鎖，不影響插入）。

2.4 RC 隔離級別 + 無索引

在RC 隔離級別下，如果select...for update的查詢條件是無索引呢，加的又是什么鎖呢？

多數(shù)讀者憑感覺都是鎖表了，我們來驗(yàn)證一下。

我們執(zhí)行語句：select * from user_info_tab where age ='26' for update;（age是沒有加索引的），然后開啟另外一個(gè)事務(wù)去更新數(shù)據(jù)。如下圖：

由上圖可以發(fā)現(xiàn)，事務(wù)一 先執(zhí)行select......for update，然后事務(wù)二先更新別的行，發(fā)現(xiàn)可以順利執(zhí)行，如果執(zhí)行for update的同一行，還是會(huì)阻塞等待。

可推出結(jié)論，select...for update的查詢條件是無索引，主要還是行鎖。我們看下具體的加鎖情況：

SELECT * FROM performance_schema.data_locks\G;

發(fā)現(xiàn)一共加了兩把鎖，分別是：IX意向排他鎖（表鎖）、一把X排他鎖（行鎖，對應(yīng)主鍵的X鎖）。

為什么不是一個(gè)鎖表的X鎖呢？ 這是因?yàn)?

若age列上沒有索引，MySQL會(huì)走聚簇(主鍵)索引進(jìn)行全表掃描過濾。每條記錄都會(huì)加上X鎖。但是，為了效率考慮，MySQL在這方面進(jìn)行了改進(jìn)，在掃描過程中，若記錄不滿足過濾條件，會(huì)進(jìn)行解鎖操作。同時(shí)優(yōu)化違背了2PL原則。

3.RR 隔離級別

3.1  RR隔離級別 + 唯一索引

如果是RR（可重復(fù)）的數(shù)據(jù)庫隔離級別呢，select...for update的查詢條件是唯一索引的話，加的又是什么鎖呢？

我們知道RR隔離級別比RC隔離級別，主要差異還是有間隙鎖這個(gè)概念。接下來我們還是通過實(shí)驗(yàn)去驗(yàn)證，先把數(shù)據(jù)庫隔離級別設(shè)置為RR：

mysql> set global transaction isolation level repeatable read; (設(shè)置完好像要重啟一下)

Query OK, 0 rows affected (0.00 sec)

我們執(zhí)行語句：select * from user_info_tab where user_name ='杰倫' for update;（user_name是唯一索引的），然后開啟另外一個(gè)事務(wù)去更新數(shù)據(jù)。如下圖：

由上圖可以發(fā)現(xiàn)，即使是RR數(shù)據(jù)庫隔離級別，事務(wù)一先執(zhí)行select...for update，然后事務(wù)一先更新別的行，發(fā)現(xiàn)可以順利執(zhí)行，如果執(zhí)行更新for update的那一行，還是會(huì)阻塞超時(shí)。

再去看下具體加了什么鎖：

大家可以發(fā)現(xiàn)，不管是RC隔離級別還是RR隔離級別，select...for update，查詢條件是唯一索引，命中數(shù)據(jù)庫表記錄時(shí)，一共會(huì)加三把鎖：一把IX意向排他鎖 （表鎖，不影響插入），一把對應(yīng)主鍵的X排他鎖（行鎖），一把對應(yīng)唯一索引的X排他鎖 （行鎖）。

3.2 RR 隔離級別 + 主鍵

如果是 RR（可重復(fù)讀）的數(shù)據(jù)庫隔離級別，select...for update的查詢條件是主鍵的話，加的又是什么鎖呢？

根據(jù)前面的實(shí)驗(yàn)結(jié)果，我們其實(shí)可以推測得出來了，應(yīng)該跟RC隔離級別一樣，會(huì)加兩把鎖：一把IX意向排他鎖（表鎖，不影響插入），一把對應(yīng)主鍵的X排他鎖（行鎖，影響對應(yīng)主鍵那一行的插入）。

我們通過語句確認(rèn)一下，先輸入一下語句：

begin;
select * from user_info_tab where id ='1570070' for update;
SELECT * FROM performance_schema.data_locks\G;

大家可以看下，跟我們的推測是一樣的：

3.3 RR 隔離級別 + 普通索引

在RR隔離級別下，如果select...for update的查詢條件是普通索引的話，除了會(huì)加X鎖，IX鎖，還會(huì)加Gap 鎖。

Gap鎖的提出，是為了解決幻讀問題引入的，它是一種加在兩個(gè)索引之間的鎖。

我們來驗(yàn)證一下，先開始事務(wù)一，執(zhí)行一下操作：

begin;
select * from user_info_tab where city ='廣州' for update;

接著開啟事務(wù)二

begin;
insert into user_info_tab(`user_name`,`age`,`city`,`status`) values('方燕',27,'汕頭','1');

驗(yàn)證流程圖如下：

大家可以發(fā)現(xiàn)，插入新記錄，會(huì)被阻塞，那是因?yàn)橛虚g隙鎖的緣故，我們再看下到底加了哪些鎖：

發(fā)現(xiàn)相對于RC隔離級別，確實(shí)多了間隙鎖，鎖住范圍了。我畫一下這種場景的加鎖示意圖：

如果要插入汕頭城市的記錄，會(huì)被Gap鎖鎖住了，因此會(huì)阻塞。

因此，在RR隔離級別下，如果select...for update的查詢條件是普通索引的話，命中查詢記錄的話，除了會(huì)加X鎖（行鎖），IX鎖（表鎖，不影響插入），還會(huì)加Gap 鎖（間隙鎖，會(huì)影響插入）。

大家如果對間隙鎖這塊不是很熟悉的話，可以看下我以前的這篇文章哈，兩萬字詳解！InnoDB鎖專題！

3.4  RR隔離級別 + 無索引

在RR隔離級別下，如果select...for update的查詢條件是無索引的話，會(huì)鎖全表嘛？來一起驗(yàn)證一下

我們直接按順序執(zhí)行以下這些語句：

begin;
select * from user_info_tab where age ='26' for update;
select OBJECT_NAME,INDEX_NAME, LOCK_TYPE,LOCK_MODE, LOCK_STATUS, LOCK_DATA FROM performance_schema.data_locks;

可以發(fā)現(xiàn)加了這么多鎖：

一共五把鎖，這個(gè)IX鎖（表級別，意向排他鎖），我們可以理解，跟前面幾個(gè)例子的一樣。后面三把行鎖，就是把每一行的數(shù)據(jù)記錄，都加了X排他鎖（行鎖，鎖的對象對應(yīng)于主鍵Id），我們也可以理解。但是這個(gè)第二行，是一把怎么樣的X鎖呢？

我谷歌了一下,什么是supremum pseudo-record，找到了這個(gè)解釋:

For the last interval, the next-key lock locks the gap above the largest value in the index and the “supremum” pseudo-record having a value higher than any value actually in the index. The supremum is not a real index record, so, in effect, this next-key lock locks only the gap following the largest index value.

翻譯過來，大概意思就是：相當(dāng)于比索引中所有值都大，但卻不存在索引中，相當(dāng)于最后一行之后的間隙鎖。我理解就是如果查詢條件有索引的話，類似于一個(gè)（索引最大值，+無窮）的虛擬間隙鎖。

但是因?yàn)槲覀兊牟樵冏侄蝍ge并沒有索引，鎖為X鎖，lock_data值為supremum pseudo-record，它表示：全表行鎖，要走聚簇索引進(jìn)行全部掃描。

也就是說RR隔離級別下，對于select...for update，查詢條件無索引的話，會(huì)加一個(gè)IX鎖（表鎖，不影響插入），每一行實(shí)際記錄行的X鎖，還有對應(yīng)于supremum pseudo-record的虛擬全表行鎖。這種場景，通俗點(diǎn)講，其實(shí)就是鎖表了。

我們來做個(gè)實(shí)驗(yàn)，驗(yàn)證虛擬全表行鎖的存在，先開啟事務(wù)一，執(zhí)行：

begin;
select * from user_info_tab where age ='26' for update;

然后開啟事務(wù)二，執(zhí)行一個(gè)插入語句:

begin;
insert into user_info_tab(id,`user_name`,`age`,`city`,`status`) values(1,'小明',31,'北京','1');

大家可以看下，阻塞了，:

加餐

大家有沒有發(fā)現(xiàn)，田螺哥列舉RR數(shù)據(jù)庫隔離級別的例子，select...for update條件都是命中數(shù)據(jù)庫表記錄的。在這里，田螺哥給大家出道題。在RR隔離級別下，如果select...for update的查詢條件，沒命中當(dāng)前數(shù)據(jù)表記錄的話，又加什么鎖呢？

我們來搞點(diǎn)刺激的，select...for update 搞兩個(gè)條件：一個(gè)唯一索引(user_name) + 一個(gè)無索引(status)，然后沒命中當(dāng)前數(shù)據(jù)表記錄，你覺得會(huì)加什么鎖呢?

CREATE TABLE `user_info_tab` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_name` varchar(255) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `city` varchar(255) DEFAULT NULL,
  `status` varchar(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_user_name` (`user_name`) USING BTREE,
  KEY `idx_city` (`city`)
) ENGINE=InnoDB AUTO_INCREMENT=1570074 DEFAULT CHARSET=utf8mb3

我們按順序執(zhí)行者幾條語句：

begin;
select * from  user_info_tab  where user_name ='杰倫'  and status ='0'  for update ;
select OBJECT_NAME,INDEX_NAME, LOCK_TYPE,LOCK_MODE, LOCK_STATUS, LOCK_DATA FROM performance_schema.data_locks;

最后

我是撿田螺的小男孩。通過本文，大家學(xué)到哪些知識點(diǎn)呢？

1. select...for update在不同場景，都加了什么鎖。
2. 如何查看一個(gè)SQL 加了什么鎖 （執(zhí)行完原生SQL，再執(zhí)行SELECT * FROM performance_schema.data_lock
3. 如何手寫個(gè)死鎖 (分別開兩個(gè)事務(wù)，制造鎖沖突，文章的例子，好多都是死鎖的case)