共计 11945 个字符，预计需要花费 30 分钟才能阅读完成。
本篇文章为大家展示了 MYSQL 中一个特殊的 MDL LOCK 死锁的示例分析，内容简明扼要并且容易理解，绝对能使你眼前一亮，通过这篇文章的详细介绍希望你能有所收获。
一、问题由来
前段开发反馈时间线上数据库老是出现死锁情况，而我们设置了 innodb_print_all_deadlocks，但是在
相应的时间点没有找到任何相应的死锁的信息，从而导致我们获得任何有用的信息，也不能定位问题的
原因。
二、问题思考和分析
后来开发将出错码发给我，我看到这个错误码确实是 MYSQL 报出来的如下：
{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
那么能够肯定一点这个死锁是 MYSQL 报出来的。那么为什么 innodb 没有任何表示呢？难道是什么 BUG
继而在
https://bugs.mysql.com/
找了一下 BUGS 也没有找，后来我思考这个问题，既然是死锁就会是相应的死锁检测算法抛出来，我们知道 MYSQL
上层还有 MDL LOCK，并不是只有 innodb 相应的 lock 才会有进行死锁检测，会不是因为 MDL LOCK 死锁照成的呢？
我在 MDL.CC 中找到了如下代码其实也是我上文说的 MDL_context::acquire_lock 函数，上文说过这个函数是
根据 MDL_REQUEST 尝试获得 MDL LOCK 的主要函数。
点击 (此处) 折叠或打开
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;
}
注意红色部分，其实这里死锁问题基本确定不是 innodb 层触发的，既然不是 innodb 下层触发，innodb 当然不会打印
出任何信息。为什么 innodb 层不打印死锁信息的原因找到了，但是什么情况下会出现 MDL LOCK 的死锁呢?
三、问题定位
既然要产生死锁必须满足一些条件：
1、至少 2 个独立的线程(会话)。
2、单位操作中包含多个相对独立的加锁步骤，有一定的时间差
比如一个事物里面的多个操作
还比如 repeat 操作(虽然非常段也是有的)。关于 replace 的死锁问题参考我的文章如下：
http://blog.itpub.net/7728585/viewspace-2141409/
3、多个线程 (会话) 之间加锁对象必须有相互等待的情况发生，并且等待出现环状。
顺便提一句，死锁一般处理方式有 3 种
A、事前预测
B、资源分级
C、事后检测释放
而 MDL LOCK 和 INNODB 都使用了 C 时候检测释放，其算法应该是利用图的 (DSF or BSF) 遍历进行判定，这一块以后要好好看一下。
那么我考虑到使用 MYSQLDUMP 进行的备份的时候可能出现一些 MDL LOCK 的情况，比如加上 -F, –flush-logs –single-transaction 会
短暂的使用 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
如前文所述这个操作会在 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)
而导致所有的需要在 GLOBAL 上加 IX 锁的操作全部等待比如 (DML 操作 /DDL 操作)，但是这里的 flush table with read lock
并不符合上面描述的死锁产生的条件，不管如何先检查一下是不是 MYSQLDUMP 的问题，这一检查真的检查到了问题，我们
MYSQL 备份的时候，进行了一次单独的表结构的备份，同事认为很快而没有加 –single-transaction，然后检查备份结束的
时间，基本和死锁出现的时间点一致，那么问题转为如果不加 –single-transaction，MYSQLDUMP 如何加锁。
实际上这个时候从 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 数据只有两个表 kkkk 和 llll，我们可以看到 mysqldump 通过 LOCK TABLES `kkkk` READ ,`llll` READ 进行加锁，
备份完成后使用 unlock tables 解锁。其实这就是问题的根本原因。它会照成 MDL 死锁的产生。如果加上 –single-transaction 则不会，
会使用 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ 改变隔离级别为 RR 使用事物来保证数据的一致，
而不是 LOCK TABLES 来保证数据的一致性。
四、模拟 MDL 死锁和兼容性分析
首先我们需要看一下 LOCK TABLES a READ ,b READ 到底加了哪些 MDL LOCK，这个通过我在 MDL.CC 中加入的打印函数 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
我们可以清楚的看到本语句会对 a 和 b 分别调用函数 MDL_context::acquire_lock 进行加锁为 object 的 SRO MDL 锁类型，虽然是一个语句但是加锁却是分开的，
对于 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) 不兼容。
如果有另外一个事物需要对 a 和 b 进行 DML 操作，那么 MDL 死锁出现了如下：
THREAD1 THREAD2
begin;(事物开始)
insert into b values(1);
LOCK TABLES a READ ,b READ ; (堵塞)
insert into a values(1);(堵塞死锁)
现在我们来分析一下
线程 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 先不考虑，可以看到会在 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 需求则进入了 MDL_LOCK WAIT QUE 等待了
及由于 b 表已经被线程 1 的 insert 语句拿到了 SW 锁，线程 2 想要获得 b 表的 SRO 锁需要等待
Waiting for table metadata lock | lock table a read,b read
线程 1
再次执行
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，显然他们不兼容，死锁出现 (VICTIM)
MDL 死锁出现后根据一个权重来进行回滚如下：
static const uint DEADLOCK_WEIGHT_DML= 0;
static const uint DEADLOCK_WEIGHT_ULL= 50;
static const uint DEADLOCK_WEIGHT_DDL= 100;
虽然没有研究死锁检测的原理，但是这里应该是带权重的一个图，回滚权重小的操作。最后线程 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
总结一下这里的死锁
线程 1 获得 b 的 SW MDL LOCK– 线程 2 获得 a 的 SRO MDL LOCK，而拿不到 b 的 SRO MDL LOCK(堵塞)– 线程 1 拿不到 a 的 SW MDL LOCK(堵塞)
这样 线程 1 和线程 2 都堵塞了，根据 MDL LOCK 死锁图的权重释放 线程 1 因为它都是 DML 操作。
五、处理
知道原因处理就相对简单了，备份表结构也加上 –single-transaction 就好了，虽然备份表结构时间很短，但是高压力下也会出现死锁的情况。
上述内容就是 MYSQL 中一个特殊的 MDL LOCK 死锁的示例分析，你们学到知识或技能了吗？如果还想学到更多技能或者丰富自己的知识储备，欢迎关注丸趣 TV 行业资讯频道。
