MySQL間隙鎖死鎖問題 · php開發筆記

#### 一、場景還原當時同事A在線上代碼中使用了Mybatis-plus的如下方法 ~~~java com.baomidou.mybatisplus.extension.service.IService saveOrUpdate(T, com.baomidou.mybatisplus.core.conditions.Wrapper<T>) ~~~ 該方法先執行了update操作，如果更新到就不再執行后續操作，如果沒有更新到，才進行主鍵查詢，查詢到了就修改，未查詢到就新增。具體方法如下 ~~~java /** * <p> * 根據updateWrapper嘗試更新，否繼續執行saveOrUpdate(T)方法 * 此次修改主要是減少了此項業務代碼的代碼量（存在性驗證之后的saveOrUpdate操作） * </p> * * @param entity 實體對象 */ default boolean saveOrUpdate(T entity, Wrapper<T> updateWrapper) { return update(entity, updateWrapper) || saveOrUpdate(entity); } ~~~ 那么這個方法的做法，為什么會導致間隙鎖死鎖呢？咱們一起來分析并還原間隙鎖死鎖的場景。 #### 二、什么是間隙鎖間隙鎖是MySQL行鎖的一種，與行鎖不同的是間隙鎖可能鎖定的是一行數據，也可能鎖住一個間隙。鎖定規則如下： * 當修改的數據存在時，間隙鎖只會鎖定當前行。 * 當修改的數據不存在時，間隙鎖會向左找第一個比當前索引值小的值，向右找第一個比當前索引值大的值（沒有則為正無窮），將此區間鎖住，從而阻止其他事務在此區間插入數據。 #### 三、間隙鎖的作用與行鎖（例如樂觀鎖高級實現，MVCC）組合成Next-key lock，在可重復讀這種隔離級別下一起工作避免幻讀。 #### 四、如何關閉間隙鎖（強烈不建議關閉） 1、降低隔離級別，例如降為提交讀。 2、直接修改my.cnf，將開關，innodb\_locks\_unsafe\_for\_binlog改為1，默認為0即開啟 #### 五、還原線上間隙鎖死鎖的場景 ##### 5.1 復現間隙鎖死鎖 ###### 5.1.1 我們先準備一個表 ~~~sql mysql> select * from t_gap_lock; +----+--------+------+ | id | name | age | +----+--------+------+ | 1 | 張一 | 21 | | 5 | 李五 | 25 | | 6 | 趙六 | 26 | | 9 | 王九 | 29 | | 12 | 十二 | 12 | +----+--------+------+ ~~~ 表中的id數據咱們準備了三個間隙： * 間隙一：1-5 * 間隙二：6-9 * 間隙三：12-正無窮 ###### 5.1.2 操作 1、此時我們開啟事務一，然后執行更新id=3的數據，按照咱們的理論，id=3這個數據不存在，說明它會在1-5之間加間隙鎖。 ~~~sql #開啟事務一 begin; #事務一在1-5之間加間隙鎖 update t_gap_lock t set t.age = 23 where t.id = 3; ~~~ 2、然后我們開啟事務二，然后執行更新id=7的數據，按照咱們的理論，id=7這個數據不存在，說明它會在6-9之間加間隙鎖 ~~~sql #開啟事務二 begin; #事務二在6-9之間加間隙鎖 update t_gap_lock t set t.age = 27 where t.id = 7; ~~~ 3、那么重點來了，此時我們需要做的操作就是讓事務一在6-9之間插入數據，會發現此時事務已經被阻塞，無法執行insert，因為事務二已經對該區間加了間隙鎖。 ~~~sql #事務一在6-9之間插入數據 insert into t_gap_lock(id, name, age) values(8,'李八',28); ~~~ 4、在事務一等待鎖的同時，咱們讓事務二同時在1-5之間插入數據，這個時候會發現，只要事務二一執行插入。MySQL立即報了死鎖，我們就會見到如下提示：``\[40001\]\[1213\] Deadlock found when trying to get lock; try restarting transaction`。 ~~~sql # 同時事務二在1-5之間插入數據 insert into t_gap_lock(id, name, age) values(3,'李三',23); ~~~ ###### 5.1.3 整個死鎖過程進行原理分析 1、首先事務一開啟事務后，更新id=3的數據，此數據不存在，所以事務一會鎖住1-5這個間隙，即為1-5這個間隙添加間隙鎖，同理，事務二會為6-9這個間隙添加間隙鎖； 2、然后我們讓事務一在6-9這個間隙插入數據，因為事務二已經加了間隙鎖，所以事務一需要等待事務二釋放間隙鎖才能進行插入操作，此時事務一等待事務二釋放間隙鎖； 3、同理，事務二在1-5間隙插入時需要等待事務一釋放間隙鎖，兩個事務相互等待，死鎖產生。那么咱們此時就能大概明白最初那個Mybatis-plus的saveOrUpdate方法為什么會造成間隙鎖死鎖的問題，也就是線上存在兩個并發事務，然后更新的時候都沒有更新到，此時都在自己的間隙加了間隙鎖，然后再到彼此的區間進行數據插入，此時就會造成兩個事務互相等待對方的釋放間隙鎖，從而導致死鎖。也許有同學會想，線上的數據幾乎不可能剛好會存在1-5，6-9這種間隙，來給并發事務各自加鎖，又剛好到彼此區間插入數據的場景，所以我們就會有接下來驗證間隙鎖加鎖是非互斥的，再一次深度還原間隙鎖死鎖的場景。 ##### 5.2 驗證間隙鎖加鎖非互斥 ###### 5.2.1 依然以t\_gap\_lock為例 ~~~sql mysql> select * from t_gap_lock; +----+--------+------+ | id | name | age | +----+--------+------+ | 1 | 張一 | 21 | | 5 | 李五 | 25 | | 6 | 趙六 | 26 | | 9 | 王九 | 29 | | 12 | 十二 | 12 | +----+--------+------+ ~~~ ###### 5.2.2 操作 1、此時咱們開啟事務一，然后執行更新id=13的數據，按照咱們的理論，id=13這個數據不存在，說明它會在13-正無窮（因為當前索引樹上沒有比13更大的值）之間加間隙鎖。 ~~~sql #開啟事務一 begin; #事務一在13-正無窮添加間隙鎖 update t_gap_lock t set t.age = 13 where t.id = 13; ~~~ 2、然后我們開啟事務二，然后也執行更新id=13的數據，按照咱們的理論，事務二也會對13-正無窮之間加間隙鎖 ~~~sql #開啟事務二 begin; #在13-正無窮添加間隙鎖 update t_gap_lock t set t.age = 13 where t.id = 13; ~~~ 3、那么重點來了，此時我們需要做的操作就是讓事務一在13-正無窮之間插入數據，會發現此時事務已經被阻塞，無法執行insert，因為事務二已經對該區間加了間隙鎖。 ~~~sql #事務一在13-正無窮中新增數據 insert into t_gap_lock(id, name, age) values (13,'十六',16); ~~~ 4、在事務一等待鎖的同時，咱們讓事務二同時在13-正無窮之間插入數據，這個時候會發現，只要事務二一執行插入。MySQL立即報了死鎖，我們就會見到如下提示：`[40001][1213] Deadlock found when trying to get lock; try restarting transaction`。 ~~~sql #事務二在13-正無窮中新增數據 insert into t_gap_lock(id, name, age) values (13,'十六',16); ~~~ 5、因為咱們已經用1-5以及6-9這種明顯的間隙還原了間隙鎖死鎖，所以13-正無窮發生間隙鎖死鎖的原理與其無異，這里有個非常大的區別就是事務一已經在13-正無窮加了間隙鎖，事務二依然可以對此間隙加間隙鎖，所以我們用實際證明了間隙鎖加鎖是非互斥的。此時咱們回憶一下Mybatis-plus的saveOrUpdate方法，發現線上只要出現兩個并發事務去修改同一條不存在的數據，就會立馬出現間隙鎖死鎖。 ##### 5.3 驗證當修改數據存在時，間隙鎖只會鎖住當前行還有一個比較重要的點就是，當修改的數據存在時，MySQL只會鎖住當前行，咱們一起來分析下整個過程。 ###### 5.3.1 依然以t\_gap\_lock為例 ~~~sql mysql> select * from t_gap_lock; +----+--------+------+ | id | name | age | +----+--------+------+ | 1 | 張一 | 21 | | 5 | 李五 | 25 | | 6 | 趙六 | 26 | | 9 | 王九 | 29 | | 12 | 十二 | 12 | +----+--------+------+ ~~~ ###### 5.3.2 操作 1、此時我們開啟事務一，然后執行更新id=12的數據，按照咱們的理論，id=12這個數據存在，說明MySQL只會鎖定id=12這一行數據。 ~~~sql #開啟事務一 begin; #事務一只在12上加間隙鎖 update t_gap_lock t set t.age = 12 where t.id = 12; ~~~ 2、然后我們開啟事務二，然后執行更新id=13的數據，按照咱們的理論，id=13這個數據不存在，說明它會在13-正無窮（因為當前索引樹上沒有比13更大的值）之間加間隙鎖 ~~~sql #開啟事務二 begin; #事務二在13-正無窮添加間隙鎖 update t_gap_lock t set t.age = 13 where t.id = 13; ~~~ 3、那么重點來了，此時我們需要做的操作就是讓事務一在13-正無窮之間插入數據，會發現此時事務已經被阻塞，無法執行insert，因為事務二已經對該區間加了間隙鎖。 ~~~sql #事務一在13-正無窮中新增數據 insert into t_gap_lock(id, name, age) values (15,'十五',15); ~~~ 4、在事務一等待鎖的同時，咱們讓事務二在12-正無窮之間插入數據，這個時候會發現，事務二能夠正常插入，說明事務二沒有被間隙鎖阻塞，待事務二提交或回滾后，事務一也正常提交。 ~~~sql #事務二在13-正無窮中新增數據 insert into t_gap_lock(id, name, age) values (13,'十六',16); ~~~ 5、通過以上驗證，MySQL在更新id=12，即數據存在時，并沒有對12-正無窮添加間隙鎖，而是只鎖定了id=12這一行數據，從而降低鎖的顆粒度以提高性能。