如何避免SQL Server表插入重复数据？ASP.NET Web Forms问题求助

问题根源 #

你遇到的重复数据问题，核心是并发场景下的非原子操作冲突：现有代码的「查询最大ID→生成新ID→插入数据」是三个独立步骤，高并发时多个请求会同时查到相同的最大ID，进而生成完全一致的新ID，最终导致重复插入。

最佳实践方案（按可靠性排序） #

1. 数据库端实现自动ID生成（最推荐）

彻底把ID生成逻辑交给数据库，避免应用层的并发竞争：

• 修改表结构：将ID拆分为前缀和自增序列两部分，新增计算列自动组合：

  -- 新增前缀字段和自增列
  ALTER TABLE TEST_DATA ADD PIdPrefix VARCHAR(10) NOT NULL;
  ALTER TABLE TEST_DATA ADD SequenceNo INT IDENTITY(1,1) NOT NULL;
  -- 新增计算列作为最终ID（持久化提升查询性能）
  ALTER TABLE TEST_DATA ADD ID AS CONCAT(PIdPrefix, RIGHT('0000' + CAST(SequenceNo AS VARCHAR(4)), 4)) PERSISTED;
  -- 给ID加唯一约束兜底
  ALTER TABLE TEST_DATA ADD CONSTRAINT UQ_TEST_DATA_ID UNIQUE (ID);
  

• 修改应用代码：插入时不再手动生成ID，直接传入前缀即可：

  using (Entity EntityData= new Entity())
  {
      string newId = getPID.Replace("-", "");
      TEST_DATA addDATA = new TEST_DATA();
      addDATA.PIdPrefix = newId;
      addDATA.NUMBER_NO = XXXX_NO;
      addDATA.RECDATE = DateTime.Now;
      addDATA.MODIFY_TIME = DateTime.Now;
  
      EntityData.TEST_DATA.Add(addDATA);
      EntityData.SaveChanges();
      // 如需返回生成的ID，直接取addDATA.ID即可
  }
  

  这种方式完全由数据库保证ID的唯一性，彻底解决并发问题。

2. 应用层加分布式锁（无法改表结构时用）

如果不能修改表结构，就给同一NUMBER_NO的插入操作加锁，强制串行执行：

• 使用SQL Server内置的应用锁（无需额外组件）：

  using (Entity EntityData= new Entity())
  {
      // 获取针对当前NUMBER_NO的应用锁
      var lockResult = EntityData.Database.SqlQuery<int>(
          "EXEC sp_getapplock @Resource = @resource, @LockMode = 'Exclusive', @LockTimeout = 5000",
          new SqlParameter("@resource", $"TEST_DATA_LOCK_{XXXX_NO}")
      ).FirstOrDefault();
  
      if (lockResult != 0)
      {
          // 锁获取失败，可抛出异常或重试
          throw new Exception("获取锁失败，请稍后重试");
      }
  
      try
      {
          // 原有查询、生成ID、插入逻辑
          var checkID = EntityData.TEST_DATA.AsNoTracking()
                      .Where(o => o.NUMBER_NO == XXXX_NO)
                      .OrderByDescending(o => o.ID)
                      .FirstOrDefault();
  
          toolbox tool = new toolbox();
          string ID = checkID == null ? tool.getID(getPID, null) : tool.getID(getPID, checkID.ID);
  
          TEST_DATA addDATA = new TEST_DATA();
          addDATA.ID = ID;
          addDATA.NUMBER_NO = XXXX_NO;
          addDATA.RECDATE = DateTime.Now;
          addDATA.MODIFY_TIME = DateTime.Now;
  
          EntityData.TEST_DATA.Add(addDATA);
          EntityData.SaveChanges();
      }
      finally
      {
          // 释放锁
          EntityData.Database.ExecuteSqlCommand(
              "EXEC sp_releaseapplock @Resource = @resource",
              new SqlParameter("@resource", $"TEST_DATA_LOCK_{XXXX_NO}")
          );
      }
  }
  

3. 数据库唯一约束（兜底必备）

无论用哪种方案，都要给ID字段添加唯一约束，防止意外重复：

ALTER TABLE TEST_DATA ADD CONSTRAINT UQ_TEST_DATA_ID UNIQUE (ID);

这样当重复插入发生时，数据库会抛出UniqueConstraintViolation异常，应用层可以捕获后重试（比如重新查询生成ID再插入）。

为什么原有代码失效？ #

原有代码使用AsNoTracking()做快照读，并发场景下多个请求会读到相同的最新ID记录，生成相同的新ID后同时插入，而数据库此时没有唯一约束，就会产生重复数据。

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