你真的瞭解事務嗎？

事務用於處理數據的一致性，事務的定義是，處於同一個事務中的操作是一個工作單元，要麼全部執行成功，要麼全部執行失敗。把事務的概念應用到在實際的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，多連接，單一事務模式，不再贅述。