SQL INSERT OUTPUT并发提交时数据错配丢失问题咨询

你的INSERT ... OUTPUT语法本身和自增标识列没有兼容性缺陷，数据库层面的OUTPUT子句是天然线程安全的，不会在并发场景下跨连接返回错误的插入ID。你遇到的问题来自两个层面：应用层通用数据库封装的并发竞态bug，以及流程缺失事务边界控制。

首先先解释你观察到的「自增序号连续但表中无A的记录」现象：

SQL Server的IDENTITY自增列有明确的设计规则：只要INSERT语句开始执行，就会立刻占用一个连续的自增序号，哪怕后续INSERT因为事务回滚、语句执行报错等原因最终没有将记录持久化到表中，这个被占用的序号也不会被回退复用。你看到B的VacReqNum比A大1，只能证明A的INSERT语句曾经启动执行并分配了序号，不能证明A的插入最终成功提交。

你全代码库通用的Cm.ex方法存在典型的线程安全问题：大概率内部使用了全局静态变量、单例对象的共享成员变量来存储SQL执行的返回值（也就是OUTPUT子句返回的VacReqID），没有做线程间的数据隔离。两个请求几乎同时提交时，会按如下时序触发异常：

1. 用户A的请求线程执行INSERT，数据库为A生成VacReqID = A_ID，分配自增序号N，准备将A_ID写入Cm.ex的返回值存储区
2. 用户B的请求线程几乎同时执行INSERT，数据库为B生成VacReqID = B_ID，分配自增序号N+1，抢先将Cm.ex的返回值存储区覆盖为B_ID
3. A的线程此时才读取Cm.ex的返回值，拿到的是已经被B覆盖的B_ID，完全拿不到自己的A_ID
4. 后续A的流程拿着错误的B_ID执行明细表插入、详情查询操作：要么因为关联ID不匹配触发异常，导致A的主表插入事务回滚（所以最终表里没有A的记录）；要么明细表插入逻辑错误地将A的休假明细关联到B的主表ID，触发后续校验失败回滚A的主表事务
5. 最终A组装邮件时用错误的B_ID查询数据，自然拿到的是B的休假申请内容；而B的线程全程没有被干扰，拿到的是自己正确的B_ID，所以数据完全正常。

• 第一优先级修复Cm.ex的线程安全问题：
  • 排查Cm.ex内部逻辑，将所有用于存储SQL执行结果、连接状态、Command对象的共享静态变量/单例成员变量，全部改为方法级局部变量，确保不同请求线程的执行上下文完全隔离，不会出现返回值互相覆盖的问题
  • 确保每次调用Cm.ex时使用独立的数据库连接、独立的Command对象，禁止在多线程间共享数据库操作实例
• 补全事务边界：
  • 将「插入申请主表、插入休假申请明细表、查询申请详情」的全流程包裹在同一个数据库事务中，任何一步执行失败就整体回滚，避免出现主表有记录、明细表无数据的不一致问题
  • 事务执行全程复用同一个数据库连接，不要跨连接传递事务上下文
• 替换SQL拼接写法：
  • 废弃当前直接拼接用户输入生成SQL的写法，所有传入参数（sReqStatus/sVacNotes/sHRVacNotes/查询用的sVacReqID）全部改用参数化查询传入，从根源上避免SQL注入风险，同时解决特殊字符转义导致的SQL执行失败问题
• 可选优化：
  不需要插入后再单独执行一次SELECT查询，可以直接在INSERT的OUTPUT子句中把需要的字段（包括自增生成的VacReqNum）一次性返回，减少一次数据库交互，降低并发出错概率，示例写法：

INSERT INTO tblWP_TS_VacRequest (VacReqID, ReqStatus, ApproveDate, SubmitDate, CreateDate, VacNotes, HRVacNotes) 
OUTPUT Inserted.VacReqID, Inserted.VacReqNum
VALUES(NEWID(), @sReqStatus, NULL, GETDATE(), GETDATE(), @sVacNotes, @sHRVacNotes);

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