聊聊Select for update到底加了什么鎖
前言
大家，我是田螺。
最近在開發需求的時候，用到了select...for update。在代碼評審的時候，一位同事說 ，唯一索引+一個非索引字段，是否可能會鎖全表呢？本文田螺哥將通過9個實驗操作的例子，給大家驗證select...for update到底加了什么鎖，是表鎖還是行鎖。
這是本文的提綱哈：
因為加鎖是跟數據庫的隔離級別息息相關的。而常用的數據庫隔離級別也就RC(讀已提交)和RR(可重復讀)，所以本文分別根據RC(讀已提交) 和 RR(可重復讀)隔離級別展開講述。
1. 環境準備
設置數據庫隔隔離級別
自動提交關閉
建表語句
初始化數據(接下來的實驗證明，都是基于這幾條初始數據)
MYSQL 版本
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 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排他鎖（行鎖，對于主鍵索引）
我們執行語句，查詢一下到底加的是什么鎖。
因此在RC隔離級別下，如果條件是主鍵，那么select......for update加的就是兩把鎖，一把IX意向排他鎖（不影響插入），一把對應主鍵的X排他鎖（行鎖，會鎖住主鍵索引那一行）。
2.3 RC 隔離級別 + 普通索引
在RC 隔離級別下，如果select......for update的查詢條件是普通索引，加的又是什么鎖呢？
我們這里先給原來表加上普通索引：
我們執行語句：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的查詢條件是無索引，主要還是行鎖。我們看下具體的加鎖情況：
發現一共加了兩把鎖，分別是：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排他鎖（行鎖，影響對應主鍵那一行的插入）。
我們通過語句確認一下，先輸入一下語句：
大家可以看下，跟我們的推測是一樣的：
3.3 RR 隔離級別 + 普通索引
在RR隔離級別下，如果select...for update的查詢條件是普通索引的話，除了會加X鎖，IX鎖，還會加Gap 鎖。
Gap鎖的提出，是為了解決幻讀問題引入的，它是一種加在兩個索引之間的鎖。
我們來驗證一下，先開始事務一，執行一下操作：
接著開啟事務二
驗證流程圖如下：
大家可以發現，插入新記錄，會被阻塞，那是因為有間隙鎖的緣故，我們再看下到底加了哪些鎖：
發現相對于RC隔離級別，確實多了間隙鎖，鎖住范圍了。我畫一下這種場景的加鎖示意圖：
如果要插入汕頭城市的記錄，會被Gap鎖鎖住了，因此會阻塞。
因此，在RR隔離級別下，如果select...for update的查詢條件是普通索引的話，命中查詢記錄的話，除了會加X鎖（行鎖），IX鎖（表鎖，不影響插入），還會加Gap 鎖（間隙鎖，會影響插入）。
大家如果對間隙鎖這塊不是很熟悉的話，可以看下我以前的這篇文章哈，兩萬字詳解！InnoDB鎖專題！
3.4 RR隔離級別 + 無索引
在RR隔離級別下，如果select...for update的查詢條件是無索引的話，會鎖全表嘛？來一起驗證一下
我們直接按順序執行以下這些語句：
可以發現加了這么多鎖：
一共五把鎖，這個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的虛擬全表行鎖。這種場景，通俗點講，其實就是鎖表了。
我們來做個實驗，驗證虛擬全表行鎖的存在，先開啟事務一，執行：
然后開啟事務二，執行一個插入語句:
大家可以看下，阻塞了，:
加餐
大家有沒有發現，田螺哥列舉RR數據庫隔離級別的例子，select...for update條件都是命中數據庫表記錄的。在這里，田螺哥給大家出道題。在RR隔離級別下，如果select...for update的查詢條件，沒命中當前數據表記錄的話，又加什么鎖呢？
我們來搞點刺激的，select...for update 搞兩個條件：一個唯一索引(user_name) + 一個無索引(status)，然后沒命中當前數據表記錄，你覺得會加什么鎖呢?
我們按順序執行者幾條語句：
最后
我是撿田螺的小男孩。通過本文，大家學到哪些知識點呢？
- select...for update在不同場景，都加了什么鎖。
- 如何查看一個SQL 加了什么鎖 （執行完原生SQL，再執行SELECT * FROM performance_schema.data_lock
- 如何手寫個死鎖 (分別開兩個事務，制造鎖沖突，文章的例子，好多都是死鎖的case)
