首页 新闻 会员 周边 捐助

SQL Server 主备切换后自增Id发飙猛增的问题

0
悬赏园豆:100 [已解决问题] 解决于 2024-01-21 07:24

园子的会员号(VipId)是通过 SQL Server 数据库表中的自增Id字段生成的,发现一个奇怪问题,每次主备切换后,自增Id字段如吃了兴奋剂般发飙猛增,比如今天就从3581狂飙至4581,请问如何避免出现这个问题?

dudu的主页 dudu | 高人七级 | 园豆:30357
提问于:2024-01-18 13:49

数据库恢复模式:如果你的数据库采用了完整恢复模式,主备切换时可能涉及到事务日志的复制和应用,这可能会导致自增ID的值发生变化。在这种情况下,数据库引擎可能会重新计算标识列的起始值。

数据库复制延迟:如果使用数据库镜像或者Always On可用性组,可能存在一些复制延迟。在主备切换后,备份数据库的自增ID可能会“追赶”主数据库。

echo_lovely 10个月前
< >
分享
最佳答案
0

多切换几次, 园子的vip数据就遥遥领先拉.

chatgpt回复的

以下是一些可能导致 gap 的原因:

事务回滚: 如果主服务器上的事务在切换过程中回滚,可能会导致备用服务器上的自增 ID 产生间隔。

镜像和 Always On 切换时的延迟: 在数据库镜像或者 Always On 可用性组中,主服务器和备用服务器之间可能存在一些数据复制的延迟。如果在主服务器上插入了新的记录并在备用服务器上发生了切换,那么备用服务器上的自增 ID 可能会有一些间隔。

非同步提交: 如果配置了非同步提交,主服务器上的提交操作可能不会立即传播到备用服务器,从而导致备用服务器上的自增 ID 产生间隔。

缓存和批量操作: SQL Server 在执行批量插入或者使用标识列时可能会对自增 ID 进行缓存。在切换时,未提交的缓存可能导致间隔。
收获园豆:100
czd890 | 专家六级 |园豆:14488 | 2024-01-18 15:35

顺着回答在搜索一下
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).

https://learn.microsoft.com/zh-cn/sql/t-sql/statements/create-table-transact-sql-identity-property?view=sql-server-ver15#remarks

服务器重新启动或出现其他故障后的连续值 - SQL Server 可能出于性能原因而缓存标识值,在数据库故障或服务器重新启动期间,一些分配的值可能丢失。 这可能导致在插入时标识值之间有间隔。 如果不允许有间隔,应用程序应使用自己的机制来生成键值。 将序列生成器与 NOCACHE 选项结合使用可以限制从未提交的事务的间隔。
czd890 | 园豆:14488 (专家六级) | 2024-01-18 15:35

@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

dudu | 园豆:30357 (高人七级) | 2024-01-21 07:24
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册