Transactional replication(事务复制)详解之如何跳过一个事务

在transactional replication, 经常会遇到数据同步延迟的情况。有时候这些延迟是由于在publication中执行了一个更新,例如update ta set col=? Where ?,这个更新包含巨大的数据量。在subscription端,这个更新会分解成多条命令(默认情况下每个数据行一个命令),应用到subscription上。 不得已的情况下,我们需要跳过这个大的事务,让replication继续运行下去。

现在介绍一下transactional replication的一些原理和具体的方法

当publication database的article发生更新时, 会产生相应的日志,Log reader会读取这些日志信息,将他们写入到Distribution 数据库的msrepl_transactions和msrepl_commands中。

Msrepl_transactions中的每一条记录都有一个唯一标识xact_seqno,xact_seqno对应日志中的LSN。 所以可以通过xact_seqno推断出他们在publication database中的生成顺序,编号大的生成时间就晚,编号小的生成时间就早。

Distributionagent包含两个子进程,reader和writer。 Reader负责从Distribution 数据库中读取数据,Writer负责将reader读取的数据写入到订阅数据库.

reader是通过sp_MSget_repl_commands来读取Distribution数据库中(读取Msrepl_transactions表和Msrepl_Commands表)的数据

下面是sp_MSget_repl_commands的参数定义

CREATE PROCEDURE sys.sp_MSget_repl_commands 

( 

@agent_id int, 

@last_xact_seqno varbinary(16), 

@get_count tinyint = 0, -- 0 = no count, 1 = cmd and tran (legacy), 2 = cmd only 

@compatibility_level int = 7000000, 

@subdb_version int = 0, 

@read_query_size int = -1 

)

这个存储过程有6个参数,在Transactional replication 中,只会使用前4个(并且第三个参数和第四个参数的值是固定不变的.分别为0和10000000)。下面是一个例子:

execsp_MSget_repl_commands 46,0x0010630F000002A900EA00000000,0,10000000

@agent_id表示Distributionagentid,每个订阅都会有一个单独的Distributionagent来处理数据。 带入@agent_id后,就可以找到订阅对应的publication 和所有的article。

@last_xact_seqno 表示上一次传递到订阅的LSN。

大致逻辑是:Reader读取subscription database的MSreplication_subscriptions表的transaction_timestamp列,获得更新的上一次LSN编号,然后读取分发数据库中LSN大于这个编号的数据。 Writer将读取到的数据写入订阅,并更新MSreplication_subscriptions表的transaction_timestamp列。然后Reader会继续用新的LSN来读取后续的数据,再传递给Writer,如此往复。

如果我们手工更新transaction_timestamp列,将这个值设置为当前正在执行的大事务的LSN,那么distribution agent就会不读取这个大事务,而是将其跳过了。

下面以一个实例演示一下

环境如下

Publisher: SQL108W2K8R21

Distributor: SQL108W2K8R22

Subscriber: SQL108W2K8R23

图中高亮的publication中包含3个aritcles,ta,tb,tc

其中ta包含18,218,200万数据,然后我们进行了一下操作

在11:00进行了更新语句,

update ta set c=-11

后续陆续对表ta,tb,tc执行一些插入操作

insert tb values(0,0)

insert tc values(0,0)

之后我们启动replication monitor ,发现有很大的延迟,distribution agent一直在传递a)操作产生的数据

在subscription database中执行下面的语句,得到当前最新记录的事务编号

declare @publisher sysname 

declare @publicationDB sysname 

declare @publication sysname 

set @publisher='SQL108W2K8R22' 

set @publicationDB='pubdb' 

set @publication='pubdbtest2'

select transaction_timestamp From MSreplication_subscriptions 

where 

publisher=@publisher and 

publisher_db=@publicationDB and 

publication=@publication

在我的环境中,事务编号为0x0000014900004E9A0004000000000000

返回到distribution database,执行下面的语句,得到紧跟在大事务后面的事务编号. 请将参数替换成您实际环境中的数据。(请注意,如果执行下列语句遇到性能问题,请将参数直接替换成值)

declare @publisher sysname 

declare @publicationDB sysname 

declare @publication sysname 

declare @transaction_timestamp [varbinary](16) 

set @publisher='SQL108W2K8R21' 

set @publicationDB='publicationdb2' 

set @publication='pubtest' 

set @transaction_timestamp= 0x0000014900004E9A0004000000000000

select top 1 xact_seqno from MSrepl_commands with (nolock) where xact_seqno>@transaction_timestamp and 

article_id in ( 

  select article_id From MSarticles a inner join MSpublications p on a.publication_id=p.publication_id and a.publisher_id=p.publisher_id and a.publisher_db=p.publisher_db 

  inner join sys.servers s on s.server_id=p.publisher_id 

  where p.publication=@publication and p.publisher_db=@publicationDB and s.name=@publisher 

) 

and publisher_database_id =( 

    select id From MSpublisher_databases pd inner join MSpublications p on pd.publisher_id=p.publisher_id 

    inner join sys.servers s on pd.publisher_id=s.server_id and pd.publisher_db=p.publisher_db 

    where s.name=@publisher and p.publication=@publication and pd.publisher_db=@publicationDB 

) 

Order by xact_seqno

在我的环境中,事务编号为0x0000018C000001000171

在subscription database中执行下面的语句,跳过大的事务。请将参数替换成您实际环境中的数据

declare @publisher sysname

declare @publicationDB sysname 

declare @publication sysname 

declare @transaction_timestamp [varbinary](16) 

set @publisher='SQL108W2K8R22' 

set @publicationDB='pubdb' 

set @publication='pubdbtest2' 

set @transaction_timestamp= 0x0000018C000001000171

update MSreplication_subscriptions set transaction_timestamp=@transaction_timestamp 

where publisher=@publisher and publisher_db=@publicationDB and publication=@publication

执行完成后开启distribution agent job即可。

接下来您就会发现,事务已经成功跳过,ta在订阅端不会被更新,后续的更新会逐步传递到订阅,延迟消失。

(0)

相关推荐

  • c#实现sqlserver事务处理示例

    复制代码 代码如下: private static void ExecuteSqlTransaction(string connectionString)    {        using (SqlConnection connection = new SqlConnection(connectionString))        {            connection.Open();            SqlCommand command = connection.CreateC

  • 使用Sqlserver事务发布实现数据同步(sql2008)

    事务的功能在sqlserver中由来已久,因为最近在做一个数据同步方案,所以有机会再次研究一下它以及快照等,发现还是有很多不错的功能和改进的.这里以sqlserver2008的事务发布功能为例,对发布订阅的方式简要介绍一下操作流程,一方面做个总结备份,一方面与大家进行一下分享和交流.费话就不多说了,进入一下今天的正题:) 这里要说明一下环境:首先我在本地局域网内有两台安装有sqlserver2008的机器(注意:已发布的快照版本无法向老版本数据库兼容,意味着2008下创建的事务或快照发布,无法被

  • MSSQL 事务说明

    1.什么是事务:事务是一个不可分割的工作逻辑单元,在数据库系统上执行并发操作时事务是做为最小的控制单元来使用的.他包含的所有数据库操作命令作为一个整体一起向系提交或撤消,这一组数据库操作命令要么都执行,要么都不执行. 2.事务的语句 开始事物:BEGIN TRANSACTION 提交事物:COMMIT TRANSACTION 回滚事务:ROLLBACK TRANSACTION 3.事务的4个属性 ①原子性(Atomicity):事务中的所有元素作为一个整体提交或回滚,事务的个元素是不可分的,事务

  • MSSQL事务的存储过程

    在酒店管理系统开发中,我们会创建房间表和房间类型表(房型表)这两个表,如下图所示: 房型表:RoomType             房间表:Room 首先这两个表的关系:Room是从表,RoomType是主表,两表有主外键关系,RoomType.rTypeId=Room.rTypeId 分析这两个表,我们会发现这样一个问题:在房间表和房型表管理中,当我们修改或者删除一个RoomType表,对应的Room表我们也要做修改和删除,那么我们怎么解决这个问题呢?首先可以在数据库中设置级联删除,或者利用

  • Sqlserver 存储过程中结合事务的代码

    复制代码 代码如下: --方式一 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[USP_ProcedureWithTransaction_Demo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[USP_ProcedureWithTransaction_Demo] GO -- ===================

  • SQLServer分布式事务问题

    一. 问题现象 在执行 SQL Server分布式事务时,在SQL Server 2005下收到如下错误: 消息 7391,级别 16,状态 2,过程 xxxxx,第 16 行 无法执行该操作,因为链接服务器 "xxxxx" 的 OLE DB 访问接口 "SQLNCLI" 无法启动分布式事务. 在SQL Server 2000下收到如下错误: 该操作未能执行,因为 OLE DB 提供程序 'SQLOLEDB' 无法启动分布式事务. [OLE/DB provider

  • Transactional replication(事务复制)详解之如何跳过一个事务

    在transactional replication, 经常会遇到数据同步延迟的情况.有时候这些延迟是由于在publication中执行了一个更新,例如update ta set col=? Where ?,这个更新包含巨大的数据量.在subscription端,这个更新会分解成多条命令(默认情况下每个数据行一个命令),应用到subscription上. 不得已的情况下,我们需要跳过这个大的事务,让replication继续运行下去. 现在介绍一下transactional replicatio

  • MySQL 复制详解及简单实例

    MySQL 复制详解及简单实例 主从复制技术在MySQL中被广泛使用,主要用于同步一台服务器上的数据至多台从服务器,可以用于实现负载均衡,高可用和故障切换,以及提供备份等等.MySQL支持多种不同的复制技术,诸如单向,半同步异步复制等以及不同级别的复制,诸如数据库级别,表级,跨库同步等等.本文简要描述了一个基本的主从复制并给出示例. 1.复制的基本原理(步骤) a.在主库上把数据更改记录的二进制日志(binary log)     b.从库上的I/O线程连接到主库并请求发送其二进制日志文件(主库

  • Spring Boot事务配置详解

    1.在启动主类添加注解:@EnableTransactionManagement 来启用注解式事务管理,相当于之前在xml中配置的<tx:annotation-driven />注解驱动. 2.在需要事务的类或者方法上面添加@Transactional() 注解,里面可以配置需要的粒度: 这么多东西提供配置: Isolation :隔离级别 隔离级别是指若干个并发的事务之间的隔离程度,与我们开发时候主要相关的场景包括:脏读取.重复读.幻读. 我们可以看 org.springframework.

  • SpringCloud微服务开发基于RocketMQ实现分布式事务管理详解

    目录 消息队列实现分布式事务原理 RocketMQ的事务消息 代码实现 基础配置 发送半消息 执行本地事务与回查 Account-Service消费消息 测试 小结 消息队列实现分布式事务原理 首先让我们来看一下基于消息队列实现分布式事务的原理方案. 柔性事务 发送消息的服务有个OUTBOX数据表,在进行INSERT.UPDATE.DELETE 业务操作时也会给OUTBOX数据表INSERT一条消息记录,这样可以保证原子性,因为这是基于本地的ACID事务. OUTBOX表充当临时消息队列,然后我

  • C++对象的浅复制和深复制详解及简单实例

    C++对象的浅复制和深复制详解及简单实例 浅复制:两个对象复制完成后共享某些资源(内存),其中一个对象的销毁会影响另一个对象 深复制:两个对象复制完成后不会共享任何资源,其中一个对象的销毁不会影响另一个对象 下面我们来看一段代码,以便直观的理解: #include<iostream> #include<string.h> using namespace std; class Student { int no; char *pname; public: Student(); Stud

  • mysql的存储过程、游标 、事务实例详解

    mysql的存储过程.游标 .事务实例详解 下面是自己曾经编写过的mysql数据库存储过程,留作存档,以后用到的时候拿来参考. 其中,涉及到了存储过程.游标(双层循环).事务. [说明]:代码中的注释只针对当时业务而言,无须理会. 代码如下: DELIMITER $$ DROP PROCEDURE IF EXISTS `transferEmailTempData`$$ CREATE PROCEDURE transferEmailTempData(IN jobId VARCHAR(24)) BEG

  • JSP 中spring事务配置详解

    JSP 中spring事务配置详解 前几天被问到,如何防止服务器宕机,造成的数据操作的不完全. 问了一下同事,是事务.哎,恍然大悟,迷糊一时了. 声明式的事务配置,这个是最推荐的,配置到service层. <?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:context=&

  • LCN分布式事务解决方案详解

    目录 一.什么是分布式事务? 二.lcn的实现思路 2.1 本地执行的状态怎么提交给全局事务? 2.2 本地事务的提交或回滚怎么实现? 三.lcn的使用 3.1 下载lcn-manager (全局的事务管理器) 3.2 配置lcn-manager 3.3 启动lcn 3.4 模拟转账服务 3.4.1 add-service 3.4.2 decr-service 3.5 2 个微服务都需要添加依赖 3.6 需要自定义数据库的连接池 3.7 使用 3.7.1 事务的发起者 3.7.2 添加配置文件

  • Java复杂链表的复制详解

    目录 1.题目 2.解法 2.1 拼接+拆分 3.代码 1.题目 请实现 copyRandomList 函数,复制一个复杂链表.在复杂链表中,每个节点除了有一个 next 指针指向下一个节点,还有一个 random 指针指向链表中的任意节点或者 null. 题目来源:力扣(LeetCode) 链接:https://leetcode-cn.com/problems/fu-za-lian-biao-de-fu-zhi-lcof 2.解法 2.1 拼接+拆分 首先我们逐个将节点复制并且和原来的链表连起

  • 详解如何利用GORM实现MySQL事务

    目录 前言 禁用默认事务 自动事务 手动事务 嵌套事务 SavePoint.RollbackTo 小结 前言 为了确保数据一致性,在项目中会经常用到事务处理,回滚操作还是比较常见的需求:事务处理可以用来维护数据库的完整性,保证成批的sql语句要么全部都执行,要么全不执行,对于MySQL事务相信大家应该都不陌生,这篇文章主要总结一下在Go语言中Gorm是如何实现事务的:感兴趣的小伙伴们可以参考借鉴,希望对大家能有所帮助. 禁用默认事务 gorm事务默认是开启的.为了确保数据一致性,Gorm会在事务

随机推荐