We have a stored procedure which is attempting to delete 1-2 rows at a time from a temporal table overnight. The stored procedure is executed several times simultaneously (around 10-15 threads) by parallel processes, and so attempts may be made to delete 30 rows simultaneously by different SPIDs, for example.
DELETE FROM dbo.Table WHERE ID IN (12);
DELETE FROM dbo.Table WHERE ID IN (102);
DELETE FROM dbo.Table WHERE ID IN (4);
DELETE FROM dbo.Table WHERE ID IN (7, 9);
With the other tables in our database, this works without issue. With the temporal table, deadlocks are guaranteed every time. Only one of the deletions will succeed; the rest will fail.
We have tried using WITH (ROWLOCK), as well as adding a non-clustered index on the column by which rows are being selected for deletion. These make no difference.
The default isolation level in use is Read Committed Snapshot Isolation (RCSI).
Can anyone please advise what is going on here? Is it due to the underlying row versioning/history table process in the temporal table? How can we delete efficiently from a temporal table without seeing deadlocks?