MySQL 加鎖范圍三——普通索引和普通字段
前言
前面已經(jīng)介紹了主鍵索引的加鎖范圍和非主鍵唯一索引的加鎖范圍。
主鍵索引：
- 加鎖時(shí)，會(huì)先給表添加意向鎖，IX 或 IS;
- 加鎖是如果是多個(gè)范圍，是分開加了多個(gè)鎖，每個(gè)范圍都有鎖;(這個(gè)可以實(shí)踐下 id < 20 的情況)
- 主鍵等值查詢，數(shù)據(jù)存在時(shí)，會(huì)對該主鍵索引的值加行鎖 X,REC_NOT_GAP;
- 主鍵等值查詢，數(shù)據(jù)不存在時(shí)，會(huì)對查詢條件主鍵值所在的間隙添加間隙鎖 X,GAP;
- 主鍵等值查詢，范圍查詢時(shí)情況則比較復(fù)雜：
- 8.0.17 版本是前開后閉，而 8.0.18 版本及以后，修改為了前開后開區(qū)間;
- 臨界 <= 查詢時(shí)，8.0.17 會(huì)鎖住下一個(gè) next-key 的前開后閉區(qū)間，而 8.0.18 及以后版本，修復(fù)了這個(gè) bug。
非主鍵唯一索引:
- 非主鍵唯一索引等值查詢，數(shù)據(jù)存在，for update 是會(huì)在主鍵加鎖的，而 for share 只有在走覆蓋索引的情況下，會(huì)僅在自己索引上加鎖;
- 非主鍵索引等值查詢，數(shù)據(jù)不存在，無論是否索引覆蓋，相當(dāng)于一個(gè)范圍查詢，僅僅會(huì)在非主鍵索引上加鎖，加的還是間隙鎖，前開后開區(qū)間;
- 在非主鍵唯一索引范圍查詢時(shí)，不是覆蓋索引的時(shí)候，會(huì)對相應(yīng)的范圍加前開后閉區(qū)間，并且如果存在數(shù)據(jù)，會(huì)對對應(yīng)的主鍵加行鎖;
- 在非主鍵唯一索引范圍查詢時(shí)，如果是覆蓋索引時(shí)，會(huì)對所有的后閉區(qū)間對應(yīng)的主鍵，加行鎖;
- 在非主鍵唯一索引加鎖時(shí)，還是存在 next-key 鎖住下一個(gè)區(qū)間的 bug。
這篇文章來一起看一下普通索引和普通字段的加鎖范圍是什么?
1數(shù)據(jù)庫表數(shù)據(jù)
- CREATE TABLE `t` (
- `id` int NOT NULL COMMENT '主鍵',
- `a` int DEFAULT NULL COMMENT '唯一索引',
- `c` int DEFAULT NULL COMMENT '普通索引',
- `d` int DEFAULT NULL,
- PRIMARY KEY (`id`),
- UNIQUE KEY `uniq_a` (`a`),
- KEY `idx_c` (`c`)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
數(shù)據(jù)庫數(shù)據(jù)如下：
思路和非主鍵唯一索引相同，只不過唯一的區(qū)別是這里看的是 c 和 d 字段。
因?yàn)榍懊嫘』锇閷?data_locks 應(yīng)該有了一定的了解，這里就直接分析 data_locks 的數(shù)據(jù)信息。
2普通索引
普通索引等值查詢 —— 數(shù)據(jù)存在
- mysql> begin; select * from t where c = 210 for update;
直接分析 data_locks
- 表意向鎖;
- 索引 idx_c 上添加了 210 區(qū)間的前開后閉鎖;
- 索引 idx_c 上添加了 215 區(qū)間的間隙鎖，LOCK_MODE 為 X,GAP;
- 主鍵上添加了 15 的行鎖 ，LOCK_MODE 為 X,REC_NOT_GAP。
主要是因?yàn)槠胀ㄋ饕荒芪ㄒ绘i定一條記錄，所以要鎖定該字段的前后范圍。
普通索引等值查詢 —— 數(shù)據(jù)不存在
- mysql> begin; select * from t where c = 211 for update;
直接分析 data_locks
- 表意向鎖;
- 索引 idx_c 上添加了 215 區(qū)間的間隙鎖。
分析是因?yàn)閿?shù)據(jù)不存在，只需要鎖住 215 間隙就可以了，因?yàn)?215 和 210 肯定不屬于這個(gè)范圍。
普通索引范圍查詢
- mysql> begin; select * from t where c > 210 and c <= 215 for update;
對于鎖住 idx_c 索引的 215 的前開后閉區(qū)間是可以理解的，但是鎖住了 220 就不太理解了，應(yīng)該也是那個(gè) bug 沒有完全修復(fù)。
普通字段
普通字段就更好理解了。
對普通字段而言，無論是哪個(gè)查詢，都需要掃描全部記錄，所以這個(gè)鎖直接加在了主鍵上，并且是鎖住全部的區(qū)間。
3總結(jié)
本文在基于第一篇和第二篇的基礎(chǔ)上，直接通過分析 data_locks 的信息，進(jìn)行判斷加鎖范圍。
- select * from performance_schema.data_locks;
| LOCK_MODE | LOCK_DATA | 鎖范圍 |
|---|---|---|
| X,REC_NOT_GAP | 15 | 15 那條數(shù)據(jù)的行鎖 |
| X,GAP | 15 | 15 那條數(shù)據(jù)之前的間隙，不包含 15 |
| X | 15 | 15 那條數(shù)據(jù)的間隙，包含 15 |
- LOCK_MODE = X 是前開后閉區(qū)間;
- X,GAP 是前開后開區(qū)間(間隙鎖);
- X,REC_NOT_GAP 行鎖。
從而得出普通索引和普通字段的結(jié)論。
普通索引
普通索引等值查詢，因?yàn)椴荒艽_定唯一性，所以即使定位到記錄，也是會(huì)向后查詢，直到查詢到不為該值的記錄，從而鎖定該值的區(qū)間;
普通索引的鎖也是加載該索引上的，如果涉及到存在的記錄，會(huì)對該主鍵加行鎖;
普通索引的范圍查詢，同樣出現(xiàn) next-key 查詢下一個(gè)區(qū)間的 bug。
普通字段
普通字段查詢，會(huì)查詢?nèi)恚@里鎖的話就會(huì)鎖住主鍵的所有區(qū)間。
本文轉(zhuǎn)載自微信公眾號(hào)「程序員小航」，可以通過以下二維碼關(guān)注。轉(zhuǎn)載本文請聯(lián)系程序員小航公眾號(hào)。
