导读:MySQL 的 DDL(Data Definition Language) 包括增减字段、增减索引等操作。在 MySQL 5.6 之前,MySQL 的 DDL 操作会按照原来的表复制一份,并做相应的修改。
例如,对表 A 进行 DDL 的具体过程如下:
在 2-4 的过程中,如果表 A 数据量比较大,拷贝到表 B 的过程会消耗大量时间,并占用额外的存储空间。此外,由于 DDL 操作占用了表 A 的写锁,所以表 A 上的 DDL 和 DML 都将阻塞无法提供服务。
因此,MySQL 5.6 增加了 Online DDL,允许在不中断数据库服务的情况下进行 DDL 操作。
ALTER 语句中可以指定参数 ALGORITHM 和 LOCK 分别指定 DDL 执行的方式和 DDL 期间 DML 的兵法控制
不过并不是所有的 DDL 操作都能用 INPLACE 的方式执行,具体的支持情况可以在 MySQL Reference Manual — Online DDL Operations) 中查看。
例如 Table 14.10 中显示修改列的数据类型不支持 INPLACE
这时尝试将原类型为 FLOAT 的 column_name 改为 INT
会报错
Online DDL 过程中占用 exclusive MDL 的步骤执行很快,所以几乎不会阻塞 DML 语句。
不过,在 DDL 执行前或执行时,其他事务可以获取 MDL。由于需要用到 exclusive MDL,所以必须要等到其他占有 metadata lock 的事务提交或回滚后才能执行上面两个涉及到 MDL 的地方。
前面提到 Online DDL 执行过程中需要获取 MDL,MDL (metadata lock) 是 MySQL 5.5 引入的表级锁,在访问一个表的时候会被自动加上,以保证读写的正确性。当对一个表做 DML 操作的时候,加 MDL 读锁;当做 DDL 操作时候,加 MDL 写锁。
为了在大表执行 DDL 的过程中同时保证 DML 能并发执行,前面使用了 ALGORITHM=INPLACE 的 Online DDL,但这里仍然存在死锁的风险,问题就出在 Online DDL 过程中需要 exclusive MDL 的地方。
例如:Session 1 在事务中执行 SELECT 操作,此时会获取 shared MDL。由于是在事务中执行,所以这个 shared MDL 只有在事务结束后才会被释放。
这时 Session 2 想要执行 DML 操作也只需要获取 shared MDL,仍然可以正常执行。
但如果 Session 3 想执行 DDL 操作就会阻塞,因为此时 Session 1 已经占用了 shared MDL,而 DDL 的执行需要先获取 exclusive MDL,因此无法正常执行。
通过 show processlist可以看到 ALTER 操作正在等待 MDL。
由于 exclusive MDL 的获取优先于 shared MDL,后续尝试获取 shared MDL 的操作也将会全部阻塞
到这一步,后续无论是 DML 和 DDL 都将阻塞,直到 Session 1 提交或者回滚,Session 1 占用的 shared MDL 被释放,后面的操作才能继续执行。
上面这个问题主要有两个原因:
对于问题 1:不少 ORM(例如 pymysql)都默认将用户语句封装成事务执行,如果客户端程序中断退出,还没来得及提交或者回滚事务,就会出现 Session 1 中的情况。这时可以在 infomation_schema.innodb_trx中找出未完成的事务对应的线程,并强制退出
可以看到 Session 1 正在执行的事务对应的 trx_mysql_thread_id 为 9,然后执行 KILL 9即可中断 Session 1 中的事务。
对于问题 2:在查询很多的情况下,会导致阻塞的 session 迅速增多,对于这种情况,可以先中断 DDL 操作,防止对服务造成过大的影响。也可以尝试在从库上修改表结构后进行主从切换或者使用 pt-osc 等第三方工具。
<END>