SQL點滴之SSIS中的交易處理

在介紹了SQL Server中的事務的概念之後，現在我們可以繼續介紹這次的點滴了。

我們可以把SSIS中的整個package包含在一個事務中，但是如果在package的執行過程中有一個表需要鎖定應該怎麼處理呢？SSIS內建的交易處理可以解決這個問題。

SSIS中的包，容器例如Loop，Foreach Loop，Sequence）或者一個單獨的任務中都可以設定交易處理選項。交易處理選項有下面一些值      

內建的交易處理要使用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，這樣第一個表中的資料會被“剪下”到第二個表中。