Serializable range deadlocks
We frequently experience deadlocks around an INSERT statement on the following table ... the stored procedure where the deadlocks occur [uses] SERIALIZABLE
Yes. That is the expected behavior of the SERIALIZABLE isolation level. It's not widely understood, but deadlocks are how SERIALIZABLE enforces the isolation level. It doesn't prevent concurrent sessions from attempting to write conflicting changes; if two sessions read data and then both attempt conflicting changes, a deadlock occurs, and only one write succeeds.
So if you don't want to deal with deadlocks, you're using the wrong concurrency model.
If you simply want to prevent concurrent execution of this transaction, forcing sessions to execute it one-at-a-time, the simplest way is to use an application lock:
BEGIN TRANSACTION
exec sp_getapplock @Resource = 'MyTransaction', @LockMode = 'Exclusive';
. . .
You have two queries on the deadlock-affected table that have very similar WHERE clauses. This one:
SELECT MAX(CreatedOn)
FROM dbo.ID_Stub IDS
WHERE IDS.IDReference = LTRIM(RTRIM(@IDReference))
AND IDS.CreatedBy = @CreatedBy
AND IDS.AdditionalID = @AdditionalID
And this one:
DELETE dbo.ID_Stub
WHERE IDReference = LTRIM(RTRIM(@IDReference))
AND CreatedBy = @CreatedBy
AND AdditionalID = @AdditionalID;
Depending on the data in dbo.ID_Stub
, you may be able to reduce the amount of range-locking going on (which is there due to the SERIALIZABLE isolation level, as you mentioned) by adding those other two fields to your nonclustered index key.
CREATE NONCLUSTERED INDEX idx_IDReference
ON dbo.ID_Stub (IDReference, CreatedBy, AdditionalID)
WITH
(
...all your other options
);
If you can't address the underlying problem of having to use the SERIALIZABLE isolation level (David Browne has makes a great suggestion with the getapplock procedure), then this could mitigate the problem at least.
You can do before and after testing with those specific queries on their own, checking the locks taken with sp_WhoIsActive @get_locks = 1;
) to see if you get different locks taken with the wider index.