建站
咨詢
售后
建站咨詢
新聞中心
數(shù)據(jù)庫死鎖，是最難調試與追蹤的。
目前成都創(chuàng)新互聯(lián)已為成百上千的企業(yè)提供了網(wǎng)站建設、域名、網(wǎng)絡空間、成都網(wǎng)站托管、企業(yè)網(wǎng)站設計、潛山網(wǎng)站維護等服務，公司將堅持客戶導向、應用為本的策略，正道將秉承"和諧、參與、激情"的文化，與客戶和合作伙伴齊心協(xié)力一起成長，共同發(fā)展。
場景如下：
同一個表，事務內先插入一條記錄，再更新這條記錄，并發(fā)時會死鎖。
并且能夠復現(xiàn)。
可以通過什么工具模擬并發(fā)事務，查看信息，解決問題呢?這是今天要分享的內容。
一、前置準備
- set session transaction isolation level repeatable read;
- set session autocommit=0;
- create table t (
- id int(20) primary key AUTO_INCREMENT,
- cell varchar(20) unique
- )engine=innodb;
- start transaction;
- insert into t(cell) values(11111111111);
- insert into t(cell) values(22222222222);
- insert into t(cell) values(33333333333);
- commit;
說明：
- 案發(fā)時，事務隔離級別RR;
- 多終端實驗，需要關閉事務自動提交;
- 建表，設置PK與unique，初始化數(shù)據(jù);
二、并發(fā)事務模擬
- Session A：
- start transaction;
- insert into t(cell)values(44444444444); [1]
- Session B：
- start transaction;
- insert into t(cell) values(55555555555); [2]
- update t set cell=123 where cell=44444444444; [3]
- update t set cell=456 where cell=55555555555; [4]
開啟兩個終端模擬并發(fā)事務：
- 紅色SQL為事務A;
- 黑色SQL為事務B;
- [1][2][3][4]為執(zhí)行時序;
三、實驗現(xiàn)象
- insert into t(cell)values(44444444444); [1]
事務A插入數(shù)據(jù)，***執(zhí)行
結果：插入成功
- insert into t(cell) values(55555555555); [2]
事務B插入數(shù)據(jù)，第二執(zhí)行
結果：插入成果
- update t set cell=123 where cell=44444444444; [3]
事務A修改[1]中插入的數(shù)據(jù)，第三執(zhí)行
結果：阻塞，等待執(zhí)行結果
畫外音：修改一條自己插入的數(shù)據(jù)，在等待什么呢?
- update t set cell=456 where cell=55555555555; [4]
事務B修改[2]中插入的數(shù)據(jù)，***執(zhí)行
結果：
- 事務B死鎖，事務B被回滾;
- 事務A中，[3]語句阻塞結束，執(zhí)行成功;
畫外音：說明事務A中阻塞的語句，確實在等事務B中的某個鎖。
四、結果分析
兩個事務，各自修改自己插入的數(shù)據(jù)，卻產生了死鎖，確實詭異。
上述實驗現(xiàn)象的兩個核心問題是：
- 語句[3]阻塞，在等待什么鎖?
- 語句[4]死鎖，此時事務A和事務B一定是彼此占住一把鎖，請求彼此的鎖，這些鎖又是什么呢?
工具一：
- show engine innodb status;
執(zhí)行之后，顯示的內容如下(放大仔細看)：
信息很多，別急，樓主娓娓道來。
***部分，關鍵詞是：
- Transaction 1，事務3998;
- 在執(zhí)行
- update t set cell=123 where cell=44444444444;
畫外音：英文比較差沒事，抓關鍵詞。
畫外音，InnoDB存儲引擎，聚集索引與非聚集索引的實現(xiàn)方式，決定了鎖會加在聚集索引上，詳見文章：
《1分鐘了解MyISAM與InnoDB的索引差異》。
第二部分，關鍵詞是：
- Transaction 2，事務3999;
- 正在執(zhí)行
- update t set cell=456 where cell=55555555555;
通過show engine innodb status; 能夠看到很多事務與鎖之間的信息，對分析問題十分有幫助，這些信息，能夠解釋一些問題，但仍有兩個疑惑：
(1)事務1為啥想拿55555555555的鎖?
畫外音：這正是，事務1被阻塞的原因。
(2)事務2為啥想拿11111111111的鎖?死鎖的發(fā)生，說明事務1此時真占著11111111111的鎖，這又是為什么呢?
畫外音：***個事務占111搶555，第二個事務占555搶111，循環(huán)嵌套，才會死鎖。
工具二：
- explain
為了進一步尋找原因，可以通過explain看下導致死鎖語句的執(zhí)行計劃。
- explain update t set cell=456 where cell=55555555555;
(1) select_type：SIMPLE
這是一個簡單類型的SQL語句，不含子查詢或者UNION。
(2) type：index
訪問類型，即找到所需數(shù)據(jù)使用的遍歷方式，潛在的方式有：
- ALL(Full Table Scan)：全表掃描;
- index：走索引的全表掃描;
- range：***where子句的范圍索引掃描;
- ref/eq_ref：非唯一索引/唯一索引單值掃描;
- const/system：常量掃描;
- NULL：不用訪問表;
上述掃描方式，ALL最慢，逐步變快，NULL最快。
懷疑點1：明明cell字段有uniq索引，為何要進行走PK索引的全表掃描呢?
(3) possible_keys：NULL
可能在哪個索引找到記錄。
(4) key：PRIMARY
實際使用索引。
畫外音：使用PK進行的全表掃描。
(5) ref：NULL
哪些列，或者常量用于查找索引上的值。
懷疑點2：where條件中的查詢條件55555555555，本來應該作為在索引上被檢索的值呀?
(6) rows：5
找到所需記錄，預估需要讀取的行數(shù)。
懷疑點3：明明修改的是5，為何初始化的1，2，3，以及***個事務插入的4，以及第二個事務插入的5，都要被讀取呢?不應該全表掃描呀。
通過explain，基本已經可以判斷：
- update t set cell=456 where cell=55555555555;
并沒有和我們預想一樣，走cell索引進行查詢，而是走了PK索引進行了全表掃描。
再仔細一看：
- create table t (
- id int(20) primary key AUTO_INCREMENT,
- cell varchar(20) unique
- )engine=innodb;
建表的時候cell定義的是字符串類型。
而更新的時候，
- update t set cell=456 where cell=55555555555;
使用的是整數(shù)類型。
類型轉換，會導致全表掃描，出現(xiàn)鎖升級，鎖住全部記錄。
加上引號，再次通過explain驗證一下：
- explain update t set cell= '456 ' where cell= '55555555555 ';
果然印證了猜想：
- type：range，變?yōu)榱俗咚饕淖址葘Γ秶鷴呙?
- possible_keys：cell，通過cell索引找到了記錄;
- key：cell，實際使用cell索引;
- ref：const，使用了常量' 555'進行比對;
- rows：1，預估讀取行數(shù)是1;
這下全部可以解釋了。
總結
就本例而言：需要注意字符串與整數(shù)之間的強制類型轉換，有時候少一個引號，就會使得行鎖升級為表鎖。
死鎖是MySQL中非常難調試的問題，常見的思路與方法有：
- 通過多終端模擬并發(fā)事務，復現(xiàn)死鎖;
- 通過show engine innodb status; 可以查看事務與鎖的信息;
- 通過explain可以查看執(zhí)行計劃;
思路比結論更重要，希望大家有收獲。
【本文為專欄作者“58沈劍”原創(chuàng)稿件，轉載請聯(lián)系原作者】
文章標題：兩個小工具，MySQL死鎖分析，新技能又Get！
網(wǎng)頁網(wǎng)址：http://fisionsoft.com.cn/article/djpoosh.html
