为何相同锁定逻辑代码仍出现脏读？遗留SQL序列替代问题排查

我来帮你拆解这个老方案踩的坑——用单值表当序列在高并发下出问题，哪怕你觉得锁定逻辑一致，其实背后藏着几个容易被忽略的细节：

一、脏读的核心原因：看似一致的锁定，实际存在间隙或隔离级别的漏洞 #

很多时候我们以为加了锁就万事大吉，但实际执行逻辑的顺序或隔离级别的设置会直接导致脏读：

• 分步操作的间隙窗口：如果你的存储过程是先SELECT当前值、再UPDATE递增，哪怕加了UPDLOCK，这两步之间的时间差就是脏读的窗口。比如：

  -- 风险极高的分步写法
  DECLARE @NextVal INT;
  SELECT @NextVal = CurrentVal FROM SequenceTable WITH (UPDLOCK);
  UPDATE SequenceTable SET CurrentVal = @NextVal + 1;
  SELECT @NextVal;
  

  这里SELECT到UPDATE之间，其他事务如果用了NOLOCK提示或者READ UNCOMMITTED隔离级别，就能读到未被更新的旧值（甚至如果你的事务没提交，对方还能读到你未提交的临时值，这就是脏读）。
• 隔离级别的隐性设置：如果数据库默认隔离级别是READ UNCOMMITTED，或者调用存储过程的会话显式设置了这个级别，哪怕你在存储过程里加了锁，对方依然能绕过锁读取未提交的数据。很多人会忽略调用方的隔离级别，只盯着存储过程内部的逻辑。

二、CPU飙升+严重阻塞的根源：高并发下的锁竞争与自旋等待 #

单值表只有一行数据，高并发下所有请求都在争抢这一行的更新锁，这会触发两个问题：

• 自旋等待消耗CPU：SQL Server在等待行锁时，会先进入自旋等待（spinlock）——也就是线程不停循环检查锁是否释放，而不是休眠。这种循环是CPU密集型的，大量请求同时自旋就会导致CPU占用飙升。
• 锁持有时间过长加剧阻塞：如果存储过程的事务包裹范围过大（比如除了序列生成，还包含其他业务逻辑），锁会被持有更久，导致后续请求排队等待，引发严重阻塞。

三、看似相同的锁定逻辑，实际执行计划的隐性差异 #

你以为锁定逻辑一致，但实际执行计划可能偷偷变了：

• 统计信息过期：如果SequenceTable的统计信息过期，SQL Server可能会选择更粗粒度的锁（比如页锁甚至表锁），而不是预期的行锁，这会加剧阻塞。
• 参数嗅探或执行计划偏离：存储过程的执行计划如果因为参数嗅探偏离，可能会导致锁定逻辑的实际执行和预期不一致，比如原本的UPDLOCK没有正确应用到行上。

排查与修复建议 #

1. 改成原子化更新语句：把读和写合并成一个原子操作，彻底消除间隙窗口，这是最有效的解决脏读的方法：

   -- 原子化写法，无间隙，避免脏读
   UPDATE SequenceTable
   SET CurrentVal = CurrentVal + 1
   OUTPUT inserted.CurrentVal;
   

2. 检查并修正隔离级别：确保数据库默认隔离级别是READ COMMITTED（SQL Server 2012前的默认值，但可能被修改过），存储过程内部也可以显式设置：

   SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
   

3. 排查锁类型与等待：用sys.dm_tran_locks查看当前锁的类型和等待资源，确认是否是行锁，有没有锁升级的情况：

   SELECT resource_type, request_mode, request_status, session_id
   FROM sys.dm_tran_locks
   WHERE resource_database_id = DB_ID('YourDBName');
   

4. 缩短锁持有时间：把序列生成的逻辑从大事务中剥离出来，只在生成序列的瞬间持有锁，执行完立刻释放。

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