死锁查看处理(三)

查看sql server日志 ，管理-》sql server 日志

1、打开跟踪标记
DBCC TRACEON (3605,1204,1222,-1)
参数：3605 将DBCC的结果输出到错误日志。
1204 返回参与死锁的锁的资源和类型，以及受影响的当前命令。
1222 返回参与死锁的锁的资源和类型，以及使用了不符合任何 XSD 架构的 XML 格式的受影响的当前命令(比1204更进一步,SQL 2005及以上可用)。
-1 以全局方式打开指定的跟踪标记。以上跟踪标志作用域都是全局，即在SQL Server运行过程中，会一直发挥作用，直到SQL Server重启。

2、打开标记后等待出现死锁的清喉的时候可以查看当时的死锁导致的原因

11/13/2015 16:22:46,spid20s,未知,waiter id=process3899390 mode=S requestType=wait
11/13/2015 16:22:46,spid20s,未知,waiter-list
11/13/2015 16:22:46,spid20s,未知,owner id=process3d48718 mode=X
11/13/2015 16:22:46,spid20s,未知,owner-list
11/13/2015 16:22:46,spid20s,未知,keylock hobtid=72058844238774272 dbid=7 objectname=CZ.dbo.jh_plan indexname=IX_jh_plan id=lock10068a40 mode=X associatedObjectId=72058844238774272
11/13/2015 16:22:46,spid20s,未知,waiter id=process3d48718 mode=U requestType=wait
11/13/2015 16:22:46,spid20s,未知,waiter-list
11/13/2015 16:22:46,spid20s,未知,owner id=process3899390 mode=X
11/13/2015 16:22:46,spid20s,未知,owner-list
11/13/2015 16:22:46,spid20s,未知,keylock hobtid=72057594893893632 dbid=7 objectname=CZ.dbo.dd_plan indexname=PK_dd_plan id=lock3b236f00 mode=X associatedObjectId=72057594893893632
11/13/2015 16:22:46,spid20s,未知,resource-list

11/13/2015 16:22:46,spid20s,未知,select * from jh_plan where line_code = '19' and run_date >= '2015-11-14 00:00:00' and  run_date <= '2015-11-14 23:59:59'
11/13/2015 16:22:46,spid20s,未知,inputbuf
11/13/2015 16:22:46,spid20s,未知,select * from jh_plan where line_code = '19' and run_date >= '2015-11-14 00:00:00' and  run_date <= '2015-11-14 23:59:59'
11/13/2015 16:22:46,spid20s,未知,frame procname=adhoc line=1 stmtstart=2 sqlhandle=0x020000006b35f006dab8657da040de97a3886fa58262e03b
11/13/2015 16:22:46,spid20s,未知,SELECT * FROM [jh_plan] WHERE [line_code]=@1 AND [run_date]>=@2 AND [run_date]<=@3
11/13/2015 16:22:46,spid20s,未知,frame procname=adhoc line=1 stmtstart=104 sqlhandle=0x0200000086f066245bc062f34196946c3d0c6ff03b8b3260
11/13/2015 16:22:46,spid20s,未知,executionStack
11/13/2015 16:22:46,spid20s,未知,process id=process3899390 taskpriority=0 logused=431428 waitresource=KEY: 7:72058844238774272 (c24d4f496ac0) waittime=3442 ownerId=420541355 transactionname=implicit_transaction lasttranstarted=2015-11-13T16:22:26.120 XDES=0x12a64820 lockMode=S schedulerid=2 kpid=12792 status=suspended spid=227 sbid=0 ecid=0 priority=0 trancount=1 lastbatchstarted=2015-11-13T16:22:43.280 lastbatchcompleted=2015-11-13T16:22:43.280 clientapp=应用程序名 hostname=F****D hostpid=3900 loginname=sa isolationlevel=read committed (2) xactid=420541355 currentdb=7 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128570

11/13/2015 16:22:46,spid20s,未知,delete from dd_plan where run_date<='2015-11-14 23:59:59' and run_date>='2015-11-14 00:00:00' and line_code='8'
11/13/2015 16:22:46,spid20s,未知,inputbuf
11/13/2015 16:22:46,spid20s,未知,delete from dd_plan where run_date<='2015-11-14 23:59:59' and run_date>='2015-11-14 00:00:00' and line_code='8'
11/13/2015 16:22:46,spid20s,未知,frame procname=adhoc line=1 sqlhandle=0x020000007eb2a0019213c2cc953b61bb064dce8017feea0f
11/13/2015 16:22:46,spid20s,未知,DELETE [dd_plan]  WHERE [run_date]<=@1 AND [run_date]>=@2 AND [line_code]=@3
11/13/2015 16:22:46,spid20s,未知,frame procname=adhoc line=1 stmtstart=104 sqlhandle=0x020000009bc3043a138da3f7377431d783967cf992cba41f
11/13/2015 16:22:46,spid20s,未知,executionStack
11/13/2015 16:22:46,spid20s,未知,process id=process3d48718 taskpriority=0 logused=220044 waitresource=KEY: 7:72057594893893632 (ba3920a8f3cc) waittime=598 ownerId=420544213 transactionname=implicit_transaction lasttranstarted=2015-11-13T16:22:29.797 XDES=0x25801590 lockMode=U schedulerid=7 kpid=11688 status=suspended spid=166 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2015-11-13T16:22:46.113 lastbatchcompleted=2015-11-13T16:22:46.113 clientapp=应用程序名 hostname=P***0 hostpid=2724 loginname=sa isolationlevel=read committed (2) xactid=420544213 currentdb=7 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128058
11/13/2015 16:22:46,spid20s,未知,process-list
11/13/2015 16:22:46,spid20s,未知,deadlock victim=process3d48718
11/13/2015 16:22:46,spid20s,未知,deadlock-list

针对以上死锁问题进行分析
process-list 有两个程序，ID 分别为 process3d48718 (A) 与 process3899390 (B)

当前执行语句
进程A delete from dd_plan where run_date<=’2015-11-14 23:59:59’ and run_date>=’2015-11-14 00:00:00’ and line_code=’8’ 正在执行删除操作。
进程B select * from jh_plan where line_code = ‘19’ and run_date >= ‘2015-11-14 00:00:00’ and run_date <= ‘2015-11-14 23:59:59’ 正在执行查询操作。

资源等待
索引PK_dd_plan 进程B(process3899390) 已经申请到了 拥有模式X，进程A(process3d48718) 正在等待此索引的使用
索引IX_jh_plan 进程A(process3d48718） 已经申请到了 拥有模式X ，而进场B(process3899390)正在等待使用此索引导致的死锁。

避免此种索引的方式是，若是需要在一个事务中同时操作多个表的时候，需要确定好每个表的使用循序，例如 表一dd_plan ，表二jh_plan 这样就不会出现死锁的现象。