Read Commited事务中如何避免IF EXISTS语句返回NULL值

Read Committed隔离级别下，原有第一类线程的代码拆成了两段完全独立的非原子查询，偶现的NULL值就是并发操作刚好卡进两段查询的时间缝里导致的：

• 第一类线程跑IF EXISTS判断时，表里确实有created IS NULL的记录，判断结果为真
• 刚跑完判断、还没执行后面的取值查询时，第二类线程的批量UPDATE刚好跑完，把所有原本符合条件的记录的created字段全改成了非空的当前时间
• 等第一类线程真正执行取值的SELECT时，读已提交的隔离规则要求读已经提交的最新数据，这时候已经没有created IS NULL的行了，@import_id就一直是初始的NULL；更麻烦的是，之前的IF EXISTS判断已经为真，代码根本不会进ELSE分支做插入，最终就返回了无效的NULL值。

全程不改动Read Committed的事务隔离级别，核心思路就是把“判断有没有记录”和“拿记录的值”这两步合并成一个动作，消掉中间的时间差：

• 删掉原来冗余的IF EXISTS前置判断，直接用一次SELECT去匹配现有的未标记行，把拿到的id直接赋值给变量；没拿到有效值的时候，再走插入逻辑
• 把原来用的@@IDENTITY换成更靠谱的SCOPE_IDENTITY()，毕竟高并发下如果表上挂了插入触发器，@@IDENTITY很容易拿到触发器往其他自增表插的id，不是真正的Import表的自增值
• 几千个并发线程抢行的时候，可以在SELECT上加上不改变隔离级别的锁提示，碰到被其他事务锁住的行直接跳过，不用傻等锁释放，减少线程挂起

改写后的第一类线程代码：

DECLARE @import_id int

-- 单次执行完成“匹配待处理行+变量赋值”，没匹配到行时变量就是NULL
-- 锁提示完全兼容RC隔离级别：READPAST跳过被锁行，UPDLOCK给读到的行加更新锁，防止多个线程抢同一条
SELECT TOP 1 @import_id = [id]
FROM [dbo].[Import] WITH (READPAST, UPDLOCK)
WHERE created IS NULL
ORDER BY id;

-- 只要没拿到有效id就执行插入，从流程上保证变量最后一定有有效值
IF @import_id IS NULL
BEGIN
    INSERT INTO [dbo].[Import] ([startdate])
    VALUES (GETDATE());

    SET @import_id = SCOPE_IDENTITY();
END

• 原代码的根本漏洞就是把判断和取值拆成了两个时间点，改写后直接把“有没有有效行”的判断换成了“实际有没有拿到id”的结果，哪怕刚匹配到行就被第二类线程的UPDATE改掉，代码也会因为id是NULL自动走插入，再也不会出现“判断为真但拿不到值、也不插入”的空窗
• 加的READPAST、UPDLOCK提示只对当前这一条SELECT生效，不会突破Read Committed的隔离边界，还能把高并发下的行锁竞争压到最低，不会出现几千个线程一起堵在锁等待上的情况
• 第二类线程原来的UPDATE逻辑半行都不用改，就能和改写后的逻辑兼容运行

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