今天有个新应用做压力测试，频繁报数据库死锁，使用dbcc traceon(3604,1204) 命令打开死锁事件日志，在errorlog中发现以下信息：
Deadlock encountered .... Printing deadlock information2006-11-30 14:08:46.15 spid4 2006-11-30 14:08:46.15 spid4 Wait-for graph2006-11-30 14:08:46.15 spid4 2006-11-30 14:08:46.15 spid4 Node:12006-11-30 14:08:46.15 spid4 KEY: 8:1977058079:2 (a200c69811cb) CleanCnt:1 Mode: X Flags: 0x02006-11-30 14:08:46.15 spid4 Grant List 2::2006-11-30 14:08:46.15 spid4 Owner:0x7bb89c40 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:88 ECID:02006-11-30 14:08:46.15 spid4 SPID: 88 ECID: 0 Statement Type: UPDATE Line #: 12006-11-30 14:08:46.15 spid4 Input Buf: Language Event: UPDATE JMS_MESSAGES SET TXID= 574 , TXOP= N'D' WHERE MESSAGEID= 10000173 AND DESTINATION= N'QUEUE.d3PlatformMdb' 2006-11-30 14:08:46.15 spid4 Requested By: 2006-11-30 14:08:46.15 spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:62 ECID:0 Ec:(0x45105508) Value:0x7a072c20 Cost:(0/464)2006-11-30 14:08:46.15 spid4 2006-11-30 14:08:46.15 spid4 Node:22006-11-30 14:08:46.15 spid4 RID: 8:1:91:56 CleanCnt:1 Mode: X Flags: 0x22006-11-30 14:08:46.15 spid4 Grant List 0::2006-11-30 14:08:46.15 spid4 Owner:0x29f497e0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:62 ECID:02006-11-30 14:08:46.15 spid4 SPID: 62 ECID: 0 Statement Type: DELETE Line #: 12006-11-30 14:08:46.15 spid4 Input Buf: Language Event: DELETE FROM JMS_MESSAGES WHERE TXID= 571 AND TXOP= N'D' 2006-11-30 14:08:46.15 spid4 Requested By: 2006-11-30 14:08:46.15 spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:88 ECID:0 Ec:(0x20D3B508) Value:0x7b7292a0 Cost:(0/184)2006-11-30 14:08:46.15 spid4 Victim Resource Owner:2006-11-30 14:08:46.15 spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:88 ECID:0 Ec:(0x20D3B508) Value:0x7b7292a0 Cost:(0/184)2006-11-30 14:08:46.78 spid4
原来是DELETE FROM JMS_MESSAGES WHERE TXID= 571 AND TXOP= N'D'跟UPDATE JMS_MESSAGES SET TXID= 574 , TXOP= N'D' WHERE MESSAGEID= 10000173 AND DESTINATION= N'QUEUE.d3PlatformMdb' 。
经查看事件探查器的事件，两个进程执行的sql分别是
62INSERT INTO JMS_TRANSACTIONS (TXID) values( 571 )UPDATE JMS_MESSAGES SET TXID= 571 , TXOP= N'D' WHERE MESSAGEID= 10000178 AND DESTINATION= N'QUEUE.d3PlatformMdb' COMMIT
DELETE FROM JMS_MESSAGES WHERE TXID= 571 AND TXOP= N'D' DELETE FROM JMS_TRANSACTIONS WHERE TXID = 571 COMMIT
88INSERT INTO JMS_TRANSACTIONS (TXID) values( 574 )UPDATE JMS_MESSAGES SET TXID= 574 , TXOP= N'D' WHERE MESSAGEID= 10000173 AND DESTINATION= N'QUEUE.d3PlatformMdb' COMMIT
DELETE FROM JMS_MESSAGES WHERE TXID= 574 AND TXOP= N'A' DELETE FROM JMS_TRANSACTIONS WHERE TXID = 574 COMMIT
一眼看上去，这个两个进程不至于产生死锁呀，都不是操作的同一行记录。后来经仔细分析，原来在JMS_MESSAGES 的MESSAGEID上没有索引，在执行UPDATE JMS_MESSAGES SET TXID= 574 , TXOP= N'D' WHERE MESSAGEID= 10000173 AND DESTINATION= N'QUEUE.d3PlatformMdb' 时，使用了DESTINATION上的索引，导致sqlserver锁定了多行记录，而DELETE FROM JMS_MESSAGES WHERE TXID= 574 AND TXOP= N'A' 去想删除这条记录，因此造成死锁。实际上MESSAGEID是唯一的，把MESSAGEID设为主键后，死锁解决。
转载于:https://www.cnblogs.com/seawwh/archive/2011/11/01/2231248.html
相关资源：Sql Server 死锁的监控分析解决思路