如何在SQL Server 2008中高效使用LOCK_ESCALATION

Question

如何在SQL Server 2008中高效使用LOCK_ESCALATION

12

我目前在 SQL Server 2008 中遇到了特定用户表频繁死锁的问题。以下是关于这个特定表格的一些事实:

该表有大量的行 (1 到 2 百万行)
在该表上使用的所有索引选项中，只有"use row lock" 被勾选 修改：该表只有一个主键索引
该表中的行经常被多个事务更新，但它们是唯一的 (例如，每小时可能会对不同的唯一行执行一千个或更多的更新语句)
该表不使用分区。

检查该表时，在sys.tables上发现 lock_escalation 设置为 TABLE

我很想将该表的 lock_escalation 设置为 DISABLE，但我不确定会产生什么副作用。据我所了解，使用 DISABLE 将最小化从 TABLE 级别升级锁定，如果与索引的行锁设置相结合，理论上应该最小化遇到的死锁问题。

从我在Determining threshold for lock escalation中读到的内容来看，当单个事务提取 5000 行时，锁定会自动升级。

在这种情况下，单个事务是什么意思？一个单一的会话/连接通过单独的更新/选择语句获取了 5000 行吗？

还是一个 SQL 更新/选择语句获取了 5000 行或更多行？

任何见解都将不胜感激，顺便说一下，我是个新手 DBA。

谢谢

- Avias

1

一次连接/事务，更新（或插入）5000行或更多行将导致锁升级。是的-您可以关闭它-但我认为这不是一个很好的主意-如果您禁用锁升级，则会将更多的压力和工作放在SQL Server锁管理器上-虽然它可能防止表锁定，但它将显着降低语句的性能。毕竟：SQL Server在每个连接达到5000个锁之后升级锁有一个很好的理由... - marc_s

@RemusRusanu 我理解你的观点，因为在仔细查看我的特定表后，似乎除了主键之外没有其他索引。由于现实约束（生产服务器 *咳嗽），我目前无法使用分析器（因为实际拥有服务器的人担心分析器可能会导致减速），但我会尽快获取死锁图。同时，是否有任何 DMV 可用于检测像我现在遇到的问题？谢谢 - Avias

3

sys.dm_db_index_usage_statsпјҡеӨ§йҮҸзҡ„user_scansдёҺдҪҺж•°йҮҸзҡ„user_seeksиЎЁжҳҺзјәеӨұзҙўеј•гҖӮ - Remus Rusanu

@Namphibian 这正是我试图避免的。通过将锁定减少到行级别，我试图避免死锁，即使这会耗费更多资源。再次强调，我尽可能地在性能和死锁风险之间取得平衡。 - Avias

@Avias 行锁和页锁是死锁的主要候选者，而不是表锁。也许我没有表述清楚，但如果一个事务发出了一个表锁，直到锁被清除，其他任何事务都无法开始，因此死锁不会发生。 - Namphibian

显示剩余8条评论

2个回答

1

我在快速谷歌搜索禁用表锁升级后找到了这篇文章。虽然不是对原帖的真正回答，但我认为它仍然适用于一次性脚本，并且值得注意。有一个很好的小技巧可以暂时禁用表锁升级。
打开另一个连接并发出类似以下命令。

BEGIN TRAN
SELECT * FROM mytable (UPDLOCK, HOLDLOCK) WHERE 1=0
WAITFOR DELAY '1:00:00'
COMMIT TRAN

来自微软知识库，锁升级无法发生如果有不同的SPID当前持有不兼容的表锁。

- Mark

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Roji P Thomas · Accepted Answer

当一个语句在一个单一对象上持有超过5000个锁时，LOCK升级就会触发。一个语句在同一张表的两个不同索引上每个持有3000个锁，并不会触发升级。

当尝试进行锁升级并且对象上存在冲突锁时，该尝试将被中止并在另外1250个锁（已持有的而非已获取的）之后重试。

因此，如果您的更新是针对单个行执行的，并且在该列上有支持索引，则锁升级就不是您的问题。您可以使用Profiler中的Locks->锁升级事件来验证这一点。

我建议您捕获死锁跟踪以确定死锁的实际原因。