The X_table has around 25K rocords (52MB in size), the application deletes records from this table, when it deletes the deadlock occurs, spid 1 hold X lock while deleting and spid 2 request for S lock until the spid 1 completes its delete and vice versa, which leads to deadlock.
Below is the simple delete statement.
exec sp_executesql N'delete from X_table where internal_name = @tableName',N'@tableName nvarchar(21)',@tableName=N'Tbl_Deleteme'
I've attached deadlock graph and estimated plan for delete as well for reference.
Could you please share your thoughts to mitigate this deadlock issue?
Thanks in advance...