SQL Server 2005锁的问题
SQL Server通过锁定资源来保证数据库的一致性。SQL Server中的锁不会对行、页、表或索引等资源有实际影响，它更像一个预订系统，所有任务在数据库内预订某些资源时都遵守它。过多的锁或长时间持有的锁会导致阻塞和其他问题，但锁本身也可能产生一些问题。
1 解决锁内存问题
为了确定SQL Server中锁使用的内存量，可以监视SQL Server中的“锁内存（KB）”计数器和系统监视器（Perfmon）中的“内存管理”对象。通过设置sp_configure中的锁选项，可以修改SQL Server中锁的内存配额。使用SQLServer：Locks计数器，可以了解更多关于锁行为的细节。
如果系统中的锁内存消耗完了，SQL Server不能分配更多的锁内存，session会收到消息1204：
The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time.
Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions.
这个消息说得很清楚：需要增加锁的内存配额，或者减少系统中锁的数量。
如果锁占用很大的内存，应该首先尝试找出造成这么多锁的根本原因。例如，可能是SQL Server的锁升级不充分。如果是这样，就需要修改锁的配置。一旦修改了锁动态配置，就影响了锁升级的行为，由此可能造成意外的影响。
如果数据库不需要任何写访问，建议将其设置为只读的。这会降低系统中产生的锁的数量。在一个只读的数据库中，SQL Server仍会发行数据库的共享锁和读表的意向共享锁，但行锁、页锁及SERIALIZABLE隔离级别的行锁，都不会被发行。例如，对于只在夜间更新的报表数据库，用户可以将在白天对数据库的查询设置为只读的。这样做对锁内存的影响会降低，这也是SQL Server的锁管理器必须做的。还可以在同一台服务器上对只读数据库创建数据库快照，SQL Server不会在数据库快照上发行共享锁。
为了减少锁内存，同样建议将读操作与写操作分开。一种方法是把报表从一个OLTP系统中分开，通过创建报表服务器和使用事务复制或SQL Server集成服务（SSIS）来为另一个用户查询读操作的服务器获取数据。这会去掉OLTP主服务器的共享锁。如果数据库服务器能够支持这种方法，可以考虑用一个数据库快照来定期卸载读操作。在本章后面我们还可以看到使用一种基于行版本的快照隔离级别来减少读数据查询产生的锁。
2 锁超时
默认状态下，一个被阻塞的查询会无限地等待一个未被满足的锁的请求。通过使用LOCK_TIMEOUT设置，可以指定一个session锁等待的时间。当锁超时发生时，session会收到消息1222：
Lock request time out period exceeded.
使用LOCK_TIMEOUT给事务带来了问题，因为错误1222发生后，SQL Server只是退出当前程序语句，而并没有中止事务。因此需要在Transact-SQL代码中使用TRY/CATCH模块来捕获1222错误。若发生了超时，可能需要回滚事务。若要了解更多内容，可以参考Inside SQL Server 2005：The Storage Engine（《Microsoft SQL Server 2005：存储引擎》，电子工业出版社，2007）第8章的“设置锁超时”。
3 锁升级
SQL Server经常会锁定表中独立的行，尤其当更新和删除比较少的行时。但执行大规模更新时，SQL Server选择表中某行或某页的锁进行升级，以更好的使用锁内存资源。但有时锁升级会造成阻塞，我们希望减少锁升级的数量（参考KB文章323630《如何解决SQL Server会中由锁升级引起的阻塞问题》，获取更多详细信息）。
检测锁升级有很多方法，最简单的方法是使用SQL Trace/Profiler中的Lock：Escalation事件类。当升级发生时，该事件被触发。但一个升级会有多个触发，所以将它们绑定在一起很重要。
确保选择Lock:Escalation事件类的默认列，这些列提供基本信息。但我们添加以下列可能也很有用：TransactionID、DatabaseID、DatabaseName和ObjectID。因为可能看到trace中一个升级事件的多行，可以使用TransactionID将它们绑定在一起，特定对象（即表）可以使用ObjectID。
通过监视表锁的数目或者和它们的持续时间，可以检测到正在发生的升级。如果可以估计应用系统很少需要（或者曾经需要）表上的共享锁或独占锁，就可以推断无论什么时候我们看到这样的锁，它都由锁升级产生。可以通过sys.dm_tran_locks DMV在给定的时间点探测表锁。下面的查询显示了一个实例：
SELECT
request_session_id,
resource_type,
DB_NAME(resource_database_id) AS DatabaseName,
OBJECT_NAME(resource_associated_entity_id) AS TableName,
request_mode,
request_type,
request_status
FROM sys.dm_tran_locks AS TL
JOIN sys.all_objects AS AO
ON TL.resource_associated_entity_id = AO.object_id
WHERE request_type = ’LOCK’
AND request_status = ’GRANT’
AND request_mode IN (’X’,’S’)
AND AO.type = ’U’
AND resource_type = ’OBJECT’
AND TL.resource_database_id = DB_ID();
上面用来查找表锁的查询引用了sys.all_objects的目录视图，所以返回信息的范围限制在查询运行的数据库上。由于sys.dm_tran_locks没有返回锁定对象更详细的信息，就没有办法得知这个对象是否是表。这样一来，就必须加入返回那些信息的数据库的一些东西，而在这种情况下，sys.all_objects包含这些信息，而且OBJECT_NAME()函数可以返回表的名称。（实例见第1章“性能故障检修方法”。）但是，它们都只返回当前数据库的信息。因此，查询过滤器的最后一个条件限制了当前数据库中那些资源的返回行。
另一种策略是使用sp_lock系统存储过程，它返回锁类型，从而可以查看表锁。不幸的是，为了过滤sp_lock，必须抓取临时数据，然后查询它并在一个WHERE子句中过滤。可以从sp_lock存储过程中提取key并执行它，但是它只适合于查询sys.dm_tran_locks DMV并对其过滤。
解决锁升级
防止多余的锁升级的最简单的方法是减少以下批量操作的批次大小：插入、更新或删除。例如，若必须执行批量更新，可以限制行数，或锁数量的最大值5 000。我们需要尝试找到防止升级的合适数值。SQL Server的查询优化器可以检测到表遍历和任何的锁升级。
目前，对每个表防止锁升级最有趣的方法是在表上创建意向锁，这样SQL Server就不能升级锁。通常锁升级只影响很少的查询或只影响某个查询，我们可以关注有问题的表。微软KB文章323630《如何解决SQL Server中由锁升级引起的阻塞问题》给出了一个很好的例子。对于SQL Server实例数据库AdventureWorks中的表Sales.SalesOrderDetail，若要防止锁升级，如下代码可以使这张表在1小时内防止锁升级：
BEGIN TRAN；
SELECT *
FROM Sales.SalesOrderDetail WITH (UPDLOCK, HOLDLOCK)
WHERE 1=0;
WAITFOR DELAY ’1:00:00’；
COMMIT；
这个查询可以防止表Sales.SalesOrderDetail上的锁升级(虽然事务日志不会比没有它增加更多）。当升级尝试发生时，仍然可以在SQL Profiler中看到Lock:Escalation事务，但是通过检查sys.dm_tran_locks，我们能验证事务只采取了行锁。不幸的是，这个请求使表的一个事务无期限地open，即使它并不锁定任何行。而且，如果这张表有其他表的外键或触发器，SQL Server也会升级它的锁，所以阻止一张表的锁升级不像想象中那么简单。
一种有风险的选择是完全关掉锁升级。例如，可以设置跟踪标识1211，它禁止整个SQL Server实例的锁升级。问题是虽然这个选项可以减少阻塞，但是它会造成更多的锁，因此锁内存增加。如果系统的锁内存消耗完，会使SQL Server停止或降低它的性能。还可以使用跟踪标识1224，它禁止锁升级，直到锁管理器使用掉SQL Server实例的40%的非AWE动态分配内存。如果锁内存的数量达到可用非AWE内存的60%，锁内存就会被消耗完。
另一种降低锁升级的方法是使用查询的“行锁”或“页锁”提示。这必须在每张表每个查询上完成。遇到的问题和所有锁提示的问题相同：查询不能使用更优化的计划。如果指定“页锁”提示，SQL Server仍然会在表锁级别进行锁升级，而且在其他不需要升级的情况下禁止使用行锁，行锁会有更好的行为。
还可以设置索引选项，通过SQL Server 2005的CREATE/ALTER INDEX语句可以改善sp_indexoption系统存储过程的性能。从根本上说，通过SET选项设置ALLOW_ROW_LOCKS或ALLOW_PAGE_LOCKS为OFF，可以防止索引上的行锁或页锁。这两个选项的默认值是ON。这些选项有效地控制索引叶子节点的颗粒度，迫使SQL Server在一个较高的颗粒度起点使用锁。重申一下，通常来说这些选项没有很高的价值，因为它们阻止了可能受益于较低锁颗粒度的查询计划。
如果由于读操作产生锁升级，我们可以试着去掉数据库中的读活动，通过使用数据库快照，复制到报表数据库上，或使用一个“快照隔离”级别。
