SSIS 你真的了解事务吗?

事务用于处理数据的一致性,事务的定义是,处于同一个事务中的操作是一个工作单元,要么全部执行成功,要么全部执行失败。把事务的概念应用到在实际的SSIS Package场景中,如何在Package中实现事务,事务的行为是什么样的,你真的了解吗?

SSIS默认支持Task组件级别的事务,在默认情况下,单个Task组件在开始执行时,会打开连接,开启一个事务,等到Task组件执行完成,提交事务,关闭连接,也就是说,默认情况下,单个Task组件在单个事务中执行查询,因此,在单个Execute SQL Task组件中执行大量的TSQL脚本,不是明智的选择,因为,这会导致日志文件的激增。如果Task组件执行失败,SSIS引擎自动进行回滚Task级别上的事务。如果在Task中使用begin tran命令开启一个显式事务,必须在组件中显式提交事务;当执行显式事务的组件失败时,组件会回滚显式事务。SSIS支持更复杂的事务处理,包括单一连接的本机事务,和跨连接的分布式事务处理。

一,SSIS支持的事务

在SSIS Package中,按照事务的分布性,可以把事务分为两种类型:

  • 分布式事务:通过分布式事务协调器(DTC,Distributed Transaction Coordinator) 实现跨连接,Task和Package的事务处理;
  • 本机事务:是SQL Server引擎级别事务,通过TSQL事务命令管理的单一连接的事务处理;

事务处理,都依赖连接管理器的支持。让Package使用本机事务的关键是所有的任务组件都使用相同的连接管理器,并且连接管理器(Connection Manager)上的属性RetainSameConnection设置为True:

让Package使用分布式事务的关键是所有的任务组件使用的连接管理器都支持DTC事务,并且连接管理器的SupportsDTCTransactions属性值都为True:

二,单个Task组件的事务处理

最常用的Task组件是Execute SQL Task组件,在该组件中执行的TSQL脚本处于同一个事务中。在该Task组件执行时,打开连接,开启一个事务,直到所有的TSQL脚本都成功执行,组件执行成功;一旦该Task中的某个TSQL脚本执行失败,事务回滚,这意味着,该Task中的所有已经执行的TSQL脚本都将回滚。因此,在单个Execute SQL Task组件中执行大量的TSQL脚本,不是明智的选择,因为,这会导致日志文件的激增。

创建测试表,测试表只有一列:

create table dbo.dt_test
(ID int)

在Execute SQL Task组件中执行以下语句:

insert into dbo.dt_test
values(1)

insert into dbo.dt_test
values('a')

Task组件执行失败,从数据库中查看,测试表中没有插入任何数据,这说明,单个Task中的所有TSQL语句都包含在单个事务中。

三,本机事务处理(多Task组件,单一连接,单一事务)

在SSIS的任务和容器组件中,很多操作都需要连接到数据库执行查询,使用本机事务处理的关键是,所有的任务组件都使用相同的连接管理器,并且设置连接管理的属性RetainSameConnection为True,其默认值是False。

如果连接管理器的属性RetainSameConnection值是False,那么每个Task组件在开始执行时,打开连接,在组件结束时,关闭连接。在组件执行结束时,如果存在未提交的事务,那么组件会自动回滚Task组件的TSQL查询语句。由于每个组件都会打开和关闭连接,即使两个组件,使用的是同一个连接管理器,它们使用的连接都是不同的。

  • 案例1:有两个组件,在一个组件中创建一个临时表或临时变量,在另一个组件中是不能使用的,这是因为在第一个组件结束时,连接也被关闭,临时表或临时变量的生命周期结束。
  • 案例2:在循环任务中连接数据库时,设置RetainSameConnection值是True,能够避免频繁地打开/关闭连接。在Package开始执行时,打开连接,package结束时,关闭连接,保证所有task组件使用的都是同一个连接。

如果连接管理器的属性RetainSameConnection值是true,那么连接管理器会保持打开,直到Package结束,连接才会关闭。在连接关闭时,SSIS引擎会检查连接中是否存在未提交的事务,如果存在,执行事务回滚。

  • 案例3:有两个组件,使用的是同一个连接管理器,属性RetainSameConnection值是true。在一个组件中创建一个临时表或临时变量,在另一个组件中可以使用,这是因为在第一个组件结束时,连接没有被关闭,两个组件使用的是同一个连接,临时表或临时变量的生命周期会持续到Package结束。
  • 案例4:将连接管理器的属性RetainSameConnection设置为true,在上游组件中开启事务,在下游组件中提交事务,实现本机事务处理。

示例,利用TSQL命令(begin/commit/rollback tran)实现事务的提交或回滚

Package的设计如下图所示:

Exec TSQL组件执行的TSQL语句是:dbo.dt_test只有一列 ID,是int类型,该组件会执行失败。

insert into dbo.dt_test
values('a')

case1,设置连接管理器的RetainSameConnection属性值为False,rollback tran组件执行报错

从Progess选项卡中,查看Exec TSQL组件抛出的错误消息是:

insert into dbo..." failed with the following error: "Conversion failed when converting the varchar value 'a' to data type int.".

rollback tran 组件抛出的错误消息是:

[Execute SQL Task] Error: Executing the query "rollback tran" failed with the following error: "The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.". 

分析:由于连接管理器的属性RetainSameConnection为False,每个Task组件都是单独打开和关闭连接,begin tran组件已经把显式事务提交,rollback tran组件没有begin tran子句,无法执行事务回滚。

case2,设置连接管理器的RetainSameConnection属性值为True

1,设置连接管理器的RetainSameConnection属性值为True,rollback tran组件执行报错

把连接管理器的属性RetainSameConnection设置为True,使所有的Task组件使用的连接都是相同的。再次执行Package,还是失败,错误原因是:

[Execute SQL Task] Error: Executing the query "rollback tran" failed with the following error: "The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.".

这是由于Execute SQL Task 在失败时,SSIS引擎自动进行事务的回滚。

这个结论可以通过增加一个Insert 1 组件来实现,向测试表中插入数值1,代码如下:

insert into dbo.dt_test
values(1)

在Exec TSQL组件上添加OnPostExecute断点,在Task执行之后,抛出错误之前,停止程序的运行:

重新执行Package,在执行到断点时,测试表已经插入的数值1 被删除,这说明,Exec TSQL组件的失败,会导致整个事务自动回滚。

结论1:当Execute SQL Task执行失败时,SSIS自动进行事务的回滚,但是当Execute SQL Task执行成功时,不会自动提交显式事务;开启一个显式事务,必须显式提交事务。

2,开启一个显式事务,需要显式提交事务

把Exec TSQL执行的语句修改插入数值2,这样,组件执行成功:

insert into dbo.dt_test
values(2)

如果把commit tran组件禁用,Package执行成功,但是测试表中没有插入任何数据,这说明,Package执行完成之后,连接管理器检测到有未提交的事务,自动把未提交的事务回滚。下图所示,事务并没有提交成功,而是被回滚。

结论2:开启一个显式事务,需要显式提交事务。当连接关闭时,SSIS会回滚未提交的显式事务。SSIS会检查每一个连接内是否存在未提交的显式事务,如果存在,那么回滚该事务;对于隐式事务,当TSQL语句执行结束时,会自动提交或回滚。

四,分布式事务处理(多Task组件,多连接,单一事务)

本机事务只能使用单一连接,在同一个连接中通过TSQL命令执行事务处理,不能实现跨连接,不能跨数据库的事务,由于SSIS经常需要处理多个数据库的数据,本机事务无法实现跨数据库的事务处理,用户可以通过MS DTC(微软分布式事务服务)实现分布式事务处理。

在SSIS 引擎服务器上启用MS DTC服务,并在Package的Task组件上设置相应的TransactionOption,就能使用分布式事务。

每个可执行组件(Task或Container)都包含Transactions属性组,SSIS通过这两个属性实现事务处理:

  • IsoLationLevel:设置事务的隔离级别;
  • TransactionOption:设置事务选项;
    • Supported:如果已经存在一个事务,那么当前组件加入到事务中;
    • Not Supported:即使存在一个事务,当前组件也不会加入到事务中;
    • Requried:如果存在事务,那么当前组件加入到事务中;如果不存在事务,那么启动一个事务。

案例:两个Task,一个事务

在单一Package的不同Task组件中引用分布式事务,简化的设计如下图,两个Task组件使用不同的连接管理器:

设置Required组件的TransactionOption属性为Required,开启一个分布式事务处理:

设置Supported组件的TransactionOption属性为Supported,加入到当前的事务中,这就意味着,一个事务就包含两个Task,两个连接:

这样设置之后,在同一个Package的不同的Task组件中,一个跨连接的分布式事务处理建立完成。

分布式事务处理,还支持多Package,多连接,单一事务模式,不再赘述。

posted @ 2017-08-16 10:00  悦光阴  阅读(2589)  评论(8编辑  收藏  举报