SQL Server服务器端跟踪并定位导致死锁的SQL

《数据库锁机制》《模拟系统登录时卡死以简单了解数据库原理》《oracle数据表死锁的解决方法》《深入浅出sqlserver中的死锁》《怎么捕获和记录sqlserver中发生的死锁》《sqlserver并发事务：死锁跟踪（一）简单测试》《sqlserver并发事务：死锁跟踪（二）确定死锁锁定的资源》《sqlserver并发事务：死锁跟踪（三）6种跟踪死锁的方法总结》

说明

在多线程/多进程的程序中并发操作数据库，如果对数据库的运行原理理解的不深入，编写的代码没有考虑数据库运行原理的话，就很有可能导致死锁的发生，那么就需要赶紧解决问题，而我最近也遇到了一个sqlserver死锁的问题，所以趁此机会研究一下死锁的排查方法

sqlserver死锁时候报错的信息一般是：事务(进程id54)与另一个进程被死锁在锁资源上，并且已被选作死锁牺牲品。请重新运行该事务。

那么应该怎么排查？最好的方法当然是先定位到导致死锁的两条sql，然后再去相关的程序源码中找到相应的sql、检查对应的代码逻辑，分析并发情况下死锁的原因，然后破坏死锁产生的条件来解决死锁问题

上面的相关博客中有多种方法，这里先整理出一个简单、有效的方法：利用服务器端跟踪。其消耗小，在最繁忙的系统中也可使用。其他的方法会在日后慢慢研究和整理补充

参考《数据库锁机制》，创建数据库、创建表、模拟200万条记录

然后分别在sqlservermanagementstudio打开两个会话，分别执行下面的两条语句以模拟死锁

--会话一begintranselect*fromtestlock..test(holdlock);--holdlock意思是加共享锁，直到事务结束才释放updatetestlock..testsetname='joker';commit;--会话二begintranselect*fromtestlock..test(holdlock);updatetestlock..testsetname='xumeng';commit;

1.编写如下脚本，并执行

--定义参数declare@rcintdeclare@traceidintdeclare@maxfilesizebigintset@maxfilesize=5--初始化跟踪exec@rc=sp_trace_create@traceidoutput,0,n'e:dblogdeadlockdetect',@maxfilesize,null--此处的e:dblogdeadlockdetect是文件名（可自行修改），sql会自动在后面加上.trc的扩展名if(@rc!=0)gotoerror--设置跟踪事件declare@onbitset@on=1--下述语句中的148指的是locks:deadlockgraph事件(参见sys.trace_events),12指的是spid列(参见sys.trace_columns)execsp_trace_setevent@traceid,148,12,@onexecsp_trace_setevent@traceid,148,11,@onexecsp_trace_setevent@traceid,148,4,@onexecsp_trace_setevent@traceid,148,14,@onexecsp_trace_setevent@traceid,148,26,@onexecsp_trace_setevent@traceid,148,64,@onexecsp_trace_setevent@traceid,148,1,@on--启动跟踪execsp_trace_setstatus@traceid,1--记录下跟踪id，以备后面使用selecttraceid=@traceidgotofinisherror:selecterrorcode=@rcfinish:go

运行上述语句后，每当sqlserver中发生死锁事件，都会自动往文件e:dblogdeadlockdetect.trc中插入一条记录。

2.暂停和停止服务器端跟踪

--先执行这个sql，否则如果直接执行下面暂停的sql会报错：无法停止或修改默认跟踪。请使用sp_configure将其关闭execsp_configure'showadvancedoptions',1;goreconfigure;goexecsp_configure'defaulttraceenabled',0;goreconfigure;go--如果要暂停上面的服务器端跟踪，可运行下面的语句：--注意：要想停止服务器跟踪，必须先执行这条暂停的sql命令，不能直接执行下面的停止的sql命令execsp_trace_setstatus1,0;--第一个参数表示traceid,即步骤1中的输出参数。第二个参数表示将状态改为0，即暂停--先执行暂停的sql，才能接着执行停止跟踪的sql，如果直接执行停止的sql会报错的！--如果要停止上面的服务器端跟踪，可运行下面的语句：execsp_trace_setstatus1,2;--第一个参数表示traceid,即步骤1中的输出参数。第二个参数表示将状态改为2，即停止

3.查看跟踪文件内容

对于上面生成的跟踪文件(e:dblogdeadlockdetect.trc),可通过两种方法查看：

1).执行t-sql命令

select*fromfn_trace_gettable('e:dblogdeadlockdetect.trc',1)

结果中的textdata列即以xml的形式返回死锁的详细信息。将textdata的信息复制输出到文本文件中，然后通过搜索sql的关键词，比如select、update、where、from、set等来找到信息中的sql，就可以找到导致死锁的两条sql语句。同时在该信息中还有死锁相关的进程id等信息！

2).在sqlserverprofiler中打开。

依次进入profiler（以管理员身份打开）->文件->打开跟踪文件->选择e:dblogdeadlockdetect.trc，就可以看到以图形形式展现的死锁信息了。

截图如下，可以看到死锁的的关键字：deadlockgraph，先选中死锁的那条记录，然后到下面的图形界面将鼠标放在图形上，就会展示对应的sql语句