背景
看到許多寫select for update是行鎖還是表鎖的文章，但每篇文章的結論好像都不太一樣。同時，是行鎖還是表鎖的問題直接影響着系統的性能，是以特意為大家調研一番，也就有了本篇文章，一共為大家彙總驗證了20個場景下的結論。
對于軟體或架構來說，特别是在有大版本更新的情況下，脫離了具體版本的結論往往是無意義的。針對這個問題，網絡上之是以有多個版本的答案，最主要的原因就是脫離MySQL的版本以及事務隔離級别。
本文就基于兩個MySQL版本（5.7.x、8.0.x）、兩種常見事務隔離級别（讀已送出、可重複讀）來逐一驗證。總共有四大類情況，20個小場景。最後，再給大家彙總一個結論性的驗證結果。大家可以收藏，已備用到時查閱對照。
通過閱讀本文，你不僅能能夠學到相關的結論，同時也提供了一套科學的實驗方法論，個人覺得後者對大家來說更為重要。
環境準備
在驗證之前，我們先準備好具體的環境和資料。
建表語句：
CREATE TABLE `user` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`user_no` varchar(16) DEFAULT NULL COMMENT '使用者編号',
`user_name` varchar(16) DEFAULT NULL COMMENT '使用者名',
`age` int(3) DEFAULT NULL COMMENT '年齡',
`address` varchar(128) DEFAULT NULL COMMENT '位址',
PRIMARY KEY (`id`),
UNIQUE KEY `un_idx_user_no` (`user_no`),
KEY `idx_user_name` (`user_name`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
初始化資料：
insert into user values(null,'0001','user01',18,'北京');
insert into user values(null,'0002','user02',19,'上海');
insert into user values(null,'0003','user03',20,'廣州');
insert into user values(null,'0004','user04',21,'深圳');
insert into user values(null,'0005','user05',22,'杭州');
資料庫版本：
版本一：
>select @@version;
5.7.22
版本二：
>select @@version;
8.0.18
查詢資料事務隔離級别：
>select @@transaction_isolation;
REPEATABLE-READ
MySQL innodb支援的四種事務隔離級别：
- READ_UNCOMMITTED：讀未送出；
- READ_COMMITTED：讀已送出，後文簡稱為RC；
- REPEATABLE_READ：可重複讀，MySQL預設的事務隔離級别。後文簡稱為RR；
- SERIALIZABLE：串行讀；
設定全局隔離級别：
set global transaction isolation level REPEATABLE READ;
set global transaction isolation level READ COMMITTED;
設定會話隔離級别：
set session transaction isolation level REPEATABLE READ;
set session transaction isolation level READ COMMITTED;
關閉自動送出：
> set @@autocommit=0; //設定自動送出關閉
在執行完鎖語句之後，可執行commit指令進行事務送出。
commit;
準備完以上資料，便可以開始每一個場景的驗證了。每個場景都起了一個編号，比如：V5.x-RR-主鍵，表示在MySQL 5.7.x，事務隔離級别為RR（可重複讀），條件字段為主鍵的場景下進行的實驗。
場景1.1：V5.x-RR-主鍵
操作：使用主鍵ID作為條件查詢，然後新開啟一個事務去更新資料。
分析思路：一，如果更新資料被阻塞，則說明加鎖成功；二，如果更新其他資料成功，則說明是行鎖，如果更新其他資料失敗則說明是表鎖。三，部分場景會測試插入操作；後續所有操作基本雷同。
執行悲觀鎖查詢：
select * from user where id = 1 for update;
執行更新操作：
update user set age = age +1 where id = 1;
此處更新操作被阻塞，說明資料鎖定成功。
在此場景下，來看一下資料庫加的什麼鎖。
當第二條語句被阻塞時，執行檢視鎖資訊語句：
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
注意，必須是正在執行第二條語句，且第二條語句處于阻塞狀态下，上述語句才能查詢到資料。
查詢結果如下：
鎖資訊
第二條記錄為for update鎖表語句，第一條記錄為單純的update語句。可以看出，此場景下，lock_mode為X，lock_type為RECORD，lock_data為1。
lock_mode為X（排他鎖）：即寫鎖，允許獲得排他鎖的事務更新資料，阻止其他事務取得相同資料集的共享讀鎖和排他寫鎖。
lock_type為RECORD，說是是行級鎖，lock_data表示鎖定了1條記錄。
執行更新其他記錄操作：
update user set age = age +1 where id = 2;
執行成功。
結論：當查詢條件為主鍵時，select for update為行級鎖。
當我們執行完一個場景之後，我們需要執行commit指令将目前事物送出。
場景1.2：V5.x-RR-唯一索引
執行悲觀鎖操作：
select * from user where user_no = '0001' for update;
執行更新操作：
update user set age = age +1 where id = 1;
此處更新操作被阻塞，說明資料鎖定成功。
查詢鎖資訊，同場景一的主鍵一緻。
執行更新其他記錄操作：
update user set age = age +1 where id = 2;
執行成功。
結論：當查詢條件為唯一索引時，select for update為行級鎖。
場景1.3：V5.x-RR-普通索引
執行悲觀鎖操作：
select * from user where user_name = 'user01' for update;
執行更新操作：
update user set age = age +1 where id = 1;
此處更新操作被阻塞，說明資料鎖定成功。
查詢鎖資訊：
鎖資訊
此時，鎖類型不僅僅是X排他鎖，同時還添加了GAP（間隙鎖），也就是說針對資料添加了排他間隙鎖。
執行更新其他記錄操作：
update user set age = age +1 where id = 2;
執行成功。
此時再進行一筆插入操作：
insert into user values(null,'0006','user05',23,'重慶');
執行成功。
由于存在了間隙鎖，再執行一筆user_name與查詢條件相同的插入操作：
insert into user values(null,'0008','user01',24,'成都');
執行阻塞，說明此時有排他間隙鎖的存在。
結論：當查詢條件為普通索引時，select for update為行級鎖，同時會有排他間隙鎖存在，當插入資料滿足鎖語句查詢條件（相等、範圍等）時，會發生阻塞。
場景1.4：V5.x-RR-無索引
執行悲觀鎖操作：
select * from user where address = '北京' for update;
執行更新操作：
update user set age = age +1 where id = 1;
此處更新操作被阻塞，說明資料鎖定成功。
執行更新其他記錄操作：
update user set age = age +1 where id = 2;
執行被阻塞。
此時查詢鎖表資訊展示如下：
鎖資訊
這裡比較奇怪是lock_type，很明顯，上述鎖操作已經鎖住了整張表，但lock_type依舊為RECORD。出處暫時有些費解。
結論：當查詢條件無索引時，select for update為表級鎖。
場景1.5：V5.x-RR-索引-範圍查詢
執行悲觀鎖操作：
select * from user where id > 1 for update;
執行更新操作：
update user set age = age +1 where id = 1;
執行成功，說明并沒有鎖定id為1的記錄。
執行插入操作：
insert into user values(null,'0007','user07',24,'武漢');
插入操作被阻塞。這是因為插入的資料生成的id滿足大于1的條件，會被阻塞。
所資訊如下：
鎖資訊
此時，lock_type雖然是RECORD，但是lock_data顯示supremum pseudo-record ，這就是InnoDB為了解決幻讀問題的臨鍵鎖(Next-key Lock)，這裡間隙鎖和臨鍵鎖可以看做是一樣的。
需要注意的是：supremum pseudo-record有可能是間隙鎖，需要結合死鎖日志裡的heap no判斷。heap no 1是間隙鎖。
結論：當查詢條件有索引且查詢條件為範圍時，select for update會采用間隙鎖或臨鍵鎖，對指定範圍内的資料進行加鎖。當然，當查詢條件無索引時，與場景1.4一緻，為表鎖。
場景2.1：V8.x-RR-主鍵
執行悲觀鎖查詢：
select * from user where id = 1 for update;
執行更新操作：
update user set age = age +1 where id = 1;
此處更新操作被阻塞，說明資料鎖定成功。
檢視資料庫對應的鎖：
SELECT * FROM performance_schema.data_locks;
注意，在MySQL 8中，采用了performance_schema替代了MySQL5中基于INFORMATION_SCHEMA的鎖查詢方式。
鎖資訊
上述查詢結果中，有兩條記錄。lock_type字段展示鎖範圍，lock_mode字段展示了鎖的類型。可以看到，該SQL語句先是在表範圍上加了一把IX（意向排他鎖，表鎖）。然後，在記錄（Record）範圍上添加了一把X（排他鎖），一把REC_NOT_GAP（行鎖），綜合起來就是對這條記錄添加了行級排他鎖，其他事務不能夠再對其添加任何鎖了。
這裡，既然在表的層面上添加了IX（意向排他鎖），為什麼不是表鎖呢？這是因為意向排他鎖的作用僅僅表名意向的鎖，當其他事務要對全表的資料進行加鎖時，那麼就不需要判斷每一條資料是否被加鎖了。
事務在給一行記錄加排他鎖前，必須先取得該表的IX鎖，意向排他鎖之間互相相容，可以并行，不會産生沖突。意向排他鎖存在的意義是為了更高效的擷取表鎖，主要目的是顯示事務正在鎖定某行或者試圖鎖定某行。
繼續實驗，執行更新其他記錄操作：
update user set age = age +1 where id = 2;
執行成功。
結論：當查詢條件為主鍵時，select for update為行級鎖。
場景2.2：V8.x-RR-唯一索引
執行悲觀鎖操作：
select * from user where user_no = '0001' for update;
執行更新操作：
update user set age = age +1 where id = 1;
此處更新操作被阻塞，說明資料鎖定成功。
查詢鎖資訊：
鎖資訊
此時，可以看到三把鎖，一把表級别的IX鎖，一把基于唯一索引的行級排他鎖，一把基于主鍵的行級排他鎖。
執行更新其他記錄操作：
update user set age = age +1 where id = 2;
執行成功。
結論：當查詢條件為唯一索引時，select for update為行級鎖。
場景2.3：V8.x-RR-普通索引
執行悲觀鎖操作：
select * from user where user_name = 'user01' for update;
執行更新操作：
update user set age = age +1 where id = 1;
此處更新操作被阻塞，說明資料鎖定成功。
查詢鎖資訊：
鎖資訊
此時，可以看到四把鎖，一把表級别的IX鎖，一把基于普通索引的X排他鎖，一把基于主鍵的行級排他鎖，一把基于普通索引的X,GAP排他間隙鎖。
執行更新其他記錄操作：
update user set age = age +1 where id = 2;
執行成功，說明更新操作沒有影響。
既然有排他間隙鎖，此時需再測試一筆插入操作：
insert into user values(null,'0006','user05',23,'重慶');
執行成功。
再執行一筆插入操作：
insert into user values(null,'0007','user01',24,'武漢');
注意這裡插入的記錄user_name與鎖查詢條件相同，發現操作被阻塞。
通過兩筆插入操作可以看出，排他間隙鎖會阻塞符合查詢條件（user_name='user01'）的資料的插入。
結論：當查詢條件為普通索引時，select for update為行級鎖，同時會多一把排他間隙鎖，如果插入資料滿足鎖語句的查詢條件（等于、範圍條件等），則無法插入。
場景2.4：V8.x-RR-無索引
執行悲觀鎖操作：
select * from user where address = '北京' for update;
執行更新操作：
update user set age = age +1 where id = 1;
此處更新操作被阻塞，說明資料鎖定成功。
查詢鎖資訊：
鎖資訊
此時，資料庫一共加了8把鎖，一把表級别的IX意向排他鎖，6把基于主鍵的針對資料記錄（總共6條）的X鎖，一把針對記錄的supremum pseudo-record鎖。
執行更新其他記錄操作：
update user set age = age +1 where id = 2;
執行被阻塞。
結論：當查詢條件無索引時，select for update為表級鎖。
場景2.5：V8.x-RR-索引-範圍查詢
執行悲觀鎖操作：
select * from user where id > 1 for update;
執行更新操作：
update user set age = age +1 where id = 1;
執行成功，說明并沒有鎖定id為1的記錄。
執行插入操作：
insert into user values(null,'0007','user07',24,'武漢');
插入操作被阻塞。這是因為插入的資料生成的id滿足大于1的條件，會被阻塞。
查詢鎖資訊如下：
鎖資訊
此時，鎖資訊對比場景2.4，少了一條不滿足條件記錄（id=1）的鎖，其他符合條件的資料均被鎖。
結論：當查詢條件有索引且查詢條件為範圍時，select for update會采用間隙鎖或臨鍵鎖，對指定範圍内的資料進行加鎖。
完成了上面針對RR事務隔離級别的驗證，下面将資料庫事務隔離級别切換為RC。
set global transaction isolation level READ COMMITTED;
注意，此處可能需要重新開機資料庫，如果通過指令配置無效，可通過資料庫配置檔案進行配置，重新開機。
另外，也可以通過在所有指令視窗執行session級别的設定，也可以達到效果，設定完成之後注意需要進行驗證。
場景3.1：V5.x-RC-主鍵
執行悲觀鎖查詢：
select * from user where id = 1 for update;
執行更新操作：
update user set age = age +1 where id = 1;
此處更新操作被阻塞，說明資料鎖定成功。
鎖資訊與RR事務相同。
執行更新其他記錄操作：
update user set age = age +1 where id = 2;
執行成功。
結論：當查詢條件為主鍵時，select for update為行級鎖。
場景3.2：V5.x-RC-唯一索引
執行悲觀鎖操作：
select * from user where user_no = '0001' for update;
執行更新操作：
update user set age = age +1 where id = 1;
此處更新操作被阻塞，說明資料鎖定成功。
查詢鎖資訊，與RR一緻。
執行更新其他記錄操作：
update user set age = age +1 where id = 2;
執行成功。
結論：當查詢條件為唯一索引時，select for update為行級鎖。
場景3.3：V5.x-RC-普通索引
執行悲觀鎖操作：
select * from user where user_name = 'user01' for update;
執行更新操作：
update user set age = age +1 where id = 1;
此處更新操作被阻塞，說明資料鎖定成功。
查詢鎖資訊如下：
鎖資訊
再把RR場景下的鎖資訊貼出來：
鎖資訊
可以看出，RC事務隔離級别時比RR事務隔離級别時少了一個GAP（間隙鎖）。
執行更新其他記錄操作：
update user set age = age +1 where id = 2;
執行成功。
此時再進行一筆插入操作：
insert into user values(null,'0009','user01',24,'鄭州');
執行成功。
再驗證下間隙鎖是否真的不存在，執行一筆user_name與查詢條件相同的插入操作：
insert into user values(null,'0008','user01',24,'成都');
執行成功，說明此時間隙鎖的不存在了。
結論：當查詢條件為普通索引時，select for update為行級鎖，無間隙鎖。
場景3.4：V5.x-RC-無索引
執行悲觀鎖操作：
select * from user where address = '北京' for update;
執行更新操作：
update user set age = age +1 where id = 1;
此處更新操作被阻塞，說明資料鎖定成功。
鎖資訊如下：
鎖資訊
顯示基于主鍵的排他鎖，這塊挺出乎意料的，并沒有進行表鎖。
執行更新其他記錄操作：
update user set age = age +1 where id = 2;
執行成功。
再執行一筆插入操作，插入資料與查詢條件address一緻：
insert into user values(null,'0011','user01',24,'北京');
執行成功。
結論：當查詢條件無索引時，select for update為行級鎖，也就說，在RC事務隔離級别下，即便無索引，也是隻鎖記錄，與通常的直知覺不同。
原因：會出現上述情況的原因是，本來如果鎖條件上沒有索引，MySQL會走聚簇（主鍵）索引進行全表掃描過濾，每條記錄都會添加上X鎖。但為了效率，MySQL會對掃描過程中不滿足條件的記錄進行解鎖操作。
場景3.5：V5.x-RC-索引-範圍查詢
執行悲觀鎖操作：
select * from user where id > 1 for update;
執行更新操作：
update user set age = age +1 where id = 1;
執行成功，說明并沒有鎖定id為1的記錄。
執行更新操作：
update user set age = age +1 where id = 2;
操作被阻塞。這是因為操作的資料的id滿足大于1的條件，會被阻塞。
所資訊如下：
鎖資訊
結論：當查詢條件有索引且查詢條件為範圍時，select for update對指定範圍内的資料進行加鎖。
場景4.1：V8.x-RC-主鍵
執行悲觀鎖查詢：
select * from user where id = 1 for update;
執行更新操作：
update user set age = age +1 where id = 1;
此處更新操作被阻塞，說明資料鎖定成功。
鎖資訊同RR。
繼續實驗，執行更新其他記錄操作：
update user set age = age +1 where id = 2;
執行成功。
結論：當查詢條件為主鍵時，select for update為行級鎖。
場景4.2：V8.x-RC-唯一索引
執行悲觀鎖操作：
select * from user where user_no = '0001' for update;
執行更新操作：
update user set age = age +1 where id = 1;
此處更新操作被阻塞，說明資料鎖定成功。
鎖資訊同RR。
執行更新其他記錄操作：
update user set age = age +1 where id = 2;
執行成功。
結論：當查詢條件為唯一索引時，select for update為行級鎖。
場景4.3：V8.x-RC-普通索引
執行悲觀鎖操作：
select * from user where user_name = 'user01' for update;
執行更新操作：
update user set age = age +1 where id = 1;
此處更新操作被阻塞，說明資料鎖定成功。
查詢鎖資訊：
鎖資訊
對照一下RR場景下的鎖資訊：
鎖資訊
可以看出RC場景下筆RR場景下少了一條行級間隙鎖。
執行更新其他記錄操作：
update user set age = age +1 where id = 2;
執行成功，說明更新操作沒有影響。
驗證一下是否有排他間隙鎖，此時需再測試一筆插入操作：
insert into user values(null,'0010','user05',23,'重慶');
執行成功。
再執行一筆插入操作：
insert into user values(null,'0007','user01',24,'武漢');
注意這裡插入的記錄user_name與鎖查詢條件相同，執行成功，說明真的不存在X，GAP（排他間隙鎖）。
結論：當查詢條件為普通索引時，select for update為行級鎖。
場景4.4：V8.x-RC-無索引
執行悲觀鎖操作：
select * from user where address = '北京' for update;
執行更新操作：
update user set age = age +1 where id = 1;
此處更新操作被阻塞，說明資料鎖定成功。
查詢鎖資訊：
鎖資訊
對照一下RR場景：
鎖資訊
對于RR場景，RC場景下，隻有一條排他行鎖（X,REC_NOT_GAP）。
執行更新其他記錄操作：
update user set age = age +1 where id = 2;
執行成功。
結論：當查詢條件無索引時，select for update為行級鎖。這裡的原因與場景3.4一緻。
場景4.5：V8.x-RC-索引-範圍查詢
執行悲觀鎖操作：
select * from user where id > 1 for update;
執行更新操作：
update user set age = age +1 where id = 1;
執行成功，說明并沒有鎖定id為1的記錄。
執行插入操作：
insert into user values(null,'0012','user12',24,'--');
執行成功。
查詢鎖資訊如下：
鎖資訊
對照RR場景下的鎖資訊：
鎖資訊
此時，RC場景下，少了臨鍵鎖，排他鎖也變為了行級排他鎖。
結論：當查詢條件有索引且查詢條件為範圍時，select for update會對指定範圍内的資料進行加鎖，隻會阻塞符合條件的記錄，不影響插入操作。
場景及結論
完成了上面的實驗之後，我們通過一個表格來總結一下所有的場景和結論。
版本主鍵唯一索引普通索引無索引範圍查詢MySQL 5.7.x - RRX：行鎖X，行鎖X,GAP：行鎖，間隙鎖，條件範圍内會阻塞表鎖指定範圍加鎖，insert阻塞MySQL 8.0.x - RRX,REC_NOT_GAP：行級排他鎖X,REC_NOT_GAP：行級排他鎖X；X,REC_NOT_GAP；X,GAP：行鎖+排他間隙鎖，阻塞範圍内insert；表鎖，每條記錄一個X鎖指定範圍加鎖，insert阻塞MySQL 5.7.x - RCX：行鎖X，行鎖X，行鎖，無間隙鎖；行鎖指定範圍加鎖，更新、insert阻塞MySQL 8.0.x - RCX,REC_NOT_GAP：行級排他鎖X,REC_NOT_GAP：行級排他鎖X,REC_NOT_GAP：行鎖，無間隙鎖；X,REC_NOT_GAP：行鎖指定範圍加鎖，不阻塞insert
從上面表中我們可以總結出以下結論（基于RR、RC兩種事務隔離級别）：
- 無論哪個版本的MySQL，查詢條件為主鍵、唯一索引、普通索引的情況下，為行鎖；
- 查詢條件為普通索引時，事務隔離級别為RR時，MySQL還會添加一個間隙鎖，條件内的插入、更新會被阻塞；
- 事務隔離級别為RR時，查詢條件無索引，為表鎖；
- 事務隔離級别為RC時，查詢條件無索引，為行鎖；
- 查詢條件為範圍時，有索引的情況下，除MySQL 8.0.x RC場景下不阻塞插入操作，其他場景均阻塞指定範圍更新、插入操作；
通過上面的結論，我們可以看出，并不是簡單的說“有索引就是行鎖，無索引就是表鎖”，因為在事務隔離級别為RC時，無索引，同樣表現（被優化）為行鎖。
至于，根據範圍條件（大于、小于、不等于、between、like等）查詢、查詢無結果等情況，大家可根據上述實驗方法進行自行驗證。
本文為大家提供了實驗方法，并針對常見的場景給出了結論，希望能夠幫到你，也希望大家能夠點贊、轉發、收藏，以備不時之需。