SQLServer死锁案例分析

今天有个新应用做压力测试，频繁报数据库死锁，使用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设为主键后，死锁解决。

、

作者：wwh