MSSQL ChangeTracking的不一致问题

栏目: 数据库 · SQL Server · 发布时间: 5年前

内容简介:在使用Change Tracking进行同步的过程中,当对数据量比较大的表进行同步时,如果处理的不妥当,很可能会出现不一致的问题:重复插入、更新丢失。造成这些问题的主要原因在于:这篇文章记录下某些问题的重现步骤及可能的解决方案。假设我们将从主库同步数据到从库,5分钟同步一次,初始状态数据和版本都是一致的(假设为1)。开始遇到这个问题,解决方案似乎简单:丢弃重复的插入。但这会引发另一个问题:更新丢失。

在使用Change Tracking进行同步的过程中,当对数据量比较大的表进行同步时,如果处理的不妥当,很可能会出现不一致的问题:重复插入、更新丢失。造成这些问题的主要原因在于:

  • 获取版本差异的函数 CHANGETABLE(last_ver),只能传入最近一次(上一次)同步的版本号,然后返回上次版本到当前版本的差异。而不能指定两个版本号:上次版本 和 某一特定版本,即不能CHANGTABLE(last_ver, spec_ver)。
  • 当前版本是快速变化的,即函数CHANGE_TRACKING_CURRENT_VERSION()的返回值是快速变化的。它是针对整个数据库,而不是针对单表。
  • 调用CHANGETABLE()和CHANGE_TRACKING_CURRENT_VERSION()之间存在操作的时间差。换言之,假设调用CHANGE_TRACKING_CURRENT_VERSION()返回的当前版本是100,接下来紧接着就调用CHANGETABLE(50),并不代表返回的就是版本50-100的差异结果集,有可能是50-105的,因为最近版本可能已经发生了变化。

这篇文章记录下某些问题的重现步骤及可能的解决方案。假设我们将从主库同步数据到从库,5分钟同步一次,初始状态数据和版本都是一致的(假设为1)。

重复插入问题

从库 主库
操作 版本 操作 版本
等待5分钟(同步周期)...
取本地版本1 1 - 1
- 1 insert 记录A 2
取主库版本2 CHANGE_TRACKING_CURRENT_VERSION() 1 - 2
将主库版本保存为本地版本
(作为下次同步时的本地版本)
2 - 2
- 2 insert 记录B 3
调用CHANGETABLE(1), 预期获得的是版本1-2的差异,实际获得的是版本1-3的差异. 2 - 3
执行同步,insert 记录A、B 2 - 3
等待5分钟(同步周期)...
取本地版本2 2 - 3
取主库版本3 CHANGE_TRACKING_CURRENT_VERSION() 2 - 3
将主库版本保存为本地版本
(作为下次同步时的本地版本)
3 - 3
调用CHANGETABLE(2), 获得版本2-3的差异(新增了记录B) 3 - 3
执行同步,insert 记录B,因为B已经存在,因此会抛出异常 3 - 3

开始遇到这个问题,解决方案似乎简单:丢弃重复的插入。但这会引发另一个问题:更新丢失。

更新丢失

因为主库可能对记录B进行更新,如果简单地将重复记录丢弃,就会引发更新丢失:

从库 主库
操作 版本 操作 版本
等待5分钟(同步周期)...
取本地版本1 1 - 1
- 1 insert 记录A 2
取主库版本2 CHANGE_TRACKING_CURRENT_VERSION() 1 - 2
将主库版本保存为本地版本
(作为下次同步时的本地版本)
2 - 2
- 2 insert 记录B 3
调用CHANGETABLE(1), 预期获得的是版本1-2的差异,实际获得的是版本1-3的差异(新增了记录A、B). 2 - 3
执行同步,insert 记录A、B 2 - 3
- 2 update 记录B 4
等待5分钟(同步周期)...
取本地版本2 2 - 4
取主库版本4 CHANGE_TRACKING_CURRENT_VERSION() 2 - 4
将主库版本保存为本地版本
(作为下次同步时的本地版本)
4 - 4
调用CHANGETABLE(2), 获得版本2-4的差异(新增了记录B) 4 - 4
执行同步,insert 记录B,因为B已经存在,因此会抛出异常,丢弃更新,则失掉了对B的更新 4 - 4

对于上面的情况,几乎可以立即想到:当插入存在重复时,就对该记录执行update。

这个方案是 官方文档 中对不一致问题的第2种处理方式。还有一种方式,就是利用SYS_CHANGE_VERSION,在调用CHANGETABLE获取到变更表格后,跳过所有SYS_CHANGE_VERSION 大于通过CHANGE_TRACKING_CURRENT_VERSION()获得的主库版本的记录。

这个方法我没做尝试,感兴趣的同学可以试下。按官方文档的说法,这个方法会造成参照完整性的问题:如果插入了一个新行,它的SYS_CHANGE_VERSION大于当前主库版本,那么将在下次更新时同步。而另一张表的外键引用了它,并且其记录在本次更新时同步,那么就会引发参照完整性问题。

这个解决方案我觉得是从库去掉参照完整性约束,然后只读。

使用自增主键进行新增带来的不一致问题

因为ChangeTracking存在变更记录的保存时长问题(默认是2天),所以如果同步程序因为某些原因中断了2天以上,那么再次开启时,就会丢失部分数据。而因为很多表是有自增字段的,所以就想到新增的数据依据自增字段来新增(查询从库最后一个自增ID:lastId,从主库取自增ID大于lastId的值,然后插入从库),依据ChangeTracking来做更新和删除。也就是说,不处理CHANGETABLE()函数返回表中,SYS_CHANGE_OPERATION为“I”的行。

这样会产生一个隐藏BUG,从而造成不一致的问题:更新丢失。

从库 主库
操作 版本 操作 版本
等待5分钟(同步周期)...
取本地版本1 1 - 1
- 1 insert 记录A 2
insert 记录A (基于自增id) 1 - 2
- 1 update 记录A 3
取主库版本3 CHANGE_TRACKING_CURRENT_VERSION() 1 - 3
将主库版本保存为本地版本
(作为下次同步时的本地版本)
3 - 3
调用CHANGETABLE(1),获得版本1-3的差异。
新增了记录A,因为插入操作通过自增id的模式处理,因此跳过
3 - 3

可以看到,在使用这种方式时,丢失掉了对记录A的更新操作。

因此,如果为表开启了 Change Tracking,那么增删改都要使用Change Tracking的机制来完成。如果应用程序中断超过“保鲜期”,那么可以将中断时期内的数据通过自增ID的方式新增过去。

因为查询时间过近导致的“不一致”问题

这种情况下,数据并没有不一致,只是因为查询的时间离当下时间过近,造成数据“看上去”不一致。其实是因为从库还没有来得及更新。

假设更新一个user_play_log表,用来记录玩家的游戏登录/退出。里面含有一个login_time和logout_time。每次玩家进入游戏insert一条记录,且login_time为当前时间,logout_time为null;退出游戏时,logout_time更新为当前时间。

假设5分钟同步一次,当前时间是早上8点23分,我们查询凌晨0点到早上8点的记录。 直观上,我们认为上一次同步时间是在8点20,那么查询0点到8点的记录应该是一致的(简单起见,认为同步可以瞬间在1秒内完成)。

其实并非如此:

时间 操作 从库 主库
7:12 玩家登录 - login_time: 7:12, logout_time: null
7:15 数据同步 login_time: 7:12, logout_time: null login_time: 7:12, logout_time: null
... 期间数据无变化
8:20 数据同步 login_time: 7:12, logout_time: null login_time: 7:12, logout_time: null
8:21 玩家退出 login_time: 7:12, logout_time: null login_time: 7:12, logout_time: 8:21
8:23 查询0点-8点数据
发现数据不一致
login_time: 7:12, logout_time: null ogin_time: 7:12, logout_time: 8:21
8:25 数据同步 login_time: 7:12, logout_time: 8:21 login_time: 7:12, logout_time: 8:21

当数据量大的时候,会存在大量的这种情况,会造成每次对比都发现数据不一致。实际只要将查询时间往前推的早一些,就能看到数据是一致的。

其他方法1:强制一致性

可以采用下面的方法来保证数据一致,这个方法有时也在分布式环境下取代事务。

在取差异数据前取一下主库版本,取数据后再取一下主库版本,如果两个版本一致,则处理差异数据;否则重新取数据。

这个方法在实际生产环境中很难使用,因为CHANGE_TRACKING_CURRENT_VERSION()在数据库中是全局的,而不是基于某个表的,因此当数据库繁忙时,会出现一直取不到一致数据的情况(before_ver和after_ver总是不相等):

这个方法的伪代码如下:

long before_ver = 0;
long after_ver = 0;
DataTabe data = null;

do{
    before_ver = CHANGE_TRACKING_CURRENT_VERSION();
    data = ChangeTable(table_name, last_ver);
    after_ver = CHANGE_TRACKING_CURRENT_VERSION();
}while(before_ver != after_ver);

handleData(data);

它的使用条件是:

  1. 共享变量,也就是version变化的不频繁(没有很多其他进/线程对它操作)。
  2. before_ver和after_ver之间的操作,也就是ChangeTable要执行的迅速。

这两个条件,当数据库繁忙时,都不适用。

其他方法2:使用SnapShot事务隔离

这个是官方文档的推荐方法,但是因为生产主库不允许做变动,所以没有尝试。仔细看过官方文档后,这个是最优方案。

参考文档: Work with Change Tracking (SQL Server)

这篇文章讨论了使用ChangeTracking可能遇到的不一致问题,并提出了几个解决的方法。

感谢阅读,希望这篇文章能给你带来帮助!

MSSQL ChangeTracking的不一致问题


以上就是本文的全部内容,希望本文的内容对大家的学习或者工作能带来一定的帮助,也希望大家多多支持 码农网

查看所有标签

猜你喜欢:

本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们

Hello World

Hello World

Hannah Fry / W. W. Norton Company / 2018-9 / GBP 17.99

A look inside the algorithms that are shaping our lives and the dilemmas they bring with them. If you were accused of a crime, who would you rather decide your sentence—a mathematically consistent ......一起来看看 《Hello World》 这本书的介绍吧!

随机密码生成器
随机密码生成器

多种字符组合密码

UNIX 时间戳转换
UNIX 时间戳转换

UNIX 时间戳转换