MySQL 核心模塊揭秘—主鍵索引范圍查詢加什么鎖？
1. 準(zhǔn)備工作
創(chuàng)建測試表：
CREATE TABLE `t1` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`i1` int DEFAULT '0',
PRIMARY KEY (`id`) USING BTREE,
KEY `idx_i1` (`i1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;插入測試數(shù)據(jù)：
INSERT INTO `t1` (`id`, `i1`) VALUES
(10, 101), (20, 201), (30, 301), (40, 401);2. 可重復(fù)讀
把事務(wù)隔離級別設(shè)置為 REPEATABLE-READ（如已設(shè)置，忽略此步驟）：
SET transaction_isolation = 'REPEATABLE-READ';
-- 確認(rèn)設(shè)置成功
SHOW VARIABLES like 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name | Value |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+執(zhí)行以下 select 語句：
begin;
select * from t1 ignore index(idx_i1)
where id >= 10 and id < 30 for share;查看加鎖情況：
select
engine_transaction_id, object_name, index_name,
lock_type, lock_mode, lock_status, lock_data
from performance_schema.data_locks
where object_name = 't1'
and lock_type = 'RECORD'\G
***************************[ 1. row ]***************************
engine_transaction_id | 281479856983976
object_name | t1
index_name | PRIMARY
lock_type | RECORD
lock_mode | S,REC_NOT_GAP
lock_status | GRANTED
lock_data | 10
***************************[ 2. row ]***************************
engine_transaction_id | 281479856983976
object_name | t1
index_name | PRIMARY
lock_type | RECORD
lock_mode | S
lock_status | GRANTED
lock_data | 20
***************************[ 3. row ]***************************
engine_transaction_id | 281479856983976
object_name | t1
index_name | PRIMARY
lock_type | RECORD
lock_mode | S,GAP
lock_status | GRANTED
lock_data | 30lock_data = 10、lock_mode = S,REC_NOT_GAP 表示對主鍵索引中 <id = 10> 的記錄加了共享普通記錄鎖。
沒有按照默認(rèn)行為加共享 Next-Key 鎖，是因?yàn)槭纠?SQL 使用主鍵索引進(jìn)行范圍掃描，從 <id = 10> 的記錄開始，不關(guān)心它前面的記錄。
示例 SQL 并不在意其它事務(wù)往 <id = 10> 的記錄前面插入什么記錄，不需要鎖住它前面的間隙，加普通記錄鎖就可以了。
如果有其它事務(wù)往 <id = 10> 的記錄前面的間隙插入記錄，示例 SQL 還能保證可重復(fù)讀嗎？
這個是沒問題的。
因?yàn)槠渌聞?wù)往 <id = 10> 的記錄前面的間隙插入記錄，這些記錄的 id 字段值一定小于 10，在示例 SQL 的 where 條件覆蓋范圍之外，不影響示例 SQL 的可重復(fù)讀。
其它事務(wù)要往 t1 表中插入記錄，id 大于等于 10、小于等于 19 的記錄都會插入到 <id = 10> 和 <id = 20> 之間的間隙。這個間隙不歸 <id = 10> 的記錄上的鎖管轄。
當(dāng)然了，因?yàn)榇嬖谥麈I索引，t1 表中 <id = 10> 的記錄刪除之前，其它事務(wù)想要再插入 <id = 10> 的記錄是不可能的。
lock_data = 20、lock_mode = S 表示對主鍵索引中 <id = 20> 的記錄加了共享 Next-Key 鎖，這是可重復(fù)讀隔離級別下的默認(rèn)行為，不多解釋。
lock_data = 30、lock_mode = S,GAP 表示對主鍵索引中 <id = 30> 的記錄加了共享間隙鎖。
沒有按照默認(rèn)行為加共享 Next-Key 鎖，是因?yàn)?<id = 30> 的記錄位于示例 SQL 的 where 條件覆蓋范圍之外。
示例 SQL 不關(guān)心 <id = 30> 的記錄本身，只需要保證其它事務(wù)不能往這條記錄前面的間隙插入記錄，加共享間隙加就滿足需求了。
3. 讀已提交
把事務(wù)隔離級別設(shè)置為 READ-COMMITTED（如已設(shè)置，忽略此步驟）：
SET transaction_isolation = 'READ-COMMITTED';
-- 確認(rèn)設(shè)置成功
SHOW VARIABLES like 'transaction_isolation';
+-----------------------+----------------+
| Variable_name | Value |
+-----------------------+----------------+
| transaction_isolation | READ-COMMITTED |
+-----------------------+----------------+執(zhí)行以下 select 語句：
begin;
select * from t1 ignore index(idx_i1)
where id >= 10 and id < 30 for share;查看加鎖情況：
select
engine_transaction_id, object_name, index_name,
lock_type, lock_mode, lock_status, lock_data
from performance_schema.data_locks
where object_name = 't1'
and lock_type = 'RECORD'\G
***************************[ 1. row ]***************************
engine_transaction_id | 281479856983976
object_name | t1
index_name | PRIMARY
lock_type | RECORD
lock_mode | S,REC_NOT_GAP
lock_status | GRANTED
lock_data | 10
***************************[ 2. row ]***************************
engine_transaction_id | 281479856983976
object_name | t1
index_name | PRIMARY
lock_type | RECORD
lock_mode | S,REC_NOT_GAP
lock_status | GRANTED
lock_data | 20lock_data = 10、lock_mode = S,REC_NOT_GAP 表示對主鍵索引中 <id = 10> 的記錄加了共享普通記錄鎖，這是讀已提交隔離級別的默認(rèn)行為，不多解釋。
lock_data = 20、lock_mode = S,REC_NOT_GAP 表示對主鍵索引中 <id = 20> 的記錄加了共享普通記錄鎖，這是讀已提交隔離級別的默認(rèn)行為，不多解釋。
可重復(fù)讀隔離級別對主鍵索引中 <id = 30> 的記錄加了鎖，讀已提交隔離級別為什么沒有對主鍵索引中 <id = 30> 的記錄加鎖呢？
其實(shí)讀已提交隔離級別下，InnoDB 從主鍵索引中讀取 <id = 30> 的記錄之后，也會加共享普通記錄鎖。
InnoDB 把這條記錄返回給 server 層之后，server 層判斷這條記錄不匹配 where 條件，會通知 InnoDB 釋放這條記錄上剛剛加的共享普通記錄鎖。
我們最終看到的結(jié)果就是示例 SQL 沒有對主鍵索引中 <id = 30> 的記錄加鎖。
這種加了鎖又釋放的方式，一般情況下沒什么影響，但是如果因?yàn)檫@種方式造成了死鎖，我們不了解這個邏輯，就會有點(diǎn)摸不著頭腦了。
4. 總結(jié)
可重復(fù)讀隔離級別下，對某條記錄加了鎖，要等到事務(wù)提交或者回滾時(shí)才釋放。
讀已提交隔離級別下，對某條記錄加了鎖，如果 server 層或者 InnoDB 發(fā)現(xiàn)記錄不匹配 where 條件，會馬上釋放鎖。
