串連SQLServer時，因啟用串連池導致孤立事務的原因分析和解決辦法

標籤：應用程式調用   對象   hle   html   保持活動   前一行   htm   pen   資料庫服務   

 

本文出處:http://www.cnblogs.com/wy123/p/6110349.html 

 

之前遇到過這麼一種情況：

　　串連資料庫的部分Session會出現不定時的阻塞，這種阻塞時間長度時短，有時候持續較長時間，有時間期間較短，沒有什麼規律。
　  之後分析相關預存程序和代碼寫法，發現是預存程序中開啟了事務，而應用程式在調用預存程序發生異常之後沒有進行特別的處理（提交或者復原），
　  那麼在執行方法發生異常之後，串連關閉了，但是資料庫中遺留有活動事務（dbcc opentran對應的SessionId是sleeping狀態），於是就產生了阻塞。
　  關鍵是活動事務會不定時自己消失，就有點詭異了，這是本文的重點。

 

這種機制跟串連池有關：

當應用程式串連資料庫的時候開啟了串連池，如果應用程式調用了一個開啟了事務操作的預存程序，
當發生異常的時候，有可能會出現資料庫連接關閉，而預存程序中的事務既沒有提交，也沒有復原的情況
這種情況下就會產生“孤立事務”，也就是說，因為開啟事務的資料量串連斷掉了，而事務還處於活動狀態，
實際上開啟串連池的情況下，資料庫連接的關閉，並不是物理上的關閉，而是將資料庫連接返回到串連池。
此時如果沒有外界的幹預，包括沒有對這個資料庫連接沒有被重用，或者這個串連沒有物理斷開，或者是沒有重啟應用程式，或者沒有資料庫伺服器，這個事務將一直持續下去。
因為活動事務將阻塞其他Session對相關表的排他性訪問，所以就表現為阻塞。

 

 

 

如何判斷是否發生了串連池中的串連重用

首先，一個串連資料庫的過程中，有沒有重用串連池中的串連，在SQL Server中有哪些區別？
以ado.net為例，如果在連接字串中加入pooling=false;則表示不啟用串連池.
如下，連續執行兩次資料庫訪問，兩次資料庫訪問均在連接字串中加入了pooling=false;表示不啟用串連池

　　

　　如下是觀察到profile中的串連動作，注意這裡第一次串連斷開之後，有一個logout，第二次訪問資料的時候，有一個login

 　　

　　如果將上述兩個方法中連接字串中的pooling=false;改為pooling=true;再次連續執行兩個方法，
　　會發現第二次串連資料的之前，也即在第一個logout之後，第二次login之前，有一個exec sp_reset_connection的動作。
　　exec sp_reset_connection的執行標誌著串連從串連池中重用了串連，關於這個動作的作用下面再說

 

 

什麼情況下會出現資料庫連接關閉，而事務保持活動狀態 

　　首先，參考如下，編寫一個事務性預存程序，用waitfor delay ‘00:00:50‘的方式延長其事務提交時間，造成連線逾時（預設ado.net串連30秒）

 

 

在ado.net中調用這個預存程序，串連超過30秒之後逾時異常，當前執行方法的資料庫連接被關閉，此時並不關閉Visual Studio，類比應用程式並沒有終止

 

 　　

　　此時查詢資料中的活動事務，發現有一個活動事務，活動事務是上次執行“TimeoutFunction”造成的，
　　但此時“TimeoutFunction”發生了異常，資料庫連接被正常關閉，　　
　　此時，執行這個方法造成的事務還是活動狀態的，如下

 

而此時觀察SessionId = 57的狀態，他是sleeping啊，已經開始呼呼睡大覺了。 

 

如果此時對事務中的表執行查詢操作，會發現是被阻塞的，事實上t1這張表在上述方法執行之前一行資料都沒有

 

 

資料庫連接被重用，第一次串連遺留在資料庫中的事務被復原

　　上面在執行第一個方法之後，並沒有中斷VS的調試狀態，我們繼續執行第二個方法，此時第二個方法會重用第一個方法的資料庫連接，
　　至於為什麼說他就重用了第一個方法的資料庫連接，一開始就說了。
　　當執行exec sp_reset_connection的時候，活動事務被復原。查詢能夠正常執行。如下

 

　　查詢在exec sp_reset_connection之後正常完，因為事務是被復原的，所以t1表沒有任何資料

 

　　上述樣本就類比出來類似這麼一種情境，當連接字串中開啟了串連池之後
　　一個方法執行逾時串連被關閉之後，其調用的預存程序中的事務並沒有顯式的提交或者復原，造成串連關閉而事務繼續保持活動狀態的情況
　　比如web程式，一個方法執行完成之後，連線逾時但是正常關閉（歸還串連池），事務保持活動狀態，
　　此時web伺服器並沒有停止下來，也就是應用程式沒有直接關閉，也就是類似於Visual Studio繼續保持DEBUG狀態，
　　此時事務一直保持活動狀態知道串連被重用（或者應用程式被關閉），那麼其他Session發起對活動事務鎖定的對象，就會發生阻塞。
　　問題就出在這裡，主觀上無法保證串連池中的那個串連什麼時候被重用，也就無法保證活動事務要持續多久，
　　如果活動事務一直保持，那麼阻塞就一直保持，這顯然是不可接受的

 

關於sp_reset_connection的作用，我就懶得打字了，參考《Microsoft SQL Server企業級平台實踐》第316頁

 

如何避免串連關閉而事務保持活動

　　1，本質因為預存程序執行時間超過了串連的時間導致串連關閉的，那麼就可以從分析事務性操作逾時的原因入手。

　　2，可以在應用程式的代碼中catch的中，進行異常處理時候，保證串連關閉之前，活動事物最終提交或者復原（作出明確的處理）

　　3，關閉串連池，這種情況下，任何被關閉的資料庫連接，其發起的事務都將被復原，但串連池也是為了提交資料效能，可行性不大。

　　4，從效能上以及串連池機制中分析，也只能緩解這個問題，而逃不過這個問題，
　　　 實際上，面對連線逾時斷開而是事務繼續保持活動狀態這種情況，在預存程序的事務性操作中加入try catch也是無濟於事的，
　　　 那麼就可以使用SET XACT_ABORT ON;命令，確保在任何異常情況下，對事務進行復原。關於XACT_ABORT可參考聯機叢書。

 

　　　 

 

串連SQLServer時，因啟用串連池導致孤立事務的原因分析和解決辦法