今天有个新应用做压力测试，频繁报数据库死锁，使用dbcc traceon(3604,1204) 命令打开死锁事件日志，在errorlog中发现以下信息：
Deadlock encountered .... Printing deadlock information
2006-11-30 14:08:46.15 spid4
2006-11-30 14:08:46.15 spid4 Wait-for graph
2006-11-30 14:08:46.15 spid4
2006-11-30 14:08:46.15 spid4 Node:1
2006-11-30 14:08:46.15 spid4 KEY: 8:1977058079:2 (a200c69811cb) CleanCnt:1 Mode: X Flags: 0x0
2006-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:0
2006-11-30 14:08:46.15 spid4 SPID: 88 ECID: 0 Statement Type: UPDATE Line #: 1
2006-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:2
2006-11-30 14:08:46.15 spid4 RID: 8:1:91:56 CleanCnt:1 Mode: X Flags: 0x2
2006-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:0
2006-11-30 14:08:46.15 spid4 SPID: 62 ECID: 0 Statement Type: DELETE Line #: 1
2006-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分别是
62
INSERT 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
88
INSERT 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设为主键后，死锁解决。