MySql一個生產死鎖案例分析
接到上級一個生產環境MySQL死鎖日志信息文件，需要找出原因并解決問題。我將死鎖日志部分貼出如下：
在mysql中使用命令：SHOW ENGINE INNODB STATUS;總能獲取到最近一些問題信息，通過搜索deadlock 關鍵字即可找到死鎖的相關日志信息。
2019-09-25 13:28:25 7fc0301ca700InnoDB: transactions deadlock detected, dumping detailed information. 2019-09-25 13:28:25 7fc0301ca700 *** (1) TRANSACTION: TRANSACTION 48000896642, ACTIVE 0 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 5 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 5 MySQL thread id 1585229, OS thread handle 0x7fc030e7c700, query id 36142536228 100.64.177.170 oms8 updating update tb_elc_forecast set capital = capital - 1.0, outbound = outbound + 1.0 where id = 1131766 and capital >= 1.0 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 1697 page no 31889 n bits 120 index `PRIMARY` of table `oms8`.`tb_elc_forecast` trx id 48000896642 lock_mode X locks rec but not gap waiting Record lock, heap no 20 PHYSICAL RECORD: n_fields 46; compact format; info bits 0 0: len 4; hex 801144f6; asc D ;; 1: len 6; hex 000b2d138e83; asc - ;; 2: len 7; hex 1b0013b0a9145c; asc \;; 3: len 30; hex 3637666533626536382d616562622d313165392d393838332d6436636337; asc 67fe3be68-aebb-11e9-9883-d6cc7; (total 37 bytes); 4: len 10; hex 4d324c50303530303030; asc M2LP050000;; 5: len 11; hex 5730323130313439393137; asc W0210149917;; 6: len 4; hex 20bcbe4c; asc L;; 7: len 4; hex 000080bf; asc ;; 8: len 4; hex 006e2f47; asc n/G;; 9: len 4; hex 005c5646; asc \VF;; 10: len 4; hex 00000000; asc ;; 11: len 7; hex 323031392d3039; asc 2019-09;; 12: len 9; hex 4252414e4453495445; asc BRANDSITE;; 13: len 14; hex 776d735f656c635f62616f7a756e; asc wms_elc_baozun;; 14: len 4; hex 80000002; asc ;; 15: len 4; hex 80000001; asc ;; 16: len 4; hex 80000000; asc ;; 17: len 5; hex 99a3b313ab; asc ;; 18: SQL NULL; 19: len 3; hex 737973; asc sys;; 20: SQL NULL; 21: len 4; hex 80019737; asc 7;; 22: SQL NULL; 23: SQL NULL; 24: SQL NULL; 25: SQL NULL; 26: SQL NULL; 27: len 2; hex 3130; asc 10;; 28: len 12; hex 4252414e44534954455f3130; asc BRANDSITE_10;; 29: SQL NULL; 30: len 30; hex 494c4c554d494e4154494e4720464143452042415345205320374d4c2f2e; asc ILLUMINATING FACE BASE S 7ML/.; (total 88 bytes); 31: len 9; hex 4252414e4453495445; asc BRANDSITE;; 32: len 1; hex 59; asc Y;; 33: len 1; hex 59; asc Y;; 34: len 4; hex 80000000; asc ;; 35: len 0; hex ; asc ;; 36: len 4; hex 00000000; asc ;; 37: len 4; hex 00000000; asc ;; 38: len 4; hex 80000000; asc ;; 39: len 4; hex 80000000; asc ;; 40: SQL NULL; 41: SQL NULL; 42: len 4; hex 00000000; asc ;; 43: len 4; hex 00000000; asc ;; 44: SQL NULL; 45: SQL NULL; *** (2) TRANSACTION: TRANSACTION 48000896643, ACTIVE 0 sec starting index read mysql tables in use 1, locked 1 4 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 3 MySQL thread id 1584766, OS thread handle 0x7fc0301ca700, query id 36142536229 100.64.177.168 oms8 updating update tb_elc_forecast set capital = capital - 1.0, outbound = outbound + 1.0 where id = 1164727 and capital >= 1.0 *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 1697 page no 31889 n bits 120 index `PRIMARY` of table `oms8`.`tb_elc_forecast` trx id 48000896643 lock_mode X locks rec but not gap Record lock, heap no 20 PHYSICAL RECORD: n_fields 46; compact format; info bits 0 0: len 4; hex 801144f6; asc D ;; 1: len 6; hex 000b2d138e83; asc - ;; 2: len 7; hex 1b0013b0a9145c; asc \;; 3: len 30; hex 3637666533626536382d616562622d313165392d393838332d6436636337; asc 67fe3be68-aebb-11e9-9883-d6cc7; (total 37 bytes); 4: len 10; hex 4d324c50303530303030; asc M2LP050000;; 5: len 11; hex 5730323130313439393137; asc W0210149917;; 6: len 4; hex 20bcbe4c; asc L;; 7: len 4; hex 000080bf; asc ;; 8: len 4; hex 006e2f47; asc n/G;; 9: len 4; hex 005c5646; asc \VF;; 10: len 4; hex 00000000; asc ;; 11: len 7; hex 323031392d3039; asc 2019-09;; 12: len 9; hex 4252414e4453495445; asc BRANDSITE;; 13: len 14; hex 776d735f656c635f62616f7a756e; asc wms_elc_baozun;; 14: len 4; hex 80000002; asc ;; 15: len 4; hex 80000001; asc ;; 16: len 4; hex 80000000; asc ;; 17: len 5; hex 99a3b313ab; asc ;; 18: SQL NULL; 19: len 3; hex 737973; asc sys;; 20: SQL NULL; 21: len 4; hex 80019737; asc 7;; 22: SQL NULL; 23: SQL NULL; 24: SQL NULL; 25: SQL NULL; 26: SQL NULL; 27: len 2; hex 3130; asc 10;; 28: len 12; hex 4252414e44534954455f3130; asc BRANDSITE_10;; 29: SQL NULL; 30: len 30; hex 494c4c554d494e4154494e4720464143452042415345205320374d4c2f2e; asc ILLUMINATING FACE BASE S 7ML/.; (total 88 bytes); 31: len 9; hex 4252414e4453495445; asc BRANDSITE;; 32: len 1; hex 59; asc Y;; 33: len 1; hex 59; asc Y;; 34: len 4; hex 80000000; asc ;; 35: len 0; hex ; asc ;; 36: len 4; hex 00000000; asc ;; 37: len 4; hex 00000000; asc ;; 38: len 4; hex 80000000; asc ;; 39: len 4; hex 80000000; asc ;; 40: SQL NULL; 41: SQL NULL; 42: len 4; hex 00000000; asc ;; 43: len 4; hex 00000000; asc ;; 44: SQL NULL; 45: SQL NULL; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 1697 page no 33024 n bits 120 index `PRIMARY` of table `oms8`.`tb_elc_forecast` trx id 48000896643 lock_mode X locks rec but not gap waiting Record lock, heap no 5 PHYSICAL RECORD: n_fields 46; compact format; info bits 0 0: len 4; hex 8011c5b7; asc ;; 1: len 6; hex 000b2d138e82; asc - ;; 2: len 7; hex 1a0013b08b1598; asc ;; 3: len 30; hex 3638303365643664302d616562622d313165392d613830332d6436636337; asc 6803ed6d0-aebb-11e9-a803-d6cc7; (total 37 bytes); 4: len 10; hex 53383648343030303030; asc S86H400000;; 5: len 11; hex 5730323130313439393137; asc W0210149917;; 6: len 4; hex 20bcbe4c; asc L;; 7: len 4; hex 000080bf; asc ;; 8: len 4; hex 4001b448; asc @ H;; 9: len 4; hex 80c6de47; asc G;; 10: len 4; hex 00000000; asc ;; 11: len 7; hex 323031392d3039; asc 2019-09;; 12: len 9; hex 4252414e4453495445; asc BRANDSITE;; 13: len 14; hex 776d735f656c635f62616f7a756e; asc wms_elc_baozun;; 14: len 4; hex 80000002; asc ;; 15: len 4; hex 80000001; asc ;; 16: len 4; hex 80000000; asc ;; 17: len 5; hex 99a3b313b1; asc ;; 18: SQL NULL; 19: len 3; hex 737973; asc sys;; 20: SQL NULL; 21: len 4; hex 80019737; asc 7;; 22: SQL NULL; 23: SQL NULL; 24: SQL NULL; 25: SQL NULL; 26: SQL NULL; 27: len 2; hex 3130; asc 10;; 28: len 12; hex 4252414e44534954455f3130; asc BRANDSITE_10;; 29: SQL NULL; 30: len 30; hex 494c4c554d494e4154494e4720464143452042415345205320374d4c2f2e; asc ILLUMINATING FACE BASE S 7ML/.; (total 88 bytes); 31: len 9; hex 4252414e4453495445; asc BRANDSITE;; 32: len 1; hex 59; asc Y;; 33: len 1; hex 59; asc Y;; 34: len 4; hex 80000000; asc ;; 35: len 0; hex ; asc ;; 36: len 4; hex 00000000; asc ;; 37: len 4; hex 00000000; asc ;; 38: len 4; hex 80000000; asc ;; 39: len 4; hex 80000000; asc ;; 40: SQL NULL; 41: SQL NULL; 42: len 4; hex 00000000; asc ;; 43: len 4; hex 00000000; asc ;; 44: SQL NULL; 45: SQL NULL; *** WE ROLL BACK TRANSACTION (2)
通過分析日志，我們知道如下信息：
1.發生死鎖的兩個事務中的SQL
事務1.update tb_elc_forecast set capital = capital - 1.0, outbound = outbound + 1.0 where id = 1131766 and capital >= 1.0 事務2.update tb_elc_forecast set capital = capital - 1.0, outbound = outbound + 1.0 where id = 1164727 and capital >= 1.0
2.兩條SQL都使用了Record lock(locks rec but not gap)記錄鎖(非間隙所)
通過查看表結構定義得知tb_elc_forecast將id字段定義為主鍵，表結構如下(精簡版)：
CREATE TABLE `tb_elc_forecast` ( `id` int(10) NOT NULL AUTO_INCREMENT COMMENT 'id自增', `sku` varchar(50) NOT NULL COMMENT '商品', `customerid` varchar(50) DEFAULT NULL COMMENT '貨主', `capital` float(10,2) DEFAULT '0.00' COMMENT '占用', `outbound` float(10,2) DEFAULT '0.00' COMMENT '出庫', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
通過以上信息，我們不難發現，肇事SQL會先對主鍵聚集索引加記錄排他鎖，而不是間隙鎖。理論上好像怎么都不會產生死鎖。因為這里只有一個臨界資源，那就是聚集索引。從理論上來說，要發生死鎖，必須存在至少兩個臨界資源，兩個獨立事務，且每個事務執行過程中相間執行，互鎖對方需要的臨界資源，從而導致死鎖。示意圖如下:
通過以上死鎖理論理解分析，似乎光看這兩個肇事SQL，不足以發生死鎖的條件。那是為什么呢？我們不能孤立在這堆死鎖日志里分析問題，應該去程序代碼里找，找到肇事SQL所在的事務代碼才行。這個死鎖日志只是表達了發生死鎖時涉事的兩個SQL。并不一定能直接分析得出問題結論。
根據目前的情況，我做出如下假設：
1.事務A執行update tb_elc_forecast set capital = capital - 1.0, outbound = outbound + 1.0 where id = 1131766 and capital >= 1.0 前，id=1131766的記錄被事務B查詢了，加了共享鎖在可重復讀事務隔離級別下。同樣事務B執行update tb_elc_forecast set capital = capital - 1.0, outbound = outbound + 1.0 where id = 1164727 and capital >= 1.0 前，id=1164727的記錄被事務A查詢了，加了共享鎖在可重復讀事務隔離級別下。最終兩個事務的update語句無法獲取id記錄的排他鎖導致死鎖。
那么事情果真如此嗎？通過模擬測試，結果是否定的。
事實上MySQL對于查詢語句，只要查詢完成就會釋放共享鎖，而不必等待事務結束，且和事務隔離級別無關。所以此種情況應該排除。
2.T1時刻事務A執行update tb_elc_forecast set capital = capital - 1.0, outbound = outbound + 1.0 where id = 1131766 and capital >= 1.0 事務B執行update tb_elc_forecast set capital = capital - 1.0, outbound = outbound + 1.0 where id = 1164727
and capital >= 1.0
T2時刻事務A執行update tb_elc_forecast set capital = capital - 1.0, outbound = outbound + 1.0 where id = 1164727 and capital >= 1.0 事務B執行update tb_elc_forecast set capital = capital - 1.0, outbound = outbound + 1.0 where id = 1131766
and capital >= 1.0。每個事務通過執行兩個update操作，這樣形成了死鎖條件。
那么事情果真如此嗎？通過模擬測試，結果是肯定的。
結合死鎖圖示，這里的臨界資源1是id=1164727的記錄鎖，臨界資源是id=1131766 的記錄鎖。update操作會占用各自id的記錄鎖資源。
通過一番查找，比較容易找到程序里對應的事務代碼（使用了肇事SQL的業務邏輯代碼），貼出部分如下：
for (ElcForecastTran entity : insertList) { ElcForecast elcForecast = new ElcForecast(); String period = new SimpleDateFormat("yyyy-MM").format(request.getOrderTime()); elcForecast.setCustomerid(entity.getCustomerid()); elcForecast.setSku(entity.getSku()); elcForecast.setPeriod(period); elcForecast.setTp(entity.getTp()); elcForecast.setChannel(entity.getChannel()); elcForecast.setDepth(2); // 子代理 elcForecast.setIsActive(1); // 已發布 Integer id = elcForecastMapper.selectId(elcForecast); if(id == null){ isCapitalForecastFail = true; builder.append(entity.getSku()).append(","); logger.error("updateCapital fail,update forecast:{},request:{}",elcForecast,request); continue; } elcForecast.setId(id); elcForecast.setCapital(entity.getCapital()); // 占用額度 cnt = elcForecastMapper.updateCapitalOutbound(elcForecast); }
通過上下業務環境得知，此處事務方法，會存在同時被多線程調用問題，且代碼：elcForecastMapper.updateCapitalOutbound(elcForecast);置于循環語句中，更新的對象elcForecast再不同線程中可能會存在重復的對象，故形成上述2死鎖猜想條件。
那么怎么解決呢？可能有多種方法，一種是將事務拆分成小事務，不要把整個循環置于一個事務中。二是對此處事務加分布式鎖，保證一次只允許一個線程調用即可。具體方案視業務情況來定。
接下來我通過一個具體的死鎖實驗來講述以上死鎖發生的原理
首先打開兩個MySQL客戶端，并執行set SESSION autocommit=0;關閉事務自動提交功能
準備兩條測試SQL：update tb_elc_forecast set capital = capital - 1.0 where id=5133;和update tb_elc_forecast set capital = capital - 1.0 where id=5137;
分別在兩個客戶端執行事務啟動語句：START TRANSACTION;
客戶端A執行：update tb_elc_forecast set capital = capital - 1.0 where id=5133;
客戶端B執行：update tb_elc_forecast set capital = capital - 1.0 where id=5137;
客戶端A執行：update tb_elc_forecast set capital = capital - 1.0 where id=5137;
客戶端B執行：update tb_elc_forecast set capital = capital - 1.0 where id=5133;
此時你該看到客戶端B死鎖發生了，并犧牲了自己，讓客戶端A事務存活。
客戶端A執行：commit;
客戶端B執行：commit;
實驗如圖：
分別為客戶端A和B截圖
客戶端執行：SHOW ENGINE INNODB STATUS;
可以看到MySQL最近的死鎖信息記錄如下：
===================================== 2019-09-25 19:32:10 0x3a88 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 10 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 44 srv_active, 0 srv_shutdown, 4905 srv_idle srv_master_thread log flush and writes: 4949 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 40 OS WAIT ARRAY INFO: signal count 41 RW-shared spins 0, rounds 78, OS waits 35 RW-excl spins 0, rounds 39, OS waits 1 RW-sx spins 2, rounds 60, OS waits 2 Spin rounds per wait: 78.00 RW-shared, 39.00 RW-excl, 30.00 RW-sx ------------------------ LATEST DETECTED DEADLOCK ------------------------ 2019-09-25 19:31:55 0x52c4 *** (1) TRANSACTION: TRANSACTION 304429, ACTIVE 37 sec starting index read 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 13, OS thread handle 27608, query id 12406 localhost ::1 root updating update tb_elc_forecast set capital = capital - 1.0 where id=5137 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 467 page no 6 n bits 120 index PRIMARY of table `test`.`tb_elc_forecast` trx id 304429 lock_mode X locks rec but not gap waiting Record lock, heap no 6 PHYSICAL RECORD: n_fields 44; compact format; info bits 0 0: len 4; hex 80001411; asc ;; 1: len 6; hex 00000004a52e; asc .;; 2: len 7; hex 22000002f72374; asc " #t;; 3: len 30; hex 39643231346264652d656532312d343034612d393862632d396261356232; asc 9d214bde-ee21-404a-98bc-9ba5b2; (total 36 bytes); 4: len 10; hex 304d4d30303130303030; asc 0MM0010000;; 5: len 7; hex 59534c44303031; asc YSLD001;; 6: len 4; hex 0000a041; asc A;; 7: len 4; hex 000080bf; asc ;; 8: len 4; hex 000040c0; asc @ ;; 9: len 4; hex 00000000; asc ;; 10: len 4; hex 00000000; asc ;; 11: len 7; hex 323031392d3033; asc 2019-03;; 12: len 9; hex 4252414e4453495445; asc BRANDSITE;; 13: len 14; hex 776d735f656c635f62616f7a756e; asc wms_elc_baozun;; 14: len 4; hex 80000002; asc ;; 15: len 4; hex 80000000; asc ;; 16: len 4; hex 80000000; asc ;; 17: len 5; hex 99a282e9bb; asc ;; 18: SQL NULL; 19: len 6; hex 363739303539; asc 679059;; 20: SQL NULL; 21: len 4; hex 8000140d; asc ;; 22: SQL NULL; 23: SQL NULL; 24: SQL NULL; 25: SQL NULL; 26: SQL NULL; 27: len 2; hex 3031; asc 01;; 28: len 12; hex 4252414e44534954455f3031; asc BRANDSITE_01;; 29: SQL NULL; 30: len 30; hex 4c4f43204332204242205741544552e280bbe580a9e7a2a7e6b481e99da2; asc LOC C2 BB WATER ; (total 45 bytes); 31: len 9; hex 4252414e4453495445; asc BRANDSITE;; 32: len 1; hex 59; asc Y;; 33: len 1; hex 59; asc Y;; 34: len 4; hex 80000000; asc ;; 35: SQL NULL; 36: len 4; hex 00000000; asc ;; 37: len 4; hex 00000000; asc ;; 38: len 4; hex 80000000; asc ;; 39: len 4; hex 80000000; asc ;; 40: SQL NULL; 41: SQL NULL; 42: len 4; hex 00000000; asc ;; 43: len 4; hex 00000000; asc ;; *** (2) TRANSACTION: TRANSACTION 304430, ACTIVE 30 sec starting index read, 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 14, OS thread handle 21188, query id 12407 localhost ::1 root updating update tb_elc_forecast set capital = capital - 1.0 where id=5133 *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 467 page no 6 n bits 120 index PRIMARY of table `test`.`tb_elc_forecast` trx id 304430 lock_mode X locks rec but not gap Record lock, heap no 6 PHYSICAL RECORD: n_fields 44; compact format; info bits 0 0: len 4; hex 80001411; asc ;; 1: len 6; hex 00000004a52e; asc .;; 2: len 7; hex 22000002f72374; asc " #t;; 3: len 30; hex 39643231346264652d656532312d343034612d393862632d396261356232; asc 9d214bde-ee21-404a-98bc-9ba5b2; (total 36 bytes); 4: len 10; hex 304d4d30303130303030; asc 0MM0010000;; 5: len 7; hex 59534c44303031; asc YSLD001;; 6: len 4; hex 0000a041; asc A;; 7: len 4; hex 000080bf; asc ;; 8: len 4; hex 000040c0; asc @ ;; 9: len 4; hex 00000000; asc ;; 10: len 4; hex 00000000; asc ;; 11: len 7; hex 323031392d3033; asc 2019-03;; 12: len 9; hex 4252414e4453495445; asc BRANDSITE;; 13: len 14; hex 776d735f656c635f62616f7a756e; asc wms_elc_baozun;; 14: len 4; hex 80000002; asc ;; 15: len 4; hex 80000000; asc ;; 16: len 4; hex 80000000; asc ;; 17: len 5; hex 99a282e9bb; asc ;; 18: SQL NULL; 19: len 6; hex 363739303539; asc 679059;; 20: SQL NULL; 21: len 4; hex 8000140d; asc ;; 22: SQL NULL; 23: SQL NULL; 24: SQL NULL; 25: SQL NULL; 26: SQL NULL; 27: len 2; hex 3031; asc 01;; 28: len 12; hex 4252414e44534954455f3031; asc BRANDSITE_01;; 29: SQL NULL; 30: len 30; hex 4c4f43204332204242205741544552e280bbe580a9e7a2a7e6b481e99da2; asc LOC C2 BB WATER ; (total 45 bytes); 31: len 9; hex 4252414e4453495445; asc BRANDSITE;; 32: len 1; hex 59; asc Y;; 33: len 1; hex 59; asc Y;; 34: len 4; hex 80000000; asc ;; 35: SQL NULL; 36: len 4; hex 00000000; asc ;; 37: len 4; hex 00000000; asc ;; 38: len 4; hex 80000000; asc ;; 39: len 4; hex 80000000; asc ;; 40: SQL NULL; 41: SQL NULL; 42: len 4; hex 00000000; asc ;; 43: len 4; hex 00000000; asc ;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 467 page no 6 n bits 120 index PRIMARY of table `test`.`tb_elc_forecast` trx id 304430 lock_mode X locks rec but not gap waiting Record lock, heap no 54 PHYSICAL RECORD: n_fields 44; compact format; info bits 0 0: len 4; hex 8000140d; asc ;; 1: len 6; hex 00000004a52d; asc -;; 2: len 7; hex 21000002d522ea; asc ! " ;; 3: len 30; hex 34326237346466332d383133372d346639372d393435382d613939613266; asc 42b74df3-8137-4f97-9458-a99a2f; (total 36 bytes); 4: len 10; hex 304d4d30303130303030; asc 0MM0010000;; 5: len 7; hex 59534c44303031; asc YSLD001;; 6: len 4; hex 00004842; asc HB;; 7: len 4; hex 000080bf; asc ;; 8: len 4; hex 000040c0; asc @ ;; 9: SQL NULL; 10: len 4; hex 00004842; asc HB;; 11: len 7; hex 323031392d3033; asc 2019-03;; 12: SQL NULL; 13: SQL NULL; 14: len 4; hex 80000001; asc ;; 15: len 4; hex 80000002; asc ;; 16: len 4; hex 80000001; asc ;; 17: len 5; hex 99a282e9bb; asc ;; 18: len 5; hex 99a288b39e; asc ;; 19: len 6; hex 363739303539; asc 679059;; 20: SQL NULL; 21: SQL NULL; 22: SQL NULL; 23: SQL NULL; 24: SQL NULL; 25: SQL NULL; 26: SQL NULL; 27: len 2; hex 3031; asc 01;; 28: len 12; hex 4252414e44534954455f3031; asc BRANDSITE_01;; 29: SQL NULL; 30: len 30; hex 4c4f43204332204242205741544552e280bbe580a9e7a2a7e6b481e99da2; asc LOC C2 BB WATER ; (total 45 bytes); 31: len 9; hex 4252414e4453495445; asc BRANDSITE;; 32: len 1; hex 59; asc Y;; 33: len 1; hex 59; asc Y;; 34: len 4; hex 80000000; asc ;; 35: SQL NULL; 36: len 4; hex 00006041; asc `A;; 37: len 4; hex 00000000; asc ;; 38: len 4; hex 80000002; asc ;; 39: len 4; hex 80000001; asc ;; 40: len 21; hex 4f4d53e5ba93e5ad98e69fa5e8afa2e68890e58a9f; asc OMS ;; 41: len 30; hex 61303730393438612d633531642d346331362d616433392d393434393038; asc a070948a-c51d-4c16-ad39-944908; (total 36 bytes); 42: len 4; hex 00000000; asc ;; 43: len 4; hex 00000000; asc ;; *** WE ROLL BACK TRANSACTION (2) ------------ TRANSACTIONS ------------ Trx id counter 304432 Purge done for trx's n:o < 304432 undo n:o < 0 state: running but idle History list length 13 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 283332952971320, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 283332952973936, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 283332952973064, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 283332952972192, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 283332952968704, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 283332952967832, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 283332952966960, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 304429, ACTIVE 52 sec 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 2 MySQL thread id 13, OS thread handle 27608, query id 12406 localhost ::1 root ---TRANSACTION 304408, ACTIVE 4442 sec 2 lock struct(s), heap size 1136, 1 row lock(s) MySQL thread id 9, OS thread handle 14984, query id 12411 localhost 127.0.0.1 root starting show ENGINE INNODB STATUS ---TRANSACTION 304407, ACTIVE 4450 sec 2 lock struct(s), heap size 1136, 1 row lock(s) MySQL thread id 8, OS thread handle 25856, query id 12278 localhost 127.0.0.1 root -------- FILE I/O -------- I/O thread 0 state: wait Windows aio (insert buffer thread) I/O thread 1 state: wait Windows aio (log thread) I/O thread 2 state: wait Windows aio (read thread) I/O thread 3 state: wait Windows aio (read thread) I/O thread 4 state: wait Windows aio (read thread) I/O thread 5 state: wait Windows aio (read thread) I/O thread 6 state: wait Windows aio (write thread) I/O thread 7 state: wait Windows aio (write thread) I/O thread 8 state: wait Windows aio (write thread) I/O thread 9 state: wait Windows aio (write thread) Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] , ibuf aio reads:, log i/o's:, sync i/o's: Pending flushes (fsync) log: 0; buffer pool: 0 609 OS file reads, 1233 OS file writes, 226 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 207, seg size 209, 0 merges merged operations: insert 0, delete mark 0, delete 0 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 2267, node heap has 0 buffer(s) Hash table size 2267, node heap has 1 buffer(s) Hash table size 2267, node heap has 0 buffer(s) Hash table size 2267, node heap has 0 buffer(s) Hash table size 2267, node heap has 0 buffer(s) Hash table size 2267, node heap has 0 buffer(s) Hash table size 2267, node heap has 0 buffer(s) Hash table size 2267, node heap has 0 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 412469041 Log flushed up to 412469041 Pages flushed up to 412469041 Last checkpoint at 412469032 0 pending log flushes, 0 pending chkp writes 118 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total large memory allocated 8585216 Dictionary memory allocated 128725 Buffer pool size 512 Free buffers 255 Database pages 256 Old database pages 0 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 580, created 672, written 1023 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 256, unzip_LRU len: 0 I/O sum[10]:cur[0], unzip sum[0]:cur[0] -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 0 read views open inside InnoDB Process ID=27356, Main thread ID=21352, state: sleeping Number of rows inserted 13258, updated 11, deleted 0, read 108651 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================
關于死鎖測試，最好使用MySQL自帶的Console客戶端實驗，不要使用第三方可視化工具，效果不太好。
浙公網安備 33010602011771號