mysql中online ddl 问题

online ddl的意思是：在执行ddl期间，不会阻塞新产生的dml请求。

你遇到的问题在于：发起ddl之前，会话1已经启动一个读写事务，对该表加上mdl锁；导致会话2的ddl会被阻塞；进而导致会话3的请求也被阻塞。

想要模拟online ddl的正确做法是：
会话1：发起online ddl（可以选个大表，耗时较久，方便观察）
会话2：发起dml请求（此时不应该被阻塞才对）

关于mdl锁的详细介绍可以查看手册：https://dev.mysql.com/doc/refman/8.0/en/metadata-locking.html

Online DDL并不是让你随时随地地可以更新表结构，也还是有限制的。mdl是为了解决DDL操作和DML操作之间操作一致性。

因为DDL在执行时是需要获取MDL锁的，而你这个表上有长事务，所以这个时候不应该执行DDL，应该放弃执行DDL，这是MySQL的玩法。否则就会出现你现在会话3的问题，会员1阻塞了会话2，而会话3的select又被会话2的DDL阻塞了。

最佳玩法，DDL获取不到DML锁就马上放弃。
方法一：
show processlist;   #先看看涉及变更表上有没有在跑的大查询，有的话，就不能DDL变更了，等他跑完或杀了他们。

方法二：
1.set lock_wait_timeout=5;   #设置线程级别锁等待超时为5秒
2.DDL
3.退出连接线程

我这里加了set lock_wait_timeout=5;是为了防止DDL长期获取不到元数据锁而卡住，而他卡住会导致全表堵塞（你会话3的现象）。5秒获取不到锁，你当前会话会自动放弃后续的第二步骤的DDL语句。

铎铎是只猫 发表于 2023-8-11 08:03
Online DDL并不是让你随时随地地可以更新表结构，也还是有限制的。mdl是为了解决DDL操作和DML操作之间操作 ...

select 都加mdl锁，这个在pg 和oracle 中都没有的。所以很纳闷。

jian.tang 发表于 2024-1-9 10:13
select 都加mdl锁，这个在pg 和oracle 中都没有的。所以很纳闷。

不同数据库的加锁和锁管理方式不同，这个确实没办法。
针对这种情况的建议是：
1. 检测大事务、长事务，让事务尽快结束（提交/回滚）。
2. 不要在业务高峰期执行ddl（执行ddl前要先检查当前是否有未释放的MDL锁，不管是共享还是排他类型）。
3. 做好大事务、长事务、及锁等待监控，当无论是行锁还是MDL锁等待时都要发出告警，及时介入处理。