MYSQL中一個(gè)特殊的MDL LOCK死鎖的示例分析

269次閱讀

共計(jì) 11945 個(gè)字符，預(yù)計(jì)需要花費(fèi) 30 分鐘才能閱讀完成。

本篇文章為大家展示了 MYSQL 中一個(gè)特殊的 MDL LOCK 死鎖的示例分析，內(nèi)容簡(jiǎn)明扼要并且容易理解，絕對(duì)能使你眼前一亮，通過(guò)這篇文章的詳細(xì)介紹希望你能有所收獲。

一、問(wèn)題由來(lái)
前段開(kāi)發(fā)反饋時(shí)間線上數(shù)據(jù)庫(kù)老是出現(xiàn)死鎖情況，而我們?cè)O(shè)置了 innodb_print_all_deadlocks，但是在
相應(yīng)的時(shí)間點(diǎn)沒(méi)有找到任何相應(yīng)的死鎖的信息，從而導(dǎo)致我們獲得任何有用的信息，也不能定位問(wèn)題的
原因。

二、問(wèn)題思考和分析
后來(lái)開(kāi)發(fā)將出錯(cuò)碼發(fā)給我，我看到這個(gè)錯(cuò)誤碼確實(shí)是 MYSQL 報(bào)出來(lái)的如下：
{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)出來(lái)的。那么為什么 innodb 沒(méi)有任何表示呢？難道是什么 BUG
繼而在
https://bugs.mysql.com/
找了一下 BUGS 也沒(méi)有找，后來(lái)我思考這個(gè)問(wèn)題，既然是死鎖就會(huì)是相應(yīng)的死鎖檢測(cè)算法拋出來(lái)，我們知道 MYSQL
上層還有 MDL LOCK，并不是只有 innodb 相應(yīng)的 lock 才會(huì)有進(jìn)行死鎖檢測(cè)，會(huì)不是因?yàn)?MDL LOCK 死鎖照成的呢？
我在 MDL.CC 中找到了如下代碼其實(shí)也是我上文說(shuō)的 MDL_context::acquire_lock 函數(shù)，上文說(shuō)過(guò)這個(gè)函數(shù)是
根據(jù) MDL_REQUEST 嘗試獲得 MDL LOCK 的主要函數(shù)。

點(diǎn)擊 (此處) 折疊或打開(kāi)

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í)這里死鎖問(wèn)題基本確定不是 innodb 層觸發(fā)的，既然不是 innodb 下層觸發(fā)，innodb 當(dāng)然不會(huì)打印
出任何信息。為什么 innodb 層不打印死鎖信息的原因找到了，但是什么情況下會(huì)出現(xiàn) MDL LOCK 的死鎖呢?

三、問(wèn)題定位

既然要產(chǎn)生死鎖必須滿足一些條件：
1、至少 2 個(gè)獨(dú)立的線程(會(huì)話)。
2、單位操作中包含多個(gè)相對(duì)獨(dú)立的加鎖步驟，有一定的時(shí)間差
比如一個(gè)事物里面的多個(gè)操作
還比如 repeat 操作(雖然非常段也是有的)。關(guān)于 replace 的死鎖問(wèn)題參考我的文章如下：
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 的問(wèn)題，這一檢查真的檢查到了問(wèn)題，我們
MYSQL 備份的時(shí)候，進(jìn)行了一次單獨(dú)的表結(jié)構(gòu)的備份，同事認(rèn)為很快而沒(méi)有加 –single-transaction，然后檢查備份結(jié)束的
時(shí)間，基本和死鎖出現(xiàn)的時(shí)間點(diǎn)一致，那么問(wèn)題轉(zhuǎn)為如果不加 –single-transaction，MYSQLDUMP 如何加鎖。

實(shí)際上這個(gè)時(shí)候從 general 日志來(lái)看，加鎖如下：
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 通過(guò) LOCK TABLES `kkkk` READ ,`llll` READ 進(jìn)行加鎖，
備份完成后使用 unlock tables 解鎖。其實(shí)這就是問(wèn)題的根本原因。它會(huì)照成 MDL 死鎖的產(chǎn)生。如果加上 –single-transaction 則不會(huì)，
會(huì)使用 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ 改變隔離級(jí)別為 RR 使用事物來(lái)保證數(shù)據(jù)的一致，
而不是 LOCK TABLES 來(lái)保證數(shù)據(jù)的一致性。

四、模擬 MDL 死鎖和兼容性分析
首先我們需要看一下 LOCK TABLES a READ ,b READ 到底加了哪些 MDL LOCK，這個(gè)通過(guò)我在 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

我們可以清楚的看到本語(yǔ)句會(huì)對(duì) a 和 b 分別調(diào)用函數(shù) MDL_context::acquire_lock 進(jìn)行加鎖為 object 的 SRO MDL 鎖類型，雖然是一個(gè)語(yǔ)句但是加鎖卻是分開(kāi)的，
對(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;(事物開(kāi)始)
insert into b values(1);

LOCK TABLES a READ ,b READ ; (堵塞)

insert into a values(1);(堵塞死鎖)

現(xiàn)在我們來(lái)分析一下
線程 1
begin;(事物開(kāi)始)
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 語(yǔ)句拿到了 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)重來(lái)進(jìn)行回滾如下：
static const uint DEADLOCK_WEIGHT_DML= 0;
static const uint DEADLOCK_WEIGHT_ULL= 50;
static const uint DEADLOCK_WEIGHT_DDL= 100;
雖然沒(méi)有研究死鎖檢測(cè)的原理，但是這里應(yīng)該是帶權(quán)重的一個(gè)圖，回滾權(quán)重小的操作。最后線程 1 的事物操作被回滾了。
從打印來(lái)看事物回滾后，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)樗际?DML 操作。

五、處理

知道原因處理就相對(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)注丸趣 TV 行業(yè)資訊頻道。

正文完