如何排查MySQL死鎖警告

本篇內(nèi)容介紹了“如何排查MySQL死鎖警告”的有關(guān)知識(shí)，在實(shí)際案例的操作過(guò)程中，不少人都會(huì)遇到這樣的困境，接下來(lái)就讓小編帶領(lǐng)大家學(xué)習(xí)一下如何處理這些情況吧！希望大家仔細(xì)閱讀，能夠?qū)W有所成！

 故障背景

國(guó)慶期間，收到一條從未見(jiàn)過(guò)的報(bào)警，后面間歇性地又報(bào)出類(lèi)似的偶現(xiàn)報(bào)警，便忽然來(lái)了興致，摘了其中一條，探究一下其中的故事。

*** (1) TRANSACTION:  TRANSACTION 6286508066, ACTIVE 0 sec updating or deleting  mysql tables in use 1, locked 1  LOCK WAIT 9 lock struct(s), heap size 1136, 14 row lock(s), undo log entries 1  MySQL thread id 189619143, OS thread handle 140619931252480, query id 1148803196 10.200.18.103 ke_information updating  update `user_feed_26` set `notification` = 1, `mtime` = '2020-10-03 09:11:11' where `user_id` = 2000000126212250 and `action` in ('resblock_weekly', 'bizcircle_weekly', 'district_weekly') and `notification` = 0 *** (1) WAITING FOR THIS LOCK TO BE GRANTED:  RECORD LOCKS space id 2229 page no 263938 n bits 264 index idx_user_id of table `lianjia_user_feed`.`user_feed_26` trx id 6286508066 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 93 PHYSICAL RECORD: n_fields 5; compact format; info bits 0   0: len 8; hex 80071afd5112d89a; asc     Q   ;;   1: len 14; hex 6f6e5f7368656c665f616761696e; asc on_shelf_again;;   2: len 1; hex 81; asc  ;;   3: len 12; hex 313034313033373433363737; asc 104103743677;;   4: len 4; hex 95f12ab5; asc   * ;;

從日志的字面意思來(lái)看，顯然，是MySQL數(shù)據(jù)庫(kù)在執(zhí)行事務(wù)時(shí)，發(fā)現(xiàn)了死鎖的情況，那么這種死鎖是如何產(chǎn)生的，背后又潛藏著怎樣的隱患，又該如何去解決呢，我們一起來(lái)排查一下~

排查過(guò)程

霧里看花

剛開(kāi)始收到這個(gè)報(bào)警，第一反應(yīng)，是有不同事務(wù)互相鎖，結(jié)果產(chǎn)生了死鎖。那么壞了，十有八九是某個(gè)代碼片段里寫(xiě)的邏輯出了問(wèn)題。但是排查了一整圈，涉及到這個(gè)sql的代碼，既沒(méi)有開(kāi)啟事務(wù)，更沒(méi)有多個(gè)事務(wù)，那么代碼的bug基本上就可以排除了。

那么這些個(gè)事務(wù)是怎么來(lái)的呢？眾所周知，MySQL的事務(wù)支持與存儲(chǔ)引擎有關(guān)，MyISAM不支持事務(wù)，INNODB支持事務(wù)，更新時(shí)采用的是行級(jí)鎖。由于我們的數(shù)據(jù)庫(kù)采用的是INNODB引擎，意味著，會(huì)將update語(yǔ)句當(dāng)做一個(gè)事務(wù)來(lái)處理。那難道是更新同一條數(shù)據(jù)，出現(xiàn)的沖突嗎？于是找DBA同學(xué)要來(lái)了死鎖日志（數(shù)據(jù)庫(kù)版本：5.7.24 事務(wù)隔離級(jí)別為RR）。

事務(wù)一日志：

*** (1) TRANSACTION:  TRANSACTION 6286508066, ACTIVE 0 sec updating or deleting  mysql tables in use 1, locked 1  LOCK WAIT 9 lock struct(s), heap size 1136, 14 row lock(s), undo log entries 1  MySQL thread id 189619143, OS thread handle 140619931252480, query id 1148803196 10.200.18.103 ke_information updating  update `user_feed_26` set `notification` = 1, `mtime` = '2020-10-03 09:11:11' where `user_id` = 2000000126212250 and `action` in ('resblock_weekly', 'bizcircle_weekly', 'district_weekly') and `notification` = 0 *** (1) WAITING FOR THIS LOCK TO BE GRANTED:  RECORD LOCKS space id 2229 page no 263938 n bits 264 index idx_user_id of table `lianjia_user_feed`.`user_feed_26` trx id 6286508066 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 93 PHYSICAL RECORD: n_fields 5; compact format; info bits 0   0: len 8; hex 80071afd5112d89a; asc     Q   ;;  1: len 14; hex 6f6e5f7368656c665f616761696e; asc on_shelf_again;;  2: len 1; hex 81; asc  ;;   3: len 12; hex 313034313033373433363737; asc 104103743677;;   4: len 4; hex 95f12ab5; asc   * ;;

由日志可以看出，事務(wù)一執(zhí)行的sql語(yǔ)句是：

update `user_feed_26` set `notification` = 1, `mtime` = '2020-10-03 09:11:11' where `user_id` = 2000000126212250 and `action` in ('resblock_weekly', 'bizcircle_weekly', 'district_weekly') and `notification` = 0

在等待的鎖是：

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:  ECORD LOCKS space id 2229 page no 263938 n bits 264 index idx_user_id of table `lianjia_user_feed`.`user_feed_26` trx id 6286508066 lock_mode X locks gap before rec insert intention waiting

這里顯示的是事務(wù)在等待什么鎖。RECORD LOCKS 表示記錄鎖，并且可以看出要加鎖的索引為idx_user_id，space id為2229，page no為263938，lock_mode X 標(biāo)識(shí)該記錄鎖為排它鎖，insert intention waiting 表示要加的鎖為插入意向鎖，并處于鎖等待狀態(tài)。

Record lock, heap no 93 PHYSICAL RECORD: n_fields 5; compact format; info bits 0  0: len 8; hex 80071afd5112d89a; asc     Q   ;;  1: len 14; hex 6f6e5f7368656c665f616761696e; asc on_shelf_again;;  2: len 1; hex 81; asc  ;;  3: len 12; hex 313034313033373433363737; asc 104103743677;;  4: len 4; hex 95f12ab5; asc   * ;;

結(jié)合索引信息第二行 on_shelf_again 可以知道，這行鎖的 action 字段是 on_shelf_again ;

事務(wù)二日志：

 *** (2) TRANSACTION:  TRANSACTION 6286508067, ACTIVE 0 sec updating or deleting, thread declared inside InnoDB 4980  mysql tables in use 1, locked 1  12 lock struct(s), heap size 1136, 22 row lock(s), undo log entries 3  MySQL thread id 189619144, OS thread handle 140620050204416, query id 1148803197 10.200.17.37 pt_user updating  UPDATE `user_feed_26` SET  `notification` = '1' , `mtime` = '2020-10-03 09:11:11'  WHERE `user_id` = '2000000126212250'  AND `action` in ( 'deal','price_changed','ting_shou','house_new_picture','house_new_vr','price_changed_rise','on_shelf_again')  AND `notification` = '0' *** (2) HOLDS THE LOCK(S):  RECORD LOCKS space id 2229 page no 263938 n bits 264 index idx_user_id of table `lianjia_user_feed`.`user_feed_26` trx id 6286508067 lock_mode X locks gap before rec Record lock, heap no 83 PHYSICAL RECORD: n_fields 5; compact format; info bits 0   0: len 8; hex 80071afd5112d89a; asc     Q   ;;   1: len 4; hex 6465616c; asc deal;;  2: len 1; hex 81; asc  ;;   3: len 12; hex 313034313032363731333238; asc 104102671328;;   4: len 4; hex 95e14632; asc   F2;;  Record lock, heap no 93 PHYSICAL RECORD: n_fields 5; compact format; info bits 0   0: len 8; hex 80071afd5112d89a; asc     Q   ;;   1: len 14; hex 6f6e5f7368656c665f616761696e; asc on_shelf_again;;   2: len 1; hex 81; asc  ;;   3: len 12; hex 313034313033373433363737; asc 104103743677;;   4: len 4; hex 95f12ab5; asc   * ;;  *** 省略……  *** (2) WAITING FOR THIS LOCK TO BE GRANTED:  RECORD LOCKS space id 2229 page no 263938 n bits 264 index idx_user_id of table `lianjia_user_feed`.`user_feed_26` trx id 6286508067 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 87 PHYSICAL RECORD: n_fields 5; compact format; info bits 32   0: len 8; hex 80071afd5112d89a; asc     Q   ;;   1: len 15; hex 64697374726963745f7765656b6c79; asc district_weekly;;   2: len 1; hex 80; asc  ;;   3: len 8; hex 3233303038373831; asc 23008781;;   4: len 4; hex 95f63035; asc   05;;

事務(wù)二的日志，相比于事務(wù)一多了持有鎖的信息：

 *** (2) HOLDS THE LOCK(S):  RECORD LOCKS space id 2229 page no 263938 n bits 264 index idx_user_id of table `lianjia_user_feed`.`user_feed_26` trx id 6286508067 lock_mode X locks gap before rec Record lock, heap no 83 PHYSICAL RECORD: n_fields 5; compact format; info bits 0  0: len 8; hex 80071afd5112d89a; asc     Q   ;;   1: len 4; hex 6465616c; asc deal;;   2: len 1; hex 81; asc  ;;   3: len 12; hex 313034313032363731333238; asc 104102671328;;   4: len 4; hex 95e14632; asc   F2;;  Record lock, heap no 93 PHYSICAL RECORD: n_fields 5; compact format; info bits 0   0: len 8; hex 80071afd5112d89a; asc     Q   ;;   1: len 14; hex 6f6e5f7368656c665f616761696e; asc on_shelf_again;;   2: len 1; hex 81; asc  ;;   3: len 12; hex 313034313033373433363737; asc 104103743677;;   4: len 4; hex 95f12ab5; asc   * ;;  *** 省略……

從日志看，事務(wù)二持有一個(gè)記錄鎖，RECORD LOCKS這是個(gè)記錄鎖，space id為2229，page no為263938 并且通過(guò)索引信息可以看出，事務(wù)二恰好持有事務(wù)一需要的那行記錄鎖，即：

Record lock, heap no 93 PHYSICAL RECORD: n_fields 5; compact format; info bits 0   0: len 8; hex 80071afd5112d89a; asc     Q   ;;   1: len 14; hex 6f6e5f7368656c665f616761696e; asc on_shelf_again;;   2: len 1; hex 81; asc  ;;   3: len 12; hex 313034313033373433363737; asc 104103743677;;   4: len 4; hex 95f12ab5; asc   * ;;  lock_mode X locks gap before rec 表示這是一個(gè)排他鎖，并且是一個(gè)間隙鎖  *** (2) WAITING FOR THIS LOCK TO BE GRANTED:  RECORD LOCKS space id 2229 page no 263938 n bits 264 index idx_user_id of table `lianjia_user_feed`.`user_feed_26` trx id 6286508067 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 87 PHYSICAL RECORD: n_fields 5; compact format; info bits 32   0: len 8; hex 80071afd5112d89a; asc     Q   ;;   1: len 15; hex 64697374726963745f7765656b6c79; asc district_weekly;;   2: len 1; hex 80; asc  ;;   3: len 8; hex 3233303038373831; asc 23008781;;   4: len 4; hex 95f63035; asc   05;;

同樣，這里顯示的是事務(wù)二在等待什么鎖。RECORD LOCKS 表示記錄鎖，并且可以看出要加鎖的索引為idx_user_id，space id為2229，page no為263938 lock_mode X 標(biāo)識(shí)該記錄鎖為排它鎖，insert intention waiting 表示要加的鎖為插入意向鎖，并處于鎖等待狀態(tài)。雖然，事務(wù)一的日志中沒(méi)有標(biāo)明它持有了哪些鎖，但是結(jié)合事務(wù)二等待的鎖結(jié)構(gòu)中 district_weekly 字段來(lái)看，事務(wù)一是持有該鎖的，因此，兩個(gè)事務(wù)形成了互相等待鎖釋放的場(chǎng)景，從而形成了死鎖。

那么疑問(wèn)來(lái)了，兩個(gè)sql：

# sql1:  update `user_feed_26` set `notification` = 1, `mtime` = '2020-10-03 09:11:11' where `user_id` = 2000000126212250 and `action` in ('resblock_weekly', 'bizcircle_weekly', 'district_weekly') and `notification` = 0 # sql2:  UPDATE `user_feed_26` SET  `notification` = '1' , `mtime` = '2020-10-03 09:11:11'  WHERE `user_id` = '2000000126212250'  AND `action` in ( 'deal','price_changed','ting_shou','house_new_picture','house_new_vr','price_changed_rise','on_shelf_again')  AND `notification` = '0'

明明兩個(gè)語(yǔ)句的where條件不一樣，也不交叉，為什么會(huì)占用彼此的鎖呢？

山窮水復(fù)

為了驗(yàn)證這種case，我們?cè)诰€(xiàn)下嘗試進(jìn)行復(fù)現(xiàn)。表結(jié)構(gòu)如下：

#CREATE TABLE `user_feed_26` (    `feed_id` int(10) NOT NULL AUTO_INCREMENT,    `user_id` bigint(20) NOT NULL,  ……    PRIMARY KEY (`feed_id`),    KEY `idx_user_id` (`user_id`,`action`,`notification`,`feed_target`),  …… ) ENGINE=InnoDB AUTO_INCREMENT=371826027 DEFAULT CHARSET=utf8 COMMENT='用戶(hù)推送表';

但是無(wú)論如何，都是鎖等待，而不會(huì)形成死鎖。這是怎么回事呢？

帶著懷疑的態(tài)度，我們查看了一下語(yǔ)句的執(zhí)行計(jì)劃：

通過(guò)執(zhí)行計(jì)劃我們發(fā)現(xiàn)，這里并沒(méi)有走死鎖日志里出現(xiàn)的那個(gè)idx_user_id索引，而是走的主鍵索引，因此并沒(méi)有產(chǎn)生死鎖。

大膽猜測(cè)：是因?yàn)槟M的數(shù)據(jù)量太小，導(dǎo)致并沒(méi)有走復(fù)合索引。

于是，我們往線(xiàn)下模擬庫(kù)里灌入了大概100w左右的隨機(jī)數(shù)據(jù)，再次查看執(zhí)行計(jì)劃：

果然，當(dāng)數(shù)據(jù)量變大之后，就會(huì)走對(duì)應(yīng)的復(fù)合索引了。再經(jīng)過(guò)一次嘗試，果然復(fù)現(xiàn)出了線(xiàn)上那種死鎖場(chǎng)景，但是問(wèn)題來(lái)了，為什么會(huì)出現(xiàn)這種情況呢？

柳暗花明

為了了解背后真實(shí)的原理，我們?cè)俅窝凶x了MySQL鎖相關(guān)的資料，也得知了事情的真相。

首先，簡(jiǎn)單說(shuō)一下MySQL加鎖的基本原則：

•  原則 1：加鎖的基本單位是 next-key lock。next-key lock 是前開(kāi)后閉區(qū)間；

•  原則 2：查找過(guò)程中訪(fǎng)問(wèn)到的對(duì)象才會(huì)加鎖。

優(yōu)化 1：唯一索引上的等值查詢(xún)加鎖時(shí)，next-key lock 退化為行鎖。

優(yōu)化 2：非唯一索引上的等值查詢(xún)加鎖時(shí)，對(duì)where條件中的值所在區(qū)間向右（后）遍歷時(shí)，該區(qū)間的右邊界不滿(mǎn)足等值條件的時(shí)候，next-key lock 退化為間隙鎖。這個(gè)比較難理解，舉個(gè)例子：

若在表ta的列a上有非唯一索引：index_a,該索引中存在的值為：1，1，3，3，7，9：當(dāng)你執(zhí)行select a from ta where ta.a=5時(shí)，就會(huì)從3開(kāi)始往右（后）遍歷，此時(shí)對(duì)應(yīng)的 是(3,7]但是由于該區(qū)間的最后一個(gè)值7不滿(mǎn)足=5的條件，因此該next-key lock就退化為gap lock (3,7)。

由此可知，當(dāng)我們執(zhí)行的update語(yǔ)句，在查詢(xún)的時(shí)候，給對(duì)應(yīng)的索引idx_user_id加上了間隙鎖，從而互相之間產(chǎn)生了死鎖。舉個(gè)簡(jiǎn)單的例子說(shuō)明一下：

•  事務(wù)2執(zhí)行了一個(gè)update, where 條件為3，因此獲得了（1，3）的Gap鎖；

•  事務(wù)1也執(zhí)行了一個(gè)update，where條件為5，因此獲得了一個(gè)（5，+∞），同時(shí)等待（1，7）插入意向鎖；

•  事務(wù)2又執(zhí)行了一個(gè)update，where條件為8，那么他將等待（5，+∞）。

于是乎，死鎖就產(chǎn)生了。

那么，如何避免這種死鎖再次發(fā)生呢？

通過(guò)唯一索引（一般主鍵都是）來(lái)更新，先通過(guò)select語(yǔ)句查出符合條件的記錄的唯一索引，再通過(guò)唯一索引來(lái)更新。

select id from table where a=? and b=?;  update table set column=xxx where idid= id;

避免在同一時(shí)間點(diǎn)運(yùn)行多個(gè)對(duì)同一表進(jìn)行讀寫(xiě)的腳本，特別注意加鎖且操作數(shù)據(jù)量比較大的語(yǔ)句；我們經(jīng)常會(huì)有一些定時(shí)腳本，避免它們?cè)谕粫r(shí)間點(diǎn)運(yùn)行；如本次事件所示，Gap 鎖往往是程序中導(dǎo)致死鎖的真兇，由于默認(rèn)情況下 MySQL 的隔離級(jí)別是 RR，所以如果能確定幻讀和不可重復(fù)讀對(duì)應(yīng)用的影響不大，可以考慮將隔離級(jí)別改成 RC，可以避免 Gap 鎖導(dǎo)致的死鎖。

“如何排查MySQL死鎖警告”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識(shí)可以關(guān)注億速云網(wǎng)站，小編將為大家輸出更多高質(zhì)量的實(shí)用文章！