溫馨提示×
您好，登錄后才能下訂單哦！
點(diǎn)擊 登錄注冊(cè) 即表示同意《億速云用戶服務(wù)條款》
您好，登錄后才能下訂單哦！
本篇文章為大家展示了MYSQL中一個(gè)特殊的MDL LOCK死鎖的示例分析，內(nèi)容簡(jiǎn)明扼要并且容易理解，絕對(duì)能使你眼前一亮，通過這篇文章的詳細(xì)介紹希望你能有所收獲。
一、問題由來
前段開發(fā)反饋時(shí)間線上數(shù)據(jù)庫(kù)老是出現(xiàn)死鎖情況，而我們?cè)O(shè)置了innodb_print_all_deadlocks，但是在
相應(yīng)的時(shí)間點(diǎn)沒有找到任何相應(yīng)的死鎖的信息，從而導(dǎo)致我們獲得任何有用的信息，也不能定位問題的
原因。
二、問題思考和分析
后來開發(fā)將出錯(cuò)碼發(fā)給我，我看到這個(gè)錯(cuò)誤碼確實(shí)是MYSQL報(bào)出來的如下：
{ "ER_LOCK_DEADLOCK", 1213, "Deadlock found when trying to get lock; try restarting transaction" },
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
那么能夠肯定一點(diǎn)這個(gè)死鎖是MYSQL報(bào)出來的。那么為什么innodb沒有任何表示呢？難道是什么BUG
繼而在
https://bugs.mysql.com/
找了一下BUGS也沒有找，后來我思考這個(gè)問題，既然是死鎖就會(huì)是相應(yīng)的死鎖檢測(cè)算法拋出來，我們知道MYSQL
上層還有MDL LOCK，并不是只有innodb相應(yīng)的lock才會(huì)有進(jìn)行死鎖檢測(cè)，會(huì)不是因?yàn)镸DL LOCK死鎖照成的呢？
我在MDL.CC中找到了如下代碼其實(shí)也是我上文說的MDL_context::acquire_lock 函數(shù)，上文說過這個(gè)函數(shù)是
根據(jù)MDL_REQUEST嘗試獲得MDL LOCK的主要函數(shù)。
點(diǎn)擊(此處)折疊或打開
switch (wait_status)
{
case MDL_wait::VICTIM:
my_error(ER_LOCK_DEADLOCK, MYF(0));
break;
case MDL_wait::TIMEOUT:
my_error(ER_LOCK_WAIT_TIMEOUT, MYF(0));
break;
case MDL_wait::KILLED:
if (get_owner()->is_killed() == ER_QUERY_TIMEOUT)
my_error(ER_QUERY_TIMEOUT, MYF(0));
else
my_error(ER_QUERY_INTERRUPTED, MYF(0));
break;
default:
DBUG_ASSERT(0);
break;
}
注意紅色部分，其實(shí)這里死鎖問題基本確定不是innodb層觸發(fā)的，既然不是innodb下層觸發(fā)，innodb當(dāng)然不會(huì)打印
出任何信息。為什么innodb層不打印死鎖信息的原因找到了，但是什么情況下會(huì)出現(xiàn)MDL LOCK的死鎖呢?
三、問題定位
既然要產(chǎn)生死鎖必須滿足一些條件：
1、至少2個(gè)獨(dú)立的線程(會(huì)話)。
2、單位操作中包含多個(gè)相對(duì)獨(dú)立的加鎖步驟，有一定的時(shí)間差
比如一個(gè)事物里面的多個(gè)操作
還比如repeat操作(雖然非常段也是有的)。 關(guān)于replace的死鎖問題參考我的文章如下：
http://blog.itpub.net/7728585/viewspace-2141409/
3、多個(gè)線程(會(huì)話)之間加鎖對(duì)象必須有相互等待的情況發(fā)生，并且等待出現(xiàn)環(huán)狀。
順便提一句，死鎖一般處理方式有3種
A、事前預(yù)測(cè)
B、資源分級(jí)
C、事后檢測(cè)釋放
而MDL LOCK和INNODB都使用了C時(shí)候檢測(cè)釋放，其算法應(yīng)該是利用圖的(DSF or BSF)遍歷進(jìn)行判定，這一塊以后要好好看一下。
那么我考慮到使用MYSQLDUMP進(jìn)行的備份的時(shí)候可能出現(xiàn)一些MDL LOCK的情況，比如加上-F, --flush-logs --single-transaction會(huì)
短暫的使用 flush table with read lock;
如下：
2017-08-08T06:22:44.916055Z 15 Connect root@localhost on using Socket
2017-08-08T06:22:44.916270Z 15 Query /*!40100 SET @@SQL_MODE='' */
2017-08-08T06:22:44.916521Z 15 Query /*!40103 SET TIME_ZONE='+00:00' */
2017-08-08T06:22:44.916604Z 15 Query FLUSH TABLES
2017-08-08T06:22:44.922889Z 15 Query FLUSH TABLES WITH READ LOCK
2017-08-08T06:22:44.923009Z 15 Refresh
/mysqldata/mysql5.7/bin/mysqld, Version: 5.7.13-log (Source distribution). started with:
Tcp port: 3307 Unix socket: /mysqldata/mysql5.7/mysqld3307.sock
Time Id Command Argument
2017-08-08T06:22:44.949003Z 15 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
2017-08-08T06:22:44.949089Z 15 Query START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
2017-08-08T06:22:44.949200Z 15 Query SHOW VARIABLES LIKE 'gtid\_mode'
2017-08-08T06:22:44.953060Z 15 Query SELECT @@GLOBAL.GTID_EXECUTED
2017-08-08T06:22:44.953160Z 15 Query UNLOCK TABLES
如前文所述這個(gè)操作會(huì)在GLOBAL上加一把S鎖
2017-08-03T18:19:11.603971Z 3 [Note] (--->MDL PRINT) Namespace is:GLOBAL
2017-08-03T18:19:11.603994Z 3 [Note] (----->MDL PRINT) Mdl type is:MDL_SHARED(S)
而導(dǎo)致所有的需要在GLOBAL上加IX鎖的操作全部等待比如(DML操作/DDL操作)，但是這里的flush table with read lock
并不符合上面描述的死鎖產(chǎn)生的條件，不管如何先檢查一下是不是MYSQLDUMP的問題，這一檢查真的檢查到了問題，我們
MYSQL備份的時(shí)候，進(jìn)行了一次單獨(dú)的表結(jié)構(gòu)的備份，同事認(rèn)為很快而沒有加--single-transaction，然后檢查備份結(jié)束的
時(shí)間，基本和死鎖出現(xiàn)的時(shí)間點(diǎn)一致，那么問題轉(zhuǎn)為如果不加--single-transaction，MYSQLDUMP如何加鎖。
實(shí)際上這個(gè)時(shí)候從general日志來看，加鎖如下：
2017-08-08T06:33:22.427691Z 20 Init DB dumptest
2017-08-08T06:33:22.427794Z 20 Query SHOW CREATE DATABASE IF NOT EXISTS `dumptest`
2017-08-08T06:33:22.428100Z 20 Query show tables
2017-08-08T06:33:22.428443Z 20 Query LOCK TABLES `kkkk` READ /*!32311 LOCAL */,`llll` READ /*!32311 LOCAL */
2017-08-08T06:33:22.428551Z 20 Query show table status like 'kkkk'
2017-08-08T06:33:22.428870Z 20 Query SET SQL_QUOTE_SHOW_CREATE=1
2017-08-08T06:33:22.428929Z 20 Query SET SESSION character_set_results = 'binary'
2017-08-08T06:33:22.429026Z 20 Query show create table `kkkk`
2017-08-08T06:33:22.429157Z 20 Query SET SESSION character_set_results = 'utf8'
2017-08-08T06:33:22.429212Z 20 Query show fields from `kkkk`
2017-08-08T06:33:22.429534Z 20 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `kkkk`
2017-08-08T06:33:22.429680Z 20 Query SET SESSION character_set_results = 'binary'
2017-08-08T06:33:22.429721Z 20 Query use `dumptest`
2017-08-08T06:33:22.429769Z 20 Query select @@collation_database
2017-08-08T06:33:22.429830Z 20 Query SHOW TRIGGERS LIKE 'kkkk'
2017-08-08T06:33:22.430141Z 20 Query SET SESSION character_set_results = 'utf8'
2017-08-08T06:33:22.430195Z 20 Query show table status like 'llll'
2017-08-08T06:33:22.430411Z 20 Query SET SQL_QUOTE_SHOW_CREATE=1
2017-08-08T06:33:22.430456Z 20 Query SET SESSION character_set_results = 'binary'
2017-08-08T06:33:22.430493Z 20 Query show create table `llll`
2017-08-08T06:33:22.430557Z 20 Query SET SESSION character_set_results = 'utf8'
2017-08-08T06:33:22.430599Z 20 Query show fields from `llll`
2017-08-08T06:33:22.430813Z 20 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `llll`
2017-08-08T06:33:22.430909Z 20 Query SET SESSION character_set_results = 'binary'
2017-08-08T06:33:22.430945Z 20 Query use `dumptest`
2017-08-08T06:33:22.431003Z 20 Query select @@collation_database
2017-08-08T06:33:22.431098Z 20 Query SHOW TRIGGERS LIKE 'llll'
2017-08-08T06:33:22.431330Z 20 Query SET SESSION character_set_results = 'utf8'
2017-08-08T06:33:22.431375Z 20 Query UNLOCK TABLES
我的dumptest數(shù)據(jù)只有兩個(gè)表kkkk和llll，我們可以看到mysqldump通過LOCK TABLES `kkkk` READ ,`llll` READ 進(jìn)行加鎖，
備份完成后使用unlock tables解鎖。其實(shí)這就是問題的根本原因。它會(huì)照成MDL死鎖的產(chǎn)生。如果加上--single-transaction 則不會(huì)，
會(huì)使用SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ改變隔離級(jí)別為RR使用事物來保證數(shù)據(jù)的一致，
而不是LOCK TABLES來保證數(shù)據(jù)的一致性。
四、模擬MDL死鎖和兼容性分析
首先我們需要看一下 LOCK TABLES a READ ,b READ 到底加了哪些MDL LOCK，這個(gè)通過我在MDL.CC中加入的打印函數(shù)my_print_ticket可以看到(具體在上文)
如下：
LOCK TABLES a READ ,b READ ;
2017-08-08T07:12:11.764164Z 2 [Note] (acquire_lock)THIS MDL LOCK acquire ok!
2017-08-08T07:12:11.764184Z 2 [Note] (>MDL PRINT) Thread id is 2:
2017-08-08T07:12:11.764201Z 2 [Note] (->MDL PRINT) DB_name is:test
2017-08-08T07:12:11.764258Z 2 [Note] (-->MDL PRINT) OBJ_name is:a
2017-08-08T07:12:11.764344Z 2 [Note] (--->MDL PRINT) Namespace is:TABLE
2017-08-08T07:12:11.764363Z 2 [Note] (----->MDL PRINT) Mdl type is:MDL_SHARED_READ_ONLY(SRO)
2017-08-08T07:12:11.764376Z 2 [Note] (------>MDL PRINT) Mdl duration is:MDL_TRANSACTION
2017-08-08T07:12:11.764586Z 2 [Note] (acquire_lock)THIS MDL LOCK acquire ok!
2017-08-08T07:12:11.764605Z 2 [Note] (>MDL PRINT) Thread id is 2:
2017-08-08T07:12:11.764620Z 2 [Note] (->MDL PRINT) DB_name is:test
2017-08-08T07:12:11.764634Z 2 [Note] (-->MDL PRINT) OBJ_name is:b
2017-08-08T07:12:11.764648Z 2 [Note] (--->MDL PRINT) Namespace is:TABLE
2017-08-08T07:12:11.764687Z 2 [Note] (----->MDL PRINT) Mdl type is:MDL_SHARED_READ_ONLY(SRO)
2017-08-08T07:12:11.764704Z 2 [Note] (------>MDL PRINT) Mdl duration is:MDL_TRANSACTION
我們可以清楚的看到本語句會(huì)對(duì)a和b分別調(diào)用函數(shù)MDL_context::acquire_lock進(jìn)行加鎖為object的SRO MDL鎖類型，雖然是一個(gè)語句但是加鎖卻是分開的，
對(duì)于SRO鎖兼容性如下
Request | Granted requests for lock |
type | S SH SR SW SWLP SU SRO SNW SNRW X |
----------+---------------------------------------------+
SRO | + + + - - + + + - - |
可以看到SRO和SW\SNRW\X均不兼容，也就是和DML(SW)\SNRW(LOCK TABLE WRITE)\X(DDL) 不兼容。
如果有另外一個(gè)事物需要對(duì)a和b進(jìn)行DML操作，那么MDL 死鎖出現(xiàn)了如下：
THREAD1 THREAD2
begin;(事物開始)
insert into b values(1);
LOCK TABLES a READ ,b READ ; (堵塞)
insert into a values(1);(堵塞死鎖)
現(xiàn)在我們來分析一下
線程1
begin;(事物開始)
insert into b values(1);
獲得MDL LOCK 如下：
2017-08-08T07:25:45.875676Z 3 [Note] (acquire_lock)THIS MDL LOCK acquire ok!
2017-08-08T07:25:45.875699Z 3 [Note] (>MDL PRINT) Thread id is 3:
2017-08-08T07:25:45.875713Z 3 [Note] (--->MDL PRINT) Namespace is:GLOBAL
2017-08-08T07:25:45.875726Z 3 [Note] (---->MDL PRINT) Fast path is:(Y)
2017-08-08T07:25:45.875740Z 3 [Note] (----->MDL PRINT) Mdl type is:MDL_INTENTION_EXCLUSIVE(IX)
2017-08-08T07:25:45.875757Z 3 [Note] (------>MDL PRINT) Mdl duration is:MDL_STATEMENT
2017-08-08T07:25:45.875772Z 3 [Note] (------->MDL PRINT) Mdl status is:GRANTED
2017-08-08T07:25:45.875798Z 3 [Note] (acquire_lock)THIS MDL LOCK acquire ok!
2017-08-08T07:25:45.875812Z 3 [Note] (>MDL PRINT) Thread id is 3:
2017-08-08T07:25:45.875826Z 3 [Note] (->MDL PRINT) DB_name is:test
2017-08-08T07:25:45.875839Z 3 [Note] (-->MDL PRINT) OBJ_name is:b
2017-08-08T07:25:45.875853Z 3 [Note] (--->MDL PRINT) Namespace is:TABLE
2017-08-08T07:25:45.875875Z 3 [Note] (---->MDL PRINT) Fast path is:(Y)
2017-08-08T07:25:45.875888Z 3 [Note] (----->MDL PRINT) Mdl type is:MDL_SHARED_WRITE(SW)
2017-08-08T07:25:45.875900Z 3 [Note] (------>MDL PRINT) Mdl duration is:MDL_TRANSACTION
2017-08-08T07:25:45.875913Z 3 [Note] (------->MDL PRINT) Mdl status is:GRANTED
GLOBAL先不考慮，可以看到會(huì)在b表上獲得SW的object mdl lock.
線程2
LOCK TABLES a READ ,b READ ; (堵塞)
2017-08-08T07:27:28.684508Z 2 [Note] (acquire_lock)THIS MDL LOCK acquire ok!
2017-08-08T07:27:28.684534Z 2 [Note] (>MDL PRINT) Thread id is 2:
2017-08-08T07:27:28.684546Z 2 [Note] (->MDL PRINT) DB_name is:test
2017-08-08T07:27:28.684558Z 2 [Note] (-->MDL PRINT) OBJ_name is:a
2017-08-08T07:27:28.684569Z 2 [Note] (--->MDL PRINT) Namespace is:TABLE
2017-08-08T07:27:28.684580Z 2 [Note] (----->MDL PRINT) Mdl type is:MDL_SHARED_READ_ONLY(SRO)
2017-08-08T07:27:28.684592Z 2 [Note] (------>MDL PRINT) Mdl duration is:MDL_TRANSACTION
2017-08-08T07:27:28.684604Z 2 [Note] (------->MDL PRINT) Mdl status is:GRANTED
2017-08-08T07:27:28.684622Z 2 [Note] (acquire_lock)THIS MDL LOCK acquire WAIT(MDL_LOCK WAIT QUE)!
2017-08-08T07:27:28.684633Z 2 [Note] (>MDL PRINT) Thread id is 2:
2017-08-08T07:27:28.684644Z 2 [Note] (->MDL PRINT) DB_name is:test
2017-08-08T07:27:28.684824Z 2 [Note] (-->MDL PRINT) OBJ_name is:b
2017-08-08T07:27:28.684838Z 2 [Note] (--->MDL PRINT) Namespace is:TABLE
2017-08-08T07:27:28.684850Z 2 [Note] (----->MDL PRINT) Mdl type is:MDL_SHARED_READ_ONLY(SRO)
2017-08-08T07:27:28.684871Z 2 [Note] (------>MDL PRINT) Mdl duration is:MDL_TRANSACTION
2017-08-08T07:27:28.684884Z 2 [Note] (------->MDL PRINT) Mdl status is:EMPTY
我們可以清楚的看到a表上的SRO獲得成功，但是b表上的MDL LOCK 需求則進(jìn)入了MDL_LOCK WAIT QUE等待了
及由于b表已經(jīng)被線程1的insert語句拿到了SW鎖，線程2想要獲得b表的SRO鎖需要等待
Waiting for table metadata lock | lock table a read,b read
線程1
再次執(zhí)行
mysql> insert into a values(1);
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
2017-08-08T07:31:13.053322Z 3 [Note] (acquire_lock)THIS MDL LOCK acquire ok!
2017-08-08T07:31:13.053359Z 3 [Note] (>MDL PRINT) Thread id is 3:
2017-08-08T07:31:13.053388Z 3 [Note] (--->MDL PRINT) Namespace is:GLOBAL
2017-08-08T07:31:13.053401Z 3 [Note] (---->MDL PRINT) Fast path is:(Y)
2017-08-08T07:31:13.053417Z 3 [Note] (----->MDL PRINT) Mdl type is:MDL_INTENTION_EXCLUSIVE(IX)
2017-08-08T07:31:13.053589Z 3 [Note] (------>MDL PRINT) Mdl duration is:MDL_STATEMENT
2017-08-08T07:31:13.053613Z 3 [Note] (------->MDL PRINT) Mdl status is:GRANTED
2017-08-08T07:31:13.053658Z 3 [Note] (acquire_lock)THIS MDL LOCK acquire WAIT(MDL_LOCK WAIT QUE)!
2017-08-08T07:31:13.053676Z 3 [Note] (>MDL PRINT) Thread id is 3:
2017-08-08T07:31:13.053694Z 3 [Note] (->MDL PRINT) DB_name is:test
2017-08-08T07:31:13.053711Z 3 [Note] (-->MDL PRINT) OBJ_name is:a
2017-08-08T07:31:13.053728Z 3 [Note] (--->MDL PRINT) Namespace is:TABLE
2017-08-08T07:31:13.054065Z 3 [Note] (----->MDL PRINT) Mdl type is:MDL_SHARED_WRITE(SW)
2017-08-08T07:31:13.054092Z 3 [Note] (------>MDL PRINT) Mdl duration is:MDL_TRANSACTION
2017-08-08T07:31:13.054118Z 3 [Note] (-------->MDL PRINT) Mdl status is:VICTIM
我們這里線程1想要獲得a表的sw mdl lock，但是線程2持有a表的SRO mdl lock，顯然他們不兼容，死鎖出現(xiàn)(VICTIM)
MDL死鎖出現(xiàn)后根據(jù)一個(gè)權(quán)重來進(jìn)行回滾如下：
static const uint DEADLOCK_WEIGHT_DML= 0;
static const uint DEADLOCK_WEIGHT_ULL= 50;
static const uint DEADLOCK_WEIGHT_DDL= 100;
雖然沒有研究死鎖檢測(cè)的原理，但是這里應(yīng)該是帶權(quán)重的一個(gè)圖，回滾權(quán)重小的操作。最后線程1的事物操作被回滾了。
從打印來看事物回滾后，LOCK TABLES a READ ,b READ ;獲得了全部的SRO MDL LOCK成功了，打印如下：
2017-08-08T07:31:13.062693Z 2 [Note] (acquire_lock)THIS MDL LOCK acquire ok!
2017-08-08T07:31:13.062710Z 2 [Note] (>MDL PRINT) Thread id is 2:
2017-08-08T07:31:13.062725Z 2 [Note] (->MDL PRINT) DB_name is:test
2017-08-08T07:31:13.062741Z 2 [Note] (-->MDL PRINT) OBJ_name is:a
2017-08-08T07:31:13.062756Z 2 [Note] (--->MDL PRINT) Namespace is:TABLE
2017-08-08T07:31:13.062768Z 2 [Note] (----->MDL PRINT) Mdl type is:MDL_SHARED_READ_ONLY(SRO)
2017-08-08T07:31:13.062781Z 2 [Note] (------>MDL PRINT) Mdl duration is:MDL_TRANSACTION
2017-08-08T07:31:13.062795Z 2 [Note] (------->MDL PRINT) Mdl status is:GRANTED
2017-08-08T07:31:13.062954Z 2 [Note] (acquire_lock)THIS MDL LOCK acquire ok!
2017-08-08T07:31:13.062974Z 2 [Note] (>MDL PRINT) Thread id is 2:
2017-08-08T07:31:13.062989Z 2 [Note] (->MDL PRINT) DB_name is:test
2017-08-08T07:31:13.063005Z 2 [Note] (-->MDL PRINT) OBJ_name is:b
2017-08-08T07:31:13.063023Z 2 [Note] (--->MDL PRINT) Namespace is:TABLE
2017-08-08T07:31:13.063039Z 2 [Note] (----->MDL PRINT) Mdl type is:MDL_SHARED_READ_ONLY(SRO)
2017-08-08T07:31:13.063052Z 2 [Note] (------>MDL PRINT) Mdl duration is:MDL_TRANSACTION
2017-08-08T07:31:13.063065Z 2 [Note] (------->MDL PRINT) Mdl status is:GRANTED
總結(jié)一下這里的死鎖
線程1 獲得b的SW MDL LOCK-->線程2 獲得a的SRO MDL LOCK，而拿不到b的SRO MDL LOCK(堵塞)-->線程1 拿不到a的SW MDL LOCK(堵塞)
這樣 線程1和線程2都堵塞了，根據(jù)MDL LOCK死鎖圖的權(quán)重釋放 線程1因?yàn)樗际荄ML操作。
五、處理
知道原因處理就相對(duì)簡(jiǎn)單了，備份表結(jié)構(gòu)也加上 --single-transaction就好了，雖然備份表結(jié)構(gòu)時(shí)間很短，但是高壓力下也會(huì)出現(xiàn)死鎖的情況。
上述內(nèi)容就是MYSQL中一個(gè)特殊的MDL LOCK死鎖的示例分析，你們學(xué)到知識(shí)或技能了嗎？如果還想學(xué)到更多技能或者豐富自己的知識(shí)儲(chǔ)備，歡迎關(guān)注億速云行業(yè)資訊頻道。
免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號(hào)
手機(jī)網(wǎng)站二維碼
