主键约束冲突排查：存储过程已做唯一性校验仍报错

报错信息 #

"Violation of PRIMARY KEY constraint 'PK__#documen__16C6400F5AFF46C6'. Cannot insert duplicate key in object 'dbo.#documents'. The duplicate key value is (78812702)"

问题描述 #

我编写的存储过程运行时触发上述主键约束冲突错误，明明在向临时表#documents插入数据前已经添加了唯一性校验逻辑，但还是出现报错，这一情况十分奇怪。请问是否有人遇到过类似问题？可能的原因是什么？

简化后的存储过程代码 #

drop table #documents
drop table #documentstest
begin
set  nocount on;

    BEGIN TRY  
    CREATE table #documents (VersionId int primary key, 
                                 MasterId int, 
                                 CadName varchar(160), 
                                 Revision varchar(10),
                                 Iteration int,
                                 LifecycleState varchar(20),
                                 ModifiedOn datetime,
                                 Lvl int)

    CREATE table #documentstest (Documentid int primary key, 
                                 MasterId int, 
                                 CadName varchar(160), 
                                 Revision varchar(10),
                                 Iteration int,
                                 LifecycleState varchar(20),
                                 ModifiedOn datetime,
                                 Lvl int)

    INSERT INTO #documents (VersionId, MasterId, CadName, Revision, Iteration, LifecycleState, ModifiedOn, Lvl)
    VALUES ('12345', '8945656', 'test.prt', 'A', 1, 'study', '2022-12-12', 1);

    INSERT INTO #documentstest (Documentid, MasterId, CadName, Revision, Iteration, LifecycleState, ModifiedOn, Lvl)
    VALUES ('123456', '435345', 'test1.prt', 'A', 1, 'study', '2022-12-12', 1);

    INSERT INTO #documentstest (Documentid, MasterId, CadName, Revision, Iteration, LifecycleState, ModifiedOn, Lvl)
    VALUES ('12345', '689789', 'test2.prt', 'A', 1, 'study', '2022-12-12', 1);

    DECLARE @lvl int
    SET @lvl=0

    declare @Rownum  int
    set @Rownum=1

    DECLARE @rowCount int

    DECLARE @publishError bit
    SET @publishError=0

    PRINT CONVERT(VARCHAR(25), GETDATE(), 21) + ' - LoopB - Level = ' + CAST(@lvl AS VARCHAR(25))

    --select distinct VersionId from #documents

    PRINT CONVERT(VARCHAR(25), GETDATE(), 21) + ' - try adding data' 

    BEGIN
        INSERT  #documents
        SELECT distinct Documentid, MasterId, CadName, Revision, Iteration, LifecycleState, ModifiedOn, lvl FROM #documentstest
        where @Rownum=1
        and Documentid not in (select distinct VersionId from #documents) -- added condiotion to check existing key
        SET @rowCount = 5
        PRINT CONVERT(VARCHAR(25), GETDATE(), 21) + ' - done with adding' 
    END

    PRINT CONVERT(VARCHAR(25), GETDATE(), 21) + ' - executing after issue'
    INSERT INTO #documents (VersionId, MasterId, CadName, Revision, Iteration, LifecycleState, ModifiedOn, Lvl)
    VALUES ('1238', '689789', 'test2.prt', 'A', 1, 'study', '2022-12-12', 1);

    select * from #documents 
    select * from #documentstest

    END TRY
    BEGIN CATCH 
    THROW;
    PRINT CONVERT(VARCHAR(25), GETDATE(), 21) + ' Caught Exception' 
    --  set @publishError=1
    --select * from #documents
    --select * from #documentstest
    --PRINT CONVERT(VARCHAR(25), GETDATE(), 21) + ' - executing after issue'
    END CATCH

PRINT CONVERT(VARCHAR(25), GETDATE(), 21) + ' Executing the END' + CAST(@publishError AS VARCHAR(25))
end

原因分析及解决方案 #

可能的原因 #

1. 竞态条件导致校验失效
   当前的校验逻辑（Documentid not in (select ...)）和插入操作是分开执行的，并非原子操作。如果存储过程被多个会话同时调用，在执行校验到实际插入数据的时间窗口内，其他会话可能已经插入了相同的VersionId，导致校验结果失效，最终触发主键冲突。

2. 实际业务逻辑与简化版存在差异
   提供的简化版代码中，#documentstest的重复Documentid会被not in过滤，不会插入到#documents。但实际报错的是78812702，说明实际代码中可能存在：

   • @Rownum的判断逻辑在某些场景下失效，导致过滤条件不生效；
   • 实际的数据源（非#documentstest）本身存在重复值，或者校验时的子查询没有覆盖所有需要检查的情况。

解决方案 #

1. 使用原子操作替代先查后插
   改用MERGE语句，它是原子性的，能避免竞态条件，同时完成校验和插入：

   MERGE INTO #documents AS target
   USING (
       SELECT DISTINCT Documentid, MasterId, CadName, Revision, Iteration, LifecycleState, ModifiedOn, lvl 
       FROM #documentstest 
       WHERE @Rownum=1
   ) AS source
   ON (target.VersionId = source.Documentid)
   WHEN NOT MATCHED THEN
       INSERT (VersionId, MasterId, CadName, Revision, Iteration, LifecycleState, ModifiedOn, Lvl)
       VALUES (source.Documentid, source.MasterId, source.CadName, source.Revision, source.Iteration, source.LifecycleState, source.ModifiedOn, source.lvl);
   

2. 检查实际业务逻辑的过滤条件
   排查实际代码中@Rownum的赋值和判断逻辑，确保过滤条件在所有场景下都能正确生效；同时确认数据源是否存在未被处理的重复值。

3. 临时启用重复键忽略（应急方案）
   如果只是需要跳过重复数据而不报错，可以在创建临时表时给主键加上IGNORE_DUP_KEY=ON，但这只是临时方案，无法解决根本问题：

   CREATE table #documents (
       VersionId int primary key with (IGNORE_DUP_KEY=ON), 
       MasterId int, 
       CadName varchar(160), 
       Revision varchar(10),
       Iteration int,
       LifecycleState varchar(20),
       ModifiedOn datetime,
       Lvl int
   )
   

内容的提问来源于stack exchange，提问作者smy