SQL Server存储过程插入后读取偶发无数据事务问题咨询

你遇到的偶发无报错未处理数据的问题，核心原因有2个：

1. sp2中查询tbl1时加了with (nolock)提示，该提示会启用读未提交隔离级别，虽然同会话的未提交数据理论上可读取，但如果遇到高并发下的页拆分、行版本生成延迟等极端场景，存在极小概率读不到刚插入的记录；更常见的可能是插入完成后、sp2查询前的瞬间，其他会话删除了该条id对应的数据，nolock不会加共享锁阻塞删除操作，就会出现查不到的情况。
2. 查询@vbody后没有做非空校验，一旦查不到数据，@vbody会被赋值为NULL，后续逻辑没有触发任何报错就直接走完，所以存储过程返回执行成功，但实际上什么都没做。

另外你担心的临时表#tmp1的读写顺序问题不存在：临时表是当前会话私有，插入操作完成后才会执行后续的exists判断，不会出现读在写之前的情况。

你不需要改动整体逻辑，只需要做3处小修改即可解决问题：

• 删掉sp2中查询tbl1语句里的with (nolock)提示，使用SQL Server默认的读提交隔离级别即可，同会话插入的记录一定可以读到，也不会产生额外的性能损耗。
• 在select @vbody = tbody语句后加非空校验，查不到数据时直接抛出异常触发错误捕获，避免静默失败：

select @vbody = tbody
from tbl1
where id = @id

-- 新增校验逻辑
if @vbody is null
begin
    raiserror('未查询到id为%d的对应记录', 16, 1, @id)
    return
end

• 给sp1加显式事务，保证插入数据和sp2处理逻辑的原子性，要么全部成功要么全部回滚：

procedure sp1 
(@id, @pbody)
as
begin
    set nocount on;
    begin try
        begin tran -- 开启显式事务
            insert into tbl1 (id, tbody)
            values (@id, @pbody)

            exec sp2 @id        
        commit tran -- 全部执行成功再提交
    end try
    begin catch
        if @@trancount > 0 rollback tran -- 出错回滚所有操作
        execute sperror
    end catch
end
go

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