SQL Server 主备切换后自增Id发飙猛增的问题
0
悬赏园豆：100
[已解决问题]
解决于 2024-01-21 07:24
园子的会员号(VipId)是通过 SQL Server 数据库表中的自增Id字段生成的，发现一个奇怪问题，每次主备切换后，自增Id字段如吃了兴奋剂般发飙猛增，比如今天就从3581狂飙至4581，请问如何避免出现这个问题？
最佳答案
0
多切换几次, 园子的vip数据就遥遥领先拉.
chatgpt回复的
以下是一些可能导致 gap 的原因：
事务回滚： 如果主服务器上的事务在切换过程中回滚，可能会导致备用服务器上的自增 ID 产生间隔。
镜像和 Always On 切换时的延迟： 在数据库镜像或者 Always On 可用性组中，主服务器和备用服务器之间可能存在一些数据复制的延迟。如果在主服务器上插入了新的记录并在备用服务器上发生了切换，那么备用服务器上的自增 ID 可能会有一些间隔。
非同步提交： 如果配置了非同步提交，主服务器上的提交操作可能不会立即传播到备用服务器，从而导致备用服务器上的自增 ID 产生间隔。
缓存和批量操作： SQL Server 在执行批量插入或者使用标识列时可能会对自增 ID 进行缓存。在切换时，未提交的缓存可能导致间隔。
收获园豆：100
顺着回答在搜索一下
https://stackoverflow.com/questions/14146148/identity-increment-is-jumping-in-sql-server-database
4581 - 3581 = 1000 基本吻合
You are encountering this behaviour due to a performance improvement since SQL Server 2012.
It now by default uses a cache size of 1,000 when allocating IDENTITY values for an int column and restarting the service can "lose" unused values (The cache size is 10,000 for bigint/numeric).
服务器重新启动或出现其他故障后的连续值 - SQL Server 可能出于性能原因而缓存标识值，在数据库故障或服务器重新启动期间，一些分配的值可能丢失。 这可能导致在插入时标识值之间有间隔。 如果不允许有间隔，应用程序应使用自己的机制来生成键值。 将序列生成器与 NOCACHE 选项结合使用可以限制从未提交的事务的间隔。
@czd890: 就在你提到的这个 stackoverflow 提问 Identity increment is jumping in SQL Server database 的一个回答中提供了很简单的解决方案：
ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE=OFF
但需要 SQL Server 2017+，我们现在使用的是 SQL Server 2016，准备春节期间升级至 SQL Server 2022
数据库恢复模式：如果你的数据库采用了完整恢复模式，主备切换时可能涉及到事务日志的复制和应用，这可能会导致自增ID的值发生变化。在这种情况下，数据库引擎可能会重新计算标识列的起始值。
数据库复制延迟：如果使用数据库镜像或者Always On可用性组，可能存在一些复制延迟。在主备切换后，备份数据库的自增ID可能会“追赶”主数据库。
– echo_lovely 3个月前