顯式事務在嘗試/捕穫後保持打開狀態

我最近編寫了一個 T-SQL 腳本來對 3 個不同的表執行一些更新和插入操作。我希望它在單個事務中完成，因此我閱讀了有關如何使用 try/catch 的顯式事務的 Microsoft 文件。

根據文件，可以這樣做：

BEGIN TRANSACTION;

BEGIN TRY
   -- Generate a constraint violation error.
   DELETE FROM Production.Product
   WHERE ProductID = 980;
END TRY

BEGIN CATCH
   SELECT 
       ERROR_NUMBER() AS ErrorNumber
       ,ERROR_SEVERITY() AS ErrorSeverity
       ,ERROR_STATE() AS ErrorState
       ,ERROR_PROCEDURE() AS ErrorProcedure
       ,ERROR_LINE() AS ErrorLine
       ,ERROR_MESSAGE() AS ErrorMessage;

   IF @@TRANCOUNT > 0
       ROLLBACK TRANSACTION;
END CATCH;

IF @@TRANCOUNT > 0
   COMMIT TRANSACTION;
GO

所以我實現了這個模式並將我所有的更新和插入都放在了 TRY 子句中。問題是，程式碼在查詢完成後設法留下了一個打開的事務，我不知道這是怎麼發生的。不管在 TRY 子句中做了什麼工作，有哪些可能的場景會導致這樣的查詢使事務保持打開狀態？

有幾種類型的錯誤沒有被 T-SQL TRY/CATCH結構擷取（取自TRY…CATCH的 MSDN 頁面，為清楚起見稍作編輯）：

• 嚴重性為 20 或更高的錯誤會停止會話的 SQL Server 數據庫引擎任務處理。如果發生嚴重程度為 20 或更高的錯誤並且數據庫連接未中斷，則 TRY…CATCH 將處理該錯誤。
• 注意，例如客戶端中斷請求或斷開的客戶端連接。
• 當系統管理員使用 KILL 語句結束會話時。
• 在語句級重新編譯期間與 TRY…CATCH 構造在同一執行級別發生的錯誤，例如由於延遲名稱解析而在編譯後發生的對象名稱解析錯誤。

對於問題中提供的範常式式碼（即單個 DML 語句），有一個非常簡單的解決方法：刪除顯式事務。對於單個 DML 語句，顯式事務是不必要的，除非有額外的邏輯對某些條件進行檢查，並且可以選擇通過ROLLBACKmain 中的 doneTRY而不是作為一部分來撤消該更改，CATCH因為它不是數據庫引擎錯誤。在該特定場景之外，我看不出有理由（至少是一個好理由）為單個 DML 語句使用顯式事務。但我仍然會保留TRY/CATCH結構以進行一般錯誤處理。

當涉及到多個 DML 語句時（這是問題的真實上下文，如範常式式碼上方的措辭中所述），那麼您顯然確實需要顯式事務，因此這裡有一些想法：

• @usr 對問題的評論建議完全跳過數據庫層的錯誤處理，而是在應用層處理它。雖然應用程式碼可以處理事務和錯誤處理，但如果您沒有專門使用像實體框架這樣的 ORM （這意味著您有由 SQL 代理作業和/或支持人員通過 SSMS 呼叫的儲存過程），那麼這些儲存過程需要包括事務和錯誤處理，因為應用程序層並不總是執行的發起者，但您仍然希望在其他情況下進行事務和錯誤處理。我在以下答案中對此進行了更多討論：我們是否需要在 C# 程式碼以及儲存過程中處理事務
• 連接結束時，未送出的事務會自動回滾。這是從 SQL Server 的角度，而不是客戶端的角度。意思是，連接不再列在sys.dm_exec_connections.
• 連接池使自動回滾處理變得複雜，因為它在設計上保持連接打開，即使在客戶端“關閉”它之後也是如此。當連接保持打開以便另一個“連接”嘗試可以簡單地重新使用它時，會話本身不會“清理”，直到打開新連接並執行查詢批處理！在應用程式碼在實際重用池中的連接的“新”連接上送出的第一次執行時sp_reset_connection，將呼叫一個標記為的內部程序，除其他外，該程序將回滾該會話中的未送出事務，即現在被重複使用。這裡的問題是當使用連接池時，程序終止（可能是命令超時），並且沒有新的連接被請求，也沒有新的查詢被送出。在這種情況下，連接只是位於池中，會話仍然存在，並且沒有請求清理操作。但這不會永遠持續下去（儘管在這些情況下仍然比你想要的長）。根據SQL Server Connection Pooling (ADO.NET)的 MSDN 頁面（在刪除連接部分）：

連接池在空閒大約 4 到 8 分鐘後，或者如果池檢測到與伺服器的連接已被切斷，則從池中刪除連接。

• 取消儲存過程執行不僅僅是您的應用程式碼獲取 CommandTimeout 或呼叫顯式“取消”的問題。SQL Server Management Studio (SSMS) 或任何 IDE 也是客戶端程式碼。並且可以取消 SSMS 中正在執行的儲存過程。這樣做會產生與跳過CATCH塊相同的效果。但是在 SSMS 中取消執行仍然會讓您保持在同一個會話中，因此事務仍然處於活動狀態，直到您手動呼叫COMMITor ROLLBACK，或者直到您關閉該查詢選項卡（此時它會告訴您有一個未送出的事務並詢問是否你想送出還是不送出；我的測試表明回答“不”會回滾）。

那麼如何處理使用連接池的應用程式碼呢？我不是使用SET XACT_ABORT ON;. 我首先要嘗試的是：

• Max Pool SizeConnections 可以通過連接字元串關鍵字設置連接池的最大大小。如果池非常大，那麼任何特定連接都不太可能被快速重用。預設池大小似乎是 100。將值設置為略低於目前值（但不要太低以免無法有效利用連接池）將有助於確保更快地重用連接將意味著清理程序將被更快地呼叫，這將回滾任何打開的事務。
• 在您正在執行查詢/儲存過程的應用程式碼中，catch您可以在塊中呼叫SqlConnection.ClearPool，它應該關閉 SQL Server 級別的實際連接，這反過來應該允許自動回滾未送出的事務。您甚至可以通過檢查異常以查看它是否是命令超時或在跳過 T-SQLCATCH塊時終止程序的其他少數場景之一，如果是，則呼叫ClearPool.
• 從技術上講，您可以Pooling=false;通過在連接字元串中指定來完全禁用連接池。但是，我不認為這是必要的，並且不推薦它，除非絕對必要或有一個應用程序首先不會產生大量連接。

引用自：https://dba.stackexchange.com/questions/123178