SQL Server 高并发场景下模拟 Oracle Sequence 的方法

结果或目标

我们需要一种机制，能够在 SQL Server 中模拟 Oracle 的 Sequence 功能。具体要求如下：

• 格式灵活性：支持生成如 “2008072400001”（日期+5位流水号）这类格式，而非简单的纯数字自增。
• 高并发支持：能够承受高吞吐量请求（如每秒数千次调用），避免因行锁或页锁导致的阻塞排队。
• 线程安全：确保每次调用返回的值在全局范围内唯一且递增，不会出现重复编号。

准备条件

在开始实施之前，请确认以下环境要素：

• 数据库系统：Microsoft SQL Server 任何主流版本均支持本方案所需语法。
• 权限要求：执行存储过程和创建表的账户需具备对该表的 INSERT、DELETE 以及创建存储过程的权限。
• 理解 SCOPE_IDENTITY()：这是 SQL Server 的关键函数，用于返回当前作用域内最后生成的标识列值，比 @@IDENTITY 更安全，不会受触发器影响。

  第1步：设计专用序号表结构

  为了避免多张表共用一个 Sequence 表产生的竞争，建议为每个需要独立序号的资源创建一张专用的辅助表。该表仅包含两列：作为主键和自增器的整型 ID，以及一个占位字段。

  假设我们要为资源 T_0101001 创建序列号，创建表 SeqT_0101001：

  -- 创建表 SeqT_0101001
  CREATE TABLE SeqT_0101001(
      -- ID列为自增列，作为真正的计数器
      SeqID int IDENTITY(1,1) PRIMARY KEY,
      -- SeqVal仅为占位符，维持至少一行数据存在
      SeqVal varchar(1)
  )

  注意：此表只需保留一行记录。插入新行后，旧行的 ID 会失效，随后被删除。

  第2步：编写高并发获取序列号的存储过程

  这是核心步骤。传统的做法是先 SELECT MAX 再加 1 UPDATE，这在并发下极易冲突。改进方案采用“先插后删”的策略，利用 SCOPE_IDENTITY() 原子性地捕获刚生成的 ID，并通过 WITH (READPAST) 跳过可能被其他进程锁定的待删除行。

  -- 创建从SeqT_0101001表获取最新Sequence值的存储过程
  CREATE PROCEDURE P_GetNewSeqVal_SeqT_0101001
  AS
  BEGIN
      SET NOCOUNT ON;
      DECLARE @NewSeqValue INT;
  
      BEGIN TRANSACTION;
  
      -- 1. 插入新行，触发标识列增长，并获得最新的 SeqID
      INSERT INTO SeqT_0101001 (SeqVal) VALUES ('a');
  
      -- 2. 获取刚才插入行的标识列值（即新的序列号）
      SET @NewSeqValue = SCOPE_IDENTITY();
  
      -- 3. 删除上一轮留下的旧行（使用 READPAST 避免等待锁）
      DELETE FROM SeqT_0101001 WITH (READPAST);
  
      COMMIT TRANSACTION;
  
      RETURN @NewSeqValue;
  END;

  第3步：调用存储过程并组合业务编号

  由于 SQL Server 的标量函数限制，通常建议在应用层或存储过程中调用上述过程获取整数，再将其格式化为最终字符串。以下展示如何在 T-SQL 中直接使用获取到的整数进行格式化拼接：

  DECLARE @NewSeqVal INT;
  EXEC @NewSeqVal = P_GetNewSeqVal_SeqT_0101001;
  
  -- 生成格式：YYYYMMDD + 5位流水号（例如 2008072400001）
  SELECT 
      CONVERT(char(8), GETDATE(), 112) + 
      RIGHT('00000' + CAST(@NewSeqVal AS VARCHAR(5)), 5) AS MySequenceNo;

  参数说明：

  • CONVERT(char(8), GETDATE(), 112)：将当前日期转换为 ‘YYYYMMDD’ 格式。
  • RIGHT('00000' + ..., 5)：通过在左侧补零并截取右边5位，确保数字不足5位时自动填充前导零（如 1 变为 00001）。

  完成判断

  验证流程是否成功，可执行以下步骤：

  1. 初始检查：确保辅助表中有且仅有一行数据（可通过 SELECT * FROM SeqT_0101001 查看，虽然逻辑上应该为空或只剩一行占位，但插入动作会立即创建新行）。
  2. 多次调用测试：连续多次调用 P_GetNewSeqVal_SeqT_0101001，观察输出的 @NewSeqVal 是否严格递增。
  3. 并发测试：如果可能，使用多个连接并行执行存储过程，确认没有报主键冲突错误或死锁警告，且生成的编号无重复。

  原文有依据的排错

  问题：为什么不能用普通的 Update Max(Id)+1？

  在低并发系统中，“Select Max ID + 1, Update” 是可行的。但在高并发下，两个事务几乎同时读取到相同的 Max ID，计算出相同的下一个值，导致主键冲突（Primary Key Violation）或者其中一个事务失败重试，造成极大的性能浪费和逻辑复杂化。

  问题：为什么要用 READPAST？

  WITH (READPAST) 告诉 SQL Server 引擎，在执行 DELETE 操作时，如果某行被其他事务锁定，直接跳过该行而不是等待锁释放。这防止了当某个事务插入后立即崩溃未执行删除时，后续的事务卡死在删除操作上，从而提升了系统的吞吐量和响应速度。

  局限性与替代方案

  局限性：这种方法生成的数字序列中间可能存在空洞（Gaps）。因为每次插入都会产生一个新 ID，即使那个事务后来回滚（虽然本例中是显式 Commit，但若在外部包裹更大事务则可能受影响），或者在高负载下为了追求速度牺牲连续性。如果需要绝对连续无间断的序列号，此方法不适用。

  替代方案：如果 SQL Server 版本为 2012 及以上，推荐使用内置的 SEQUENCE 对象。它专门为此类场景设计，性能更优且语法更简洁。但对于老版本数据库或需要极端优化的特定场景，本文所述的存储过程方法依然是一个经典且有效的解决方案。

  简要总结

  SQL Server 通过巧妙的“插入-获取-删除”循环，配合 SCOPE_IDENTITY() 和 READPAST 提示，有效解决了传统自增列无法自定义格式以及通用更新法在高并发下的性能瓶颈问题。开发者可根据实际数据库版本和业务对序列连续性的要求，选择最适合的编号生成策略。

  以上就是 SQL Server 实现高并发环境下模拟 Oracle Sequence 方法的详细内容，更多关于 SQL Server 数据库优化、存储过程编写的资料请关注本站其它相关文章！