详解SQL Server Profiler分析死锁几大步骤-白红宇

详解SQL Server Profiler分析死锁几大步骤

阅读量：5030 次

发布时间：2019-06-12

本文共 4593 字，大约阅读时间需要 15 分钟。

在两个或多个SQL Server进程中，每一个进程锁定了其他进程试图锁定的资源，就会出现死锁，例如，进程process1对table1持有1个排它锁(X),同时process1对table2请求1个排它锁(X), 进程process2对table2持有1个排它锁(X),同时process2对table1请求1个排它锁(X)

类似这种情况，就会出现死锁,除非当某个外部进程断开死锁，否则死锁中的两个事务都将无限期等待下去。

Microsoft SQL Server 数据库引擎死锁监视器定期检查陷入死锁的任务。

如果监视器检测到循环依赖关系，将选择其中一个任务作为牺牲品(通常是选择占资源比较小的进程作为牺牲品)，然后终止其事务并提示错误1205。

这里我们通过SQL Server Profiler来监视分析死锁的发生过程，那样我们就会深刻理解死锁的成因。

1.创建测试表。

在 Microsoft SQL Server Management Studio上，新建一个查询，写创建表DealLockTest_1 & DealLockTest_2两个表：

脚本：

use Test--创建分析死锁使用到的两个表DealLockTest_1 & DealLockTest_2goSet Nocount On    Goif object_id('DealLockTest_1') Is Not Null    Drop Table DealLockTest_1goCreate Table DealLockTest_1(    ID int Identity(1,1) Primary Key,    Name nvarchar(512))if object_id('DealLockTest_2') Is Not Null    Drop Table DealLockTest_2goCreate Table DealLockTest_2(    ID int Identity(1,1) Primary Key,    Name nvarchar(512))Go--插入一些测试数据Insert Into DealLockTest_1(Name)    Select name From sys.all_objects    Insert Into DealLockTest_2(Name)    Select name From sys.all_objectsGo

创建好表和插入测试数据后，先执行脚本代码（因为我们不需要跟踪该代码），紧接着，我们就模拟两个会话，一个会话里面包含一个事务。这里我们就新建两个查询，其中第一个会话，是更新DealLockTest_1表后，等待5秒钟，更新DealLocktest_2.

Use TestGo    --第一个会话        Begin Tran        Update DealLockTest_1        Set Name=N'test1'        Where ID >0            /*这里的Waitfor等待，是为了容易获取死锁的发生*/            Waitfor Delay '00:00:05'                Update DealLockTest_2        Set Name=N'test2'        Where ID >0                Commit TranGo

代码写好后，我们先不要执行代码，接下来就写第二个会话代码；第二个会话更新表的顺序，刚好与第一个会话相反，是更新DealLockTest_2表后，等待5秒钟，更新DealLocktest_1.

代码

Use TestGo    --第二个会话        Begin Tran        Update DealLockTest_2        Set Name=N'test1'        Where ID >0    /*这里的Waitfor等待，是为了容易获取死锁的发生*/                    Waitfor Delay '00:00:05'                        Update DealLockTest_1        Set Name=N'test2'        Where ID >0                Commit TranGo

第二个会话代码，也先不要执行。

2.启动SQL Server Profiler，创建Trace(跟踪).

启动SQL Server Profiler工具(在Microsoft SQL Server Management Studio的工具菜单上就发现它)，创建一个Trace,Trace属性选择主要是包含:

Deadlock graph Lock: Deadlock Lock: Deadlock Chain RPC:Completed SP:StmtCompleted SQL:BatchCompleted SQL:BatchStarting

点执行按钮，启动Trace。

3.执行测试代码&监视死锁。

转到 Microsoft SQL Server Management Studio界面，执行第一个会话&第二个会话的代码，稍稍等待5秒钟，我们就会发现其中一个会话收到报错消息

我们再切换到SQL Server Profiler界面，就能发现SQL Server Profiler收到执行脚本过程发生死锁的信息。

OK,这里就先停止SQL Server Profiler上的“暂停跟踪” Or "停止跟踪"按钮，下面我们具体分析死锁发生过程。

4.分析死锁

如下图，我们可以看到第一个会话在SPID 54,第二个会话在SPID 55,一旦SQL Server发现死锁，它就会确定一个优胜者，可成功执行，和另一个作为牺牲品，要回滚。

可以到看到EventClass列中，两条SQL:BatchCompleted事件紧跟在Lock:DealLock后面，其中一条，它就是作为牺牲品，它会被回滚.而另一条SQL:BatchCompleted将会是优胜者，成功执行。

那么，谁是优胜者，谁是牺牲品呢? 不用着急，通过DealLock graph事件，所返回来的信息，我们可以知道结果。

我们虽然不能明白DealLock graph图示的含义，但通过图中描述的关系，我们知道一些有用的信息。图中左右两旁椭圆形相当一个处理节点(Process Node),当鼠标移动到上面的时候，可以看到内部执行的代码，如Insert,UPdate,Delete.有打叉的左边椭圆形就是牺牲者，没有打叉的右边椭圆形是优胜者。中间两个长方形就是一个资源节点(Resource Node)，描述数据库中的对象,如一个表、一行或一个索引。在我们当前的实例中，资源节点描述的是，在聚集索引请求获得排它锁(X)。椭圆形与长方形之间，带箭头的连线表示，处理节点与资源节点的关系，包含描述锁的模式.

接下来我们更详细的看图里面的数据说明。

先看右边作为优胜者的这椭圆形，我们可以看到内容包含有：

服务器进程 ID：服务器进程标识符 (SPID)，即服务器给拥有锁的进程分配的标识符。 服务器批 ID：服务器批标识符 (SBID)。 执行上下文 ID：执行上下文标识符 (ECID)。与指定 SPID 相关联的给定线程的执行上下文 ID。ECID = {0,1,2,3, ...n}，其中 0 始终表示主线程或父线程，并且 {1,2,3, ...n} 表示子线程。 死锁优先级： 进程的死锁优先级有关可能值的详细信息，请参阅 SET DEADLOCK_PRIORITY (Transact-SQL)。 已用日志： 进程所使用的日志空间量。 所有者 ID：正在使用事务并且当前正在等待锁的进程的事务 ID。 事务描述符： 指向描述事务状态的事务描述符的指针。

这些数据描述，对于我们理解死锁，只需要知道其中的一些就够，除非我们在专门SQL Server机构工作，才可能要深入理解它们。