SQL Server 死锁故障排除,第二部分
原文:Deadlock Troubleshooting, Part 2
在这篇博文中,将检视一个实际的死锁,然后使用在SQL Server 死锁故障排除,第一部分中描述的步骤解决它,以便你可以看到它们的实际效果。这是一个微软的内部客户要求协助解决的,死锁场景的简化版本。要建立此场景,运行下面的SQL:
-- Batch #1
CREATEDATABASEdeadlocktest
GO
USEdeadlocktest
SETNOCOUNTON
DBCCTRACEON(1222,-1)
GO
IFOBJECT_ID('t1')ISNOTNULLDROPTABLEt1
IFOBJECT_ID('p1')ISNOTNULLDROPPROCp1
IFOBJECT_ID('p2')ISNOTNULLDROPPROCp2
GO
CREATETABLEt1(c1int,c2int,c3int,c4char(5000))
GO
DECLARE@xint
SET@x=1
WHILE(@x<=1000)BEGIN
INSERTINTOt1VALUES(@x*2,@x*2,@x*2,@x*2)
SET@x=@x+1
END
GO
CREATECLUSTEREDINDEXcidxONt1(c1)
CREATENONCLUSTEREDINDEXidx1ONt1(c2)
GO
CREATEPROCp1 @p1intAS
SELECTc2,c3FROMt1WHEREc2BETWEEN@p1AND@p1+1
GO
CREATEPROCp2 @p1intAS
UPDATEt1SETc2=c2+1WHEREc1=@p1
UPDATEt1SETc2=c2-1WHEREc1=@p1
GO
现在从另外一个连接运行下面的SQL:
-- Batch #2
USEdeadlocktest
SETNOCOUNTON
WHILE(1=1)
EXECp2 4
GO
最后,保持上面的脚本运行的同时,从第三个连接运行下面的SQL:
-- Batch #3
USEdeadlocktest
SETNOCOUNTON
CREATETABLE#t1(c2int,c3int)
GO
WHILE(1=1)BEGIN
INSERTINTO#t1EXECp1 4
TRUNCATETABLE#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+1
Spid 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 死锁故障排除,第二部分SQL Server 死锁故障排除,第三部分
分享到:
相关推荐
21.1 优化时机(第二部分) 21.2 日常维护 21.3 故障排除 21.4 小结 第22章 管理 22.1 计划作业 22.2 备份和恢复 22.3 索引维护 22.4 数据存档 22.5 PowerShell 22.6 基于策略的管理 ...
21.1 优化时机(第二部分) 21.2 日常维护 21.3 故障排除 21.4 小结 第22章 管理 22.1 计划作业 22.2 备份和恢复 22.3 索引维护 22.4 数据存档 22.5 PowerShell 22.6 基于策略的管理 ...
21.1 优化时机(第二部分) 21.2 日常维护 21.3 故障排除 21.4 小结 第22章 管理 22.1 计划作业 22.2 备份和恢复 22.3 索引维护 22.4 数据存档 22.5 PowerShell 22.6 基于策略的管理 ...
21.1 优化时机(第二部分) 21.2 日常维护 21.3 故障排除 21.4 小结 第22章 管理 22.1 计划作业 22.2 备份和恢复 22.3 索引维护 22.4 数据存档 22.5 PowerShell 22.6 基于策略的管理 ...
21.1 优化时机(第二部分) 21.2 日常维护 21.3 故障排除 21.4 小结 第22章 管理 22.1 计划作业 22.2 备份和恢复 22.3 索引维护 22.4 数据存档 22.5 PowerShell 22.6 基于策略的管理 ...
21.1 优化时机(第二部分) 21.2 日常维护 21.3 故障排除 21.4 小结 第22章 管理 22.1 计划作业 22.2 备份和恢复 22.3 索引维护 22.4 数据存档 22.5 PowerShell 22.6 基于策略的管理 ...
=======第二篇:备份与恢复======= 86 一、备份和恢复的基本概念 86 (一)衡量数据库可恢复性的两个指标 86 (二)数据库故障的类型 86 (三)配置数据库的可恢复性 88 (四)归档日志文件 88 (五)启用 ...