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);
它的使用条件是:
- 共享变量,也就是version变化的不频繁(没有很多其他进/线程对它操作)。
- before_ver和after_ver之间的操作,也就是ChangeTable要执行的迅速。
这两个条件,当数据库繁忙时,都不适用。
其他方法2:使用SnapShot事务隔离
这个是官方文档的推荐方法,但是因为生产主库不允许做变动,所以没有尝试。仔细看过官方文档后,这个是最优方案。
参考文档: Work with Change Tracking (SQL Server)
这篇文章讨论了使用ChangeTracking可能遇到的不一致问题,并提出了几个解决的方法。
感谢阅读,希望这篇文章能给你带来帮助!
以上就是本文的全部内容,希望本文的内容对大家的学习或者工作能带来一定的帮助,也希望大家多多支持 码农网
猜你喜欢:- acme.sh 续期问题(路径问题)
- 缓存的一些问题和一些加密算法【缓存问题】
- 如何把设计问题转化为数学问题(方法论)
- 推荐系统中的冷启动问题和探索利用问题
- GraphQL 教程(六)—— N+1问题和缓存等问题
- Golang 并发问题(四)之单核上的并发问题
本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们。
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 时间戳转换