时间戳

首页 > 解决方案 > MySQL 无法 ALTER TABLE 正在积极写入

java - MySQL 无法 ALTER TABLE 正在积极写入

问题描述

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

UPDATEDML 中的列不受DDL 影响。

该表仅包含一行（被UPDATE'd 的那一行）。

分析

当运行涵盖此用例的集成测试时，我观察到的是测试超时（表被如此积极地写入，因此 DDL 永远不会完成），但仅适用于MySQL 5.7。通常，在我们的硬件上，测试预计会在 30 秒内完成（这确实发生在MySQL 5.6 和 8.0 上），但对于MySQL 5.7，即使 200 秒也是不够的。我尝试了不同ALGORITHM的LOCK值（参见13.1.8 ALTER TABLE Syntax），但没有成功。

当我分析我的应用程序（MySQL 5.7 案例）时，我观察到 99% 的 CPU 时间用于从套接字读取（即等待MySQL响应表已被更改），但数据库实例是一种黑色给我的盒子——当然我已经performance_schema启用并且可以对它运行查询，但我不知道我正在寻找哪些确切的信息。

合成

同时，我未能将问题减少到最小的独立单元测试——我观察到的唯一一件事是MySQL 5.7的测试运行时间与其他MySQL版本相比增加了3到10 倍，但 DDL 没有永远挂着：

在此处输入图像描述

所有MySQL版本都是从www.mysql.com下载的Windows或Debian Linux的库存版本，对或官方Docker映像进行了最小的更改。my.cnf

问题：

  1. MySQL是否真的可以永远延迟ALTER TABLEDDL 的执行？或者我观察到的只是一个非常繁忙的数据库实例？是否有可能
    • 可中断执行的请求ALTER TABLE，即如果超过某个超时，数据库将返回错误，或者
    • 强制所有其他可能SHARED在表或其某些行上加锁的连接暂停，以便在执行 DDL 时它们不会干预？
  2. 在处理原始集成测试超时时，如何从MySQL端进一步诊断情况？

标签： javamysqljdbcinnodb

解决方案

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

是的，ALTER TABLE 可以阻塞很长时间。它可能看起来像永远。实际上就是lock_wait_timeout的值，默认是 31536000 秒，也就是 365 天。

在 MySQL 中，像 ALTER TABLE 这样的 DDL 语句需要对表进行独占元数据锁。目的是确保您不会同时从两个并发会话中更改 TABLE。

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

文档指出：

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

持有元数据锁的 DML 语句的目的是，它们可以保留其表的可重复读取视图，而不必担心另一个会话正在执行 DROP TABLE 或 ALTER TABLE 以损害其表视图。这种锁定是必要的，因为 MySQL 没有版本化元数据（他们正在逐渐朝着那个方向努力）。

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

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

Online DDL Performance and Concurrency更详细地描述了 ALTER TABLE 通过获取共享元数据锁开始，因此未提交的事务不会阻塞它。但如果 ALTER TABLE 更改的性质需要，下一阶段可能会将共享元数据锁升级为独占元数据锁。此时，锁获取被阻塞，因为其他事务仍然持有自己的元数据锁。

在线 DDL 并不适用于所有类型的 ALTER TABLE 操作；有些仍然需要排他锁。例如，正如您所做的那样，更改数据类型需要独占锁。有关详细信息，请参阅在线 DDL 概述。

推荐阅读