sqlserver2005 锁升级的原因和应对办法

锁升级（escalate）
与锁的数量有关，达到阀值之后，升级所有连接、所有事物的锁，直接升级到table lock，而不会小升级到page lock

锁升级的时机：

总之，锁的数量达到一定值之后进行升级。总体值和单事物单表（索引）的两种计数方式。另外一种是以内存占用量为计数方式。一次不成功，间隔一定树木后再次试图升级。


减少锁升级：

1.优先使用行版本控制
2.跟踪标志1211。DBCC TRACEON 1211, -1
3. 跟踪标志1224
4.加大阀值，减少锁升级的可能。In many cases, it is desirable to control the escalation threshold at the object level

sp_configure 'locks', 10000;
GO
RECONFIGURE;
GO

参考：

在大多数情况下，数据库引擎使用默认的锁定和锁升级设置进行操作时提供的性能最佳。如果数据库引擎实例生成大量锁并且频繁进行锁升级，请考虑通过下列方法减少锁定：

• 对于读取操作，使用不会生成共享锁的隔离级别。
  
  • 当 READ_COMMITTED_SNAPSHOT 数据库选项为 ON 时，使用 READ COMMITTED 隔离级别。
    
  • 使用 SNAPSHOT 隔离级别。
    
  • 使用 READ UNCOMMITTED 隔离级别。此隔离级别只能用于能对脏读进行操作的系统。
    
• 使用 PAGLOCK 或 TABLOCK 表提示，使数据库引擎使用页、堆或索引锁而不是行锁。但是，使用此选项增加了用户阻止其他用户尝试访问相同数据的问题，对于并发用户较多的系统，不应使用此选项。
  

还可以使用跟踪标志 1211 和 1224 来禁用所有或某些锁升级。

内存计算方式：

具体细分：将在下列时间触发锁升级：

• 当单个 Transact-SQL 语句在单个表或索引上获取至少 5,000 个锁时。
  
• 当数据库引擎实例中的锁的数量超出了内存或配置阈值时。
  

如果由于锁冲突导致无法升级锁，则数据库引擎每当获取 1,250 个新锁时便会触发锁升级。

Transact-SQL 语句的升级阈值

当 Transact-SQL 语句在单个表或索引的引用上获取至少 5,000 个锁时，或在表已分区的情况下，在单个表分区或索引分区的引用上获取至少 5,000 个锁时，会触发锁升级。例如，如果该语句在一个索引上获取 3,000 个锁，在同一表中的另一个索引上获取 3,000 个锁，这种情况下不会触发锁升级。同样，如果语句中含有表的自联接，并且表的每一个引用仅在表中获取 3,000 个锁，则不会触发锁升级。

只有触发升级时已经访问的表才会发生锁升级。假定某个 SELECT 语句是一个按 TableA、TableB 和 TableC 的顺序访问三个表的联接。该语句在 TableA 的聚集索引中获取 3,000 个行锁，在 TableB 的聚集索引中获取至少 5,000 个行锁，但是仍无法访问 TableC。当数据库引擎检测到该语句在 TableB 中获取至少 5,000 个行锁时，会尝试升级当前事务在 TableB 中持有的所有锁。它还会尝试升级当前事务在 TableA 中持有的所有锁，但是由于 TableA 中锁的数量 < 5000，因此，升级无法成功。但它不会尝试在 TableC 中进行锁升级，因为发生升级时尚未访问该表。

数据库引擎实例的升级阈值

每当锁的数量大于锁升级的内存阈值时，数据库引擎都会触发锁升级。内存阈值取决于 locks 配置选项的设置：

• 如果 locks 选项设置为默认设置 0，当锁对象使用的内存是数据库引擎使用的内存的 24%（不包括 AWE 内存）时，将达到锁升级阈值。用于表示锁的升级结构的长度大约为 100 个字节。此阈值是动态的，因为数据库引擎动态获取并释放内存以适应各种工作负荷。
  
• 如果 locks 选项设置为非 0 值，则锁升级阈值是 locks 选项的值的 40%（或者更低，如果存在内存不足的压力）。

数据库引擎可以为升级选择任何会话中的活动语句，而且，只要实例中使用的锁内存保持在阈值之上，每获取 1,250 个新锁，它就会为升级选择语句。

监控锁的数量：

监控锁升级：
可以使用 SQL Server Profiler Lock:Escalation 事件监视锁升级，请参阅。

锁升级的具体对象：

每个升级事件主要在单个 Transact-SQL 语句级别上操作。当事件启动时，只要活动语句满足升级阈值的要求，数据库引擎就会尝试升级当前事务在活动语句所引用的任何表中持有的所有锁。如果升级事件 在语句访问表之前启动，则不会尝试升级该表上的锁。如果锁升级成功，只要表被当前语句引用并且包括在升级事件中，上一个语句中事务获取的、在事件启动时仍 被持有的锁都将被升级。

例如，假定某个会话执行下列操作：

• 开始一个事务。
  
• 更新 TableA。这将在 TableA 中生成排他行锁，直到事务完成后才会释放该锁。
  
• 更新 TableB。这将在 TableB 中生成排他行锁，直到事务完成后才会释放该锁。
  
• 执行联接 TableA 和 TableC 的 SELECT 语句。查询执行计划要求先从 TableA 中检索行，然后才从 TableC 中检索的行。
  
• SELECT 语句在从 TableA 中检索行时（此时还没有访问 TableC）触发锁升级。
  

如果锁升级成功，只有会话在 TableA 中持有的锁才会升级。这包括来自 SELECT 语句的共享锁和来自上一个 UPDATE 语句的排他锁。由于决定是否应进行锁升级时只考虑会话在 TableA 上为 SELECT 语句获取的锁，所以一旦升级成功，会话在 TableA 上持有的所有锁都将被升级到该表上的排他锁，而 TableA 上的所有其他较低粒度的锁（包括意向锁）都将被释放。

不会尝试升级 TableB 上的锁，因为 SELECT 语句中没有 TableB 的活动引用。同样，也不会尝试升级 TableC 上尚未升级的锁，因为发生升级时尚未访问该表。

锁的容量等，可参考 SP_INDEXOPTION.
相关概念：Locking hints (SQL.80).aspx