在事务中执行 SELECT 操作

最新推荐文章于 2024-07-17 11:40:56 发布

原创最新推荐文章于 2024-07-17 11:40:56 发布 · 749 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#java #sql

本文探讨了MySQL中在线DDL操作与MDL之间的冲突问题，特别是在执行大型表的DDL操作时，如何避免对DML操作产生阻塞。文章分析了不同会话下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 1> START TRANSACTION;> SELECT * FROM tbl_name;# 正常执行

这时 Session 2 想要执行 DML 操作也只需要获取 shared MDL，仍然可以正常执行。

# Session 2> SELECT * FROM tbl_name;# 正常执行

但如果 Session 3 想执行 DDL 操作就会阻塞，因为此时 Session 1 已经占用了 shared MDL，而 DDL 的执行需要先获取 exclusive MDL，因此无法正常执行。

# Session 3> ALTER TABLE tbl_name ADD COLUMN n INT;# 阻塞

通过 show processlist 可以看到 ALTER 操作正在等待 MDL。

+----+-----------------+------------------+------+---------+------+---------------------------------+-----------------+
| Id | User            | Host             | db   | Command | Time | State                           | Info            |│----+-----------------+------------------+------+---------+------+---------------------------------+-----------------+
| 11 | root            | 172.17.0.1:53048 | demo | Query   |    3 | Waiting for table metadata lock | alter table ... |+----+-----------------+------------------+------+---------+------+---------------------------------+-----------------+

由于 exclusive MDL 的获取优先于 shared MDL，后续尝试获取 shared MDL 的操作也将会全部阻塞

# Session 4> SELECT * FROM tbl_name;# 阻塞

到这一步，后续无论是 DML 和 DDL 都将阻塞，直到 Session 1 提交或者回滚，Session 1 占用的 shared MDL 被释放，后面的操作才能继续执行。

上面这个问题主要有两个原因：

Session 1 中的事务没有及时提交，因此阻塞了 Session 3 的 DDL
Session 3 Online DDL 阻塞了后续的 DML 和 DDL

对于问题 1，有些 ORM 框架默认将用户语句封装成事务执行，如果客户端程序中断退出，还没来得及提交或者回滚事务，就会出现 Session 1 中的情况。那么此时可以在 infomation_schema.innodb_trx 中找出未完成的事务对应的线程，并强制退出。

> SELECT * FROM information_schema.innodb_trx\G*************************** 1. row ***************************trx_id: 421564480355704trx_state: RUNNINGtrx_started: 2022-05-01 014:49:41trx_requested_lock_id: NULLtrx_wait_started: NULLtrx_weight: 0trx_mysql_thread_id: 9trx_query: NULLtrx_operation_state: NULLtrx_tables_in_use: 0trx_tables_locked: 0trx_lock_structs: 0trx_lock_memory_bytes: 1136trx_rows_locked: 0trx_rows_modified: 0trx_concurrency_tickets: 0trx_isolation_level: REPEATABLE READtrx_unique_checks: 1trx_foreign_key_checks: 1trx_last_foreign_key_error: NULLtrx_adaptive_hash_latched: 0trx_adaptive_hash_timeout: 0trx_is_read_only: 0trx_autocommit_non_locking: 0trx_schedule_weight: NULL1 row in set (0.0025 sec)

可以看到 Session 1 正在执行的事务对应的 trx_mysql_thread_id 为 9，然后执行 KILL 9 即可中断 Session 1 中的事务。
对于问题 2，在查询很多的情况下，会导致阻塞的 session 迅速增多，对于这种情况，可以先中断 DDL 操作，防止对服务造成过大的影响。也可以尝试在从库上修改表结构后进行主从切换或者使用 pt-osc 等第三方工具。

8 限制

仅适用于 InnoDB（语法上它可以与其他存储引擎一起使用，如 MyISAM，但 MyISAM 只允许 algorithm = copy，与传统方法相同）；
无论使用何种锁（NONE，共享或排它），在开始和结束时都需要一个短暂的时间来锁表（排它锁）；
在添加 / 删除外键时，应该禁用 foreign_key_checks 以避免表复制；
仍然有一些 alter 操作需要 copy 或 lock 表（老方法），有关哪些表更改需要表复制或表锁定，请查看官网；
如果在表上有 ON … CASCADE 或 ON … SET NULL 约束，则在 alter table 语句中不允许 LOCK = NONE；
Online DDL 会被复制到从库（同主库一样，如果 LOCK = NONE，从库也不会加锁），但复制本身将被阻止，因为 alter 在从库以单线程执行，这将导致主从延迟问题。

官方参考资料：https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-limitations.html