01 環(huán)境準(zhǔn)備在驗證之前我們先準(zhǔn)備好具體的環(huán)境和數(shù)據(jù)事務(wù)隔離級別 RR數(shù)據(jù)庫版本 5.7.26。為了方便測試索引都是整型CREATE TABLE user ( id int(11) unsigned NOT NULL AUTO_INCREMENT, user_no int(11) NOT NULL COMMENT 用戶編號, user_name varchar(16) DEFAULT NULL COMMENT 用戶名, age int(3) DEFAULT NULL COMMENT 年齡, PRIMARY KEY (id), UNIQUE KEY un_idx_user_no (user_no), KEY idx_age (age) ) ENGINEInnoDB AUTO_INCREMENT0 DEFAULT CHARSETutf8;初始化數(shù)據(jù)insert into user values(1, 10, 樓仔, 18); insert into user values(4, 15, 二哥, 28); insert into user values(8, 20, 一灰, 38);常用命令操作 start transaction; // 開啟事務(wù) commit; // 提交事務(wù) rollback; // 回滾事務(wù) select transaction_isolation; // 查看事務(wù)隔離級別 select version; // 查看數(shù)據(jù)庫版本 SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS; // 查詢鎖02 場景分類2.1 主鍵有值說明主鍵查詢查詢數(shù)據(jù)存在。執(zhí)行悲觀鎖查詢select * from user where id 1 for update;執(zhí)行更新操作被鎖住了update user set user_name 樓仔小弟 where id 1; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction查看鎖信息lock_mode 為 X排他鎖lock_type 為 RECORD行級鎖結(jié)論查詢條件為主鍵且有值行鎖2.2 主鍵空值操作主鍵查詢查詢數(shù)據(jù)不存在。執(zhí)行悲觀鎖查詢select * from user where id 2 for update;執(zhí)行插入操作被鎖住了insert into user values(3, 14, 樓仔小弟, 28); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction這里的間隙鎖鎖住的區(qū)間是 id 字段的 (14) 區(qū)間查看鎖信息lock_mode 為 X排他鎖 Gap間隙鎖lock_type 為 RECORD行級鎖結(jié)論查詢條件為主鍵且空值間隙鎖2.3 唯一索引有值說明唯一索引查詢數(shù)據(jù)存在。執(zhí)行悲觀鎖查詢select * from user where user_no 10 for update;執(zhí)行更新操作被鎖住了update user set user_name 樓仔小弟 where user_no 10; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction結(jié)論查詢條件為唯一索引且有值行鎖2.4 唯一索引空值說明唯一索引查詢數(shù)據(jù)不存在。執(zhí)行悲觀鎖查詢select * from user where user_no 11 for update;執(zhí)行插入操作被鎖住了insert into user values(3, 14, 樓仔小弟, 28); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction這里的間隙鎖鎖住的區(qū)間是 user_no 字段的 (14) 區(qū)間。結(jié)論查詢條件為唯一索引且空值間隙鎖2.5 普通索引有值說明普通索引數(shù)據(jù)存在。執(zhí)行悲觀鎖查詢select * from user where age 18 for update;執(zhí)行更新操作被鎖住了update user set user_name 樓仔小弟 where age 18; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction執(zhí)行插入操作被鎖住了insert into user values(3, 14, 樓仔小弟, 20); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction這里鎖住的是 age 字段的 [18, 28) 這區(qū)間。結(jié)論查詢條件為普通索引且有值間隙鎖2.6 普通索引空值說明普通索引數(shù)據(jù)不存在。執(zhí)行悲觀鎖查詢select * from user where age 19 for update;執(zhí)行插入操作被鎖住了insert into user values(3, 14, 樓仔小弟, 20); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction這里鎖住的是 age 字段的 (18, 28) 這區(qū)間。結(jié)論查詢條件為普通索引且空值間隙鎖2.7 索引范圍查詢說明這里的索引包括主鍵索引、唯一索引和普通索引。執(zhí)行悲觀鎖查詢select * from user where id 1 for update;執(zhí)行插入操作被鎖住了insert into user values(3, 14, 樓仔小弟, 20); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction這里其實可以對 id 1 的數(shù)據(jù)進行更新對于其它數(shù)據(jù)都被鎖住鎖住的范圍是 id 字段的 (1, 4],(4, 8],(8, 正無窮) 區(qū)間。結(jié)論查詢條件為索引且是范圍查詢間隙鎖。2.8 無索引執(zhí)行悲觀鎖查詢select * from user where user_name 樓仔 for update;執(zhí)行插入操作被鎖住了insert into user values(3, 14, 樓仔小弟, 20); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction這里明顯是鎖表了但是為什么鎖的信息還是行鎖呢知道的同學(xué)可以私我哈~~結(jié)論查詢條件為無索引表鎖。03 加鎖規(guī)則3.1 規(guī)律總結(jié)我們把上面的結(jié)論進行匯總總結(jié)如下規(guī)律當(dāng)查詢條件為主鍵和唯一索引當(dāng)有值時是行鎖當(dāng)查詢條件為主鍵和唯一索引當(dāng)為空值時是間隙鎖當(dāng)查詢條件為普通索引是間隙鎖當(dāng)查詢條件為索引且為范圍查詢是間隙鎖當(dāng)查詢條件無索引是表鎖。3.2 加鎖規(guī)則那是否有一套加鎖規(guī)則呢為了便于大家理解我先普及 3 個概念Record Lock行鎖Gap Lock間隙鎖鎖定一個范圍但不包含記錄本身Next-Key Lock行鎖 間隙鎖左開右閉比如(1,5]其實 MySQL 大佬林曉斌在極客時間講過后來也有很多博主轉(zhuǎn)發(fā)過他的加鎖規(guī)則我直接把這套規(guī)則貼一下。兩個“原則”原則 1加鎖的基本單位是 next-key lock其中 next-key lock 是前開后閉區(qū)間原則 2查找過程中訪問到的對象才會加鎖。兩個“優(yōu)化”優(yōu)化 1索引上的等值查詢給唯一索引加鎖的時候next-key lock退化為行鎖優(yōu)化 2索引上的等值查詢向右遍歷時且最后一個值不滿足等值條件的時候next-key lock 退化為間隙鎖。3.3 分析一下這里我們結(jié)合上面的案例來解讀這套加鎖規(guī)則。針對我們前面總結(jié)的 5 條規(guī)律我們先分析這兩條當(dāng)查詢條件為主鍵和唯一索引當(dāng)有值時是行鎖當(dāng)查詢條件為主鍵和唯一索引當(dāng)為空值時是間隙鎖。下面我們根據(jù) “兩個原則” “兩個優(yōu)化” 來分析一下。根據(jù) “原則 1”加鎖的基本單位是 next-key lock當(dāng) “索引上為等值查詢” 時即能查到該數(shù)據(jù)根據(jù) “優(yōu)化 1”間隙鎖退化為行鎖。同理根據(jù) “優(yōu)化 2”索引上的等值查詢向右遍歷時且最后一個值不滿足等值條件的時候next-key lock 退化為間隙鎖。再分析這兩條當(dāng)查詢條件為普通索引是間隙鎖當(dāng)查詢條件為索引且為范圍查詢是間隙鎖同上通過 “原則 1” 和 “優(yōu)化 2”普通索引是加的間隙鎖。對于范圍查詢個人認(rèn)為上面的規(guī)則還不能完全覆蓋當(dāng)時林曉斌針對這些規(guī)則舉了 4 個示例然后進行詳細(xì)剖析包括間隙鎖的范圍區(qū)間計算。因為篇幅原因這里就不再詳細(xì)展開如果后續(xù)需要我也可能會單獨出一篇。04 寫在最后最后我們再回顧一下RR 隔離級別當(dāng)查詢條件為主鍵和唯一索引當(dāng)有值時是行鎖當(dāng)查詢條件為主鍵和唯一索引當(dāng)為空值時是間隙鎖當(dāng)查詢條件為普通索引是間隙鎖當(dāng)查詢條件為索引且為范圍查詢是間隙鎖當(dāng)查詢條件無索引是表鎖。至于間隙鎖的范圍如何計算本文沒有詳細(xì)闡述但是上面的這些規(guī)則就能基本滿足我們?nèi)粘９ぷ餍枰?。如果想知道間隙鎖區(qū)間如何計算歡迎催更哈~~寫文不易家里還要帶娃又肝了一個周末不過大家的支持永遠(yuǎn)是樓仔輸出的最大動力。