July 7, 2011 at 9:15 am
Hi could somebody help decipher the following deadlock info. I've set traceflag (1204) to capture deadlock info to the error log (assuming this is less intrusive than run profiler deadlock graph). Am struggling to grips with the info recorded.
07/07/2011 11:44:12spid5sDeadlock encountered .... Printing deadlock information
07/07/2011 11:44:12spid5sWait-for graph
07/07/2011 11:44:12spid5sNULL
07/07/2011 11:44:12spid5sNode:1
07/07/2011 11:44:12spid5sKEY: 12:72057598393384960 (020068e8b274) CleanCnt:5 Mode:RangeX-X Flags: 0x1
07/07/2011 11:44:12spid5s Grant List 1:
07/07/2011 11:44:12spid5s Owner:0x00000000802B6F40 Mode: RangeX-X Flg:0x40 Ref:0 Life:02000000 SPID:75 ECID:0 XactLockInfo: 0x00000000913A99B0
07/07/2011 11:44:12spid5sRequested by:
07/07/2011 11:44:12spid5s ResType:LockOwner Stype:'OR'Xdes:0x000000008192D970 Mode: RangeS-S SPID:68 BatchID:0 ECID:0 TaskProxy:(0x0000000188C56538) Value:0x80348340 Cost:(0/0)
07/07/2011 11:44:12spid5sNULL
07/07/2011 11:44:12spid5sNode:2
07/07/2011 11:44:12spid5sKEY: 12:72057598393384960 (020068e8b274) CleanCnt:5 Mode:RangeX-X Flags: 0x1
07/07/2011 11:44:12spid5s Wait List:
07/07/2011 11:44:12spid5s Owner:0x0000000080348340 Mode: RangeS-S Flg:0x42 Ref:1 Life:02000000 SPID:68 ECID:0 XactLockInfo: 0x000000008192D9B0
07/07/2011 11:44:12spid5sRequested by:
07/07/2011 11:44:12spid5s ResType:LockOwner Stype:'OR'Xdes:0x0000000081D45970 Mode: RangeS-S SPID:77 BatchID:0 ECID:0 TaskProxy:(0x00000000A6604538) Value:0x51c9cb00 Cost:(0/0)
07/07/2011 11:44:12spid5sNULL
07/07/2011 11:44:12spid5sNode:3
07/07/2011 11:44:12spid5sKEY: 12:72057598393384960 (020068e8b274) CleanCnt:5 Mode:RangeX-X Flags: 0x1
07/07/2011 11:44:12spid5s Wait List:
07/07/2011 11:44:12spid5s Owner:0x0000000080348340 Mode: RangeS-S Flg:0x42 Ref:1 Life:02000000 SPID:68 ECID:0 XactLockInfo: 0x000000008192D9B0
07/07/2011 11:44:12spid5sRequested by:
07/07/2011 11:44:12spid5s ResType:LockOwner Stype:'OR'Xdes:0x0000000118643970 Mode: RangeS-S SPID:72 BatchID:0 ECID:0 TaskProxy:(0x0000000170FB4538) Value:0xec886940 Cost:(0/0)
07/07/2011 11:44:12spid5sNULL
07/07/2011 11:44:12spid5sNode:4
07/07/2011 11:44:12spid5sKEY: 12:72057598393384960 (020068e8b274) CleanCnt:5 Mode:RangeX-X Flags: 0x1
07/07/2011 11:44:12spid5s Wait List:
07/07/2011 11:44:12spid5s Owner:0x0000000080348340 Mode: RangeS-S Flg:0x42 Ref:1 Life:02000000 SPID:68 ECID:0 XactLockInfo: 0x000000008192D9B0
07/07/2011 11:44:12spid5sRequested by:
07/07/2011 11:44:12spid5s ResType:LockOwner Stype:'OR'Xdes:0x00000001D33583B0 Mode: RangeS-S SPID:78 BatchID:0 ECID:0 TaskProxy:(0x00000001DEFBC538) Value:0xe3997540 Cost:(0/0)
07/07/2011 11:44:12spid5sNULL
07/07/2011 11:44:12spid5sNode:5
07/07/2011 11:44:12spid5sKEY: 12:72057598393384960 (010086470766) CleanCnt:8 Mode:RangeS-U Flags: 0x1
07/07/2011 11:44:12spid5s Grant List 0:
07/07/2011 11:44:12spid5s Grant List 1:
07/07/2011 11:44:12spid5s Grant List 2:
07/07/2011 11:44:12spid5s Grant List 3:
07/07/2011 11:44:12spid5s Owner:0x0000000080344580 Mode: RangeS-S Flg:0x40 Ref:0 Life:02000000 SPID:68 ECID:0 XactLockInfo: 0x000000008192D9B0
07/07/2011 11:44:12spid5sRequested by:
07/07/2011 11:44:12spid5s ResType:LockOwner Stype:'OR'Xdes:0x00000000913A9970 Mode: X SPID:75 BatchID:0 ECID:0 TaskProxy:(0x00000000A4F9E538) Value:0x802ac340 Cost:(0/28860)
07/07/2011 11:44:12spid5sNULL
07/07/2011 11:44:12spid5sVictim Resource Owner:
07/07/2011 11:44:12spid5s ResType:LockOwner Stype:'OR'Xdes:0x000000008192D970 Mode: RangeS-S SPID:68 BatchID:0 ECID:0 TaskProxy:(0x0000000188C56538) Value:0x80348340 Cost:(0/0)
July 7, 2011 at 9:58 am
Rather use traceflag 1222 on SQL 2005 and above. Much better output.
There's no information in that deadlock graph as to the statements involved. It's going to be near-impossible to tell much. I can get you the table involved, but that's about all.
If it's reproducible, please change which traceflag you're using and post the graph produced by traceflag 1222.
oh, btw, something appears to be using serializable isolation level. Is that an intentional decision?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 7, 2011 at 2:30 pm
Gail, thanks for the reply.
I'll change the traceflag to 1222 as suggested. The deadlock frequency has gone from around 1 a week, to about 1 a day - depending on usage, so im pretty sure traceflag 1222 will have something intresting to tell tomorrow. I'll repost more usefull information when it presents itself.
I know at least one sp involved in the chain (via an email alert) and it does indeed set the isolation level to serializable. Out of interest, which part of the deadlock graph indicates it's usage? And more over, does setting the isolation level to be more restrictive not help prevent deadlocking (this is where there's gap in my understanding of islolation levels ) ?
Thanks again,
Nick
July 7, 2011 at 2:36 pm
The range locks were the hint. That means serialisable or some foreign key-related stuff.
Serialisable's a nasty one. Extensive locks held for the duration of the transaction. Without care, it makes deadlocks more likely and reduces concurrency. It's one of those things you need to be certain that you need before you use.
p.s. You've also got some parallelism happening there. Might be worth checking and seeing if queries can be optimised. Which queries, well, that is the current question.... 😀
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply