主页 / dba / 问题 / 211346
Accepted
dr_
Asked: 2018-07-05 07:11:42 +0800 CST

ALTER TABLE - ADD COLUMN 是否锁定表？

  • 772

ALTER TABLE ... ADD COLUMN我对DDL 语句有疑问。

在带有 MariaDB v10.2 的 Amazon RDS 实例上，我注意到INSERT语句已完成，并且行已正确插入表中（通过 验证SELECT），然后ALTER TABLE ... ADD COLUMN表中的 a 完成。

执行写入的任何 DML 语句不应该在ALTER TABLE操作完成之前排队吗？

我发布这个问题是因为有人要求我执行一些测试，以验证是否可以ALTER TABLE ... ADD COLUMN在工作时间在实时生产数据库上、在频繁使用的数据库上、在具有数百万行的表上运行——我觉得很不明智。即使ALTER TABLE没有在表上加锁，它也必须等到任何连接不再使用表（由于连接放置了元数据锁），这可能会在很久以后发生。
编辑：显然这个评价太悲观了。我一直在做一些测试，mysqlslap在表上执行繁重的操作（INSERT, UPDATE,DELETE语句和SELECT语句LIKE避免在ALTER TABLE ... ADD COLUMN运行时在 150 个模拟并发连接上使用索引；分析显示元数据锁，但等待时间很短（每个 1 秒），表更改在大约 30 分钟内完成，而没有运行 SQL 语句则需要 10 分钟。虽然这很令人满意，但另一方面，我想知道假设 DDL 语句是非阻塞的是否安全。

（可能值得注意的是 InnoDB 上有一个InstantADD COLUMN功能，它允许向表中即时添加列（在特定约束下），但它在 v10.3.2 之前不可用。）

mariadb amazon-rds

2 个回答

  1. Best Answer

    是的，它锁定了桌子。从 MySQL 8 的文档中，

    前面提到的例外情况是，ALTER TABLE在准备好从表和表定义缓存中清除过时的表结构的时候阻止读取（而不仅仅是写入）。此时，它必须获得一个排他锁。为此，它会等待当前读取器完成，并阻止新的读取和写入。

    从您链接的文档中，它非常明确

    使用 Instant ADD COLUMN，您可以享受结构化存储的所有好处，而无需重建表。

    正如您所说，您使用的是 10.2。所以看起来添加一列需要重建整个表。

    至于当你无法收到锁时会发生什么，

    我注意到INSERT语句已完成，并且行已正确插入表中（通过 SELECT 验证），然后ALTER TABLE ... ADD COLUMN表中的 a 完成。

    是的，这通常是在锁定期间发生的情况，但请注意，这并不总是会发生。有时报表和交易会放弃等待。有时，后端和池在等待时会收获。在停机期间执行此操作、超时以及在超时到期时从库中捕获错误总是更安全。只要您使用事务，如果触发了某些事情并且在超时之前无法获得它的锁定，事情就会回滚——一切都将是洁净的。

    • 4

  2. 它实际上取决于特定的 alter table，但无论如何我建议查看在线模式更改工具，如 ghost（来自 GitHub）或 pt-online-schema-change（通过 percona）。

    两种工具都在单独的表上进行更改并在最后切换到原始表 - 您甚至可以保留旧表以快速回滚。

    这是一条更安全的路线，尤其是对于重载的桌子

    • 3

相关问题