首页 > 数据库 >使用 SQL SERVER PROFILER 监测死锁

使用 SQL SERVER PROFILER 监测死锁

时间:2024-04-12 16:26:17浏览次数:24  
标签:事务 SET 窗口 T3 PROFILER 死锁 SQL WHERE

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

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

 



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

 

准备工作:

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

 

步骤:

1、 打开SQLServer Profiler

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

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


 

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

1、 Deadlock graph

2、 Lock:Deadlock

3、 Lock:Deadlock Chain


 

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

1、 SQL:StmtCompleted

2、 SQL:StmtStarting


 

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


 

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


 

8、 点击运行。

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

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


[sql]  view plain copy print ?  
  1. USE AdventureWorks   
  2. GO  
  3. SET TRANSACTION ISOLATION LEVEL REPEATABLE READ  
  4. GO  
  5. BEGIN TRANSACTION  
  6. SELECT  *  
  7. FROM    Sales.SalesOrderDetail  
  8. WHERE   SalesOrderDetailID = 121316  


 


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


[sql]  view plain copy print ?  
  1. USE AdventureWorks  
  2. GO  
  3. SET TRANSACTION ISOLATION LEVEL REPEATABLE READ  
  4. BEGIN TRANSACTION  
  5. SELECT  *  
  6. FROM    Sales.SalesOrderDetail  
  7. WHERE   SalesOrderDetailID = 121317  


 


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


[sql]  view plain copy print ?  
  1. UPDATE Sales.SalesOrderDetail  
  2. SET OrderQty=2  
  3. WHERE SalesOrderDetailID=121317  


 


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


[sql]  view plain copy print ?  
  1. UPDATE Sales.SalesOrderDetail  
  2. SET OrderQty=2  
  3. WHERE SalesOrderDetailID=121316  


 


 

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


 

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


 

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


 

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


 

下面是其XML格式:

 

 

分析:

在本文中,首先创建一个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、 控制数据库对象访问的顺序是相同的顺序。


 

ps:我用了这个例子,结果没出现死锁,用下面这个例子可以:

 

在一个窗口输入以下语句:

BEGIN TRAN 

USE TEST;

UPDATE T6 SET time3='2013-12-31 00:00:00.000'

WHERE time3='9999-09-09 00:00:00.000'


WAITFOR DELAY '0:0:15'

UPDATE T3 SET VALUE=5 

WHERE ID=10

COMMIT TRAN

在另一个窗口输入以下语句:

BEGIN TRAN

USE TEST;

UPDATE T3 SET VALUE=5 

WHERE ID=10

WAITFOR DELAY '0:0:15'

UPDATE T6 SET time3='2013-12-31 00:00:00.000'

WHERE time3='9999-09-09 00:00:00.000'


COMMIT TRAN

在第一个窗口执行后,在15秒之内执行第二个窗口的查询。

会发生死锁。

事务的执行过程:

①:窗口1要更新T6的数据,需要向数据库引擎申请T6的排他锁。

②:窗口2要更新T3的数据,需要向数据库引擎申请T3的排他锁。

因为请求锁的对象不同,所以它们可以同时得到想要的锁。

③:在窗口2还没有完成对T3更新前,窗口1完成了对T6的更新,然后想要更新T3的数据,此时需要请求T3的排他锁。由于排他锁与窗口2上面的T3上面的排他锁不兼容,所以窗口1必须等待窗口2执行完事务,然后放在T3上面的排他锁后,才能获得对T3的排他锁。

④:窗口2想要申请T6上的排他锁同上面道理一样。

双方都等待对方释放资源,才能继续事务操作,从而形成死锁。

例子中使用WAITFOR语句在执行两条语句间休息5秒,可以增加死锁的概率。


数据库引擎会定期检索死锁,一旦发现问题,会选取其中一个事务作为牺牲品,终止事务的运行,从而释放资源。

被牺牲的事务会显示如下信息:


(1 行受影响)

消息 1205,级别 13,状态 45,第 7 行

事务(进程 ID 55)与另一个进程被死锁在 锁 资源上,并且已被选作死锁牺牲品。请重新运行该事务。


‘1行受影响’表示第一个UPDATE语句已经执行成功。但是由于事务没有成功提交,所以回滚掉了。

 



本篇文章转自:

http://blog.csdn.net/dba_huangzj/article/details/8697600



 

2024-04-12 16:08:46【出处】:https://blog.csdn.net/YABIGNSHI/article/details/24363609

=======================================================================================

标签:事务,SET,窗口,T3,PROFILER,死锁,SQL,WHERE
From: https://www.cnblogs.com/mq0036/p/18131545

相关文章

  • MySQL基础
    1,初识SQL语句SQL语句:操作文件夹(库) 增 createdatabasedb1charsetutf8; 查 showcreatedatabasedb1; showdatabases; 改 alterdatabasedb1charsetgbk; 删 dropdatabasedb1;操作文件(表) 切换文件夹:usedb1; 查看当前所在文件夹:selectdatabase(); ......
  • MyBatis动态SQL
    MyBatis动态SQL动态SQL简介动态SQL是MyBatis的强大特性之一。如果你使用过JDBC或其它类似的框架,你应该能理解根据不同条件拼接SQL语句有多痛苦,例如拼接时要确保不能忘记添加必要的空格,还要注意去掉列表最后一个列名的逗号。利用动态SQL,可以彻底摆脱这种痛苦。使用动态......
  • mysql修改密码报错:Your password does not satisfy the current policy requirements
    参考https://blog.csdn.net/u013449046/article/details/106455041这是mysql初始化时,使用临时密码,修改自定义密码时,由于自定义密码比较简单,就出现了不符合密码策略的问题。密码策略问题异常信息:ERROR1819(HY000):Yourpassworddoesnotsatisfythecurrentpolicyrequ......
  • MySQL的CDC数据实时同步
    MySQL的CDC数据实时同步 背景近段时间,业务系统架构基本完备,数据层面的建设比较薄弱,因为笔者目前工作重心在于搭建一个小型的数据平台。优先级比较高的一个任务就是需要近实时同步业务系统的数据(包括保存、更新或者软删除)到一个另一个数据源,持久化之前需要清洗数据并且构建一......
  • MySQL数据库下载及安装教程
    MySQL数据库下载及安装教程(最最新版)一、下载mysql数据库二、安装Mysql三、验证是否安装成功(一)、命令提示符cmd窗口验证(二)、MySQL控制台验证一、下载mysql数据库进入MySQL官方网站(https://www.mysql.com/downloads/),按下图顺序点击进入下载页面。 注意:这里MSIInstal......
  • mysql半同步复制
    1、首先在master上面安装插件INSTALLPLUGINrpl_semi_sync_masterSONAME'semisync_master.so';QueryOK,0rowsaffected(0.01sec)2、设置master全局变量和超时时间SETGLOBALrpl_semi_sync_master_enabled=1;QueryOK,0rowsaffected(0.00sec)查看变量是否开......
  • SQL 执行大脚本 提示内存不足 解决办法
    SQL 执行大脚本 提示内存不足 解决办法 用微软自带的sqlcmd工具,可以导入执行。以SQL Server 2008R版本为例:第一步:Win+R 键入:cmd 命令,开启命令行工具;第二步:键入:cd C:\Program Files\Microsoft SQL Server\100\Tools\Binn (具体目录路径跟你安装的SQL位置有关)第三步:键入......
  • 丐版sqlserver AlwaysOn集群
    丐版sqlserver集群之前试过docker的,k8s的,然后发现,还是最朴素的是最简单的,希望有大佬能够汉化,他妈的,那些英文看得人要发癫啊。前置准备,参照丐版pxc集群:https://www.cnblogs.com/zwnfdswww/p/18112077如果不关防火墙:打开对应的端口即可:sudofirewall-cmd--zone=public--a......
  • NL2SQL进阶系列(2):DAIL-SQL、DB-GPT开源应用实践详解Text2SQL
    NL2SQL进阶系列(2):DAIL-SQL、DB-GPT开源应用实践详解[Text2SQL]NL2SQL基础系列(1):业界顶尖排行榜、权威测评数据集及LLM大模型(SpidervsBIRD)全面对比优劣分析[Text2SQL、Text2DSL]NL2SQL基础系列(2):主流大模型与微调方法精选集,Text2SQL经典算法技术回顾七年发展脉络梳理NL2SQL......
  • NL2SQL进阶系列(1):DB-GPT-Hub、SQLcoder、Text2SQL开源应用实践详解
    NL2SQL进阶系列(1):DB-GPT-Hub、SQLcoder、Text2SQL开源应用实践详解NL2SQL基础系列(1):业界顶尖排行榜、权威测评数据集及LLM大模型(SpidervsBIRD)全面对比优劣分析[Text2SQL、Text2DSL]NL2SQL基础系列(2):主流大模型与微调方法精选集,Text2SQL经典算法技术回顾七年发展脉络梳理1.......