自拍偷在线精品自拍偷,亚洲欧美中文日韩v在线观看不卡

一條從未見過的報(bào)警，開啟曲折的MySQL死鎖排查

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

 故障背景

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

  1. *** (1) TRANSACTION:  
  2. TRANSACTION 6286508066, ACTIVE 0 sec updating or deleting  
  3. mysql tables in use 1, locked 1  
  4. LOCK WAIT 9 lock struct(s), heap size 1136, 14 row lock(s), undo log entries 1  
  5. MySQL thread id 189619143, OS thread handle 140619931252480, query id 1148803196 10.200.18.103 ke_information updating  
  6. 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 
  7. *** (1) WAITING FOR THIS LOCK TO BE GRANTED:  
  8. 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 
  9. Record lock, heap no 93 PHYSICAL RECORD: n_fields 5; compact format; info bits 0  
  10.  0: len 8; hex 80071afd5112d89a; asc     Q   ;;  
  11.  1: len 14; hex 6f6e5f7368656c665f616761696e; asc on_shelf_again;;  
  12.  2: len 1; hex 81; asc  ;;  
  13.  3: len 12; hex 313034313033373433363737; asc 104103743677;;  
  14.  4: len 4; hex 95f12ab5; asc   * ;; 

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

排查過程

霧里看花

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

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

事務(wù)一日志： 

  1. *** (1) TRANSACTION:  
  2. TRANSACTION 6286508066, ACTIVE 0 sec updating or deleting  
  3. mysql tables in use 1, locked 1  
  4. LOCK WAIT 9 lock struct(s), heap size 1136, 14 row lock(s), undo log entries 1  
  5. MySQL thread id 189619143, OS thread handle 140619931252480, query id 1148803196 10.200.18.103 ke_information updating  
  6. 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 
  7. *** (1) WAITING FOR THIS LOCK TO BE GRANTED:  
  8. 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 
  9. Record lock, heap no 93 PHYSICAL RECORD: n_fields 5; compact format; info bits 0  
  10.  0: len 8; hex 80071afd5112d89a; asc     Q   ;; 
  11.  1: len 14; hex 6f6e5f7368656c665f616761696e; asc on_shelf_again;; 
  12.  2: len 1; hex 81; asc  ;;  
  13.  3: len 12; hex 313034313033373433363737; asc 104103743677;;  
  14.  4: len 4; hex 95f12ab5; asc   * ;; 

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

  1. 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. *** (1) WAITING FOR THIS LOCK TO BE GRANTED:  
  2. 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)。 

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

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

事務(wù)二日志： 

  1.  *** (2) TRANSACTION:  
  2. TRANSACTION 6286508067, ACTIVE 0 sec updating or deleting, thread declared inside InnoDB 4980  
  3. mysql tables in use 1, locked 1  
  4. 12 lock struct(s), heap size 1136, 22 row lock(s), undo log entries 3  
  5. MySQL thread id 189619144, OS thread handle 140620050204416, query id 1148803197 10.200.17.37 pt_user updating  
  6. 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' 
  7. *** (2) HOLDS THE LOCK(S):  
  8. 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 
  9. Record lock, heap no 83 PHYSICAL RECORD: n_fields 5; compact format; info bits 0  
  10.  0: len 8; hex 80071afd5112d89a; asc     Q   ;;  
  11.  1: len 4; hex 6465616c; asc deal;; 
  12.  2: len 1; hex 81; asc  ;;  
  13.  3: len 12; hex 313034313032363731333238; asc 104102671328;;  
  14.  4: len 4; hex 95e14632; asc   F2;;  
  15. Record lock, heap no 93 PHYSICAL RECORD: n_fields 5; compact format; info bits 0  
  16.  0: len 8; hex 80071afd5112d89a; asc     Q   ;;  
  17.  1: len 14; hex 6f6e5f7368656c665f616761696e; asc on_shelf_again;;  
  18.  2: len 1; hex 81; asc  ;;  
  19.  3: len 12; hex 313034313033373433363737; asc 104103743677;;  
  20.  4: len 4; hex 95f12ab5; asc   * ;;  
  21. *** 省略……  
  22. *** (2) WAITING FOR THIS LOCK TO BE GRANTED:  
  23. 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 
  24. Record lock, heap no 87 PHYSICAL RECORD: n_fields 5; compact format; info bits 32  
  25.  0: len 8; hex 80071afd5112d89a; asc     Q   ;;  
  26.  1: len 15; hex 64697374726963745f7765656b6c79; asc district_weekly;;  
  27.  2: len 1; hex 80; asc  ;;  
  28.  3: len 8; hex 3233303038373831; asc 23008781;;  
  29.  4: len 4; hex 95f63035; asc   05;; 

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

  1.  *** (2) HOLDS THE LOCK(S):  
  2. 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 
  3. Record lock, heap no 83 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 
  4.  0: len 8; hex 80071afd5112d89a; asc     Q   ;;  
  5.  1: len 4; hex 6465616c; asc deal;;  
  6.  2: len 1; hex 81; asc  ;;  
  7.  3: len 12; hex 313034313032363731333238; asc 104102671328;;  
  8.  4: len 4; hex 95e14632; asc   F2;;  
  9. Record lock, heap no 93 PHYSICAL RECORD: n_fields 5; compact format; info bits 0  
  10.  0: len 8; hex 80071afd5112d89a; asc     Q   ;;  
  11.  1: len 14; hex 6f6e5f7368656c665f616761696e; asc on_shelf_again;;  
  12.  2: len 1; hex 81; asc  ;;  
  13.  3: len 12; hex 313034313033373433363737; asc 104103743677;;  
  14.  4: len 4; hex 95f12ab5; asc   * ;;  
  15. *** 省略…… 

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

  1. Record lock, heap no 93 PHYSICAL RECORD: n_fields 5; compact format; info bits 0  
  2.  0: len 8; hex 80071afd5112d89a; asc     Q   ;;  
  3.  1: len 14; hex 6f6e5f7368656c665f616761696e; asc on_shelf_again;;  
  4.  2: len 1; hex 81; asc  ;;  
  5.  3: len 12; hex 313034313033373433363737; asc 104103743677;;  
  6.  4: len 4; hex 95f12ab5; asc   * ;;  
  7. lock_mode X locks gap before rec 表示這是一個(gè)排他鎖，并且是一個(gè)間隙鎖  
  8. *** (2) WAITING FOR THIS LOCK TO BE GRANTED:  
  9. 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 
  10. Record lock, heap no 87 PHYSICAL RECORD: n_fields 5; compact format; info bits 32  
  11.  0: len 8; hex 80071afd5112d89a; asc     Q   ;;  
  12.  1: len 15; hex 64697374726963745f7765656b6c79; asc district_weekly;;  
  13.  2: len 1; hex 80; asc  ;;  
  14.  3: len 8; hex 3233303038373831; asc 23008781;;  
  15.  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ù)一的日志中沒有標(biāo)明它持有了哪些鎖，但是結(jié)合事務(wù)二等待的鎖結(jié)構(gòu)中 district_weekly 字段來看，事務(wù)一是持有該鎖的，因此，兩個(gè)事務(wù)形成了互相等待鎖釋放的場(chǎng)景，從而形成了死鎖。

那么疑問來了，兩個(gè)sql： 

  1. # sql1:  
  2. 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 
  3. # sql2:  
  4. 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è)語句的where條件不一樣，也不交叉，為什么會(huì)占用彼此的鎖呢？

山窮水復(fù)

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

  1. #CREATE TABLE `user_feed_26` (  
  2.   `feed_id` int(10) NOT NULL AUTO_INCREMENT,  
  3.   `user_id` bigint(20) NOT NULL,  
  4. ……  
  5.   PRIMARY KEY (`feed_id`),  
  6.   KEY `idx_user_id` (`user_id`,`action`,`notification`,`feed_target`),  
  7. …… 
  8. ) ENGINE=InnoDB AUTO_INCREMENT=371826027 DEFAULT CHARSET=utf8 COMMENT='用戶推送表'; 

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

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

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

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

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

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

柳暗花明

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

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

  •  原則 1：加鎖的基本單位是 next-key lock。next-key lock 是前開后閉區(qū)間；
  •  原則 2：查找過程中訪問到的對(duì)象才會(huì)加鎖。

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

優(yōu)化 2：非唯一索引上的等值查詢加鎖時(shí)，對(duì)where條件中的值所在區(qū)間向右（后）遍歷時(shí)，該區(qū)間的右邊界不滿足等值條件的時(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開始往右（后）遍歷，此時(shí)對(duì)應(yīng)的 是(3,7]但是由于該區(qū)間的最后一個(gè)值7不滿足=5的條件，因此該next-key lock就退化為gap lock (3,7)。

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

  •  事務(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ā)生呢？

通過唯一索引（一般主鍵都是）來更新，先通過select語句查出符合條件的記錄的唯一索引，再通過唯一索引來更新。 

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

避免在同一時(shí)間點(diǎn)運(yùn)行多個(gè)對(duì)同一表進(jìn)行讀寫的腳本，特別注意加鎖且操作數(shù)據(jù)量比較大的語句；我們經(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)致的死鎖。 

 

責(zé)任編輯：龐桂玉 來源： DBAplus社群
MySQL死鎖數(shù)據(jù)庫
相關(guān)推薦

2018-09-05 09:33:41

DevOps轉(zhuǎn)型指標(biāo)

2019-05-13 09:25:07

大數(shù)據(jù)數(shù)據(jù)分析隱私

2025-03-27 00:04:33

AIChatGPT生成模型

2020-06-17 09:52:16

數(shù)據(jù)庫Redis技術(shù)

2024-11-18 09:10:00

2017-10-09 12:05:57

優(yōu)秀的代碼代碼量糟糕的代碼

2021-07-05 16:26:19

數(shù)據(jù)中心

2020-07-10 06:10:14

Python開發(fā)代碼

2023-07-21 15:05:04

人工智能智能汽車數(shù)字技術(shù)

2019-03-15 16:20:45

MySQL死鎖排查命令

2020-07-26 18:34:46

Python開發(fā)工具

2021-05-19 05:45:19

漏洞服務(wù)器網(wǎng)絡(luò)安全

2012-11-07 09:41:30

2023-06-18 23:13:27

MySQL服務(wù)器客戶端

2024-07-29 09:49:00

SQLMySQL執(zhí)行

2021-07-02 09:44:03

安全網(wǎng)絡(luò)購物DDoS

2021-05-12 09:42:25

AI 數(shù)據(jù)人工智能

2025-03-24 08:11:20

技巧CSS編輯器

2025-02-05 14:16:55

OpenAI人形機(jī)器人Figure

2023-02-08 17:00:07

IF 語句技巧代碼
點(diǎn)贊
收藏

51CTO技術(shù)棧公眾號(hào)