SQL点滴9—SQL Server中的事务处理以及SSIS中的内建事务

我们可以把SSIS中的整个package包含在一个事务中，但是如果在package的执行过程中有一个表需要锁定应该怎么处理呢？SSIS内建的事务处理可以解决这个问题。在此之前首先来熟悉一下SQL Server中的事务的概念。

事务

SQL Server中的事务是单个的工作单元。如果某一事务成功，则在该事务中进行的所有数据修改均会提交，成为数据库中永久的组成部分。如果事务遇到错误且必须取消或回滚，则所有的数据修改均被清除。

在SQL Server中使用事务有可能会造成一些预想不到的结果，具体来说有脏读，不可重复读和幻读三种结果。

• 脏读：脏读是指当一个事务正在访问数据库，并且对数据进行修改，而这种修改还没有提交到数据库中，另外一个事务也在访问这个数据，然后使用了这个数据。
• 不可重复读：在一个事物内多次读同一数据。在这个事务还没有结束时，另外一个事物也在访问该同一数据，那么在第一个事务两次读取之间，由于第二个事务的修改，第一个事务两次读取到的数据可能不一样。这样就发生了在一个事务内两次读取到的数据不一样，因此称为不可重复读。
• 幻读：幻读是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行修改，这种修改设计到表中的全部数据行。同时第二个事务也修改这个表中的数据，这个修改时向同一个表中插入一行新数据，这样第一个事务的用户发现还有一条数据没有修改，像发生了幻觉一样，因此称为幻读

在SQL Server中给事务指定一个隔离级别，这个隔离级别定义该事务与其他事务进行资源或数据更改相隔离的级别。事务隔离级别决定了是否锁定SQL Server对象，下面是SQL Server中的事务隔离级别。

• Rdad Uncommitted： 读取数据不需要等待解锁，这种方式会读到脏数据，因为读取的数据有可能是还没有更新的数据。这种隔离级别最低，会造成脏读，不可重复读和幻读的结果，并发性最高。
• Read Committed： 读取数据需要等待解锁，这样会读取到最新的被更新的数据。Read Committed不会造成脏读的问题，但是会造成不可重复的和幻读的问题。Read Committed是SQL Server的默认设置。
• Repeatable Read： 与Read Committed类似，它会锁定所读取的所有行，但是没有其他的连接可以更新或插入数据，这样如果select语句可能选择到这条新跟新或插入的数据，这条数据记录是不会出现在select结果中的。同时被选择出的数据也不能被其他连接更改，直到读取动作执行结束或者回滚结束。这种隔离级别不会造成脏读和不可重复读，但是会造成幻读。
• Serializable：和Repeatable Read类似，不过没有其他的连接可以插入或更新数据，同时如果在下次查询中任然使用这种事务隔离级别，你会得到相同的查询结果，就是说更新或新插入的数据任然不会出现在查询结果中。这种隔离级别不会造成脏读，不可重复读或幻读。

还有两种是SQL Server 2005中新添加的事务隔离级别

• 一种Read Committed级别的变异，当你把数据库的隔离级别设置成READ_COMMITTED_SNAPHOT，任何使用Read Committed级别的的事务不再需要锁定数据对象。执行语句时会得到select语句开始执行之时会得到所有最新的结果。
• SNAPSHOT：一种全新的级别SNAPSHOT，当你在任何数据库对象中设置事务的隔离级别为ALLOW_SNAPSHOT_ISOLATION时，其他事务都不会遇到共享锁，查询结果会得到所有更新之后的行。这种隔离级别不会造成脏读，不可更新读和幻读的结果

 所有上述的事务处理都在tempdb数据库中一个类似版本库的数据对象中自动进行，当遇到更新未被提交的情况，数据引擎会检索这个版本库得到合适的提交结果。维护这个版本库的工作由SQL Server自动进行，不需要人为干预。

SSIS中的事务处理

SSIS中的包，容器（例如Loop，Foreach Loop，Sequence）或者一个单独的任务中都可以设置事务处理选项。事务处理选项有下面一些值       　　　　　　　　

• Required-如有事务则添加，否则新添加一个
• Supported-如有有事务添加一个，没有则不添加，这是默认选项+
• NotSupported-不添加事务处理

内建的事务处理要使用Distributed Transaction Coordinator(MSDTC)服务，这个服务必须开启。MSDTC允许使用分布式事务处理，例如在一个事务中同时处理SQL Server数据库和Oracle数据库。如果没有开启这个服务会得到下面的错误提示.

Error: 0xC001401A at Transaction: The SSIS Runtime has failed to start the distributed transaction due to error 0x8004D01B "The Transaction Manager is not available.". The DTC transaction failed