聊聊 select for update 到底加了什麼鎖

前言

大家，我是田螺。

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

這是本文的提綱哈：

因爲加鎖是跟數據庫的隔離級別息息相關的。而常用的數據庫隔離級別也就RC(讀已提交)和RR(可重複讀)，所以本文分別根據RC(讀已提交) 和 RR(可重複讀)隔離級別展開講述。

環境準備

設置數據庫隔隔離級別

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)

自動提交關閉

mysql> set @@autocommit=0;  //設置自動提交關閉
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;

初始化數據 (接下來的實驗證明，都是基於這幾條初始數據)

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 隔離級別 + 唯一索引

先把隔離級別設置爲RC，因爲user_name爲唯一索引，我們使用user_name爲條件去執行select......for update語句，然後開啓另外一個事務去更新數據同一條數據，發現被阻塞了。如下圖：

事務二的更新語句爲什麼會阻塞呢？

因爲事務一的select......for update已經加了鎖。那加的是行鎖還是表鎖呢？如果加的是表鎖的話，我們更新其他行的記錄的話，應該是也會阻塞的，如果是行鎖的話，更新其他記錄是可以順利執行的。

大家可以再看下這個圖：

通過實驗，可以發現: 如果事務中是更新其他行記錄的話，是可以順利執行的。因此在 RC 隔離級別下，如果條件是唯一索引，那麼select...for update加的應該是行鎖。

有些小夥伴會很好奇，到底加了什麼鎖呢？ 接下來帶大家看看，具體加的是什麼鎖。

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

SELECT * FROM performance_schema.data_locks\G;

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

有些讀者朋友說，這裏不是加了IX表鎖嘛？爲什麼不阻塞其他行的更新？ 其實這個是意向排他鎖。

意向排他鎖：簡稱IX鎖，當事務準備在某條記錄上加上X鎖時，需要在表級別加一個 IX 鎖。如select ... for update，要給表設置IX鎖;

那既然有表鎖，爲啥事務二在執行其他行的更新語句時，並不會阻塞，這是因爲：意向鎖僅僅表明意向的鎖，意向鎖之間不會互斥，是可以並行的。，鎖的兼容性如下：

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

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

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

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

2.2 RC 隔離級別 + 主鍵

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

我們執行語句：select * from user_info_tab where id ='1570070' for update;然後開啓另外一個事務去更新數據同一條數據，發現被阻塞了。如下圖：

事務二更新的是其他行的記錄，則是可以順利執行的，如下圖：

通過實驗，可以發現:

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

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

我們執行語句，查詢一下到底加的是什麼鎖。

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

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

2.3 RC 隔離級別 + 普通索引

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

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

alter table user_info_tab add index idx_city (city);

我們執行語句：select * from user_info_tab where city ='廣州' for update;然後開啓另外一個事務去更新同一條數據，發現被阻塞了。如下圖：

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

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

發現一共加了三把鎖，分別是：IX意向排他鎖（表鎖）、兩把X排他鎖（行鎖，分別對應普通索引的X鎖，對應主鍵的X鎖）。

如果查詢條件，沒有命中數據庫表的記錄，又加什麼鎖呢？

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

發現只加了一把鎖，即IX意向排他鎖（表鎖，不影響插入）。

2.4 RC 隔離級別 + 無索引

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

多數讀者憑感覺都是鎖表了，我們來驗證一下。

我們執行語句：select * from user_info_tab where age ='26' for update;（age 是沒有加索引的），然後開啓另外一個事務去更新數據。如下圖：

由上圖可以發現，事務一 先執行select......for update，然後事務二先更新別的行，發現可以順利執行，如果執行for update的同一行，還是會阻塞等待。

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

SELECT * FROM performance_schema.data_locks\G;

發現一共加了兩把鎖，分別是：IX意向排他鎖（表鎖）、一把X排他鎖（行鎖，對應主鍵的X鎖）。

爲什麼不是一個鎖表的 X 鎖呢？ 這是因爲:

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

3.RR 隔離級別

3.1 RR 隔離級別 + 唯一索引

如果是 RR（可重複）的數據庫隔離級別呢，select...for update的查詢條件是唯一索引的話，加的又是什麼鎖呢？

我們知道RR隔離級別比RC隔離級別，主要差異還是有間隙鎖這個概念。接下來我們還是通過實驗去驗證，先把數據庫隔離級別設置爲RR：

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

Query OK, 0 rows affected (0.00 sec)

我們執行語句：select * from user_info_tab where user_name ='杰倫' for update;（user_name 是唯一索引的），然後開啓另外一個事務去更新數據。如下圖：

由上圖可以發現，即使是 RR 數據庫隔離級別，事務一先執行select...for update，然後事務一先更新別的行，發現可以順利執行，如果執行更新for update的那一行，還是會阻塞超時。

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

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

3.2 RR 隔離級別 + 主鍵

如果是 RR（可重複讀）的數據庫隔離級別，select...for update的查詢條件是主鍵的話，加的又是什麼鎖呢？

根據前面的實驗結果，我們其實可以推測得出來了，應該跟 RC 隔離級別一樣，會加兩把鎖：一把IX意向排他鎖（表鎖，不影響插入），一把對應主鍵的X排他鎖（行鎖，影響對應主鍵那一行的插入）。

我們通過語句確認一下，先輸入一下語句：

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的查詢條件是普通索引的話，除了會加 X 鎖，IX 鎖，還會加 Gap 鎖。

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

我們來驗證一下，先開始事務一，執行一下操作：

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

接着開啓事務二

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

驗證流程圖如下：

大家可以發現，插入新記錄，會被阻塞，那是因爲有間隙鎖的緣故，我們再看下到底加了哪些鎖：

發現相對於 RC 隔離級別，確實多了間隙鎖，鎖住範圍了。我畫一下這種場景的加鎖示意圖：

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

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

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

3.4 RR 隔離級別 + 無索引

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

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

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;

可以發現加了這麼多鎖：

一共五把鎖，這個IX鎖（表級別，意向排他鎖），我們可以理解，跟前面幾個例子的一樣。後面三把行鎖，就是把每一行的數據記錄，都加了 X 排他鎖（行鎖，鎖的對象對應於主鍵Id），我們也可以理解。但是這個第二行，是一把怎麼樣的 X 鎖呢？

我谷歌了一下, 什麼是supremum pseudo-record，找到了這個解釋:

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.

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

但是因爲我們的查詢字段age並沒有索引，鎖爲X鎖，lock_data值爲supremum pseudo-record，它表示：全錶行鎖，要走聚簇索引進行全部掃描。

也就是說 RR 隔離級別下，對於select...for update，查詢條件無索引的話，會加一個IX鎖（表鎖，不影響插入），每一行實際記錄行的X鎖，還有對應於supremum pseudo-record的虛擬全錶行鎖。這種場景，通俗點講，其實就是鎖表了。

我們來做個實驗，驗證虛擬全錶行鎖的存在，先開啓事務一，執行：

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

然後開啓事務二，執行一個插入語句:

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

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

加餐

大家有沒有發現，田螺哥列舉 RR 數據庫隔離級別的例子，select...for update條件都是命中數據庫表記錄的。在這裏，田螺哥給大家出道題。在RR隔離級別下，如果select...for update的查詢條件，沒命中當前數據表記錄的話，又加什麼鎖呢？

我們來搞點刺激的，select...for update 搞兩個條件：一個唯一索引 (user_name) + 一個無索引 (status)，然後沒命中當前數據表記錄，你覺得會加什麼鎖呢?

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

我們按順序執行者幾條語句：

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;

最後

我是撿田螺的小男孩。通過本文，大家學到哪些知識點呢？

select...for update在不同場景，都加了什麼鎖。
如何查看一個 SQL 加了什麼鎖（執行完原生 SQL，再執行SELECT * FROM performance_schema.data_lock
如何手寫個死鎖 (分別開兩個事務，製造鎖衝突，文章的例子，好多都是死鎖的 case)

本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/shogIDEgXTtPVPFgsS8crw

前言