由不同的索引更新解決MySQL死鎖套路
前幾篇文章介紹了用源碼的方式來(lái)調(diào)試鎖相關(guān)的信息，這里同樣用這個(gè)工具來(lái)解決一個(gè)線上實(shí)際的死鎖案例，也是我們介紹的第一個(gè)兩條 SQL 就造成死鎖的情況。因?yàn)榫€上的表結(jié)構(gòu)比較復(fù)雜，做了一些簡(jiǎn)化以后如下
CREATE TABLE `t3` ( `id` int(11) NOT NULL AUTO_INCREMENT, `a` varchar(5), `b` varchar(5), PRIMARY KEY (`id`), UNIQUE KEY `uk_a` (`a`), KEY `idx_b` (`b`) ) INSERT INTO `t3` (`id`, `a`, `b`) VALUES (1,'1','2'); # sql語(yǔ)句如下 # 事務(wù)1：t1 update t3 set b = '' where a = "1"; # 事務(wù)2：t2 update t3 set b = '' where b = "2";
兩條語(yǔ)句造成死鎖的情況用手動(dòng)的方式比較難復(fù)現(xiàn)，我們先來(lái)分析一下加鎖的過(guò)程
第一條語(yǔ)句（通過(guò)唯一索引去更新記錄）
update t3 set b = '' where a = "1";
整理一下，加了3個(gè)X鎖，順序分別是
| 序號(hào) | 索引 | 鎖類型 |
|---|---|---|
| 1 | uk_a | X |
| 2 | PRIMARY | X |
| 3 | idx_b | X |
update t3 set b = '' where b = "2";
整理一下，加了 3 個(gè) X 鎖，順序分別是
| 序號(hào) | 索引 | 鎖類型 |
|---|---|---|
| 1 | idx_b | X |
| 2 | PRIMARY | X |
| 3 | idx_b | X |
兩條語(yǔ)句從加鎖順序看起來(lái)就已經(jīng)有構(gòu)成死鎖的條件了
手動(dòng)是比較難模擬的，寫個(gè)代碼并發(fā)的去同時(shí)執(zhí)行那兩條 SQL 語(yǔ)句，馬上就出現(xiàn)死鎖了
------------------------ LATEST DETECTED DEADLOCK ------------------------ 181102 12:45:05 *** (1) TRANSACTION: TRANSACTION 50AF, ACTIVE 0 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 376, 2 row lock(s) MySQL thread id 34, OS thread handle 0x70000d842000, query id 549 localhost 127.0.0.1 root Searching rows for update update t3 set b = '' where b = "2" *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 67 page no 3 n bits 72 index `PRIMARY` of table `d1`.`t3` trx id 50AF lock_mode X locks rec but not gap waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 80000001; asc ;; 1: len 6; hex 0000000050ae; asc P ;; 2: len 7; hex 03000001341003; asc 4 ;; 3: len 1; hex 31; asc 1;; 4: len 0; hex ; asc ;; *** (2) TRANSACTION: TRANSACTION 50AE, ACTIVE 0 sec updating or deleting mysql tables in use 1, locked 1 4 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 1 MySQL thread id 35, OS thread handle 0x70000d885000, query id 548 localhost 127.0.0.1 root Updating update t3 set b = '' where a = "1" *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 67 page no 3 n bits 72 index `PRIMARY` of table `d1`.`t3` trx id 50AE lock_mode X locks rec but not gap Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 80000001; asc ;; 1: len 6; hex 0000000050ae; asc P ;; 2: len 7; hex 03000001341003; asc 4 ;; 3: len 1; hex 31; asc 1;; 4: len 0; hex ; asc ;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 67 page no 5 n bits 72 index `idx_b` of table `d1`.`t3` trx id 50AE lock_mode X locks rec but not gap waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 1; hex 32; asc 2;; 1: len 4; hex 80000001; asc ;; *** WE ROLL BACK TRANSACTION (1)
分析一下死鎖日志
*** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 67 page no 3 n bits 72 index PRIMARY of table d1.t3 trx id 50AF lock_mode X locks rec but not gap waiting
事務(wù)2：想獲取主鍵索引的 X 鎖
*** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 67 page no 3 n bits 72 index PRIMARY of table d1.t3 trx id 50AE lock_mode X locks rec but not gap
事務(wù)1：持有主鍵索引的 X 鎖
*** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 67 page no 5 n bits 72 index idx_b of table d1.t3 trx id 50AE lock_mode X locks rec but not gap waiting
事務(wù)1：想獲取普通索引 idx_b 的 X 鎖
與我們分析的完全一致，也與線上的死鎖日志一模一樣
以上就是本文的全部?jī)?nèi)容，希望對(duì)大家的學(xué)習(xí)有所幫助，也希望大家多多支持本站。
版權(quán)聲明：本站文章來(lái)源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請(qǐng)保持原文完整并注明來(lái)源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非maisonbaluchon.cn所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來(lái)源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來(lái)，僅供學(xué)習(xí)參考，不代表本站立場(chǎng)，如有內(nèi)容涉嫌侵權(quán)，請(qǐng)聯(lián)系alex-e#qq.com處理。
關(guān)注官方微信