MySQL无法更改正在主动写入的ALTER TABLE [英] MySQL failing to ALTER TABLE which is being actively written to

问题描述

在我的应用程序的一种使用情况下，我有两个并发的 MySQL 连接:

In one of the use cases of my application, I have two concurrent MySQL connections:

• 主动写入名为T的表(实际上，不断更新该表中的一行)，并且
• 另一人在同一张表上执行DDL(ALTER TABLE，添加8个新列，并将一列从varchar(80)扩展到varchar(2000)). DDL有望最终完成.

• one actively writing to a table named T (actually, continuously updating a single row in this table), and
• another one executing a DDL against the very same table (ALTER TABLE, adding 8 new columns and extending one column from varchar(80) to varchar(2000)). The DDL is expected to eventually complete.

UPDATE DML中的列受DDL的影响不.

The columns in the UPDATE DML are not affected by the DDL.

该表仅包含一行(该行为UPDATE d).

The table contains only a single row (the one being UPDATE'd).

分析

运行涵盖此用例的集成测试时，我观察到的是测试超时(表被如此积极地写入，以致DDL永远无法完成)，但仅针对 MySQL 5.7.通常，在我们的硬件上测试可以在30秒内完成测试(对于 MySQL 5.6和8.0确实如此)，但是对于 MySQL 5.7甚至200秒是不够的.我已经尝试过使用不同的ALGORITHM和LOCK值(请参见 13.1.8 ALTER TABLE语法)，没有运气.

What I observe when an integration test covering this use case is run is a test time out (the table is being so actively written to, so that the DDL never completes), but only for MySQL 5.7. Normally, the test is expected to complete in under 30 seconds on our hardware (which indeed happens for MySQL 5.6 and 8.0), but for MySQL 5.7 even 200 seconds is not sufficient. I have experimented with different ALGORITHM and LOCK values (see 13.1.8 ALTER TABLE Syntax), with no luck.

当我分析我的应用程序( MySQL 5.7情况)时，我发现99％的CPU时间花费在从套接字读取(即等待 MySQL 响应)上表已被更改)，但是数据库实例对我来说却是一个黑匣子-当然，我启用了performance_schema并可以针对它运行查询，但是我不知道我要查找的确切信息.

When I profile my application (MySQL 5.7 case), I observe that 99% of CPU time is spent reading from a socket (i. e. waiting for MySQL to respond that the table has been altered), but the database instance is a sort of a black box to me -- of course I have performance_schema enabled and can run queries against it, but I have no idea which exact information I am looking for.

合成

与此同时，我未能将问题减少到最小的独立单元测试-我观察到的唯一一件事是测试增加了 3x 至 10x 与其他 MySQL 版本相比， MySQL 5.7所用的时间，但DDL不会永远挂起:

At the same time, I failed to reduce the problem to a minimal self-contained unit test -- the only thing I observe is 3x to 10x increase in test elapsed time for MySQL 5.7 compared to other MySQL versions, but the DDL doesn't hang forever:

所有 MySQL 版本都是 Windows 或 Debian Linux 的库存版本，可以从

All MySQL versions are either stock versions for Windows or Debian Linux downloaded from www.mysql.com with minimal changes to my.cnf, or the official Docker images.

问题:

1. MySQL 在技术上是否真的可以永远延迟ALTER TABLE DDL的执行?还是我正在观察的只是一个非常繁忙的数据库实例?是否可以
   • 请求ALTER TABLE被中断执行，即. e.如果超过了某个超时，数据库将返回错误，或者
   • 是否强制所有可能甚至在表或其某些行上放置SHARED锁的其他连接都暂停，以使它们在执行DDL时不干预?

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
   • request that ALTER TABLE is executed interruptibly, i. e. an error is returned by the database if a certain time-out is exceeded, or
   • force all other connections which can potentially place even a SHARED lock on the table or some of its rows to pause, so that they don't intervene while the DDL is being executed?

推荐答案

TL; DR-提交交易以解除对ALTER TABLE的阻止.

是的，ALTER TABLE可以长时间阻止.似乎永远.它实际上是 lock_wait_timeout 的值，默认值为31536000秒，即365天.

Yes, ALTER TABLE can block for a long time. It may seem like forever. It's actually the value of lock_wait_timeout, which is 31536000 seconds by default, or 365 days.

在MySQL中，像ALTER TABLE这样的DDL语句需要排他的表格上的元数据锁定.目的是确保您不会同时更改两个并发会话中的TABLE.

In MySQL, DDL statements like ALTER TABLE require an exclusive metadata lock on the table. The purpose is to make sure you don't ALTER TABLE from two concurrent sessions at the same time.

DML语句(例如SELECT，INSERT，UPDATE，DELETE)也持有共享的"元数据锁.共享锁可以由多个会话同时持有，但会阻止互斥锁，因为互斥锁要求它们是唯一可在表上拥有任何类型的锁的锁.

DML statement like SELECT, INSERT, UPDATE, DELETE also hold "shared" metadata locks. Shared locks can be held by multiple sessions concurrently, but block exclusive locks, because exclusive locks require that they be the only one to hold any type of lock on the table.

文档状态:

这种锁定方法的含义是，一个会话内一个事务正在使用的表在事务结束之前不能在其他会话的DDL语句中使用.

This locking approach has the implication that a table that is being used by a transaction within one session cannot be used in DDL statements by other sessions until the transaction ends.

拥有元数据锁的DML语句的目的是使它们可以保留其表的可重复读取视图，而不必担心另一个会话正在执行DROP TABLE或ALTER TABLE来损害其表视图.因为MySQL没有版本化的元数据(它们正在为此逐步努力).

The purpose of DML statements holding a metadata lock is so they can preserve their repeatable-read view of the table without worry that another session is doing DROP TABLE or ALTER TABLE to compromise their view of the table. This locking is necessary because MySQL does not have versioned metadata (they are gradually working toward that).

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

This means a transaction that has run a simple SELECT and doesn't commit will block a DROP TABLE or ALTER TABLE that requires a locking change.

在线DDL的引入有些细微之处.

There is some nuance with the introduction of online DDL.

在线DDL性能和并发性更详细地描述了ALTER TABLE通过获取共享元数据锁开始，因此未提交的事务将不会阻止它.但是，如果ALTER TABLE更改的性质要求这样做，则下一阶段可以将共享元数据锁升级为独占元数据锁.此时，由于其他事务仍保留其自己的元数据锁，因此锁定获取被阻止.

Online DDL Performance and Concurrency describes in more detail that an ALTER TABLE starts out by acquiring a shared metadata lock, so the uncommitted transaction will not block it. But the next phase may upgrade the shared metadata lock to an exclusive metadata lock, if the nature of the ALTER TABLE change requires it. At this point, the lock acquisition is blocked because the other transaction still holds its own metadata lock.

联机DDL并不适用于每种类型的ALTER TABLE操作；有些仍然需要排他锁.例如，在更改数据类型时，需要排他锁.有关详细信息，请参见在线DDL概述

Online DDL doesn't apply to every type of ALTER TABLE operation; some still require exclusive locks. Changing a data type, for example, as you are doing, requires an exclusive lock. See Online DDL Overview for details.

这篇关于MySQL无法更改正在主动写入的ALTER TABLE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！