MySQL 无法更改正在主动写入的表

在我的应用程序的一个用例中，我有两个并发MySQL连接：

• 主动写入名为的表T（实际上，不断更新该表中的一行），并且
• 另一个对同一个表执行 DDL (ALTER TABLE，添加 8 个新列并从varchar(80) to varchar(2000)）。 DDL 预计最终会完成。

中的列UPDATEDML 是not受 DDL 的影响。

该表仅包含一行（该行是UPDATE'd).

Analysis

当运行涵盖此用例的集成测试时，我观察到的是测试超时（表被如此积极地写入，因此 DDL 永远不会完成），但仅限于MySQL5.7.通常，测试预计在我们的硬件上在 30 秒内完成（这确实发生在MySQL5.6 和 8.0)，但是对于MySQL5.7 即使200秒也不够。我尝试过不同的ALGORITHM and LOCK值（参见13.1.8 ALTER TABLE 语法 https://dev.mysql.com/doc/refman/8.0/en/alter-table.html），没有运气。

当我分析我的应用程序时（MySQL5.7 情况），我观察到 99% 的 CPU 时间都花在从套接字读取上（即等待MySQL回应表已被更改），但数据库实例对我来说是一种黑匣子——当然我有performance_schema启用并可以对其运行查询，但我不知道我正在寻找哪些确切信息。

合成

同时，我未能将问题简化为最小的独立单元测试——我观察到的唯一事情是3x to 10x测试所用时间增加MySQL5.7 与其他相比MySQL版本，但 DDL 不会永远挂起：

All MySQL版本是库存版本Windows or Debian Linux下载自mysql.com https://www.mysql.com/以最小的改变my.cnf，或者官方Docker images.

问题：

1. Is it indeed technically possible for MySQL to delay the execution of ALTER TABLE DDL forever? Or what I'm observing is just a very busy database instance? Is it possible to either
   • 要求ALTER TABLE是中断执行的，i。 e.如果超过一定的超时时间，数据库将返回错误，或者
   • 强制所有其他连接，这甚至可能会导致SHARED锁定表或其某些行以暂停，以便它们在执行 DDL 时不会干预？
2. 在处理原来的集成测试超时时，如何进一步诊断情况MySQL side?

TL;DR — 提交事务以解锁 ALTER TABLE。

是的，ALTER TABLE 可以阻塞很长时间。它可能看起来像是永远。它的值实际上是锁等待超时 https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_lock_wait_timeout，默认为 31536000 秒，即 365 天。

在 MySQL 中，像 ALTER TABLE 这样的 DDL 语句需要独占元数据锁 https://dev.mysql.com/doc/refman/8.0/en/metadata-locking.html在桌子上。目的是确保您不会同时从两个并发会话中更改表。

DML 语句（如 SELECT、INSERT、UPDATE、DELETE）也持有“共享”元数据锁。共享锁可以由多个会话同时持有，但会阻塞排它锁，因为排它锁要求它们是唯一一个在表上持有任何类型锁的锁。

文件指出：

这种锁定方法意味着，在事务结束之前，一个会话中的事务正在使用的表不能被其他会话在 DDL 语句中使用。

DML 语句持有元数据锁的目的是这样它们可以保留表的可重复读取视图，而不必担心另一个会话正在执行 DROP TABLE 或 ALTER TABLE 来损害它们的表视图。这种锁定是必要的，因为 MySQL 没有版本化元数据（他们正在逐步朝这个方向努力 https://mysqlserverteam.com/mysql-8-0-data-dictionary-architecture-and-design/).

这意味着运行简单 SELECT 且未提交的事务将阻止需要锁定更改的 DROP TABLE 或 ALTER TABLE。

在线 DDL 的引入存在一些细微差别。

在线DDL性能和并发性 https://dev.mysql.com/doc/refman/8.0/en/innodb-create-index-concurrency.html更详细地描述了 ALTER TABLE 通过获取共享元数据锁开始，因此未提交的事务不会阻塞它。但如果 ALTER TABLE 更改的性质需要，下一阶段可能会将共享元数据锁升级为独占元数据锁。此时，锁获取被阻止，因为另一个事务仍然持有自己的元数据锁。

在线 DDL 并不适用于所有类型的 ALTER TABLE 操作；有些仍然需要独占锁。例如，更改数据类型，就像您所做的那样，需要独占锁。看在线 DDL 概述 https://dev.mysql.com/doc/refman/8.0/en/innodb-create-index-overview.html了解详情。