Update Stored proc Deadlock in SQL Server_问答_开发者

Please help me analyze the deadlock, where I see these two update stored procs deadlocking each other. I cannot figure out what they are locking each other on, and what would resolve the deadlock issue:

2011-05-19 14:00:15.610 spid23s      Deadlock encountered .... Printing deadlock information
2011-05-19 14:00:15.610 spid23s      Wait-for graph
2011-05-19 14:00:15.610 spid23s      NULL
2011-05-19 14:00:15.610 spid23s      Node:1
2011-05-19 14:00:15.610 spid23s      PAGE: 28:1:17381               CleanCnt:3 Mode:U Flags: 0x3
2011-05-19 14:00:15.610 spid23s       Grant List 2:
2011-05-19 14:00:15.610 spid23s         Owner:0x000000013A3EE1C0 Mode: U        Flg:0x40 Ref:0 Life:00000001 SPID:238 ECID:0 XactLockInfo: 0x00000002B04199B0
2011-05-19 14:00:15.610 spid23s         SPID: 238 ECID: 0 Statement Type: UPDATE Line #: 18
2011-05-19 14:00:15.610 spid23s         Input Buf: RPC Event: Proc [Database Id = 28 Object Id = 1531152500]
2011-05-19 14:00:15.610 spid23s      Requested by: 
2011-05-19 14:00:15.610 spid23s        ResType:LockOwner Stype:'OR'Xdes:0x000000039F83AE90 Mode: U SPID:215 BatchID:0 ECID:0 TaskProxy:(0x00000002A9A8E538) Value:0xd45ccfc0 Cost:(0/0)
2011-05-19 14:00:15.610 spid23s      NULL
2011-05-19 14:00:15.610 spid23s      Node:2
2011-05-19 14:00:15.610 spid23s      PAGE: 28:1:26081               CleanCnt:2 Mode:U Flags: 0x3
2011-05-19 14:00:15.610 spid23s       Grant List 3:
2011-05-19 14:00:15.610 spid23s         Owner:0x000000043091B9C0 Mode: U        Flg:0x40 Ref:0 Life:00000001 SPID:284 ECID:0 XactLockInfo: 0x000000051D4E1D30
2011-05-19 14:00:15.610 spid23s         SPID: 284 ECID: 0 Statement Type: UPDATE Line #: 9
2011-05-19 14:00:15.610 spid23s         Input Buf: Language Event: up_BulkMailSchedule_ResetStuckItems
2011-05-19 14:00:15.610 spid23s      Requested by: 
2011-05-19 14:00:15.610 spid23s        ResType:LockOwner Stype:'OR'Xdes:0x00000002B0419970 Mode: U SPID:238 BatchID:0 ECID:0 TaskProxy:(0x0000000282C9C538) Value:0xd5770500 Cost:(0/0)
2011-05-19 14:00:15.610 spid23s      NULL
2011-05-19 14:00:15.610 spid23s      Node:3
2011-05-19 14:00:15.610 spid23s      PAGE: 28:1:17381               CleanCnt:3 Mode:U Flags: 0x3
2011-05-19 14:00:15.610 spid23s       Wait List:
2011-05-19 14:00:15.610 spid23s         Owner:0x00000004D45CCFC0 Mode: U        Flg:0x42 Ref:1 Life:00000001 SPID:215 ECID:0 XactLockInfo: 0x000000039F83AED0
2011-05-19 14:00:15.610 spid23s         SPID: 215 ECID: 0 Statement Type: UPDATE Line #: 18
2011-05-19 14:00:15.610 spid23s         Input Buf: RPC Event: Proc [Database Id = 28 Object Id = 1531152500]
2011-05-19 14:00:15.610 spid23s      Requested by: 
2011-05-19 14:00:15.610 spid23s        ResType:LockOwner Stype:'OR'Xdes:0x00000002F346DC60 Mode: U SPID:284 BatchID:0 ECID:1 TaskProxy:(0x00000002E1989360) Value:0xce7f3b00 Cost:(0/0)
2011-05-19 14:00:15.610 spid23s      NULL
2011-05-19 14:00:15.610 spid23s      Node:4
2011-05-19 14:00:15.610 spid23s      Port: 0x00000002E3150380  Xid Slot: 0, Wait Slot: -1, Task: 0x0000000005177948, (Coordinator), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
2011-05-19 14:00:15.610 spid23s       ResType:ExchangeId Stype:'AND' SPID:284 BatchID:0 ECID:0 TaskProxy:(0x00000002E188E510) Value:0x5177948 Cost:(0/10000)
2011-05-19 14:00:15.610 spid23s      NULL
2011-05-19 14:00:15.610 spid23s      Victim Resource Owner:
2011-05-19 14:00:15.610 spid23s       ResType:LockOwner Stype:'OR'Xdes:0x000000039F83AE90 Mode: U SPID:215 BatchID:0 ECID:0 TaskProxy:(0x00000002A9A8E538) Value:0xd45ccfc0 Cost:(0/0)
2011-05-19 14:00:15.610 spid34s      deadlock-list
2011-05-19 14:00:15.610 spid34s       deadlock victim=process4c51948
2011-05-19 14:00:15.610 spid34s        process-list
2011-05-19 14:00:15.610 spid34s         process id=process4c51948 taskpriority=0 logused=0 waitresource=PAGE: 28:1:17381 waittime=5023 ownerId=3636483745 transactionname=UPDATE lasttranstarted=2011-05-19T14:00:10.593 XDES=0x39f83ae90 lockMode=U schedulerid=5 kpid=10884 status=suspended spid=215 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2011-05-19T14:00:02.070 lastbatchcompleted=2011-05-19T14:00:02.070 clientapp=.Net SqlClient Data Provider hostname=xxxhostpid=3544 loginname=test\xxxisolationlevel=read uncommitted (1) xactid=3636483745 currentdb=28 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
2011-05-19 14:00:15.610 spid34s          executionStack
2011-05-19 14:00:15.610 spid34s           frame procname=mnAlert.dbo.up_BulkMailSchedule_Get_Items_To_Process line=18 stmtstart=1030 stmtend=2584 sqlhandle=0x03001c007488435bbfd1b600a69e00000100000000000000
2011-05-19 14:00:15.610 spid34s      UPDATE TOP (2048) dbo.BulkMailSchedule
2011-05-19 14:00:15.610 spid34s         SET
2011-05-19 14:00:15.610 spid34s             SelectID = @NewSelectID,
2011-05-19 14:00:15.610 spid34s             ProcessingStatusID = 2,
2011-05-19 14:00:15.610 spid34s             ProcessingStatusDate = GETDATE(),
2011-05-19 14:00:15.610 spid34s             ProcessingServer = @ProcessingServer,
2011-05-19 14:00:15.610 spid34s             LastAttemptDate = @CurrentDate,
2011-05-19 14:00:15.610 spid34s             NextAttemptDate = dbo.calcNextAttemptDate(@CurrentDate, Frequency, '02:00:00')
2011-05-19 14:00:15.610 spid34s         OUTPUT
2011-05-19 14:00:15.610 spid34s             inserted.MemberID,
2011-05-19 14:00:15.610 spid34s             inserted.GroupID AS CommunityID,
2011-05-19 14:00:15.610 spid34s             inserted.BulkMailTypeID,
2011-05-19 14:00:15.610 spid34s             inserted.Frequency,
2011-05-19 14:00:15.610 spid34s             inserted.LastSentDate,
2011-05-19 14:00:15.610 spid34s             inserted.LastAttemptDate,
2011-05-19 14:00:15.610 spid34s             inserted.NextAttemptDate,
2011-05-19 14:00:15.610 spid34s             inserted.SentCount,
2011-05-19 14:00:15.610 spid34s             inserted.FrequencyUpdateDate,
2011-05-19 14:00:15.610 spid34s         开发者_如何转开发    inserted.InsertDate,
2011-05-19 14:00:15.610 spid34s             inserted.SentMemberIDList
2011-05-19 14:00:15.610 spid34s         WHERE
2011-05-19 14:00:15.610 spid34s             SelectID is NULL AND 
2011-05-19 14:00:15.610 spid34s             NextAttemptDate <= GetDate() AND 
2011-05-19 14:00:15.610 spid34s             LastLogonDate > dateadd(dd,-@MaxDaysSinceLastLogin,GETDATE())
2011-05-19 14:00:15.610 spid34s         -- Return search prefs needed     
2011-05-19 14:00:15.610 spid34s          inputbuf
2011-05-19 14:00:15.610 spid34s      Proc [Database Id = 28 Object Id = 1531152500]    
2011-05-19 14:00:15.610 spid34s         process id=process5143b88 taskpriority=0 logused=0 waitresource=PAGE: 28:1:26081 waittime=3380 ownerId=3636479354 transactionname=UPDATE lasttranstarted=2011-05-19T14:00:03.183 XDES=0x2b0419970 lockMode=U schedulerid=11 kpid=2608 status=suspended spid=238 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2011-05-19T14:00:03.183 lastbatchcompleted=2011-05-19T14:00:03.183 clientapp=.Net SqlClient Data Provider hostname=xxxhostpid=380 loginname=test\xxxisolationlevel=read uncommitted (1) xactid=3636479354 currentdb=28 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
2011-05-19 14:00:15.610 spid34s          executionStack
2011-05-19 14:00:15.610 spid34s           frame procname=mnAlert.dbo.up_BulkMailSchedule_Get_Items_To_Process line=18 stmtstart=1030 stmtend=2584 sqlhandle=0x03001c007488435bbfd1b600a69e00000100000000000000

Looks like you don't have proper Indexes in place. Look at dbo.BulkMailSchedule table and see what indexes are available. Do you have any indexes on NextAttempdate or LastLogonDate and what is CI on this table? Also having functions like this [dbo.calcNextAttemptDate] may NOT scale well.

The only way you are going to solve problems like this is spending time to read more on deadlocks.

Refer articles from http://blogs.msdn.com/b/bartd/archive/tags/sql+deadlocks/

Steps to resolve the deadlock:

Identify the code involved in the deadlock (which you have done)

Identify a method the reproduce the deadlock on demand

Identify the isolation method required for each code set involved

Remove all calculated values in the updates and replace with a static value ( function calls etc)

Comment out all data inserts or updates from the code involved

Attempt to reproduce the deadlock

If the deadlocks are still occuring there is other code involved so start over

Enable the inserts or updates in one set of code

Attempt to reproduce the deadlock