Sample Header Ad - 728x90

Why my clustered index causing deadlock (Keylocks and Pagelocks)

1 vote
1 answer
1031 views
We are experiencing deadlock 2-3 times a day in SQL Server. Upon analysis, we have found that the deadlock is caused by a combination of keylock and pagelock occurring simultaneously. We are actually using EF to fetch data and the query involves more than 6 foreign key relations. But on some days, the system hangs more than 6 times a day due to deadlocks. We have one clustered index column in our main table (Primary key field) I shared the deadlock graph for your reference unknown unknown (@p__linq__0 datetime2(7),@p__linq__1 datetime2(7),@p__linq__2 datetime2(7),@p__linq__3 datetime2(7),@p__linq__4 datetime2(7),@p__linq__5 datetime2(7),@p__linq__6 datetime2(7),@p__linq__7 datetime2(7),@p__linq__8 datetime2(7))SELECT [Project4].[C3] AS [C1], [Project4].[C59] AS [C2], [Project4].[C60] AS [C3], [Project4].[C61] AS [C4], [Project4].[C62] AS [C5], [Project4] unknown unknown (@0 int,@1 datetime2(7),@2 int,@3 datetime2(7),@4 int,@5 int,@6 int)UPDATE [dbo].[Mapping] SET [AssToId] = NULL, [MapStatus] = @0, [Updated] = @1, [Umag] = @2, [MappingCompleteDate] = @3, [DActive] = @4, [Old_Umag] = @5 WHERE ([Id] = @6) unknown unknown (@p__linq__0 datetime2(7),@p__linq__1 datetime2(7),@p__linq__2 datetime2(7),@p__linq__3 datetime2(7),@p__linq__4 datetime2(7),@p__linq__5 datetime2(7),@p__linq__6 datetime2(7),@p__linq__7 datetime2(7),@p__linq__8 datetime2(7))SELECT [Project4].[C3] AS [C1], [Project4].[C59] AS [C2], [Project4].[C60] AS [C3], [Project4].[C61] AS [C4], [Project4].[C62] AS [C5], [Project4] unknown unknown (@p__linq__0 datetime2(7),@p__linq__1 datetime2(7),@p__linq__2 datetime2(7),@p__linq__3 datetime2(7),@p__linq__4 datetime2(7),@p__linq__5 datetime2(7),@p__linq__6 datetime2(7),@p__linq__7 datetime2(7),@p__linq__8 datetime2(7))SELECT [Project4].[C3] AS [C1], [Project4].[C59] AS [C2], [Project4].[C60] AS [C3], [Project4].[C61] AS [C4], [Project4].[C62] AS [C5], [Project4]
Asked by Jishnu Chandran (13 rep)
Mar 13, 2023, 03:42 PM
Last activity: Mar 13, 2023, 04:32 PM