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 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，依次他們的設定如下 

    1/*命名*/                
    2Create TranQueue Table    
    3/*SQLstatement設定*/    
    4IF NOT EXISTS ( SELECT * FROM sys.objects WHERE object_id = 
    5
    6OBJECT_ID(N'dbo.TranQueue') AND type in (N'U') )
    7BEGIN
    8execute('CREATE TABLE dbo.TranQueue(message nvarchar(256))')
    9END
   10/*命名*/
   11Populate TranQueue        
   12/*SQLstatement設定*/
   13INSERT INTO dbo.TranQueue VALUES ('Test Message' + CONVERT
   14
   15(NVARCHAR(23), GETDATE(), 121))
   16/*命名*/
   17Create TranQueueHistory table    
   18/*SQLstatement設定*/
   19IF NOT EXISTS ( SELECT * FROM sys.objects WHERE object_id = 
   20
   21OBJECT_ID(N'dbo.TranQueueHistory') AND type in (N'U') )
   22BEGIN
   23execute('CREATE TABLE dbo.TranQueueHistory(message nvarchar(256))')
   24END

3. 建立第二個SequenceContainer，命名為Process，TransactionOption屬性設定為Supported，這樣就會添加交易處理。  
4. 在這個SequenceContainer中添加一個Execute SQL，命名為ProcessTranQueue，它的SQLStatement設定為下面的語句。這個語句的作用，類比交易處理，刪除TranQueue表中前10條資料；OUTPUT字句將刪除的資料插入到TranQueueHistory表中，類比處理結束，更新記錄

   1DELETE TOP(10) dbo.TranQueue
   2OUTPUT DELETED.*
   3INTO dbo.TranQueueHistory
   4FROM 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，但是這條記錄並沒有提交

1SELECT * FROM dbo.TranQueueHistory WITH (NOLOCK)

11.執行下面的語句

1SELECT * FROM dbo.TranQueue

語句將阻塞在這裡，語句一直停留在執行狀態，不會結束。因為在Process TranQueue任務中我們使用TABLOCKX，在這裡將等待任務復原或者提交。或者可以寫成這樣，它任然會阻塞

1 DELETE TOP(10) dbo.TranQueue
2  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，這樣第一個表中的資料會被“剪下”到第二個表中。