不用锁定表就可以改变表吗? [英] 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...

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

• Adding a new field is like changing one field on every row.
• Field Locks would be much harder than Row locks, never mind table locks.
  
  
• You're actually changing the physical structure on the disk, every record moves.
• This really is like an UPDATE on the Whole table, but with more impact...

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