WITH (XLOCK, HOLDLOCK)
Ought to be sufficient. The
HOLDLOCK gives serializable semantics which means a key range lock will be taken on the range at the end of the index backing up the primary key. The
XLOCK means that 2 concurrent transactions can't both acquire this lock simultaneously.
This does mean that any concurrent callers to your
insert procedure will end up being blocked for the duration of the transaction.
A less blocking solution if you can add a new table would be to create another table with an
identity column and insert into that as below.
CREATE TABLE dbo.Sequence(
val int IDENTITY (10000, 1) /*Seed this at whatever your current max value is*/
CREATE PROC dbo.GetSequence
@val AS int OUTPUT
SAVE TRAN S1
INSERT INTO dbo.Sequence DEFAULT VALUES
ROLLBACK TRAN S1 /*Rolls back just as far as the save point to prevent the
sequence table filling up. The id allocated won't be reused*/