SQL Server 死锁故障排除,第二部分
原文:Deadlock Troubleshooting, Part 2
在这篇博文中,将检视一个实际的死锁,然后使用在SQL Server 死锁故障排除,第一部分中描述的步骤解决它,以便你可以看到它们的实际效果。这是一个微软的内部客户要求协助解决的,死锁场景的简化版本。要建立此场景,运行下面的SQL:
-- Batch #1
CREATE DATABASE deadlocktest
GO
USE deadlocktest
SET NOCOUNT ON
DBCC TRACEON (1222, -1)
GO
IF OBJECT_ID ('t1') IS NOT NULL DROP TABLE t1
IF OBJECT_ID ('p1') IS NOT NULL DROP PROC p1
IF OBJECT_ID ('p2') IS NOT NULL DROP PROC p2
GO
CREATE TABLE t1 (c1 int, c2 int, c3 int, c4 char(5000))
GO
DECLARE @x int
SET @x = 1
WHILE (@x <= 1000) BEGIN
INSERT INTO t1 VALUES (@x*2, @x*2, @x*2, @x*2)
SET @x = @x + 1
END
GO
CREATE CLUSTERED INDEX cidx ON t1 (c1)
CREATE NONCLUSTERED INDEX idx1 ON t1 (c2)
GO
CREATE PROC p1 @p1 int AS
SELECT c2, c3 FROM t1 WHERE c2 BETWEEN @p1 AND @p1+1
GO
CREATE PROC p2 @p1 int AS
UPDATE t1 SET c2 = c2+1 WHERE c1 = @p1
UPDATE t1 SET c2 = c2-1 WHERE c1 = @p1
GO
现在从另外一个连接运行下面的SQL:
-- Batch #2
USE deadlocktest
SET NOCOUNT ON
WHILE (1=1)
EXEC p2 4
GO
最后,保持上面的脚本运行的同时,从第三个连接运行下面的SQL:
-- Batch #3
USE deadlocktest
SET NOCOUNT ON
CREATE TABLE #t1 (c2 int, c3 int)
GO
WHILE (1=1) BEGIN
INSERT INTO #t1 EXEC p1 4
TRUNCATE TABLE #t1
END
GO
这会引起一个死锁,你应该看见其中一个批处理被一个1205错误强制退出。现在我们有一个可复现的死锁,可遵循SQL Server 死锁故障排除,第一部分博文中解决问题的步骤。
- 启用追踪标志 1222(译注:执行DBCC TRACEON (1222, -1)) 该设置脚本已经为你作为一个全局标志打开追踪标志(在dbcc traceon命令中,这“-1”是关键)。
- 得到-T1222输出(译注:打开YourMSSQLInstallDir\MSSQL\Log下的ERRORLOG文件,搜索关键字“1222”) 现在看看你的错误日志,你应该看到描述死锁的追踪标志1222输出。
- 解码-T1222输出 再一次通读SQL Server 死锁故障排除,第一部分,如果你需要更多的有关如何解释-T1222或-T1204的输出。此处是你在筛选-T1222的细节后,最终抽取的最重要的花絮:
Spid X is running this query (line 2 of proc [p1], inputbuffer “… EXEC p1 4 …”):SELECT c2, c3 FROM t1 WHERE c2 BETWEEN @p1 AND @p1+1Spid Y is running this query (line 2 of proc [p2], inputbuffer “EXEC p2 4”):UPDATE t1 SET c2 = c2+1 WHERE c1 = @p1
Spid X is waiting for a Shared KEY lock on index t1.cidx. Spid Y holds a conflicting X lock.Spid Y is waiting for an eXclusive KEY lock on index t1.idx1. Spid X holds a conflicting S lock.
- 通过数据库引擎优化顾问运行查询(译注:打开待分析的批查询,在查询窗口右点打开快捷菜单,选择“数据库引擎优化顾问中的分析查询”菜单项,打开“数据库引擎优化顾问”实用程序,在“常规”选项卡的“选择要优化的数据库和表”区域,指定要优化的数据库,但后单击工具栏的“开始分析”按钮执行分析 切换到“建议”选项卡可以看到推荐的建议,使用主菜单的“操作”-“应用建议”应用优化顾问建议,或“保存建议”将来使用)。-T1222输出告诉我们,在死锁的时候我们正在运行什么inputbuffer (输入缓存)(“EXEC p1 4”和“EXEC p2 4”)。使用在SQL Server 死锁故障排除,第一部分中讨论的步骤,在DTA(database engine tuning advisor)中,调优每一个查询。DTA(database engine tuning advisor)会为Batch 3推荐一个新的索引。通过从动作下拉列表菜单,选择“Apply Recommendations”,创建这个索引。
这时候,如果你重新运行批处理2和批处理3,你会发现死锁已经解决。你甚至不需要使用5-8步骤,或者我在该系列博文的第一部分的列表中地其它死锁规避策略。
在随后的博文中,我会检视,在这个特殊的死锁中查询计划的细节,方便理解是什么原因引起死锁,以及为什么DTA(database engine tuning advisor)的建议索引修复了它。
SQL Server 死锁故障排除,第二部分
SQL Server 死锁故障排除,第三部分