生活随笔
收集整理的這篇文章主要介紹了
MySQL死锁分析
小編覺得挺不錯的,現在分享給大家,幫大家做個參考.
1. 測試描述
環境說明：RHEL 6.4 x86_64 + MySQL 5.5.37，事務隔離級別為RC
測試表：
[sql]?view plaincopy print?
mysql>?show?create?table?t1\G?? ***************************?1.?row?***************************?? ???????Table:?t1?? Create?Table:?CREATE?TABLE?`t1`?(?? ??`a`?int(11)?NOT?NULL?DEFAULT?'0',?? ??`b`?int(11)?DEFAULT?NULL,?? ??PRIMARY?KEY?(`a`),?? ??KEY?`b`?(`b`)?? )?ENGINE=InnoDB?DEFAULT?CHARSET=utf8?? 1?row?in?set?(0.00?sec)??
測試表中的數據：
[sql]?view plaincopy print?
mysql>?select?*?from?t1;?? +?? |?a??|?b????|?? +?? |??1?|????1?|?? |??3?|????3?|?? |??5?|????6?|?? +?? 3?rows?in?set?(0.01?sec)??
2. 測試過程
3. 死鎖日志分析
此時查詢show engine innodb status\G查看死鎖信息，下面只摘取了死鎖信息部分，其他的省略。
[html]?view plaincopy print?
------------------------?? LATEST?DETECTED?DEADLOCK?? ------------------------?? 140824??1:01:24?? ***?(1)?TRANSACTION:?? TRANSACTION?110E,?ACTIVE?73?sec?starting?index?read???##?事務ID=110E，活躍了73s?? mysql?tables?in?use?1,?locked?1?? LOCK?WAIT?3?lock?struct(s),?heap?size?376,?2?row?lock(s)??##?有2個行鎖?? MySQL?thread?id?1,?OS?thread?handle?0x7f55ea639700,?query?id?81?localhost?root?updating??##?該事務的線程ID=1?? delete?from?t1?where?a=1???##?這是當前事務執行的SQL?? ***?(1)?WAITING?FOR?THIS?LOCK?TO?BE?GRANTED:??##?上面SQL等待的鎖信息?? RECORD?LOCKS?space?id?12?page?no?3?n?bits?80?index?`PRIMARY`?of?table?`test`.`t1`?trx?id?110E?lock_mode?X?locks?rec?but?not?gap?waiting?? Record?lock,?heap?no?2?PHYSICAL?RECORD:?n_fields?4;?compact?format;?info?bits?32??##?等待在主鍵上的page?num=3上有加一個X鎖(not?gap?waiting)，鎖80?bits?? ?0:?len?4;?hex?80000001;?asc?????;;?? ?1:?len?6;?hex?00000000110c;?asc???????;;?? ?2:?len?7;?hex?0d000002350084;?asc?????5??;;?? ?3:?len?4;?hex?80000001;?asc?????;;?? ?? ***?(2)?TRANSACTION:?? TRANSACTION?110C,?ACTIVE?1716?sec?starting?index?read,?thread?declared?inside?InnoDB?500??##?事務ID=110C，活躍了1716s?? mysql?tables?in?use?1,?locked?1?? 3?lock?struct(s),?heap?size?376,?2?row?lock(s),?undo?log?entries?1??##?3個鎖，2個行鎖，1個undo?log?? MySQL?thread?id?2,?OS?thread?handle?0x7f563c05e700,?query?id?82?localhost?root?updating??##?該事務的線程ID=2?? delete?from?t1?where?a=3???##?這是當前事務執行的SQL?? ***?(2)?HOLDS?THE?LOCK(S):??##?這個事務持有的鎖信息?? RECORD?LOCKS?space?id?12?page?no?3?n?bits?80?index?`PRIMARY`?of?table?`test`.`t1`?trx?id?110C?lock_mode?X?locks?rec?but?not?gap?? Record?lock,?heap?no?2?PHYSICAL?RECORD:?n_fields?4;?compact?format;?info?bits?32??##?在主鍵上的page?num=3上已持有一個X鎖(not?gap)，鎖80?bits?? ?0:?len?4;?hex?80000001;?asc?????;;?? ?1:?len?6;?hex?00000000110c;?asc???????;;?? ?2:?len?7;?hex?0d000002350084;?asc?????5??;;?? ?3:?len?4;?hex?80000001;?asc?????;;?? ?? ***?(2)?WAITING?FOR?THIS?LOCK?TO?BE?GRANTED:??##?同時這個事務還等待的鎖信息?? RECORD?LOCKS?space?id?12?page?no?3?n?bits?80?index?`PRIMARY`?of?table?`test`.`t1`?trx?id?110C?lock_mode?X?locks?rec?but?not?gap?waiting?? Record?lock,?heap?no?3?PHYSICAL?RECORD:?n_fields?4;?compact?format;?info?bits?0??##?同樣等待在主鍵上的page?num=3上有加一個X鎖(not?gap?waiting)，鎖80?bits?? ?0:?len?4;?hex?80000003;?asc?????;;?? ?1:?len?6;?hex?000000000f71;?asc??????q;;?? ?2:?len?7;?hex?ed0000022f0090;?asc?????/??;;?? ?3:?len?4;?hex?80000003;?asc?????;;?? ?? ***?WE?ROLL?BACK?TRANSACTION?(1)???##?這里選擇回滾了事務110E。?? ?? 也就是說，這個死鎖使用事務110E中的SQL沒有執行，回滾了：?? delete?from?t1?where?a=1?? 而事務110C中的SQL是正常被執行的：?? delete?from?t1?where?a=3??
-- Bosco
---- END ----
-------------------------------------------------------------------------------------------------------
總結
以上是生活随笔為你收集整理的MySQL死锁分析的全部內容，希望文章能夠幫你解決所遇到的問題。
如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。
