第十六章——处理锁、阻塞和死锁(3)——使用SQLServer Profiler侦测死锁

原文:第十六章——处理锁、阻塞和死锁(3)——使用SQLServer Profiler侦测死锁

前言:

作为DBA,可能经常会遇到有同事或者客户反映经常发生死锁,影响了系统的使用。此时,你需要尽快侦测和处理这类问题。

死锁是当两个或者以上的事务互相阻塞引起的。在这种情况下两个事务会无限期地等待对方释放资源以便操作。下面是死锁的示意图:

第十六章——处理锁、阻塞和死锁(3)——使用SQLServer Profiler侦测死锁

本文将使用SQLServer Profiler来跟踪死锁。

准备工作:

为了侦测死锁,我们需要先模拟死锁。本例将使用两个不同的会话创建两个事务。

步骤:

1、 打开SQLServer Profiler

2、 选择【新建跟踪】,连到实例。

3、 然后选择【空白】模版:

第十六章——处理锁、阻塞和死锁(3)——使用SQLServer Profiler侦测死锁

4、 在【事件选择】页中,展开Locks事件,并选择以下事件:

1、 Deadlock graph

2、 Lock:Deadlock

3、 Lock:Deadlock Chain

第十六章——处理锁、阻塞和死锁(3)——使用SQLServer Profiler侦测死锁

5、 然后打开TSQL事件,并选择以下事件:

1、 SQL:StmtCompleted

2、 SQL:StmtStarting

第十六章——处理锁、阻塞和死锁(3)——使用SQLServer Profiler侦测死锁

6、 点击【列筛选器】,在跟踪属性中,选择数据库名为需要侦测的数据库,这里使用AdventureWorks。

第十六章——处理锁、阻塞和死锁(3)——使用SQLServer Profiler侦测死锁

7、 在【组织列】中,调整顺序,如下:

第十六章——处理锁、阻塞和死锁(3)——使用SQLServer Profiler侦测死锁

8、 点击运行。

9、 然后打开SQLServer,并打开两个连接。

10、 在第一个窗口中输入并执行下面脚本:

USE AdventureWorks
GO
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
GO
BEGIN TRANSACTION
SELECT *
FROM Sales.SalesOrderDetail
WHERE SalesOrderDetailID = 121316

11、 然后在第二个窗口中输入并执行下面脚本:

USE AdventureWorks
GO
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION
SELECT *
FROM Sales.SalesOrderDetail
WHERE SalesOrderDetailID = 121317

12、现在回到第一个窗体,并运行下面的脚本:

UPDATE Sales.SalesOrderDetail
SET OrderQty=2
WHERE SalesOrderDetailID=121317

13、在第二个窗口输入下面语句:

UPDATE Sales.SalesOrderDetail
SET OrderQty=2
WHERE SalesOrderDetailID=121316

14、 然后在第二个窗口就会看到下面的消息:

第十六章——处理锁、阻塞和死锁(3)——使用SQLServer Profiler侦测死锁

15、切换到SQLServer Profiler,可以看到下面的截图:

第十六章——处理锁、阻塞和死锁(3)——使用SQLServer Profiler侦测死锁

16、 点击【Deadlock graph】时间,会显示死锁的图像:

第十六章——处理锁、阻塞和死锁(3)——使用SQLServer Profiler侦测死锁

17、可以保存死锁图像,右键然后选择导出事件数据,并另存为xdl文件:

第十六章——处理锁、阻塞和死锁(3)——使用SQLServer Profiler侦测死锁

下面是其XML格式:

第十六章——处理锁、阻塞和死锁(3)——使用SQLServer Profiler侦测死锁

分析:

在本文中,首先创建一个Profiler空白模版,然后选择下面的事件进行监控:

1、 Deadlock graph

2、 Lock:Deadlock

3、 Lock:Deadlock Chain

4、 SQL:StmtCompleted

5、 SQL:StmtStarting

然后通过限定数据库,来限制监控过得对象范围。

在配置好之后,运行跟踪,并在ssms中运行脚本。SQLServer会自动处理和侦测这种类型的死锁。然后会在第二个窗体中收到1205的错误。

在SQLServer Profiler中,演示了如何收集死锁事件,在跟踪结果中可以看到两个事务尝试在一个拥有共享锁的键上添加排它锁。通过死锁图像,可以看到死锁发生的细节。

为了避免或者最小化死锁的发生,有一些建议可以参考:

1、 确保你的事务尽可能地小,这里指范围。

2、 使用较低隔离级别的事务。

3、 对于可能的查询,使用NOLOCK查询提示。

4、 规范化数据库设计。

5、 在需要的列上创建索引,以便是表不需要经常扫描,减少锁问题的发生。

6、 控制数据库对象访问的顺序是相同的顺序。

上一篇:LeetCode Reverse Linked List (反置链表)


下一篇:media静态文件统一管理 操作内存的流 - StringIO | BytesIO PIL:python图片操作库 前端解析二进制流图片(了解) Admin自动化数据管理界面