详解SQL Server Profiler剖析死锁几大步骤.docx

详解SQL Server Profiler剖析死锁几大步骤.docx

《详解SQL Server Profiler剖析死锁几大步骤.docx》由会员分享，可在线阅读，更多相关《详解SQL Server Profiler剖析死锁几大步骤.docx（11页珍藏版）》请在冰豆网上搜索。

详解SQLServerProfiler剖析死锁几大步骤

本文将为大众讲的是SQLServerProfiler剖析死锁几大步骤，这里也是为了大众更好的做好数据库的维护工作。

在两个或多个SQLServer进程中，每一个进程锁定了其他进程试图锁定的资源，就会出现死锁，比方，进程process1对table1持有1个排它锁（X）,同时process1对table2请求1个排它锁（X）,进程process2对table2持有1个排它锁（X）,同时process2对table1请求1个排它锁（X）类似这种情况，就会出现死锁,除非当某个外部进程断开死锁，否则死锁中的两个事务都将无限期等待下去。

MicrosoftSQLServer数据库引擎死锁监视器定期检验陷入死锁的任务。

假如监视器检测到循环依靠联系，将挑选其中一个任务作为牺牲品（通常是挑选占资源比较小的进程作为牺牲品），然后终止其事务并提示不正确1205。

这里咱们议决SQLServerProfiler来监视剖析死锁的发生流程，那样咱们就会深刻理会死锁的成因。

1.建立测试表。

在MicrosoftSQLServerManagementStudio上，新建一个查询，写建立表DealLockTest_1&DealLockTest_2两个表：

脚本：

1.useTest

2.--建立

3.剖析

4.死锁运用

5.到的两个表DealLockTest_1&DealLockTest_2

6.go

7.SetNocountOn

8.Go

9.ifobject_id（'DealLockTest_1'）IsNotNull

10.DropTableDealLockTest_1

11.go

12.CreateTableDealLockTest_1

13.（

14.IDintIdentity（1,1）PrimaryKey,

15.Namenvarchar（512）

16.）

17.ifobject_id（'DealLockTest_2'）IsNotNull

18.DropTableDealLockTest_2

19.go

20.CreateTableDealLockTest_2

21.（

22.IDintIdentity（1,1）PrimaryKey,

23.Namenvarchar（512）

24.）

25.Go

CodehighlightingproducedbyActiproCodeHighlighter（freeware）

http:

//www.CodeH

-->--插入一些测试数据

1.InsertIntoDealLockTest_1（Name）

2.SelectnameFromsys.all_objects

3.InsertIntoDealLockTest_2（Name）

4.SelectnameFromsys.all_objects

5.Go

建立好表和插入测试数据后，先执行脚本代码（因为咱们不须要跟踪该代码），紧接着，咱们就模拟两个会话，一个会话里面包含一个事务。

这里咱们就新建两个查询，其中第一个会话，是更新DealLockTest_1表后，等待5秒钟，更新DealLocktest_2.

1.UseTest

2.Go

3.--第一个会话

4.BeginTran

5.UpdateDealLockTest_1

6.SetName=N'test1'

7.WhereID>0

8./*这里的Waitfor等待，是为了简单获取死锁的发生*/

9.WaitforDelay'00:

00:

05'

10.UpdateDealLockTest_2

11.SetName=N'test2'

12.WhereID>0

13.CommitTran

14.Go

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

1.UseTest

2.Go

3.--第二个会话

4.BeginTran

5.UpdateDealLockTest_2

6.SetName=N'test1'

7.WhereID>0

8./*这里的Waitfor等待，是为了简单

9.获取死锁的发生*/

10.WaitforDelay'00:

00:

05'

11.UpdateDealLockTest_1

12.SetName=N'test2'

13.WhereID>0

14.CommitTran

15.Go

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

2.启动SQLServerProfiler，建立Trace（跟踪）.

启动SQLServerProfiler工具（在MicrosoftSQLServerManagementStudio的工具菜单上就发觉它），建立一个Trace,Trace属性挑选首要是包含:

DeadlockgraphLock:

DeadlockLock:

DeadlockChainRPC:

CompletedSP:

StmtCompletedSQL:

BatchCompletedSQL:

BatchStarting

点击查看大图

点执行按钮，启动Trace。

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

转到MicrosoftSQLServerManagementStudio界面，执行第一个会话&第二个会话的代码，稍稍等待5秒钟，咱们就会发觉其中一个会话收到报错消息

咱们再切换到SQLServerProfiler界面，就能发觉SQLServerProfiler收到执行脚本流程发生死锁的信息。

点击查看大图

OK,这里就先停止SQLServerProfiler上的“暂停跟踪”Or"停止跟踪"按钮，下面咱们详细剖析死锁发生流程。

4.剖析死锁

如下图，咱们能够看到第一个会话在SPID54,第二个会话在SPID55,一旦SQLServer发觉死锁，它就会确定一个优胜者，可成功执行，和另一个作为牺牲品，要回滚。

能够到看到EventClass列中，两条SQL:

BatchCompleted事件紧跟在Lock:

DealLock后面，其中一条，它就是作为牺牲品，它会被回滚.而另一条SQL:

BatchCompleted将会是优胜者，成功执行。

那么，谁是优胜者，谁是牺牲品呢?

不用着急，议决DealLockgraph事件，所返回来的信息，咱们能够知晓结果。

点击查看大图

咱们虽然无法明白DealLockgraph图示的意思，但议决图中描述的联系，咱们知晓一些有用的信息。

图中左右两旁椭圆形相当一个处理节点（ProcessNode）,当鼠标移动到上面的时刻，能够看到内部执行的代码，如Insert,UPdate,Delete.有打叉的左边椭圆形就是牺牲者，没有打叉的右边椭圆形是优胜者。

中间两个长方形就是一个资源节点（ResourceNode），描述数据库中的对象,如一个表、一行或一个索引。

在咱们当前的实例中，资源节点描述的是，在聚集索引请求取得排它锁（X）。

椭圆形与长方形之间，带箭头的连线示意，处理节点与资源节点的联系，包含描述锁的模式.

接下来咱们更细致的看图里面的数据表明。

先看右边作为优胜者的这椭圆形，咱们能够看到内容包含有：

服务器进程ID：

服务器进程标识符（SPID），即服务器给拥有锁的进程分配的标识符。

服务器批ID：

服务器批标识符（SBID）。

执行上下文ID：

执行上下文标识符（ECID）。

与指定SPID有关联的给定线程的执行上下文ID。

ECID={0,1,2,3,...n}，其中0始终示意主线程或父线程，并且{1,2,3,...n}示意子线程。

死锁优先级：

进程的死锁优先级有关可能值的细致信息，请参阅SETDEADLOCK_PRIORITY（Transact-SQL）。

已用日志：

进程所运用的日志空间量。

所有者ID：

正在运用事务并且当前正在等待锁的进程的事务ID。

事务描述符：

指向描述事务状态的事务描述符的指针。

这些数据描述，对于咱们理会死锁，只须要知晓其中的一些就够，除非咱们在专门SQLServer机构工作，才可能要深入理会它们。

点击查看大图

下面咱们来看左边作为牺牲品的这椭圆形处理节点，它告诉咱们以下信息：

1.它是一个失败的事务。

（蓝色的交叉示意）

2.它是作为牺牲品的T-SQL代码。

3.它对右下方的资源节点有一个排它锁（X）.

4.它对右上方的资源节点请求一个排它锁（X）.

点击查看大图

咱们再来看中间两个长方形的资源节点,两个处理节点对它们各自都运用权，来执行它们各自的代码，同时又有对对方运用资源请求的动作，从而发生了资源的竞争。

这也就让咱们明白死锁发生的原由。

这里表明下资源节点的一些信息：

HoBT：

堆或B树。

用于保卫没有聚集索引的表中的B树（索引）或堆数据页的锁

associatedobjid:

关联的对象ID,这里只是索引关联的对象ID.

Indexname:

索引名

点击查看大图

让咱们再对SQLServerProfiler监视到的数据，作一次整理：

回顾图：

点击查看大图

1.在第3行SQL:

BatchStarting,SPID54（第一个会话启动）,在索引PK__DealLock__3214EC274222D4EF取得一个排它锁，再处理等待状态，（因为在这个实例中我配置了WaitforDelay'00:

00:

05'）

2.在第6行SQL:

BatchStarting,SPID55（第二个会话启动）,在索引PK__DealLock__3214EC2745F365D3取得一个排它锁，再处理等待状态，（因为在这个实例中我配置了WaitforDelay'00:

00:

05'）

3.两个进程都各自取得一个排它锁（X）,几秒过去，它们就开始请求排它锁。

SPID54（第一个会话）,先对PK__DealLock__3214EC2745F365D3请求一个排它锁（X）,但PK__DealLock__3214EC2745F365D3当前已经给SPID55（第二个会话）取得。

SPID54要于等待。

同时，

SPID55（第二个会话）,开始对PK__DealLock__3214EC274222D4EF请求一个排它锁（X）,但PK__DealLock__3214EC274222D4EF当前已经给SPID54（第一个会话）取得。

SPID55要等待。

这里就出现了进程阻塞，从而发生死锁。

4.SQLServer检验到这两个进程（第一个&第二个会话）发生死锁，并对占用资源比较少的进程，列入牺牲品名单，将它终止（Kill）。

议决左右椭圆形进程节点显示，能够发觉已用日志最少的是左边的进程节点。

5.SPID54（第一个会话）被回滚（Rollback），SPID55（第二个会话）执行成功。

到这里咱们已算完成了，对死锁的监视和剖析。

（注：

是于其他死锁的解释，死锁模式，死锁防止&预防,等等，不是本文重点，我没有提出,网上太多这方面的文章）