锁升级 - 这里发生了什么? [英] Lock Escalation - What's happening here?

问题描述

在 SQL Server 2008 中更改表(删除列)时，我单击了生成更改脚本"按钮，我注意到它生成的更改脚本删除了该列，显示go"，然后运行附加的 ALTER TABLE 语句似乎将表的锁升级设置为TABLE".示例:

While altering a table (removing a column) in SQL Server 2008, I clicked the Generate Change Script button and I noticed that the change script it generated drops the column, says "go" and then runs an additional ALTER TABLE statement that appears to set the lock escalation for the table to "TABLE". Example:

ALTER TABLE dbo.Contract SET (LOCK_ESCALATION = TABLE)

我还应该注意，这是更改脚本所做的最后一件事.它在这里做什么，为什么将 LOCK_ESCALATION 设置为 TABLE?

I should also note that this is the last thing the change script is doing. What is it doing here and why is it setting the LOCK_ESCALATION to TABLE?

推荐答案

"Lock升级"是 SQL 处理大型更新锁定的方式.当 SQL 将要更改很多行时，数据库引擎使用更少、更大的锁(例如整个表)而不是锁定许多较小的东西(例如行锁)会更有效.

"Lock Escalation" is how SQL handles locking for large updates. When SQL is going to change a lot of rows, it's more efficient for the database engine to take fewer, larger locks (e.g. entire table) instead of locking many smaller things (e.g. row locks).

但是当您有一个巨大的表时，这可能会出现问题，因为锁定整个表可能会长时间锁定其他查询.这就是权衡:许多小粒度锁比较少(或一个)粗粒度锁慢，并且如果一个进程正在等待另一个进程，则多个查询锁定表的不同部分会产生死锁的可能性.

But this can be problematic when you have a huge table, because taking a lock on the entire table may lock out other queries for a long time. That's the tradeoff: many small-granularity locks are slower than fewer (or one) coarse-grained locks, and having multiple queries locking different parts of a table creates the possibility for deadlock if one process is waiting on another.

SQL 2008 中有一个表级选项LOCK_ESCALATION，它允许控制锁升级.默认情况下，TABLE"允许锁一直升级到表级别.在大多数情况下，DISABLE 可防止锁升级到整个表.AUTO 允许表锁定，除非表已分区，在这种情况下，锁定仅针对分区级别.请参阅这篇博文 了解更多信息.

There is a table-level option, LOCK_ESCALATION, new in SQL 2008, which allows control of lock escalation. The default, "TABLE" allows locks to escalate all the way to the table level. DISABLE prevents lock escalation to the entire table in most cases. AUTO allows table locks except if the table is partitioned, in which case locks are only made up to the partition level. See this blog post for more info.

我怀疑 IDE 在重新创建表时添加了此设置，因为 TABLE 是 SQL 2008 中的默认值.请注意，SQL 2005 中不支持 LOCK_ESCALATION，因此如果尝试运行2005 实例上的脚本.此外，由于 TABLE 是默认值，因此您可以在重新运行脚本时安全地删除该行.

I suspect that the IDE adds this setting when re-creating a table because TABLE is the default in SQL 2008. Note that LOCK_ESCALATION isn't supported in SQL 2005, so you'll need to strip it if trying to run the script on a 2005 instance. Also, since TABLE is the default, you can safely remove that line when re-running your script.

另请注意，在出现此设置之前的 SQL 2005 中，所有锁都可以升级到表级别——换句话说，TABLE"是 SQL 2005 上唯一的设置.

Also note that, in SQL 2005 before this setting was present, all locks could escalate to table level-- in other words, "TABLE" was the only setting on SQL 2005.

这篇关于锁升级 - 这里发生了什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！