January 20, 2014 at 11:15 pm
HI All,
In one of my server i got deadlock , But i have little-bit confusion on this, can any one explain about this and attached deadlock txt file.
Thanks,
Ramana.
---------------------------------------------------
ALTER INDEX [UNIQUE_EMPLOYEE_EMAIL_NAME] ON [dbo].[EMPLOYEE] REBUILD PARTITION = ALL WITH
( PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF )
GO
select v.voip_phone_extension Voip_Phone_Extension,e.alias_name Alias_Name,e.emp_id Emp_ID,e.network_id Network_ID,s.site_alias Site_Alias
From Employee E, Site S, Voice_Configuration V, Employee_Detail D
Where V.Emp_Id = E.Emp_id
Why these two statements are blocking?
----------------------------------------------------------
2014-01-18 02:08:32.610 spid9s Deadlock encountered .... Printing deadlock information
2014-01-18 02:08:32.610 spid9s Wait-for graph
2014-01-18 02:08:32.610 spid9s NULL
2014-01-18 02:08:32.610 spid9s Node:1
2014-01-18 02:08:32.620 spid9s OBJECT: 8:1646628909:16 CleanCnt:2 Mode:Sch-M Flags: 0x1
2014-01-18 02:08:32.620 spid9s Grant List 1:
2014-01-18 02:08:32.630 spid9s Owner:0x000000000B4F3DC0 Mode: Sch-M Flg:0x40 Ref:0 Life:20000000 SPID:238 ECID:0 XactLockInfo: 0x000000078F7559B0
2014-01-18 02:08:32.630 spid9s SPID: 238 ECID: 0 Statement Type: ALTER INDEX Line #: 1
2014-01-18 02:08:32.640 spid9s Input Buf: Language Event: ALTER INDEX [UNIQUE_EMPLOYEE_EMAIL_NAME] ON [dbo].[EMPLOYEE] REBUILD PARTITION = ALL WITH ( PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF )
2014-01-18 02:08:32.640 spid9s Requested by:
2014-01-18 02:08:32.640 spid9s ResType:LockOwner Stype:'OR'Xdes:0x00000001E0D8B780 Mode: Sch-S SPID:195 BatchID:0 ECID:0 TaskProxy:(0x00000005380E2510) Value:0x7101080 Cost:(0/0)
2014-01-18 02:08:32.640 spid9s NULL
2014-01-18 02:08:32.640 spid9s Node:2
2014-01-18 02:08:32.640 spid9s OBJECT: 8:1646628909:22 CleanCnt:2 Mode:Sch-S Flags: 0x1
2014-01-18 02:08:32.640 spid9s Grant List 2:
2014-01-18 02:08:32.640 spid9s Owner:0x00000000075AA5C0 Mode: Sch-S Flg:0x40 Ref:1 Life:00000000 SPID:195 ECID:0 XactLockInfo: 0x00000000D46AACE0
2014-01-18 02:08:32.640 spid9s SPID: 195 ECID: 0 Statement Type: SELECT Line #: 1
2014-01-18 02:08:32.640 spid9s Input Buf: Language Event: select v.voip_phone_extension Voip_Phone_Extension,e.alias_name Alias_Name,e.emp_id Emp_ID,e.network_id Network_ID,s.site_alias Site_Alias From Employee E, Site S, Voice_Configuration V, Employee_Detail D Where V.Emp_Id = E.Em
2014-01-18 02:08:32.640 spid9s Requested by:
2014-01-18 02:08:32.640 spid9s ResType:LockOwner Stype:'OR'Xdes:0x000000078F755970 Mode: Sch-M SPID:238 BatchID:0 ECID:0 TaskProxy:(0x0000000097074538) Value:0xaae1200 Cost:(0/0)
2014-01-18 02:08:32.680 spid9s NULL
2014-01-18 02:08:32.680 spid9s Victim Resource Owner:
2014-01-18 02:08:32.680 spid9s ResType:LockOwner Stype:'OR'Xdes:0x00000001E0D8B780 Mode: Sch-S SPID:195 BatchID:0 ECID:0 TaskProxy:(0x00000005380E2510) Value:0x7101080 Cost:(0/0)
2014-01-18 02:08:37.680 spid9s Deadlock encountered .... Printing deadlock information
2014-01-18 02:08:37.680 spid9s Wait-for graph
2014-01-18 02:08:37.680 spid9s NULL
2014-01-18 02:08:37.680 spid9s Node:1
2014-01-18 02:08:37.680 spid9s OBJECT: 8:1646628909:16 CleanCnt:4 Mode:Sch-M Flags: 0x1
2014-01-18 02:08:37.680 spid9s Wait List:
2014-01-18 02:08:37.680 spid9s Owner:0x000000072AC584C0 Mode: Sch-S Flg:0x42 Ref:1 Life:00000000 SPID:275 ECID:0 XactLockInfo: 0x00000000FE8365D0
2014-01-18 02:08:37.680 spid9s SPID: 275 ECID: 0 Statement Type: SELECT Line #: 1
2014-01-18 02:08:37.680 spid9s Input Buf: Language Event: select v.voip_phone_extension Voip_Phone_Extension,e.alias_name Alias_Name,e.emp_id Emp_ID,e.network_id Network_ID,s.site_alias Site_Alias From Employee E, Site S, Voice_Configuration V, Employee_Detail D Where V.Emp_Id = E.Em
2014-01-18 02:08:37.680 spid9s Requested by:
2014-01-18 02:08:37.680 spid9s ResType:LockOwner Stype:'OR'Xdes:0x000000032ADCD1E0 Mode: Sch-S SPID:109 BatchID:0 ECID:0 TaskProxy:(0x00000002F3496510) Value:0x5c25ac0 Cost:(0/0)
2014-01-18 02:08:37.680 spid9s NULL
2014-01-18 02:08:37.680 spid9s Node:2
2014-01-18 02:08:37.680 spid9s OBJECT: 8:1646628909:16 CleanCnt:4 Mode:Sch-M Flags: 0x1
2014-01-18 02:08:37.680 spid9s Grant List 1:
2014-01-18 02:08:37.680 spid9s Owner:0x000000000B4F3DC0 Mode: Sch-M Flg:0x40 Ref:0 Life:20000000 SPID:238 ECID:0 XactLockInfo: 0x000000078F7559B0
2014-01-18 02:08:37.680 spid9s SPID: 238 ECID: 0 Statement Type: ALTER INDEX Line #: 1
2014-01-18 02:08:37.680 spid9s Input Buf: Language Event: ALTER INDEX [UNIQUE_EMPNO_CODE_STATUS] ON [dbo].[EMPLOYEE] REBUILD PARTITION = ALL WITH ( PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF )
2014-01-18 02:08:37.680 spid9s Requested by:
2014-01-18 02:08:37.680 spid9s ResType:LockOwner Stype:'OR'Xdes:0x00000000FE836590 Mode: Sch-S SPID:275 BatchID:0 ECID:0 TaskProxy:(0x00000004DACB6538) Value:0x2ac584c0 Cost:(0/0)
2014-01-18 02:08:37.680 spid9s NULL
2014-01-18 02:08:37.680 spid9s Node:3
2014-01-18 02:08:37.680 spid9s OBJECT: 8:1646628909:19 CleanCnt:2 Mode:Sch-S Flags: 0x1
2014-01-18 02:08:37.680 spid9s Grant List 2:
2014-01-18 02:08:37.680 spid9s Owner:0x0000000005D06BC0 Mode: Sch-S Flg:0x40 Ref:1 Life:00000000 SPID:109 ECID:0 XactLockInfo: 0x000000032ADCCCE0
2014-01-18 02:08:37.680 spid9s SPID: 109 ECID: 0 Statement Type: SELECT Line #: 1
2014-01-18 02:08:37.680 spid9s Input Buf: Language Event: select v.voip_phone_extension Voip_Phone_Extension,e.alias_name Alias_Name,e.emp_id Emp_ID,e.network_id Network_ID,s.site_alias Site_Alias From Employee E, Site S, Voice_Configuration V, Employee_Detail D Where V.Emp_Id = E.Em
2014-01-18 02:08:37.680 spid9s Requested by:
2014-01-18 02:08:37.680 spid9s ResType:LockOwner Stype:'OR'Xdes:0x000000078F755970 Mode: Sch-M SPID:238 BatchID:0 ECID:0 TaskProxy:(0x0000000097074538) Value:0xb7ff640 Cost:(0/0)
2014-01-18 02:08:37.680 spid9s NULL
2014-01-18 02:08:37.680 spid9s Victim Resource Owner:
2014-01-18 02:08:37.680 spid9s ResType:LockOwner Stype:'OR'Xdes:0x000000032ADCD1E0 Mode: Sch-S SPID:109 BatchID:0 ECID:0 TaskProxy:(0x00000002F3496510) Value:0x5c25ac0 Cost:(0/0)
2014-01-18 02:08:40.180 spid9s Deadlock encountered .... Printing deadlock information
2014-01-18 02:08:40.180 spid9s Wait-for graph
2014-01-18 02:08:40.180 spid9s NULL
2014-01-18 02:08:40.180 spid9s Node:1
2014-01-18 02:08:40.180 spid9s OBJECT: 8:1646628909:18 CleanCnt:3 Mode:Sch-M Flags: 0x1
2014-01-18 02:08:40.180 spid9s Grant List 1:
2014-01-18 02:08:40.180 spid9s Owner:0x000000000B923E00 Mode: Sch-M Flg:0x40 Ref:0 Life:20000000 SPID:238 ECID:0 XactLockInfo: 0x000000078F7559B0
2014-01-18 02:08:40.180 spid9s Requested by:
2014-01-18 02:08:40.180 spid9s ResType:LockOwner Stype:'OR'Xdes:0x000000015E2BCCA0 Mode: Sch-S SPID:184 BatchID:0 ECID:0 TaskProxy:(0x00000000C152A510) Value:0x643e180 Cost:(0/0)
2014-01-18 02:08:40.180 spid9s NULL
2014-01-18 02:08:40.180 spid9s Node:2
2014-01-18 02:08:40.180 spid9s OBJECT: 8:1646628909:20 CleanCnt:2 Mode:Sch-S Flags: 0x1
2014-01-18 02:08:40.180 spid9s Grant List 2:
2014-01-18 02:08:40.180 spid9s Owner:0x0000000007433200 Mode: Sch-S Flg:0x40 Ref:1 Life:00000000 SPID:184 ECID:0 XactLockInfo: 0x0000000081B22CE0
2014-01-18 02:08:40.180 spid9s SPID: 184 ECID: 0 Statement Type: SELECT Line #: 1
2014-01-18 02:08:40.180 spid9s Input Buf: Language Event: Select V.Voip_Phone_Extension Voip_Phone_Extension,S.Site_Alias Site_Alias From Employee E, Site S, Voice_Configuration V, Employee_Detail D Where V.Emp_Id = E.Emp_Id And V.Emp_Id = D.Emp_Id And D.Location_ID = S.Site_ID And V
2014-01-18 02:08:40.180 spid9s Requested by:
2014-01-18 02:08:40.180 spid9s ResType:LockOwner Stype:'OR'Xdes:0x000000078F755970 Mode: Sch-M SPID:238 BatchID:0 ECID:0 TaskProxy:(0x0000000097074538) Value:0xa5d3a80 Cost:(0/0)
2014-01-18 02:08:40.180 spid9s NULL
2014-01-18 02:08:40.180 spid9s Victim Resource Owner:
2014-01-18 02:08:40.180 spid9s ResType:LockOwner Stype:'OR'Xdes:0x000000015E2BCCA0 Mode: Sch-S SPID:184 BatchID:0 ECID:0 TaskProxy:(0x00000000C152A510) Value:0x643e180 Cost:(0/0)
January 20, 2014 at 11:56 pm
Without analyzing deadlock info more I have a question.
Do you create cartesian join intentionally? There is no join to site table.
January 21, 2014 at 12:04 am
Ville-Pekka Vahteala (1/20/2014)
Without analyzing deadlock info more I have a question.Do you create cartesian join intentionally? There is no join to site table.
In trace log ; Select query truncated but internally it have fully query
January 21, 2014 at 12:13 am
Yuk, traceflag 1204. That traceflag doesn't give enough information and makes debugging deadlocks harder than it should be.
Can you turn traceflag 1204 off, turn 1222 on instead and, if the deadlock reoccurs, post the more detailed deadlock graph here?
Also, shameless self-promotion: https://www.simple-talk.com/sql/performance/sql-server-deadlocks-by-example/
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
January 21, 2014 at 3:44 am
Since you're posting this in the 2008 forum, I'm going to assume you're on a 2008 or better server. You can look at they system_health extended event session. It's running automatically no your system and it has a much better deadlock graph available. Here's an article from Microsoft on how to do this. Here's one from SQL Server Central[/url].
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply