SQL数据库生成递增序列号最佳实践及并发主键重复问题排查

单线程调用场景下，以下自动生成递增序列号的SQL查询可以正常运行：

insert into testTable (sequence_no) 
    case when exists (select sequence_no from testTable)
         then (select top(1) sequence_no +1 from testTable order by sequence_no desc)
         else '1'
    end as sequence_no

为验证并发场景下的稳定性，测试开启2个线程同时循环10万次执行插入操作，新增线程标识字段区分写入来源：
线程1执行代码：

declare @cnt INT =0;
while @cnt<100000
begin 
insert into testTable (sequence_no, thread_no) 
    case when exists (select sequence_no from testTable)
         then (select top(1) sequence_no +1 from testTable order by sequence_no desc)
         else '1'
    end as sequence_no, '1' as thread_no
SET @cnt = @cnt + 1;
END;

线程2执行代码：

declare @cnt INT =0;
while @cnt<100000
begin 
insert into testTable (sequence_no, thread_no) 
    case when exists (select sequence_no from testTable)
         then (select top(1) sequence_no +1 from testTable order by sequence_no desc)
         else '1'
    end as sequence_no, '2' as thread_no
SET @cnt = @cnt + 1;
END;

测试结果显示仅约70%的请求执行成功，其余请求抛出如下主键冲突错误：

Violation of PRIMARY KEY constraint 'sequence_no'. Cannot insert duplicate key in object 'dbo.testTable'.

最初尝试为每次插入请求添加事务解决该问题，但测试结果无明显改善，仍有约30%的请求因主键重复失败。核心疑问为：这种手动生成递增序列号的实现方式是否存在设计缺陷？有没有更合理的改进方案？

该实现存在典型的并发竞态设计缺陷，添加普通事务无效的原因如下：

• 默认事务隔离级别下，读操作之间不会互斥，两个并发线程可以在极短的时间窗口内同时读取到当前表的最大sequence_no值，计算出完全相同的待插入序列号，最终触发主键冲突
• 普通事务仅在数据写入阶段才会加排他锁，等两个线程完成序列号计算、执行插入动作时，重复ID的问题已经产生
• 若查询阶段没有显式加互斥锁，即使提升事务隔离级别，也无法完全避免这类并发读写导致的ID重复问题

根据业务对序列号连续性、并发性能的要求，可选择以下三类方案：

方案1：使用数据库原生自增列（绝大多数场景优先推荐） #

直接将sequence_no字段设置为IDENTITY自增属性，由数据库引擎原子性维护序列号的递增生成，从底层规避并发竞态问题，写入性能远高于手动生成实现，建表与插入示例：

-- 建表时指定自增主键，从1开始每次递增1
create table testTable (
    sequence_no bigint IDENTITY(1,1) PRIMARY KEY,
    thread_no varchar(2) not null
)
-- 插入时无需指定sequence_no字段，数据库自动生成唯一递增序列号
insert into testTable(thread_no) values ('1')

注意：自增列在事务回滚、插入失败场景下会出现序列号跳号，属于数据库设计的正常行为，若业务强制要求序列号连续无缺口，不适用该方案

方案2：查询阶段显式加锁实现串行化写入 #

如果必须手动维护序列号生成逻辑，可以在查询最大值时添加更新锁、表锁提示，强制读取阶段阻塞其他并发读取请求，保证同一时间只有一个线程能拿到最新的最大值完成计算，修改后的插入语句示例：

insert into testTable (sequence_no, thread_no) 
select 
    case when exists (select 1 from testTable with (UPDLOCK, TABLOCK))
         then (select top(1) sequence_no +1 from testTable with (UPDLOCK, TABLOCK) order by sequence_no desc)
         else 1
    end, '1'

注意：该方案会让所有插入操作完全串行执行，高并发场景下写入吞吐量会出现明显下降，仅适合并发量极低的场景使用

方案3：使用独立序列对象 #

SQL Server 2012及以上版本支持独立的SEQUENCE对象，由数据库引擎原子性维护序列值递增，相比手动加表锁的方案性能更高，使用示例：

-- 创建全局序列，从1开始每次递增1
create sequence seq_testTable as bigint start with 1 increment by 1;

-- 插入时直接获取序列的下一个值作为序列号
insert into testTable (sequence_no, thread_no)
values (next value for seq_testTable, '1')

注意：序列对象和自增列特性一致，在事务回滚、插入失败场景下会出现跳号，无法保证序列号绝对连续

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