01 環(huá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”普通索引是加的間隙鎖。對于范圍查詢個人認為上面的規(guī)則還不能完全覆蓋當(dāng)時林曉斌針對這些規(guī)則舉了 4 個示例然后進行詳細剖析包括間隙鎖的范圍區(qū)間計算。因為篇幅原因這里就不再詳細展開如果后續(xù)需要我也可能會單獨出一篇。04 寫在最后最后我們再回顧一下RR 隔離級別當(dāng)查詢條件為主鍵和唯一索引當(dāng)有值時是行鎖當(dāng)查詢條件為主鍵和唯一索引當(dāng)為空值時是間隙鎖當(dāng)查詢條件為普通索引是間隙鎖當(dāng)查詢條件為索引且為范圍查詢是間隙鎖當(dāng)查詢條件無索引是表鎖。至于間隙鎖的范圍如何計算本文沒有詳細闡述但是上面的這些規(guī)則就能基本滿足我們?nèi)粘９ぷ餍枰?。如果想知道間隙鎖區(qū)間如何計算歡迎催更哈~~寫文不易家里還要帶娃又肝了一個周末不過大家的支持永遠是樓仔輸出的最大動力。