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
Last activity: Mar 13, 2023, 04:32 PM