前言

作為后端開發(fā)人員，幾乎每天都與數(shù)據(jù)庫打交道。對著變化莫測的需求，緊張的工期，很多程序員日復一日寫著CRUD代碼，無暇理會數(shù)據(jù)庫實際執(zhí)行原理，這是技術提高的門檻。
秉著工作處處皆學問，知其然知其所以然的信念，日常生活中我都會把遇到的問題摘錄下來，總結分析背后的技術原理。

一、遇見死鎖

組長讓我實現(xiàn)一個提交申請單的功能。因為用戶可以重復提交申請，而申請只能保留一份，因此我在插入新申請insert操作前，先按照用戶id刪除delete他之前所有申請記錄。確保兩個操作的一致性，我加了事務@Transactional。
簡單的功能，簡潔的實現(xiàn)。事與愿違，表中出現(xiàn)重復用戶id記錄的。查看數(shù)據(jù)發(fā)現(xiàn)，新的記錄插入成功，是舊的數(shù)據(jù)沒有刪除成功。既然是事務，為什么一個執(zhí)行成功，一個失敗呢？

+-----------------+-----------+---------------+-----------+
| id              | member_id | nick_name     | is_delete |
+-----------------+-----------+---------------+-----------+
| 181025163317920 |      3288 | 蕉仔偉??        |          |
| 181026142458439 |      3288 | 賽亞人        |          |
| 181030104733141 |      3288 | 百變小櫻      |           |
| 181030105733272 |      3288 | 柴犬慕斯      |           |
+-----------------+-----------+---------------+-----------+
4 rows in set (0.01 sec)

帶著問題找原因，看看數(shù)據(jù)庫的狀態(tài)報告：

mysql> show engine innodb status;
| InnoDB |      |
=====================================
LATEST DETECTED DEADLOCK
------------------------
2018-11-28 20:07:24 0x7f286deb7700
*** (1) TRANSACTION:
TRANSACTION 41443739, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 230974, OS thread handle 139810027362048, query id 73704659 172.31.0.8 apiuser update
INSERT INTO media_user_apply  ( id,real_name,nick_name,member_id,media_user_id,mobile,sex,id_num,education,contact_way,summary,is_delete,status,create_time,update_by,update_time,type,member_type ) VALUES( 181115110822284,'12','12',2910,null,null,1,'',0,'','',0,0,'2018-11-15 11:08:19.59',null,null,1,1 )
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1045 page no 4 n bits 136 index member_id_index of table `mongcent_news`.`media_user_apply` trx id 41443739 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 34 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000b5f; asc    _;;
 1: len 8; hex 8000a4a5997e1503; asc      ~  ;;

*** (2) TRANSACTION:
TRANSACTION 41443740, ACTIVE 0 sec inserting, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 242484, OS thread handle 139811600373504, query id 73704661 172.31.0.8 apiuser update
INSERT INTO media_user_apply  ( id,real_name,nick_name,member_id,media_user_id,mobile,sex,id_num,education,contact_way,summary,is_delete,status,create_time,update_by,update_time,type,member_type ) VALUES( 181115110822286,'12','12',2910,null,null,1,'',0,'','',0,0,'2018-11-15 11:08:19.591',null,null,1,1 )
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1045 page no 4 n bits 136 index member_id_index of table `mongcent_news`.`media_user_apply` trx id 41443740 lock_mode X locks gap before rec
Record lock, heap no 34 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000b5f; asc    _;;
 1: len 8; hex 8000a4a5997e1503; asc      ~  ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1045 page no 4 n bits 136 index member_id_index of table `mongcent_news`.`media_user_apply` trx id 41443740 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 34 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000b5f; asc    _;;
 1: len 8; hex 8000a4a5997e1503; asc      ~  ;;

*** WE ROLL BACK TRANSACTION (2)

從報告的LATEST DETECTED DEADLOCK中，我們可以看到執(zhí)行事務過程中，發(fā)生的死鎖。而發(fā)生死鎖的原因，有句話很關鍵

RECORD LOCKS space id 1045 page no 4 n bits 136 index member_id_index of table mongcent_news.media_user_apply trx id 41443739 lock_mode X locks gap before rec insert intention waiting

這句話究竟告訴了我們什么呢？我們先來普及一下MySQL鎖的知識

二、MySQL鎖

1. 共享鎖與排他鎖

在 InnoDB 中實現(xiàn)了兩個標準的行級鎖，可以簡單的看為兩個讀寫鎖：

• S ：共享鎖(Share Lock)又叫讀鎖，其他事務可以繼續(xù)加共享鎖，但是不能繼續(xù)加排他鎖。
• X ：排他鎖(Exclusive Lock)又叫寫鎖，一旦加了寫鎖之后，其他事務就不能加鎖了。

鎖與鎖之間具有兼容性，兼容性是指事務 A 獲得一個某行某種鎖之后，事務 B 同樣的在這個行上嘗試獲取某種鎖，如果能立即獲取，則稱兼容，反之叫沖突。

縱軸是代表已有的鎖，橫軸是代表嘗試獲取的鎖。

2.意向鎖

意向鎖在 InnoDB 中是表級鎖，顧名思義它是用來表達一個事務想要獲取什么。如果用行鎖加表鎖，需要遍歷每一行是否有行鎖，但使用意向鎖，只需要判斷意向鎖中的行就可以了。
事務在請求某一行的S鎖和X鎖前，需要先獲得對應表的IS、IX鎖。
意向鎖分為：

• IS：意向共享鎖(Intent Share Lock)表達一個事務想要獲取一張表中某幾行的共享鎖。
• IX：意向排他鎖(Intent Exclusive Lock)表達一個事務想要獲取一張表中某幾行的排他鎖。

在 InnoDB 中由于支持的是行級的鎖，因此 InnboDB 鎖的兼容性可以擴展如下：

三.鎖算法

1.記錄鎖(Record Lock)

記錄鎖是鎖住記錄的，這里要說明的是這里鎖住的是索引記錄，而不是我們真正的數(shù)據(jù)記錄：
如果鎖的是非主鍵索引，會在自己的索引上面加鎖之后然后再去主鍵上面加鎖鎖住。
如果沒有表上沒有索引(包括沒有主鍵)，則會使用隱藏的主鍵索引進行加鎖。
如果要鎖的列沒有索引，則會進行全表記錄加鎖。

2.間隙鎖(Gap Lock)

間隙鎖顧名思義鎖間隙，不鎖記錄。鎖間隙的意思就是鎖定某一個范圍，間隙鎖又叫 gap lock，其不會阻塞其他的 gap lcok，但是會阻塞 插入間隙鎖 ，這也是用來防止 幻讀 的關鍵。

3.插入意向鎖

插入意向鎖是一種Gap Lock，在insert操作時產(chǎn)生。

• 插入意向鎖之間互不排斥，所以即使多個事務在同一區(qū)間插入多條記錄，只要記錄本身（主鍵、唯一索引）不沖突，那么事務之間就不會出現(xiàn)沖突等待。

4.Next-key Lock

這個鎖本質(zhì)是記錄鎖+ 間隙鎖。在 RR 隔離級別下(InnoDB 默認)，InnoDB 對于行的掃描鎖定都是使用此算法，但是如果查詢掃描中有唯一索引會退化成只使用記錄鎖。
為什么呢? 因為唯一索引能確定行數(shù)，而其他索引不能確定行數(shù)，有可能在其他事務中會再次添加這個索引的數(shù)據(jù)造成幻讀。
這里也說明了為什么 MySQL 可以在 RR 級別下解決幻讀。

四.解決死鎖

理解完InnoDB的鎖與鎖算法，我們回到最初的問題。探索一下，為什么我的代碼會發(fā)生死鎖。
從網(wǎng)上找到日志的翻譯：

lock_mode X locks gap before rec 表示Gap Lock,Gap鎖是用來防止insert的
lock_mode X locks rec but not gap 表示Record Lock

lock_mode X 表示Next-Key Lock
lock_mode X insert intention waiting 表示Insert Intention Lock

因此鎖日志中lock_mode X locks gap before rec insert intention waiting的意思就是在事務1、事務2持有Gap Lock的時，再請求Insert Intention Lock。我們之前說了在持有間隙鎖時，請求插入意向鎖，會導致沖突，阻塞事務。
本來兩個事務如果分別獨立運行都能順利運行。但如果是同時執(zhí)行的時候，就不一樣了：
1.第一句命令執(zhí)行了delete操作，事務1、事務2分別都對目標索引加了Next-key Lock。
2.事務1執(zhí)行insert操作前，它會請求Insert Intention Lock，當它發(fā)現(xiàn)有事務2的Gap Lock，于是發(fā)生了沖突，事務1阻塞。同理，事務2執(zhí)行insert操作也發(fā)生了沖突，事務1阻塞。
3.此時事務1等事務2釋放鎖，事務2等事務1釋放鎖，出現(xiàn)了回路等待，產(chǎn)生了死鎖。
4.數(shù)據(jù)發(fā)現(xiàn)了死鎖，最終決定WE ROLL BACK TRANSACTION (2)，回滾了事務2的插入操作，事務1的插入操作執(zhí)行成功。

解決方法

死鎖的原因在于Gap Lock與Insert Intention Lock的沖突，而從上面的Next-key Lock定義中得知，如果掃描中有唯一索引會退化成只使用記錄鎖。所以，我們只要把delete的where條件改成使用唯一索引或者主鍵，即可避免產(chǎn)生Gap Lock，從而避免了死鎖。

后記

今晚是平安夜，在此我也祝大家的數(shù)據(jù)庫能夠平平安安。Merry Christmas !

我全部都要.jpeg