SQL点滴之SSIS中的事务处理

在介绍了SQL Server中的事务的概念之后，现在我们可以继续介绍这次的点滴了。

创新互联建站主打移动网站、成都网站设计、网站制作、网站改版、网络推广、网站维护、域名与空间、等互联网信息服务，为各行业提供服务。在技术实力的保障下，我们为客户承诺稳定，放心的服务，根据网站的内容与功能再决定采用什么样的设计。最后，要实现符合网站需求的内容、功能与设计，我们还会规划稳定安全的技术方案做保障。

我们可以把SSIS中的整个package包含在一个事务中，但是如果在package的执行过程中有一个表需要锁定应该怎么处理呢？SSIS内建的事务处理可以解决这个问题。

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

1. Required-如有事务则添加，否则新添加一个
2. Supported-如有有事务添加一个，没有则不添加，这是默认选项+
3. 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 to start. This could occur because the MSDTC Service is not running.  
 
 
 
 

注意SSIS中包中的元素的事务隔离级别是Serializable，这种级别会影响锁的持续时间。下面我们来用一个例子说明在如何package中锁定一个表

1.新建一个SequenceContainer，命名为Test Initialization。

2.这个SequenceContainer主要用来创建测试的环境，创建连个表TranQueue，TranQueueHistory，向***个表中添加一条记录，这样模拟一个事物处理过程。我们只是使用这个SequenceContainer来创建测试环境，所以设置它的TransactionOption选项为NotSupported在这个SequenceContainer中依次添加三个Execute SQL，依次他们的设置如下 

 
 
 
 

  
  
  
  
/*命名*/                   
  
  
  
  
Create TranQueue Table       
  
  
  
  
/*SQLstatement设置*/       
  
  
  
  
IF NOT EXISTS ( SELECT * FROM sys.objects WHERE object_id =    
  
  
  
  
   
  
  
  
  
OBJECT_ID(N'dbo.TranQueue') AND type in (N'U') )   
  
  
  
  
BEGIN   
  
  
  
  
execute('CREATE TABLE dbo.TranQueue(message nvarchar(256))')   
  
  
  
  
END 
  
  
  
  
/*命名*/  
  
  
  
  
Populate TranQueue          
  
  
  
  
/*SQLstatement设置*/  
  
  
  
  
INSERT INTO dbo.TranQueue VALUES ('Test Message' + CONVERT 
  
  
  
  
 
  
  
  
  
(NVARCHAR(23), GETDATE(), 121))  
  
  
  
  
/*命名*/  
  
  
  
  
Create TranQueueHistory table      
  
  
  
  
/*SQLstatement设置*/  
  
  
  
  
IF NOT EXISTS ( SELECT * FROM sys.objects WHERE object_id =   
  
  
  
  
 
  
  
  
  
OBJECT_ID(N'dbo.TranQueueHistory') AND type in (N'U') )  
  
  
  
  
BEGIN 
  
  
  
  
execute('CREATE TABLE dbo.TranQueueHistory(message nvarchar(256))')  
  
  
  
  
END 
 
 
 
 

3.创建第二个SequenceContainer，命名为Process，TransactionOption属性设置为Supported，这样就会添加事务处理。  

4.在这个SequenceContainer中添加一个Execute SQL，命名为ProcessTranQueue，它的SQLStatement设置为下面的语句。这个语句的作用，模拟事务处理，删除TranQueue表中前10条数据；OUTPUT字句将删除的数据插入到TranQueueHistory表中，模拟处理结束，更新历史记录

 
 
 
 

  
  
  
  
DELETE TOP(10) dbo.TranQueue  
  
  
  
  
OUTPUT DELETED.*  
  
  
  
  
INTO dbo.TranQueueHistory  
  
  
  
  
FROM dbo.TranQueue WITH (TABLOCKX)  
 
 
 
 

5.添加一个Execute SQL，命名为Placeholder for Breakpoint。这个任务不进行任何操作，只是为了在这设置一个断点然后在这里停下来让我们有时间验证是否会锁定表。
 
6.右击Control Flow界面添加一个变量v_SimulateFailure，类型为Int32，值为1。

7.添加一个Execute SQL命名为Simulate Failure。用它来模拟错误，设置SQLStatement为select 1/0，当pacakage执行到这里的时候会造成错误进而回滚。

8.右击Placeholder for Breakpoint和Simulate Failure之间的连线，点击Edit，设置Evaluation operation为Expression and Constraint，设置Expression为@[User::v_SimulateFailure] == 1，其他保持默认。这样之后这个自定义变量的值为1的时候才会继续往下执行。
 
9.执行package，会得到如图1的结果，package在断点处终止。 

  图1              

10.打开SQL Server Management Studion,选择对应的数据库，新建一个Query，执行下面的语句，NOLOCK选项忽略锁，这个语句查询得到一条记录 Message2011-04-10 14:22:31.043，但是这条记录并没有提交

 
 
 
 

  
  
  
  
SELECT * FROM dbo.TranQueueHistory WITH (NOLOCK)  
 
 
 
 

11.执行下面的语句

 
 
 
 

  
  
  
  
SELECT * FROM dbo.TranQueue  
 
 
 
 

语句将阻塞在这里，语句一直停留在执行状态，不会结束。因为在Process TranQueue任务中我们使用TABLOCKX，在这里将等待任务回滚或者提交。或者可以写成这样，它任然会阻塞

 
 
 
 

  
  
  
  
DELETE TOP(10) dbo.TranQueue  
  
  
  
  
INSERT INTO dbo.TranQueueHistory VALUES ('Test Message' + CONVERT(NVARCHAR(23), GETDATE(), 121))  
 
 
 
 

12. 点击Continue按钮或者Debuge按钮，会看到package执行失败，执行SELECT * FROM dbo.TranQueueHistory WITH (NOLOCK)；因为执行了回滚，不会得到任何结果。SELECT * FROM dbo.TranQueue，任然有一条记录。

 
 
 
 

  
  
  
  
SELECT * FROM dbo.TranQueueHistory WITH (NOLOCK)  
 
 
 
 

NOLOCK提示忽略锁，这个语句查询得到一条记录 Message2011-04-10 14:22:31.043，但是这条记录并没有提交

13.  执行下面的语句，

 
 
 
 

  
  
  
  
SELECT * FROM dbo.TranQueue  
 
 
 
 

sql语句将阻塞在这里，语句一直执行。因为在Process TranQueue任务中我们使用TABLOCKX，在这里将等待任务回滚或者提交。或者可以写成这样

 
 
 
 

  
  
  
  
DELETE TOP(10) dbo.TranQueue;  
  
  
  
  
INSERT INTO dbo.TranQueueHistory VALUES ('Test Message' + CONVERT(NVARCHAR(23), GETDATE(), 121))， 
 
 
 
 

它任然会阻塞     

14.  点击Continue按钮或者Debuge按钮，会看到package执行失败，执行SELECT * FROM dbo.TranQueueHistory WITH (NOLOCK)；因为执行了回滚，不会得到任何结果。执行SELECT * FROM dbo.TranQueue，任然有一条记录。

如果设置变量User::v_SimulateFailure的值为0，不会执行Simulate Failure任务，就不会回滚，TranQueue中的记录会被写入到TranQueueHistory中。这里有一个很有意思的语句：

 
 
 
 

  
  
  
  
DELETE TOP(10) dbo.TranQueue  
  
  
  
  
OUTPUT DELETED.*  
  
  
  
  
INTO dbo.TranQueueHistory  
  
  
  
  
FROM dbo.TranQueue WITH (TABLOCKX)  
 
 
 
 

如果两个表的结构有一部分是是一样的，现在想把一个表的数据导入到另外一个表中，可以使用DELETE SourceTable OUTPUT DELETE.*/DELETE.Column1,DELETE.Column2... INTO DestinationTable FROM SourceTable，这样***个表中的数据会被“剪切”到第二个表中。

网站栏目：SQL点滴之SSIS中的事务处理
路径分享：http://www.gawzjz.com/qtweb2/news39/4989.html

网站建设、网络推广公司-创新互联，是专注品牌与效果的网站制作，网络营销seo公司；服务项目有等

广告

声明：本网站发布的内容（图片、视频和文字）以用户投稿、用户转载内容为主，如果涉及侵权请尽快告知，我们将会在第一时间删除。文章观点不代表本网站立场，如需处理请联系客服。电话：028-86922220；邮箱：631063699@qq.com。内容未经允许不得转载，或转载时需注明来源： 创新互联