Investigating SQL Server Deadlocks in Delete and Insert Operations

Data and Databases
1

Concurrent DELETE and INSERT operations on the same table trigger deadlocks due to primary key locking. How can improved indexing or query adjustments help mitigate this?

-- Sample alternative code snippet
DECLARE @TempRoles TABLE (RoleID INT, UserID INT, ParentRef INT);

DELETE FROM dbo.Roles
WHERE RoleID IN (SELECT RoleID FROM @TempRoles);

INSERT INTO dbo.Roles (UserID, RoleID, ParentRef)
SELECT UserID, RoleID, ParentRef FROM @TempRoles;
2

hey guys, i think making sure the most used columns are indexed properly can help avoid huge table scans which might device locks. has anyone played with lock hints or different isolation levels to see a change?