关于Identity Insert的并发问题、性能影响及新旧主键插入方案问询

一、使用IDENTITY_INSERT的核心隐患 #

• 主键冲突风险：如果手动插入的ID值大于当前表的IDENTITY种子值，后续不指定ID的常规插入会直接触发主键重复报错，数据库默认按种子自增分配ID时，会撞到你手动插入的高位ID值。
• 锁范围扩大：开启IDENTITY_INSERT ON的会话执行插入时，除了常规的行锁、表级架构稳定性锁外，还会对IDENTITY序列的元数据加排他锁，批量插入场景下持锁时间会明显拉长。
• 操作冲突概率高：同一张表同一时间只能有一个会话开启IDENTITY_INSERT，多个会话同时尝试开启会直接报错；如果执行完插入后忘记执行SET IDENTITY_INSERT OFF，后续其他会话也无法开启该配置，极端场景下会干扰常规插入的ID序列分配。

二、未开启IDENTITY_INSERT的并发操作影响 #

• 功能可用性：绝大多数场景下不指定ID、不操作IDENTITY_INSERT的常规业务插入完全正常，不会出现功能报错，只要你手动插入的ID没有超过当前IDENTITY种子值，常规插入的ID分配逻辑不受任何影响。
• 性能影响：如果迁移操作为长事务大批次插入，IDENTITY_INSERT开启状态下会短暂占用IDENTITY元数据锁，常规插入会出现毫秒级等待，只有当迁移事务长达数秒甚至分钟级时，才会有可感知的变慢，小批量拆分的迁移操作对业务性能基本无影响。
• 死锁概率：IDENTITY_INSERT本身不会直接触发死锁，只有当两个事务出现循环资源等待时才会发生死锁，比如迁移事务先插入指定ID、再更新某条业务数据，另一个并发业务事务先更新同一条业务数据、再插入新ID，这类资源竞争顺序异常才会导致死锁，和IDENTITY_INSERT本身无直接关联。

三、新旧数据并行写入场景实操建议 #

• 提前重置IDENTITY种子：迁移历史数据前，先查询历史数据的最大旧主键值，执行DBCC CHECKIDENT ('你的表名', RESEED, 旧主键最大值)，确保后续业务新插入的ID直接从旧主键最大值+1开始分配，从根源避免主键冲突。
• 迁移任务拆分小事务：不要用单事务迁移全量历史数据，将历史数据按ID区间拆分为1000~10000条/批次的小事务，每批次插入完成后立刻提交，将持锁时间控制在毫秒级，完全避免阻塞业务写入。
• 严格控制配置生效范围：每次执行迁移批次前才开启SET IDENTITY_INSERT 你的表名 ON，该批次插入完成后立刻执行SET IDENTITY_INSERT 你的表名 OFF，不要跨批次保持开启状态，避免长时间占用元数据锁。
• 优化迁移会话隔离级别：迁移会话使用读提交快照隔离级别（RCSI），避免和业务读写操作产生阻塞，业务侧无需做任何改造。
• 迁移完成后二次校验：全量迁移结束后，执行DBCC CHECKIDENT ('你的表名', NORESEED)确认当前IDENTITY种子值大于表内最大ID值，避免后续业务插入出现主键重复。

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