Cannot insert duplicate key row even when using MAX(id)

Question

Very occasionally on a multi-user system with SQL Server 2008 R2 I am getting errors like "Cannot insert duplicate key row in object 'dbo.pages' with unique index 'UX_pages_pageid_siteid'. The duplicate key value is (141, 4385).". That index does indeed enforce uniqueness but this is a single statement that should guarantee uniqueness as far as I know:

INSERT INTO pages (pageid, siteid) 
    SELECT (SELECT ISNULL(CAST(MAX(ABS([pageid])) AS int), 1000) + 1 
    FROM pages 
    WHERE siteid = 4385), 4385;

I know this would be cleaner with an auto-incrementing identity but I can't change the schema.

The statement is executing inside o_Connection.BeginTrans() and the database has is_read_committed_snapshot_on = 1 which I suspect is involved but I cannot reproduce the problem (e.g. by amending this blog with INSERT INTO tblPapers (Url) SELECT MAX(url) + 'z' FROM tblPapers) or find any explanation in locking documentation.

Is it really the case that the nested SELECT may provide out-of-date data even though it is in the same statement and so changing the code to be FROM pages (UPDLOCK) is the correct fix?

Answer 1

Maybe not scalable but try

INSERT INTO pages with (tablock) (pageid, siteid) 
    SELECT (SELECT ISNULL(CAST(MAX(ABS([pageid])) AS int), 1000) + 1 
    FROM pages 
    WHERE siteid = 4385), 4385;

I just read the comments - if (UPDLOCK) workds then stay with that