一前言
該文源自于和一個DBA 同行 @邱神醫 (集資料庫技術和醫學知識于一身的DBA)的技術讨論。
RC級别:
create table t1(id int primary key, name varchar(30));
insert into t1 values(1, 'a'),(4, 'c'),
(7, 'b'),(10, 'a'),(20, 'd'),(30, 'b');
commit;
複制
案例一
--sess1 --sess2
begin;
delete from t1
where id = 10;
begin;
delete from t1
where id < 9; ## 被阻塞
複制
如果會話A,會話B的執行順序調整一下，會話B 則不會被阻塞。
案例二
--sess1 --sess2
begin;
delete from t1
where id < 9;
begin;
delete from t1
where id = 10;#不會阻塞
複制
案例三 sess2 使用 delete from t1 where id < 9 order by id desc; 則沒有阻塞。
--sess1 --sess2
begin;
delete from t1
where id = 10;
begin;
delete from t1
where id < 9 order by id desc; ## 沒有阻塞
複制
為什麼呢？
二 基礎知識
sql被block住說明存在鎖等待/鎖沖突，等待其他會話釋放鎖。其實問題的核心在于 加鎖順序和加鎖範圍。這裡結合 丁奇 《MySQL 實戰45講》中的講述的 加鎖方式(2個原則2個優化1個bug)
原則 1：加鎖的基本機關是 next-key lock。
原則 2：查找過程中通路到的對象才會加鎖。
優化 1：索引上的等值查詢，給唯一索引加鎖的時候，next-key lock 退化為行鎖。
優化 2：索引上的等值查詢，向右周遊時且最後一個值不滿足等值條件的時候，next-key lock 退化為間隙鎖。
一個 bug：唯一索引上的範圍查詢會通路到不滿足條件的第一個值為止。
在讀送出隔離級别下還有一個優化，即：語句執行過程中加上的行鎖，在語句執行完成後，就要把“不滿足條件的行”上的行鎖直接釋放了，不需要等到事務送出
三 案例分析
案例一
sess1 持有id = 10 該記錄的行鎖
lock_mode X locks rec but not gap
sess2 where 條件是 id<9 ，因為id=9 的記錄不存在，根據原則 一個bug 故需要申請第一個不滿足條件的記錄id=10 的next-key ，也就是(7,10]，此時id=10 的行鎖被sess1 持有，故sess2産生鎖等待被block住。
案例二
sess1 先執行delete t1 where id<9 ，根據RC模式的加鎖方式 ，雖然要擷取到第一個不滿足記錄的id=10 的next-key lock ，但是因為id=10 不符合 id<9 的條件，會釋放鎖，最終降級為 (7,10) 之間的gap lock。
再解釋一下:這個過濾操作是 MySQL Sever層做的，也就是 innodb層把包括id=10的記錄加鎖然後發送給MySQL Server層，然後 MySQL Sever層判斷是否where條件可以結束了，可以結束了則将不滿足條件的id=10解鎖。
sess2 delete where id=10 和 sess1 持有的gap lock不沖突，故可以順利執行。
案例三
sess1 持有id = 10 該記錄的行鎖
lock_mode X locks rec but not gap
sess2 where 條件是 id<9 order by id desc ，通過innodb api接口通路資料的時候從擷取到第一個滿足條件的記錄是id=7，不會通路 id=10這條記錄。故也不會加上id=10 的next-key lock. 不會與sess1持有的鎖沖突。
小結
經過這個幾個案例又複習了一次 MySQL的 加鎖機制，同時 也牆裂 安利 丁奇的課程，不管是新人還是老司機 ，都值得一讀。