MySQL 鎖體系從 MDL到間隙鎖詳解
你寫了 SELECT … FOR UPDATE，以為只鎖了一行。RR 級別下，你可能鎖了半張表。線上改表卡死、長事務堵死、沒索引的 FOR UPDATE 變成表鎖——都是"沒搞懂 MySQL 鎖體系"吃的虧。
一、先別急著學行鎖——MySQL 有三層鎖同時存在
你寫一條 UPDATE，InnoDB 同時在三個層面上了鎖：
UPDATE user SET age = 30 WHERE id = 1; 表級：MDL 讀鎖 → 保結(jié)構不變（有人在改數(shù)據(jù)，別動表結(jié)構） 表級：意向獨占鎖 IX → 表上打個標記"里面有行被鎖了" 行級：行獨占鎖 X → 鎖住 id=1 這一行
三層鎖各管各的，互不干涉。下面一層一層拆。
二、MDL 鎖——自動保安，線上改表的頭號殺手
MDL（Metadata Lock）全自動，不用你寫：
MDL 讀鎖 ← SELECT、INSERT、UPDATE、DELETE（所有 DML，用表但不改結(jié)構） MDL 寫鎖 ← ALTER、DROP、TRUNCATE、RENAME（所有 DDL，改表結(jié)構）
MDL 讀鎖兼容讀鎖，MDL 寫鎖排斥一切。 命名上注意：INSERT/DELETE/UPDATE 雖然是"寫數(shù)據(jù)"，但對表結(jié)構來說只是"用表"，所以拿 MDL 讀鎖。
最經(jīng)典的坑：長事務改表，整庫卡死
事務A（長事務）：SELECT * FROM user; 還沒提交 → 持有 MDL 讀鎖
事務B：ALTER TABLE user ADD COLUMN xxx;
→ 需要 MDL 寫鎖 → 等 A 釋放讀鎖
事務C：SELECT * FROM user;
→ 也需要 MDL 讀鎖 → 但排在 B 后面 → 也被阻塞
→ 瞬間：這張表的所有讀寫全堵死MDL 讀鎖本來互相兼容——但寫鎖排隊的瞬間，后面的所有讀寫也跟著排隊。鎖隊列是先到先得，B 排在 C 前面，C 不能插隊。
解決方案：改表前先 SELECT 查一下有沒有長事務，或者用 LOCK_WAIT_TIMEOUT 讓改表別無限等。
三、表鎖 vs MDL vs 意向鎖——三門神的分工
| 表鎖 | MDL | 意向鎖 | |
|---|---|---|---|
| 誰加的 | 你手動 LOCK TABLES | MySQL 自動加 | InnoDB 自動加 |
| 觸發(fā)條件 | 你寫才鎖 | 任何操作都加 | DML 寫行之前 |
| 鎖什么 | 整張表的讀寫 | 表結(jié)構變更的時機 | 不鎖，只是一個標記 |
| 釋放 | UNLOCK TABLES | 事務提交 | 事務提交 |
| 互斥關系 | 排斥一切讀寫 | 讀兼容讀，寫排斥一切 | 意向之間完全兼容 |
表鎖 = 手動扔核彈（LOCK TABLES WRITE 把這表全鎖了）
MDL = 自動保安（有 DDL 才打架，DML 之間相安無事）
意向鎖 = 門口登記表（有人要鎖整表時看一眼：里面有行鎖嗎？有就等）
意向鎖的意義：有人 LOCK TABLES user WRITE 時，不用一行行去查有沒有行鎖——看一眼表上有沒有 IX 標記就行。
四、行級鎖——真正決定并發(fā)度的核心
InnoDB 有三種行級鎖，但不是你顯式選哪個，而是同一句 SQL 在 RR 下自動加的組合：
記錄鎖（Record Lock）
鎖住存在的索引記錄。所有精確命中的寫操作都會加：
UPDATE user SET age = 30 WHERE id = 5; -- 鎖 id=5 這一行 DELETE FROM user WHERE id = 5; -- 鎖 id=5 SELECT * FROM user WHERE id = 5 FOR UPDATE; -- 鎖 id=5
間隙鎖（Gap Lock）
鎖住索引記錄之間的空隙。只在 RR 級別下才加：
索引 age：15 → [間隙] → 22 → [間隙] → 25 → [間隙] → 30
↑ ↑ ↑ ↑
值有記錄鎖 空隙有間隙鎖 ...
事務A：SELECT * FROM user WHERE age BETWEEN 20 AND 30 FOR UPDATE;
事務B：INSERT INTO user VALUES(NULL, '王五', 23);
→ 23 落在 22~25 的間隙 → 被間隙鎖阻塞 → 等待間隙鎖是 RR 防幻讀的真正手段——不讓新行插進查詢范圍。
Next-Key Lock（記錄鎖 + 間隙鎖合體）
RR 級別默認就是這個：鎖住記錄 + 它前面的間隙。
-- RR 下，即使你只查一行 SELECT * FROM user WHERE id = 10 FOR UPDATE; → 加的實際上是 (上一個id, 10] 這個區(qū)間 → 前面的間隙也鎖住了
| RC | RR | |
|---|---|---|
| 記錄鎖 | ? | ? |
| 間隙鎖 | ? 不用 | ? 用 |
| Next-Key Lock | ? | ?（默認） |
| 鎖范圍 | 小 | 大 |
| 并發(fā)性能 | 高 | 低 |
五、為什么 RC 有行鎖，還是會發(fā)生不可重復讀
因為行鎖管的是寫寫互斥，不管讀。MVCC 的讀走的是另一條路：
MVCC 快照讀（無鎖） 行鎖（有鎖）
───────────────── ──────────
SELECT（純讀） UPDATE
SELECT（純讀） DELETE
SELECT ... FOR UPDATE
兩條線完全獨立！不可重復讀的發(fā)生路徑：
事務A：SELECT age → 快照讀 age=25（走左邊，無鎖，完全不管右邊在干嘛） 事務B：UPDATE age=30; COMMIT（走右邊，拿行鎖寫完提交） 事務A：SELECT age → RC 新建快照 → age=30（不可重復讀?。?
行鎖管的是"兩個寫同一行別打架"，從來不管"讀看到什么數(shù)據(jù)"。
六、沒索引的 FOR UPDATE ≈ 表鎖
InnoDB 的行鎖加在索引記錄上。沒索引怎么辦？全表掃描，掃到一行鎖一行：
-- age 沒索引 SELECT * FROM user WHERE age > 25 FOR UPDATE; → 全表掃描 → 掃到的每一行全加鎖 → age=20、15、50 都鎖 → 等效表鎖，所有寫操作全堵
所以WHERE 條件走索引不只是為了快——更是為了讓鎖的范圍盡可能小。
七、長事務的鎖是怎么一步步把數(shù)據(jù)庫拖死的
長事務持鎖不釋放，最可怕的不是死鎖，是鎖等待傳導：
事務A（10分鐘）：SELECT ... FOR UPDATE，鎖住訂單 id=100 事務B：UPDATE 訂單 id=100 → 等 A 釋放 事務C：UPDATE 訂單明細，外鍵關聯(lián) id=100 → 等 B 釋放 事務D：INSERT 訂單日志，間隙被 A 鎖了 → 等 C ... 一排車堵在單行道，頭車不動，后面全卡
死鎖還好——InnoDB 會主動檢測并回滾一個。鎖等待超時更隱蔽：業(yè)務看到的是"操作超時請重試"，用戶以為系統(tǒng)掛了。
此外，RR 的間隙鎖在高并發(fā)下是性能殺手：
-- 秒殺：100個用戶同時下單 SELECT stock FROM product WHERE id = 100 FOR UPDATE; RC：只鎖 id=100 這一行，同行的 UPDATE 排隊，INSERT 不受影響 RR：行 + 前間隙全鎖，INSERT 也被堵 → 排隊人數(shù)翻倍
這就是為什么高并發(fā)場景普遍選 RC——鎖的范圍就是并發(fā)度的上限。
八、各種寫操作觸發(fā)什么鎖（一張表總結(jié)）
| 操作 | MDL | 意向鎖 | 行級鎖 |
|---|---|---|---|
| 純 SELECT | 讀鎖 | 無 | 無 |
| SELECT … FOR UPDATE | 讀鎖 | IX | RC: 記錄鎖 / RR: Next-Key Lock |
| SELECT … LOCK IN SHARE MODE | 讀鎖 | IS | 記錄鎖（共享） |
| UPDATE | 讀鎖 | IX | 記錄鎖 / Next-Key Lock |
| DELETE | 讀鎖 | IX | 記錄鎖 / Next-Key Lock |
| INSERT | 讀鎖 | IX | 記錄鎖（鎖自己插的那行） |
| ALTER TABLE | 寫鎖 | 不涉及行鎖 | 無（MDL 寫鎖直接排他） |
純 SELECT 不加任何行鎖——MVCC 快照讀。這也是"讀不阻塞寫"的根因。
九、線上排查鎖問題的三板斧
-- 1. 看當前誰在等鎖 SELECT * FROM information_schema.INNODB_LOCK_WAITS; -- 2. 看當前持有哪些鎖 SELECT * FROM information_schema.INNODB_LOCKS; -- 5.7 SELECT * FROM performance_schema.data_locks; -- 8.0 -- 3. 找長事務（超過 60 秒的） SELECT * FROM information_schema.INNODB_TRX WHERE trx_started < NOW() - INTERVAL 60 SECOND;
總結(jié)
- 三層鎖同時存在：MDL 鎖表結(jié)構 + 意向鎖做標記 + 行鎖鎖數(shù)據(jù)，各管各的
- RC 和 RR 的核心鎖差別：RC 不要間隙鎖，RR 有間隙鎖，這是并發(fā)吞吐差好幾倍的主因
- 行鎖不保護讀：MVCC 和鎖是兩條獨立的路，不可重復讀不是行鎖失效，是讀根本沒走鎖
- 沒索引的 FOR UPDATE 就是表鎖：鎖加在索引記錄上，沒有索引就全表掃全表鎖
- 長事務的鎖會傳導放大：不是死鎖才致命，鎖等待超時更隱蔽
MDL 管結(jié)構，行鎖管數(shù)據(jù)。RC 省間隙高并發(fā)，RR 間隙鎖防幻讀。行鎖不保讀，MVCC 走快照。
到此這篇關于MySQL 鎖體系從 MDL到間隙鎖詳解的文章就介紹到這了,更多相關mysql從 mdl到間隙鎖內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！
相關文章
大家好，本篇文章主要講的是MySQL之存儲函數(shù)詳細介紹，感興趣的同學趕快來看一看吧，對你有幫助的話記得收藏一下，方便下次瀏覽2021-12-12
這篇文章主要給大家介紹了關于Mysql提權的多種姿勢,姿勢包括寫入Webshell、UDF提權以及MOF提權,文中通過示例代碼介紹的非常詳細,需要的朋友可以參考下2021-08-08
MySQL中DELETE、DROP和TRUNCATE的區(qū)別與底層原理分析
MySQL中DELETE、DROP和TRUNCATE的區(qū)別：DELETE刪除數(shù)據(jù)行并保留表結(jié)構,支持條件和事務；TRUNCATE清空表數(shù)據(jù),不支持條件但速度快；DROP徹底刪除表及結(jié)構,執(zhí)行速度為DROP＞TRUNCATE＞DELETE,存儲引擎影響空間釋放和自增計數(shù)器重置2025-07-07
這篇文章主要給大家介紹了關于MySQL高級進階sql語句的相關資料,文中通過圖文以及實例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友可以參考下2022-03-03
mysql數(shù)據(jù)庫添加用戶及分配權限具體實現(xiàn)
這篇文章主要介紹了mysql數(shù)據(jù)庫添加用戶及分配權限的方法,需要的朋友可以參考下2014-02-02
這篇文章主要介紹了Navicat連接mysql報錯問題,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2024-09-09
本文主要介紹了MySQL使用組合查詢的示例代碼,如何使用UNION操作符來組合SELECT語句,文中通過示例代碼介紹的非常詳細,需要的朋友們下面隨著小編來一起學習學習吧2024-03-03
這篇文章主要介紹了超出MySQL最大連接數(shù)問題及解決方案，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2023-05-05
MySQL權限管理是數(shù)據(jù)庫安全性的重要組成部分,它決定了哪些用戶可以對數(shù)據(jù)庫執(zhí)行哪些操作,這篇文章主要介紹了MySQL中用戶和權限管理的相關資料,文中通過代碼介紹的非常詳細,需要的朋友可以參考下2025-10-10
這篇文章主要介紹了CentOS7編譯安裝MySQL5.7.24的教程，非常不錯，具有一定的參考借鑒價值,需要的朋友可以參考下2018-12-12