我希望在大约1200万条记录的表上运行以下操作。
ALTER TABLE t1
ADD c1 int NULL;
ALTER TABLE t2
ADD c2 bit NOT NULL
DEFAULT(0);
我已经在测试环境中完成了这个操作,时间看起来还不错,但在生产环境中进行之前,我想知道在创建新列时表格锁定是如何工作的(特别是当指定默认值时)。那么,有人知道吗?整个表格会被锁定,还是在插入默认值期间逐行锁定?或者会发生完全不同的事情吗?
我希望在大约1200万条记录的表上运行以下操作。
ALTER TABLE t1
ADD c1 int NULL;
ALTER TABLE t2
ADD c2 bit NOT NULL
DEFAULT(0);
我已经在测试环境中完成了这个操作,时间看起来还不错,但在生产环境中进行之前,我想知道在创建新列时表格锁定是如何工作的(特别是当指定默认值时)。那么,有人知道吗?整个表格会被锁定,还是在插入默认值期间逐行锁定?或者会发生完全不同的事情吗?
是的,它将锁定表格。
一个表格作为一个整体,具有单一的模式(列集合,带有相关类型)。因此,要更新表格的定义,至少需要一个模式锁。
试着想想相反情况下会怎样——如果每个行都被单独更新,任何并行查询将如何工作(特别是涉及新列的查询)?
默认值仅在INSERT
和DDL语句期间有用——因此,如果您为1000万行指定了一个新的默认值,则必须将该默认值应用于所有这些行。
是的,它会锁定。
DDL语句会发出模式锁(请参见此链接),这将防止访问该表直到操作完成。
实际上没有绕过这个问题的方法,如果您考虑一下就会明白。SQL需要知道表中有多少字段,在此操作期间,某些行将比其他行具有更多的字段。
另一种选择是创建一个包含正确字段的新表,插入数据,然后重命名表以交换它们。
我还没有阅读添加列时锁定机制的工作原理,但我几乎可以确定逐行操作是不可能的。
当您在 SQL Server Manager 中使用拖放进行此类操作时,请注意(我知道您在这里没有这样做,但这是一个公共论坛),因为某些更改是具有破坏性的(幸运的是,SQL Server 2008,至少是 R2,在这里更安全,因为它会告诉您“不能这样做”,而不仅仅是这样做)。
您可以在单个语句中运行两个列添加操作,并减少操作次数。