Serializable range deadlocks

Help required on an inherited deadlock problem!

The code given below appears to have been written some time back, in an attempt to address contention issues around the assigning of new ID values on a much larger/fatter [IDs] parent table, by first creating the identity values in a smaller [ID_Stub] table. However, the presence of continued deadlocks indicates that this code appears to be causing more problems than it is solving.

We frequently experience deadlocks around an INSERT statement on the following table (table and column names have been obfuscated). The table has no triggers or foreign key dependencies, but has a clustered and a non-clustered index as follows.

CREATE TABLE dbo.ID_Stub (
ID int IDENTITY(1,1) NOT NULL,
IDReference nchar(25) NULL,
AdditionalID int NULL,
CreatedBy int NOT NULL,
CreatedOn datetime NOT NULL,
CONSTRAINT PK_ID_Stub PRIMARY KEY CLUSTERED (ID) WITH 
(
    PAD_INDEX = OFF, 
    STATISTICS_NORECOMPUTE = OFF, 
    SORT_IN_TEMPDB = OFF, 
    IGNORE_DUP_KEY = OFF, 
    ONLINE = OFF, 
    ALLOW_ROW_LOCKS = ON, 
    ALLOW_PAGE_LOCKS = ON, 
    FILLFACTOR = 90
)
);
GO

CREATE NONCLUSTERED INDEX idx_IDReference ON dbo.ID_Stub (IDReference) WITH 
(
    PAD_INDEX = OFF, 
    STATISTICS_NORECOMPUTE = OFF, 
    SORT_IN_TEMPDB = OFF, 
    DROP_EXISTING = OFF, 
    ONLINE = OFF, 
    ALLOW_ROW_LOCKS = ON, 
    ALLOW_PAGE_LOCKS = ON, 
    FILLFACTOR = 70
);
GO

The table contains an average of around 70,000 rows at any one time (a process runs to trim the number of records nightly).

The default instance transaction isolation level is READ COMMITTED, however this is overridden (in the stored procedure where the deadlocks occur) to SERIALIZABLE, along with an implicit transaction. Unfortunately, we cannot consider moving to an optimising locking strategy such as RCSI at this stage.

The whole implicit transaction has been given below for completeness, but the deadlocks are occurring around the final INSERT statement on ID_Stub towards the

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';
. . .

StackExchange Database Administrators Q#226174, answer score: 9