您真的了解事务吗

事务用于处理数据的一致性，事务的定义是，处于同一个事务中的操作是一个工作单元，要么全部执行成功，要么全部执行失败。把事务的概念应用到在实际的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，多连接，单一事务模式，不再赘述。

5楼MSSQL123

看起来SSIS玩的很溜

Re: 悦光阴

@MSSQL123 ，在做Package调优时，多看了一些文章，然后自己做了一些research

4楼路虎

赞

3楼WesChen

向大神学习！

Re: 悦光阴

@WesChen，过奖了，不是大神

2楼放开那个妹子

进来膜拜一下

1楼乡里腊肉

厉害，赞