在資料庫事務設計中經常會遇到的疑惑

在這裡整理並記錄一下，也分享一下經驗：

分散式查詢和分散式交易

SQL Server 資料庫引擎允許建立與 OLE DB 資料來源（稱為連結的伺服器）的連結。連結到 OLE DB 資料來源之後，可以：
在 Transact-SQL 語句中作為表引用 OLE DB 資料來源中的行集。
將命令傳遞給 OLE DB 資料來源，並包含結果行集，作為 Transact-SQL 語句中的表。
每個分散式查詢都可以引用多個連結的伺服器，而且可以對每台連結的伺服器執行更新或讀取操作。單個分散式查詢可以對某些連結的伺服器執行讀取操作，並且對其他連結的伺服器執行更新操作。通常情況下，每當可能在某個事務中更新多台連結的伺服器中的資料時，資料庫引擎都要求相應的 OLE DB 提供者支援分散式交易。因此，連結的伺服器上所支援的查詢類型取決於 OLE DB 提供者中對事務的支援層級。OLE DB 為交易管理定義了兩個可選的介面：
ITransactionLocal 支援 OLE DB 資料來源中的本地事務。
ITransactionJoin 允許提供者聯結包含其他資源管理員的分散式交易。 

所有支援 ITransactionJoin 的提供者也都支援 ITransactionLocal。

如果在串連處於自動認可模式時執行分散式查詢，則應用以下規則：
對於不支援 ITransactionLocal 的提供者，只允許執行讀取操作。
對於支援 ITransactionLocal 的提供者，允許執行所有更新操作。 

資料庫引擎的控制執行個體會自動調用參與更新操作的每台連結的伺服器中的 ITransactionLocal 以啟動本地事務。如果語句執行成功則提交事務，如果語句執行失敗則復原事務。

 

如果分散式查詢是針對分散式資料分割檢視的，或是在串連為明確交易或隱含交易時執行，則應用以下規則：
對於不支援 ITransactionJoin 的提供者，只允許執行讀取操作。不支援任何事務或只支援 ITransactionLocal 的提供者不能參與更新操作。
如果 SET XACT_ABORT 設定為 ON，則對於支援 ITransactionJoin 的任何提供者，允許執行所有的更新操作。資料庫引擎的控制執行個體會自動調用參與更新操作的每台連結的伺服器中的 ITransactionJoin，以便在分散式交易中註冊該伺服器。然後當主控伺服器指示提交事務或復原事務時，Microsoft 分散式交易協調器 (MS DTC) 提交事務或復原事務。
如果 SET XACT_ABORT 設定為 OFF，則連結的伺服器還必須支援嵌套事務，才能對其執行更新操作。當會話已經有一個現有事務時，如果提供者支援調用 ITransactionLocal::StartTransaction，則支援嵌套事務。這樣，SQL Server 可以復原分散式查詢中的單個語句，而不必復原整個事務。

上述規則隱含了對不支援嵌套事務的提供者的以下限制：只有當 XACT_ABORT 選項設定為 ON 時，才允許在分散式交易中執行更新操作。

 SET XACT_ABORT 的用途

指定當 Transact-SQL 語句出現執行階段錯誤時，SQL Server 是否自動復原到當前事務。
當 SET XACT_ABORT 為 ON 時，如果執行 Transact-SQL 語句產生執行階段錯誤，則整個事務將終止並復原。
當 SET XACT_ABORT 為 OFF 時，有時只復原產生錯誤的 Transact-SQL 語句，而事務將繼續進行處理。如果錯誤很嚴重，那麼即使 SET XACT_ABORT 為 OFF，也可能復原整個事務。OFF 是預設設定。
編譯錯誤（如語法錯誤）不受 SET XACT_ABORT 的影響。

對於大多數 OLE DB 提供者（包括 SQL Server），必須將隱式或顯示事務中的資料修改語句中的 XACT_ABORT 設定為 ON。唯一不需要該選項的情況是在提供者支援嵌套事務時。有關詳細資料，請參閱 分散式查詢和分散式交易。

SET XACT_ABORT 的設定是在執行或運行時設定，而不是在分析時設定。

 

 XACT_STATE 的使用說明

用於報告當前正在啟動並執行請求的使用者事務狀態的純量涵式。XACT_STATE 指示請求是否有活動的使用者事務，以及是否能夠提交該事務。
XACT_STATE 返回下列值。
傳回值  含義  

1    當前請求有活動的使用者事務。請求可以執行任何操作，包括寫入資料和提交事務。
0    當前請求沒有活動的使用者事務。
-1   當前請求具有活動的使用者事務，但出現了致使事務被歸類為無法提交的事務的錯誤。請求無法提交事務或復原到儲存點；它只能請求完全復原事務。請求在復原事務之前無法執行任何寫操作。請求在復原事務之前只能執行讀操作。交易回復之後，請求便可執行讀寫操作並可開始新的事務。

當批處理結束運行時，資料庫引擎將自動復原所有不可提交的活動事務。如果事務進入不可提交狀態時未發送錯誤訊息，則當批處理結束時，將向用戶端應用程式發送一個錯誤訊息。該訊息指示檢測到並復原了一個不可提交的事務。有關無法提交的事務的詳細資料，請參閱在 Transact-SQL 中使用 TRY...CATCH。

XACT_STATE 和 @@TRANCOUNT 函數都可用於檢測當前請求是否具有活動的使用者事務。@@TRANCOUNT 不能用於確定事務是否已分類為不可提交的事務。XACT_STATE 不能用於確定是否有嵌套事務。