SQL Server Profiler -- 调试死锁

mofdan

SQL Server Profiler -- 调试死锁
　　跟踪一般的异常很简单，但处理死锁很难，死锁的消息号为1205，严重度为13。从服务器无法得到足够的数据来帮助判断到底发生了什么及为什么会发生这种状况，甚至服务器返回的错误消息也没有价值，这种消息所提供的唯一建议就是可以“重新运行该事务”。
　　SQL跟踪有提供帮助隔离和调试死锁状态的工具，但是SQL Server 2005把这些工具提升到了一个新的层次，它提供了一个非常有用的图形界面，帮助用户解决这些难以应付的问题。为了阐明到底提供了什么，将展示如何在tempdb数据库中强制引发一个死锁，由下列代码开始：
USE tempdb　　
GO
　　

　　
CREATE TABLE Deadlock_Table
　　
(
　　
ColumnA int NOT NULL PRIMARY KEY
　　
)
　　
GO
　　

　　
INSERT Deadlock_Table
　　
SELECT 1 UNION ALL SELECT 2
　　
GO
　　通过启动两个独立的事务并逆序交错更新行，可以引发一个死锁，并观察SQL跟踪是如何帮助调试该死锁的。
　　注意：下列例子假设用户已经识别了涉及引发死锁的两个存储过程或查询，无论是基于异常跟踪，还是通过启动服务器级别的跟踪标志1222（往SQL Server服务的启动参数中添加-T1222）并从SQL Server错误日志中收集死锁资源。如果确定了参与的查询后，就要在已经恢复了产品数据的SQL Server的一个高级实例上进行调查。调试死锁需要收集大量的数据，由于有些事件是被系统spid激发的，因此想要过滤跟踪仅仅收集相关的数据是不可能的。在一个忙碌的系统里，该跟踪将创建大量的荷载，因此建议始终脱机工作。
　　首先，在SQL Server管理工作室里打开两个查询窗口，用@@SPID收集spid。稍后可能会用这些spid来帮助分析收集到的跟踪数据。然后启动一个新的SQL Server性能分析器会话，并用TSQL模板来选择“RPC:Starting”和“SQL:BatchStarting”事件。将“Locks: Deadlock graph”、“Locks: Lock:Acquired”、“Lock: Lock:Released”和“Locks: Lock:Escalation”事件都添加到上述事件中，这些事件都可以在锁类别里找到。锁事件将帮助分析促成死锁状态的锁顺序，死锁图事件则可以提供出错事件的图形显示以辅助分析。
　　用户可以选择考虑添加 “SP:StmtStarting”事件，以防正在调试的一个或多个存储过程运行了大量的语句，这样可能会导致死锁。也应该添加EventSequence列，以方便收集数据后对其进行分析。下图显示了这个活动的完成事件选择对话框。

　　提示：这个例子将展示当监视SQL Server性能分析器中的跟踪时，如何以正确的顺序运行语句以及如何在正确的时间强制引发一个死锁。但是，许多死锁都依赖于精确的计时（或时误），要想重新生成死锁就必须在闭环中运行每个查询，它们最终能以正确的方式发生冲突。SQL Server管理工作室还有一个特性，它可以帮助用户按自己的次数需要任意在闭环中运行某个查询。在每个查询窗口中，为查询建立一个批处理，然后用“GO”追踪该批处理，可以指定想让该查询运行的次数。如下面的Transact-SQL代码会执行1000次MyStoredProcedure存储过程：
EXEC MyStoredProcedure　　
GO 1000
　　如果建立好事件，就可以启动性能分析器里的跟踪了。注意，在这里不能使用过滤器，因为死锁图事件可能被任意数目的系统spid激发。由于没有过滤器，用户可能会看到一些系统锁活动。在跟踪结束后，这些可以被忽略或过滤掉。
　　在这个跟踪启动后，回到第1个查询窗口（在本测试中是spid52），运行如下的批处理：
BEGIN TRANSACTION　　
UPDATE Deadlock_Table
　　
SET ColumnA = 3
　　
WHERE ColumnA = 1
　　
GO
　　接下来，在第2个查询窗口（在本测试中是spid53）中运行下列批处理：
BEGIN TRANSACTION　　
UPDATE Deadlock_Table
　　
SET ColumnA = 4
　　
WHERE ColumnA = 2
　　
GO
　　由于以上两个查询的锁是兼容的，因此都应该返回。它们各自持有Deadlock_Table表上不同行的锁，回到第一个查询窗口，启动下列更新，将开始等待第二个窗口的会话释放锁：
UPDATE Deadlock_Table　　
SET ColumnA = 4
　　
WHERE ColumnA = 2
　　
GO
　　最后，回到第2个窗口运行下列更新，将开始等待第一个窗口释放锁。由于两个会话都在等待对方释放资源，于是就会出现一个死锁：
UPDATE Deadlock_Table　　
SET ColumnA = 3
　　
WHERE ColumnA = 1
　　
GO
　　出现死锁后就可以停止跟踪了，找到生成的死锁图事件，该事件应该如下图所示。

　　死锁图事件包括大量的数据以协助调试发生的状况，还包括对象ID、索引名（如果有）及HoBt（哈希或B树）ID，这些都可以用来过滤锁资源，甚至还可以进一步用sys.partitions视图中的hobt_id列过滤锁资源。此外，还可以回滚查找在死锁发生前每个spid运行的最后一个查询事件，以确定和死锁有关的实际查询。
　　如果用户需要更多的数据进行下一步的调试，这里也提供了很多可用的锁信息。读者可能注意到在这个截图中，与死锁图事件邻近的锁事件没有一个和工作的spid有关。即便在空闲时系统也会获取和释放相当多的锁，因此，要想更仔细地查看这个锁链，就需要将数据加载到一个跟踪表里，并利用EventSequence列依正确的顺序重建发生过的事情。
　　同性能分析一样，SQL跟踪并不能实际解决死锁状态，但是可以提供较多的数据以帮助确定原因并接近解决方案。