DML+DDL 导致死锁？

DML+DDL 导致死锁？

最近线上出现了 DDL执行过程中，出现死锁问题，导致系统DB访问异常，排查后，发现是DML+DDL出现了死锁问题。

SQL一共4种类型

1. 数据定义语言 DDL：Create、Drop、Alter 操作。用于定义库和表结构的。
2. 数据查询语言 DQL：select。用于查询数据的。
3. 数据操纵语言 DML：insert、update、delete。对行记录进行增删改操作。
4. 数据控制语言 DCL：grant、revoke、commit、rollback。控制数据库的权限和事务。

MDL

MDL（MetaData Lock）就是针对于 DDL 与 DML、DQL 操作加锁，执行 DDL 自动添加写锁，执行 DML、DQL 自动添加读锁，也就是说 DML 语句可以同时执行（不考虑其他锁），而 DDL 间则会相互阻塞。

MDL不需要显式使用，在访问一个表的时候会被自动加上。

MDL的作用是保证读写的正确性。

这么个场景，如果一个查询正在遍历一个 表中的数据，而执行期间另一个线程对这个表结构做变更，删了一列，那么查询线程拿到的结果 跟表结构对不上，肯定是不行的。

因此，在MySQL 5.5版本中引入了MDL，当对一个表做增删改查操作的时候，加MDL读锁;当 要对表做结构变更操作的时候，加MDL写锁。

• 读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。
• 读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线 程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

DDL 为啥会加锁

DDL 在执行会先创建一个临时表，先将表的数据全部移到这个临时表中，然后再将临时表替换当前表。在这个过程中如果出现读写操作就会影响最终结果出错。这个过程耗时主要在将原表的数据移到临时表的过程。

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         | -   -   -   -    -    -   -    -    -    -  |

场景说明

应用：session1 客户端：session2 session1:

session2:

1. session1: 开启事务，并执行查询，持有 XXX 对象的 SHARED_READ 锁，简称SR锁。
2. session2: 执行添加分区（DDL）命令，想要获取 XXX 对象的 EXCLUSIVE 锁，简称X锁.
3. 这个状态时，session2 在等 session1 释放锁。
4. session1: 继续执行 update 命令，会申请 XXX 对象的 SHARED_WRITE 锁，简称SW锁。
5. 这个状态时，session2 在等待获取 XXX 对象的X锁，session1 想要申请SW锁，必须等session2 释放掉锁。所以 session1 在等 session2 释放锁。

Session1 和 Session2 相互等待，发生死锁。

原因：X锁和SW锁的相互互斥的机制导致的死锁。

两个会话互相等待，发生死锁，MySQL数据库会自动回滚其中一个事务。

如何解决

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

如何安全的给表执行 DDL 操作?

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

参考资料

• https://blog.csdn.net/qq_35254185/article/details/95359460?spm=1001.2101.3001.6650.1&utm_medium=distribute.pc_relevant.none-task-blog-2%7Edefault%7ECTRLIST%7ERate-1-95359460-blog-80424571.235%5Ev38%5Epc_relevant_default_base3&depth_1-utm_source=distribute.pc_relevant.none-task-blog-2%7Edefault%7ECTRLIST%7ERate-1-95359460-blog-80424571.235%5Ev38%5Epc_relevant_default_base3&utm_relevant_index=2