只要你在 SQL Server 的表里用过自增列IDENTITY就迟早会遇到“跳号”问题插入几张表编号却从 100 直接蹦到 105删掉几行历史数据下一个 ID 也不会回填最夸张的是数据库一重启下一次插入的编号能直接跳到 1000001。很多开发的第一反应都是“能不能禁止它跳号”我开始做数据库运维那几年也跟这个杠过后来才发现跳号这件事得先分清“能不能”和“该不该”否则改了还可能出更大的事故。这篇文章我会把自增列跳号的所有常见原因、真正能实现连续编号的可行方案、以及我实际跑过的订单编号连续分配完整代码一次说清。不管你是在电商订单、工单系统还是合同台账里被这个需求折磨过看完你应该能判断自家业务到底该用哪套做法。1. 你遇到的跳号到底是怎么来的1.1 自增列的本来面目它只保证唯一从来不保证连续很多人对自增列的误解都来自“自增”这两个字。SQL Server 里一张表定义了 IDENTITY(seed, increment)比如IDENTITY(1,1)它的职责只有一个保证每一行拿到一个“不重复”的数字并且新值比旧值大。它从来没承诺过这个数字序列是“无缝衔接”的。用个生活化的比喻你公司前台有个取号机它负责在客户进场时吐一张号吐出来的号码不会重复但它并不会因为你把 8 号客户请出去了就让 9 号变成 8 号也不会因为机器重启时缓存里还有几张号码没发出去就接着从上一个数开始。自增列就是这样的取号机它输出的是流水号不是回填号。所以当你看到表里数据是 1、2、3、5、7请先冷静这不是数据库坏了而是它的正常设计。真正需要让你在工作里难受的是当你把自增列直接当业务单据号使用时中间那堆“消失”的数字无法向业务方解释。1.2 四个让编号“凭空消失”的黑手我从实际项目里归纳下来导致跳号的原因基本就是下面这几类。第一类事务回滚。这是最容易触发也最让人懵的。你执行INSERT插入一行系统已经给这一行分配了自增值 10然后事务因为某种原因ROLLBACK回滚了。行消失了但那个“10”不会还给自增序列。下次插入时拿到的就是 11于是序列里缺了一个 10。为什么不能收回因为 SQL Server 给自增值的操作是不记录在事务日志里的“内存步进”它跟行数据的增删改不一样回滚管不到它。第二类删除行。你把 5 号客户删了下一条插入直接拿到 65 这个号码就成了永久的空洞。这个最好理解SQL Server 的自增机制只会往上走不会回头去找空位填坑。第三类手动显式插入。SQL Server 允许你临时开启SET IDENTITY_INSERT ON然后手动往自增列里写一个很大的值。比如你手动写了一行 ID1000那么后续自增起点就变成 1001 左右前面的 4、5、6 全部变成永远不会被使用的空洞。这通常发生在做数据迁移、历史数据补录时属于人为跳号。第四类数据库重启或服务崩溃。很多人以为“正常关库就不会跳号”实际不一定。SQL Server 2012 之后的版本在生成自增值时有基于页或内存块预分配缓存的机制数据库实例意外重启、强制切换、或者某些复制/镜像场景下宕机恢复那些已经预分到内存、还没真正写进表里的编号会全部作废。表现就是有时候重启一次下一个号直接跳了 1000、10000甚至 100000。这一部分我们单独展开说因为它最像“故障”但其实是设计行为。1.3 重启后跳号是“特性”不是故障我们线上就遇到过一模一样的案例。一套 2019 版的 SQL Server某张订单表正常运行每天编号都连续可某次机房断电后数据库起来新插入的一张订单直接拿到比前一天多了十万的编号。开发组当场炸锅以为是硬盘坏了差点走弯路去跑数据库修复。实际上SQL Server 为了减少每次插入时对系统表的频繁访问会一次性预留一批自增值。如果实例没来得及把缓存里的值用完就重启了这些预留值就被直接丢弃。下次插入时系统重新“起一盘”从比原来大很多的数字开始。这个行为在传统磁盘表、内存优化表上都存在只是在 2017、2019 这些版本里表现的更明显网上也早有人管它叫“标识值缓存”现象。这里要插一句正因为如此如果你正在做的是“对接审计、票据、合同号”这类业务把身份证号、合同编号直接建在自增列上基本就是给自己埋雷。你甚至可以提前在测试环境模拟一次SHUTDOWN WITH NOWAIT再重启看完跳号幅度后很多业务方会自动同意换方案。2. 先分清需求什么时候才需要“禁止跳号”2.1 大多数表根本不应该去管跳号请你先记住一个判断标准如果这个编号只有数据库内部在使用也就是纯粹用来当主键、用来JOIN外键、用来定位行那跳号完全不影响功能。比如用户表Users主键 UserId 用的自增列中间有一个用户被物理删除了导致 UserId 出现空洞。查询、关联、分页都不受影响因为你的WHERE UserId 100从来不会依赖 99 后面一定是 100。很多刚入门的人容易把“主键必须连续”当成强制约定其实主键只要求三件事唯一、稳定、不为空。连续不连续完全不在考虑范围内。如果你真的在报表里看到“用户 ID 有断档”那不是数据库错误而是报表展示层没有做排序号处理。给查询结果加一个ROW_NUMBER() OVER (ORDER BY UserId)就能在显示层得到 1、2、3、4 的连续序号而底层存储依然保持高效的自增特性两不耽误。2.2 只有业务单据号才需要认真对待真正需要跟“禁止跳号”死磕的是那些要打印出来、发给客户、走审批流的业务单号订单号、合同编号、发票号、工单号、收据号。这种号有几个共同点对外可见、用户会对照单据序列检查是否漏单、可能涉及财务对账或审计线索。一旦中间缺了一个号业务人员的第一反应就是“你们系统是不是漏了数据”。所以我遇到做这类系统的团队通常会先问一句你们到底是要“物理表里的编号连续”还是“给客户的单据编号连续”如果是后者最稳妥的做法是单独设计一套业务编号生成机制而不去动主键自增列。自增列继续负责内部主键和关联对外输出的号码用专门的手段生成。2.3 展示层连续与物理层连续是两回事很多人被业务方逼得没办法总想着把物理表里的 ID 弄连续。其实你先退一步想想业务方要的只是“看起来没有缺号”。如果所有单据查询都走同一套报表那直接在 SQL 查询里用ROW_NUMBER()生成显示序号比改底层表结构安全一百倍。之前有个项目合同列表要导出 Excel客户发现合同编号中间少了几个怀疑被后台删了。我们排查后发现只是有一批作废合同被物理删掉了。最后我们把需求改成“作废的合同保留记录状态标记为已作废”列表展示时显示原编号和作废标记再用ROW_NUMBER()排出连续序号。客户再也没来闹过。你看“看起来连续”有时候比“物理连续”更能满足业务诉求而且成本几乎为零。3. 真正能“禁止跳号”的几个方案3.1 误区一把 IDENTITY 当业务流水号有团队为了“解决”跳号做过这些事给表加触发器插入后检测到编号有空缺就自动改写成上一个编号 1或者定期用DBCC CHECKIDENT重置自增列再或者干脆删库重建让编号重新从 1 开始。这里我直接说结论这些操作我都不推荐在正式环境长期用。触发器改写主键、重排编号会带来外键引用失效、历史数据关联断裂、日志备份链断裂、并发插入死锁等一系列问题。尤其是DBCC CHECKIDENT重置万一表里已经有 1000 行数据重置后下一行尝试写一个跟已有主键冲突的值整个应用都会陷进去。IDENTITY的正确用法就是内部主键。如果业务一定要连续你需要换一个机制来生成业务编号而不是跟自增列的机制较劲。3.2 误区二SEQUENCE 用来兜底却仍然会跳SQL Server 2012 以后有序列对象SEQUENCE很多人觉得它比自增列高级用它生成业务单号就能防跳号。序列对象确实有两个优势独立于表、可以在多个表之间共用可以设置NO CACHE让每次取值都落盘减少重启带来的大跨度跳号。但这里有个非常关键的坑NEXT VALUE FOR生成的值同样不会随事务回滚而退回。你执行BEGIN TRAN; INSERT ...; ROLLBACK;序列已经推进了一格下一次取号还是会拿到下一个数。换句话说SEQUENCE 解决的是“重启大跳号”和“跨表共用编号”的问题但事务失败导致的小跳号依旧存在。我之前在一家公司做发票号分配曾经天真地以为把方案升级成SEQUENCE NO CACHE就万事大吉结果压测的时候发现大批事务回滚后发出去的发票号断得比原来还乱。后来才明白只要取值动作发生在业务提交之前唯一性和连续性是两码事你需要的是“取值跟着业务事务一起回滚”而不是一个独立的计数器。3.3 可行方案编号表 事务内取号真正能做到“业务失败或回滚后编号不跳”的标准办法是用一张编号表在同一个事务里完成取号和业务数据插入并且对编号表那一行加上锁。核心思路是取号不是独立的操作而是业务事务的一部分。业务事务回滚了编号表里的当前值也一并回滚于是这个号码可以被下一笔事务继续使用。先看编号表结构设计CREATE TABLE dbo.BizNumberPool ( BizType NVARCHAR(50) NOT NULL PRIMARY KEY, CurrentValue INT NOT NULL ); INSERT dbo.BizNumberPool (BizType, CurrentValue) VALUES (NORDER, 0);用BizType区分不同的单据类型比如订单、合同、工单。CurrentValue存当前已用到哪个编号。每次取号做的事很简单把当前值加 1返回加 1 后的结果。取号必须放在业务事务内部代码大致长这样CREATE PROCEDURE dbo.usp_GetNextOrderNo NextNo INT OUTPUT AS BEGIN SET NOCOUNT ON; -- 提示不要在过程内单独提交事务调用方必须把它包在业务事务里 UPDATE dbo.BizNumberPool WITH (UPDLOCK, HOLDLOCK) SET CurrentValue CurrentValue 1 WHERE BizType NORDER; SELECT NextNo CurrentValue FROM dbo.BizNumberPool WHERE BizType NORDER; END;调用方用法BEGIN TRAN; DECLARE newNo INT; EXEC dbo.usp_GetNextOrderNo newNo OUTPUT; INSERT dbo.Orders (OrderNo, ProductName, Qty) VALUES (newNo, N螺丝, 100); COMMIT;关键点在于UPDLOCK, HOLDLOCK这两个表提示。UPDLOCK让编号表那一行以更新锁的方式被锁住其他事务不能同时改这一行HOLDLOCK让这个锁一直保持到事务结束不会在UPDATE完成的一瞬间被释放。你用这种方式测试一下会发现事务 A 取号 10还没提交事务 B 同时取号会阻塞等待等 A 提交后 B 拿到 11如果 A 回滚B 拿到的还是 10因为 A 的回滚把编号表里的“当前值”也回滚到了 9B 的加一操作依然得到 10。这就是“业务回滚不跳号”的正解。3.4 删号问题再严格的分配也防不了物理删除说到这里得泼一盆冷水事务锁的方案能解决回滚跳号、并发取号错乱但它解决不了“单据已经发出去、后来被人工物理删除”的问题。你给客户开了一张编号 100 的发票第二天业务人员把这条记录从库里 DELETE 了。无论你用什么取号器已经打印出去的 100 都不可能被新单据顶替不然对账就全乱了。所以凡是“严格连续”的业务唯一合理的设计是已经生成的单据不能物理删除只允许“作废”作废后原编号保留列表里显示“已作废”新单据往下走。这样序列里虽然可能有作废号但业务上说得清原因而且没有“凭空消失”的数据。这比追求物理表连续更重要。4. 实操用事务锁写一个连续订单号4.1 表结构与编号表设计这一节我直接给一套完整可跑的示例适合拿去测试环境验证。订单表正常情况下会有一个内部主键OrderId可以继续用自增列不用管它是否连续另外放一个OrderNo作为业务编号这个才是我们要求连续的对象。CREATE TABLE dbo.Orders ( OrderId INT IDENTITY(1,1) NOT NULL PRIMARY KEY, OrderNo INT NOT NULL UNIQUE, ProductName NVARCHAR(100) NOT NULL, Qty INT NOT NULL, CreateTime DATETIME2 DEFAULT SYSDATETIME() );编号表在前面已经建好了生产环境里一张表可以放多个业务类型像个号码池。如果你有不同的分库或者租户把租户标识也加到主键里别混在一起锁。我们这里只演示ORDER类型。4.2 取号存储过程与调用方式完整的取号过程如下。注意这里不让过程内部COMMIT因为事务边界要由调用方控制。这样一旦调用方业务失败编号才可能跟着回滚。CREATE PROCEDURE dbo.usp_GetNextOrderNo NextNo INT OUTPUT AS BEGIN SET NOCOUNT ON; SET XACT_ABORT ON; UPDATE dbo.BizNumberPool WITH (UPDLOCK, HOLDLOCK) SET CurrentValue CurrentValue 1 WHERE BizType NORDER; IF ROWCOUNT 0 BEGIN RAISERROR (N编号池缺少 ORDER 类型的初始化记录, 16, 1); RETURN -1; END; SELECT NextNo CurrentValue FROM dbo.BizNumberPool WHERE BizType NORDER; END;业务方调用时必须包在事务里DECLARE err INT; BEGIN TRAN; DECLARE no INT; EXEC dbo.usp_GetNextOrderNo NextNo no OUTPUT; INSERT dbo.Orders (OrderNo, ProductName, Qty) VALUES (no, N不锈钢螺栓, 200); COMMIT;如果插入成功订单表会得到一条OrderNo1的新单。然后你把这个事务整体复制出来连续执行三次编号就是 1、2、3。4.3 并发测试回滚后编号是否归还为了验证“业务回滚不跳号”可以开两个查询窗口模拟并发。会话 A 执行BEGIN TRAN; DECLARE noA INT; EXEC dbo.usp_GetNextOrderNo NextNo noA OUTPUT; PRINT A 拿到的号 CAST(noA AS VARCHAR(20)); INSERT dbo.Orders (OrderNo, ProductName, Qty) VALUES (noA, N订单A, 1); -- 先不 COMMIT停在这个事务里此时不要提交切换到会话 B 执行相同操作你会发现 B 的EXEC一直等待。这说明锁生效了同一时间只有一个会话能取同一个业务类型的号。这时让会话 A 执行ROLLBACK会话 B 的等待立刻结束。你再看 B 打印的号它拿到的是和 A 一开始拿到的同一个号比如都是 1绝不会跳到 2。这个测试直接证明了A 回滚后编号被“还”回来了序列没有出现空洞。如果会话 A 选择的是COMMITB 拿到的号就是 2。两条路径都符合预期。4.4 这个方案的代价与适用范围很遗憾这个方案不是免费的午餐。最大的代价是同一类单据的取号操作变成了串行。哪怕你的库能支撑每秒几千次插入只要每一笔都经过这个事务锁实际吞吐量会被大大限制订单高峰期可能变成瓶颈。我自己的处理经验是如果业务严格要求单据号物理连续且并发量在每秒几笔到几十笔以内事务锁完全够用如果是电商大促、高并发秒杀那种每秒几百上千单的场景业务几乎不可能真的要求物理连续因为就算你用分布式 ID 也要牺牲可读性。这时候我会回头劝产品经理要么允许少量跳号要么用展示层连续号打发。5. 常见问题速查表与避坑清单5.1 典型问题速查表现象原因处理建议事务回滚后下一次插入自增列跳了一个数IDENTITY 值分配不随事务回滚而回退不需要处理如果对外业务号不能接受改用第 3.3 节的编号表方案数据库重启后下一次插入跳了 1000/10000/100000实例重启导致预分配的缓存值丢失属设计行为可用 SEQUENCE NO CACHE 降低幅度但事务回滚仍会小跳删掉几行数据后编号出现空洞自增机制从不回填空位正常对外展示可用 ROW_NUMBER() 生成连续显示序号怀疑当前自增值不对可能被显式插入或 DBCC CHECKIDENT 改变用SELECT IDENT_CURRENT(表名)或DBCC CHECKIDENT(表名, NO_RESEED)查看想清空表后从 1 重新开始数据已不需要保留且无外键引用TRUNCATE TABLE 表名有外键时先解除约束或用 DELETE DBCC CHECKIDENT用了 SEQUENCE 还是发现跳号NEXT VALUE FOR 生成值不回滚只是减小重启跳号想彻底跟事务回滚绑定得用编号池方案DBCC CHECKIDENT这个命令我要单独提醒它会把当前值调到你指定的位置但如果你设置的值小于表里已经存在的最大值后续插入的主键有可能与现有行冲突。所以别在正式环境拿它瞎试专门做测试可以生产库上用之前先确认数据特征。5.2 三个我踩过的坑第一个坑存储过程内部COMMIT了导致取号与业务不在同一个事务里。我最初设计取号存储过程时习惯性地在过程末尾写了COMMIT结果业务方法后续插入失败回滚时号码已经被过程提交消耗掉依然跳号。后来才改成“取号过程不提交事务由调用方统一提交”这个问题才算根治。第二个坑UPDLOCK单独使用会在UPDATE执行完后立刻释放锁起不到“保持到事务结束”的效果必须配合HOLDLOCK或者直接用BEGIN TRAN配合更高的隔离级别。我见过有同事只加了UPDLOCK并发测试时两个会话依然能同时取到同一个号后来排查半天发现是锁范围不够。第三个坑老表整理重排序号。以前有个项目要我把一张 20 万行的工单表重排编号我傻乎乎写了段循环用UPDATE一行行按顺序改。结果外键表关联全部错乱夜班折腾了三个小时。后来学乖了编号联动的不仅是当前表还有所有引用它的外键、历史报表、归档记录。真要重排也必须停业务、备份全库、按外键关系逐层更新工作量极大不如保住“作废标记”方案。写到这里把我这些年做数据库项目的体会也一并说了。严格连续的编号是种奢侈品你要么付出并发性能的代价要么付出业务操作受限的代价几乎没有第三种零成本方案。我的习惯是先把“物理连续”和“业务展示连续”分开谈能靠ROW_NUMBER()或者“作废保留”解决的问题不要碰底层编号生成。真有硬性合规要求必须物理连续就用编号表加事务锁那套并且记得把取号逻辑收敛到同一个存储过程或服务里别让所有开发各自写一套。还有一个容易被忽略的细节编号表这一行相当于全局热点数据所有取号都集中在它上面日常巡检要留意锁等待时间和死锁报告必要时针对这个存储过程单独做性能监控别等线上出事了才发现事务锁成了瓶颈。