ALTER TABLE 不锁定表? [英] ALTER TABLE without locking the table?

问题描述

在 MySQL 中执行 ALTER TABLE 语句时，整个表在语句执行期间处于读锁定状态(允许并发读取，但禁止并发写入).如果它是一个大表，INSERT 或 UPDATE 语句可能会被阻塞很长时间.有没有办法进行热修改"，比如添加一列，使表格在整个过程中仍然可以更新?

When doing an ALTER TABLE statement in MySQL, the whole table is read-locked (allowing concurrent reads, but prohibiting concurrent writes) for the duration of the statement. If it's a big table, INSERT or UPDATE statements could be blocked for a looooong time. Is there a way to do a "hot alter", like adding a column in such a way that the table is still updatable throughout the process?

我主要对 MySQL 的解决方案感兴趣，但如果 MySQL 无法做到，我会对其他 RDBMS 感兴趣.

Mostly I'm interested in a solution for MySQL but I'd be interested in other RDBMS if MySQL can't do it.

澄清一下，我的目的只是在将需要额外表列的新功能推送到生产时避免停机.任何数据库架构都会随着时间的推移而改变，这就是生活中的事实.我不明白为什么我们应该接受这些变化必然会导致停机；那只是弱.

To clarify, my purpose is simply to avoid downtime when a new feature that requires an extra table column is pushed to production. Any database schema will change over time, that's just a fact of life. I don't see why we should accept that these changes must inevitably result in downtime; that's just weak.

推荐答案

唯一的其他选择是手动完成许多 RDBMS 系统无论如何都会做的事情...
- 创建一个新表

The only other option is to do manually what many RDBMS systems do anyway...
- Create a new table

然后您可以一次将旧表的内容复制到一个块上.同时始终对源表上的任何 INSERT/UPDATE/DELETE 保持谨慎.(可以通过触发器管理.虽然这会导致减速，但它不是锁定...)

You can then copy the contents of the old table over a chunk at a time. Whilst always being cautious of any INSERT/UPDATE/DELETE on the source table. (Could be managed by a trigger. Although this would cause a slow down, it's not a lock...)

完成后，更改源表的名称，然后更改新表的名称.最好在交易中.

Once finished, change the name of the source table, then change the name of the new table. Preferably in a transaction.

完成后，重新编译使用该表的任何存储过程等.执行计划可能不再有效.

Once finished, recompile any stored procedures, etc that use that table. The execution plans will likely no longer be valid.

有些评论说这个限制有点差.所以我想我会对它提出一个新的观点来说明为什么它是这样的......

Some comments have been made about this limitation being a bit poor. So I thought I'd put a new perspective on it to show why it's how it is...

• 添加新字段就像更改每一行的一个字段.
• 字段锁比行锁更难，更不用说表锁了.
  
  
• 您实际上是在更改磁盘上的物理结构，每条记录都在移动.
• 这真的很像对整个表的更新，但影响更大......

这篇关于ALTER TABLE 不锁定表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！