MyException - 我的异常网
当前位置:我的异常网» Sql Server » 您真的了解事务吗

您真的了解事务吗

www.MyException.Cn  网友分享于:2013-08-22  浏览:0次
你真的了解事务吗?

事务用于处理数据的一致性,事务的定义是,处于同一个事务中的操作是一个工作单元,要么全部执行成功,要么全部执行失败。把事务的概念应用到在实际的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楼乡里腊肉
厉害,赞

文章评论

每天工作4小时的程序员
每天工作4小时的程序员
一个程序员的时间管理
一个程序员的时间管理
Google伦敦新总部 犹如星级庄园
Google伦敦新总部 犹如星级庄园
 程序员的样子
程序员的样子
当下全球最炙手可热的八位少年创业者
当下全球最炙手可热的八位少年创业者
那些争议最大的编程观点
那些争议最大的编程观点
旅行,写作,编程
旅行,写作,编程
程序员都该阅读的书
程序员都该阅读的书
程序员的鄙视链
程序员的鄙视链
我的丈夫是个程序员
我的丈夫是个程序员
什么才是优秀的用户界面设计
什么才是优秀的用户界面设计
不懂技术不要对懂技术的人说这很容易实现
不懂技术不要对懂技术的人说这很容易实现
老程序员的下场
老程序员的下场
聊聊HTTPS和SSL/TLS协议
聊聊HTTPS和SSL/TLS协议
Web开发者需具备的8个好习惯
Web开发者需具备的8个好习惯
为啥Android手机总会越用越慢?
为啥Android手机总会越用越慢?
程序员的一天:一寸光阴一寸金
程序员的一天:一寸光阴一寸金
程序员必看的十大电影
程序员必看的十大电影
程序员周末都喜欢做什么?
程序员周末都喜欢做什么?
那些性感的让人尖叫的程序员
那些性感的让人尖叫的程序员
2013年中国软件开发者薪资调查报告
2013年中国软件开发者薪资调查报告
科技史上最臭名昭著的13大罪犯
科技史上最臭名昭著的13大罪犯
我跳槽是因为他们的显示器更大
我跳槽是因为他们的显示器更大
10个帮程序员减压放松的网站
10个帮程序员减压放松的网站
写给自己也写给你 自己到底该何去何从
写给自己也写给你 自己到底该何去何从
如何区分一个程序员是“老手“还是“新手“?
如何区分一个程序员是“老手“还是“新手“?
程序员眼里IE浏览器是什么样的
程序员眼里IE浏览器是什么样的
我是如何打败拖延症的
我是如何打败拖延症的
看13位CEO、创始人和高管如何提高工作效率
看13位CEO、创始人和高管如何提高工作效率
2013年美国开发者薪资调查报告
2013年美国开发者薪资调查报告
代码女神横空出世
代码女神横空出世
漫画:程序员的工作
漫画:程序员的工作
老美怎么看待阿里赴美上市
老美怎么看待阿里赴美上市
10个调试和排错的小建议
10个调试和排错的小建议
亲爱的项目经理,我恨你
亲爱的项目经理,我恨你
程序猿的崛起——Growth Hacker
程序猿的崛起——Growth Hacker
团队中“技术大拿”并非越多越好
团队中“技术大拿”并非越多越好
Web开发人员为什么越来越懒了?
Web开发人员为什么越来越懒了?
程序员应该关注的一些事儿
程序员应该关注的一些事儿
初级 vs 高级开发者 哪个性价比更高?
初级 vs 高级开发者 哪个性价比更高?
“懒”出效率是程序员的美德
“懒”出效率是程序员的美德
做程序猿的老婆应该注意的一些事情
做程序猿的老婆应该注意的一些事情
程序员和编码员之间的区别
程序员和编码员之间的区别
要嫁就嫁程序猿—钱多话少死的早
要嫁就嫁程序猿—钱多话少死的早
编程语言是女人
编程语言是女人
十大编程算法助程序员走上高手之路
十大编程算法助程序员走上高手之路
中美印日四国程序员比较
中美印日四国程序员比较
5款最佳正则表达式编辑调试器
5款最佳正则表达式编辑调试器
60个开发者不容错过的免费资源库
60个开发者不容错过的免费资源库
软件开发程序错误异常ExceptionCopyright © 2009-2015 MyException 版权所有