Mysql MDL+DDL 死锁

应用背景:

应用A,springboot2+shardingJdbc5.1架构, 使用mysql数据库,其中有些订单表为分区表,按日分区。

问题描述

今日在生产环境维护分区表分区时，应用报了死锁。具体日志如下

找到DBA去排查时，基于他们的经验，他们不承认这是数据库报出的死锁，反而怀疑是我们的应用代码或者JDBC有问题，这 ...

问题复现

在排查相关资料后，我断定了是mysql的锁的机制的问题，于是我做了个测试

应用： session1   客户端： session2

session1: 开启事务，并执行查询，持有 XXX 对象的 SHARED_READ 锁，简称SR锁。
session2: 执行添加分区（DDL）命令，想要获取 XXX 对象的 EXCLUSIVE 锁，简称X锁.


这个状态时，session2 在等 session1 释放锁。
 
sessin1: 继续执行 update 命令，会申请 XXX 对象的 SHARED_WRITE 锁，简称SW锁。
这个状态时，session2 在等待获取 XXX 对象的X锁，session1 想要申请SW锁，必须等session2 释放掉锁。
所以 session1 在等 session2 释放锁。
 
两个会话互相等待，发生死锁，MySQL数据库会自动回滚其中一个事务。

session1 :

session2:

此刻死锁已经出现，我们来看下数据库里的死锁日志

测试的结果和我预想or我们遇到的一样，是X锁和SW锁的相互互斥的机制导致的死锁，而这种死锁不会在mysql的死锁日志中记录

问题原因

先给大家看一下mysql的锁的兼容矩阵

Request   |  Granted requests for lock                  |
 type     | S  SH  SR  SW  SWLP  SU  SRO  SNW  SNRW  X  |
----------+---------------------------------------------+
S         | +   +   +   +    +    +   +    +    +    -  |
SH        | +   +   +   +    +    +   +    +    +    -  |
SR        | +   +   +   +    +    +   +    +    -    -  |
SW        | +   +   +   +    +    +   -    -    -    -  |
SWLP      | +   +   +   +    +    +   -    -    -    -  |
SU        | +   +   +   +    +    -   +    -    -    -  |
SRO       | +   +   +   -    -    +   +    +    -    -  |
SNW       | +   +   +   -    -    -   +    -    -    -  |
SNRW      | +   +   -   -    -    -   -    -    -    -  |
X         | -   -   -   -    -    -   -    -    -    -  |

根据mysql锁兼容矩阵图可以看出，X锁和任何锁是不兼容的（敲黑板，背下来）.

我们的测试场景下，session2 在等待获取 xxx 对象的X锁，session1 想要申请SW锁，必须等session2 释放掉锁。
所以 session1 在等 session2 释放锁 , 进而产生死锁

解决方案

1、在session1中的查询，加上 for update, 使得session1 一开始就获取SW锁
2、将session1的查询独立出当前事务
3、优化 mysql, 将DDL操作改写成软提交方式, 获取不到锁后，释放已经拿到的锁，然后不断重试

写在后面

如何安全地给表执行DDL操作（参考 http://nwjs.net/news/249294.html）

1、生产环境的任何大表或频繁操作的小表，ddl都要非常慎重，最好在业务低峰期执行。
2、设计上要尽可能避免大事务，大事务不仅仅会带来各种锁问题，还会引起复制延迟/回滚空间爆满等各类问题。
3、设置参数 lock_wait_timeout 为较小值，使被阻塞端主动停止。
4、增强监控告警，及时发现 MDL 锁。
5、或许这样操作也是一种好办法：按新结构创建新表 -> 将旧表数据迁移至新表 -> 重命名两个表（三步都通过编写sql语句完成，比手动操作快，第二步的数据迁移操作视情况而定）。过程中最好在没人用的时候操作
6、操作ddl之前，先用以下语句查一下有没有长事务：
SELECT * FROM information_schema.INNODB_TRX;
7、多副本（主从、集群）下可以做热更新。

以上，仅代表个人观点，大家有方案可以评论区交流，欢迎大家批评指正