在 SQL Server 2008 中添加列会锁定表吗？
我想在大约 1200 万条记录的表上运行以下命令。
ALTER TABLE t1
ADD c1 int NULL;
ALTER TABLE t2
ADD c2 bit NOT NULL
DEFAULT(0);
我已经在暂存中完成了它，并且时机似乎很好，但在生产中执行此操作之前，我想知道在创建新列期间锁定如何在表上工作（特别是在指定默认值时）。那么，有人知道吗？是整个表被锁定，还是在默认值插入期间逐行被锁定？还是发生了完全不同的事情？
I want to run the following on a table of about 12 million records.
ALTER TABLE t1
ADD c1 int NULL;
ALTER TABLE t2
ADD c2 bit NOT NULL
DEFAULT(0);
I've done it in staging and the timing seemed fine, but before I do it in production, I wanted to know how locking works on the table during new column creation (especially when a default value is specified). So, does anyone know? Does the whole table get locked, or do the rows get locked one by one during default value insertion? Or does something different altogether happen?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
在 SQL Server 11 (Denali) 之前，添加具有默认值的非空列将在幕后运行更新以填充新的默认值。因此，它将在 1200 万行更新期间锁定表。在 SQL Server 11 中，情况不再如此，列是在线添加的，并且不会发生更新，请参阅 在 SQL Server 11 中添加带有值列的在线非 NULL。
在 SQL Server 11 和之前的版本中，都会在表上获取 Sch-M 锁以修改定义（添加新列元数据）。此锁与任何其他可能的访问（包括脏读）不兼容。区别在于持续时间：在 SQL Server 11 之前，此锁将针对数据大小操作（更新 12M 行）而保持。在 SQL Server 11 中，锁定仅保留很短的时间。在 SQL Server 11 之前的行更新中，不需要获取行锁，因为表上的 Sch-M 锁保证任何单个行上不会出现任何冲突。
Prior to SQL Server 11 (Denali) the add non-null column with default will run an update behind the scenes to populate the new default values. Thus it will lock the table for the duration of the 12 million rows update. In SQL Server 11 this is no longer the case, the column is added online and no update occurs, see Online non-NULL with values column add in SQL Server 11.
Both in SQL Server 11 and prior a Sch-M lock is acquired on the table to modify the definition (add the new column metadata). This lock is incompatible with any other possible access (including dirty reads). The difference is in the duration: prior to SQL Server 11 this lock will be hold for a size-of-data operation (update of 12M rows). In SQL Server 11 the lock is only held for a short brief. In the pre-SQL Server 11 update of the rows no row lock needs to be acquired because the Sch-M lock on the table guarantees that there cannot be any conflict on any individual row.
是的，它会锁定表。
表作为一个整体具有单一架构（具有关联类型的列集）。因此，至少需要架构锁来更新表的定义。
尝试思考相反的情况如何工作 - 如果每一行单独更新，任何并行查询将如何工作（特别是如果它们涉及新列）？
默认值仅在 INSERT 和 DDL 语句期间有用 - 因此，如果您为 10,000,000 行指定新的默认值，则该默认值必须应用于所有这些行。
Yes, it will lock the table.
A table, as a whole, has a single schema (set of columns, with associated types). So, at a minimum, a schema lock would be required to update the definition of the table.
Try to think about how things would work contrariwise - if each row was updated individually, how would any parallel queries work (especially if they involved the new columns)?
And default values are only useful during
INSERTand DDL statements - so if you specify a new default for 10,000,000 rows, that default value has to be applied to all of those rows.是的，它会锁定。
DDL 语句发出架构锁（请参阅此链接），这将阻止访问直到操作完成。
确实没有办法解决这个问题，如果你仔细想想，这是有道理的。 SQL 需要知道表中有多少个字段，并且在此操作期间，某些行将比其他行拥有更多字段。
另一种方法是创建一个包含正确字段的新表，插入表，然后重命名表以将其交换出来。
Yes, it will lock.
DDL statements issue a Schema Lock (see this link) which will prevent access to the table until the operation completes.
There's not really a way around this, and it makes sense if you think about it. SQL needs to know how many fields are in a table, and during this operation some rows will have more fields than others.
The alternative is to make a new table with the correct fields, insert into, then rename the tables to swap them out.
我还没有读过添加列时锁定机制是如何工作的，但我几乎 100% 确定逐行是不可能的。
当您在 SQL Server 管理器中通过拖放执行这些类型的操作时请注意（我知道您不会在这里执行此操作，但这是一个公共论坛），因为某些更改具有破坏性（幸运的是，SQL Server 2008，至少是 R2，这里更安全，因为它告诉您“不能做”而不是仅仅做）。
但是，您可以在单个语句中运行两个列添加，并减少流失。
I have not read how the lock mechanism works when adding a column, but I am almost 100% sure row by row is impossible.
Watch when you do these types of things in SQL Server Manager with drag and drop (I know you are not doing this here, but this is a public forum), as some changes are destructive (fortunately, SQL Server 2008, at least R2, is safer here as it tells you "no can do" rather than just do it).
You can run both column additions in a single statement, however, and reduce the churn.