您好,登錄后才能下訂單哦!
這篇文章給大家分享的是有關如何使用不同的索引更新解決MySQL死鎖的內容。小編覺得挺實用的,因此分享給大家做個參考,一起跟隨小編過來看看吧。
示例如下
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語句如下 # 事務1:t1 update t3 set b = '' where a = "1"; # 事務2:t2 update t3 set b = '' where b = "2";
兩條語句造成死鎖的情況用手動的方式比較難復現,我們先來分析一下加鎖的過程
第一條語句(通過唯一索引去更新記錄)
update t3 set b = '' where a = "1";
整理一下,加了3個X鎖,順序分別是
序號 | 索引 | 鎖類型 |
---|---|---|
1 | uk_a | X |
2 | PRIMARY | X |
3 | idx_b | X |
第二條語句
update t3 set b = '' where b = "2";
整理一下,加了 3 個 X 鎖,順序分別是
序號 | 索引 | 鎖類型 |
---|---|---|
1 | idx_b | X |
2 | PRIMARY | X |
3 | idx_b | X |
兩條語句從加鎖順序看起來就已經有構成死鎖的條件了
手動是比較難模擬的,寫個代碼并發的去同時執行那兩條 SQL 語句,馬上就出現死鎖了
------------------------ 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
事務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
事務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
事務1:想獲取普通索引 idx_b 的 X 鎖
與我們分析的完全一致,也與線上的死鎖日志一模一樣
感謝各位的閱讀!關于“如何使用不同的索引更新解決MySQL死鎖”這篇文章就分享到這里了,希望以上內容可以對大家有一定的幫助,讓大家可以學到更多知識,如果覺得文章不錯,可以把它分享出去讓更多的人看到吧!
免責聲明:本站發布的內容(圖片、視頻和文字)以原創、轉載和分享為主,文章觀點不代表本網站立場,如果涉及侵權請聯系站長郵箱:is@yisu.com進行舉報,并提供相關證據,一經查實,將立刻刪除涉嫌侵權內容。