ADO.NET資料連線池__.net

21世紀什麼最貴。資料庫連接。對於以資料庫做資料存放區基石的應用系統來說，資料庫連接是整個系統中最珍貴的資源之一。資料庫連接池是為了更有效地利用資料庫連接的最重要措施。它對於一個大型的應用系統的效能至關重要，特別是Web應用。 ADO.NET Data Provider（以下簡稱Data Provider）會幫我們管理 串連池，所以有人說使用串連池就像遊兒童池一樣輕鬆。但並不是說有了Data Provider程式員就萬事無憂的，不正確地使用串連池可能導致你的應用在池裡淹死。筆者希望通過本文 能讓讀者徹底明白串連池的重要性以及能根據實際情況正確 配置 串連池的參數，明白實際應用中出現的串連泄漏、“死串連”等異常情況和應對方法，讓應用輕鬆暢遊串連池。本文主要介紹ADO.NET 1.1的串連池。

 

    1、什麼是串連池 

    串連池是Data Provider提供的一個機制，使得應用程式使用的串連儲存在串連池裡而避免每次都要完成建立/關閉串連的完整過程。要理解串連池，先要理解程式裡SqlConnection.Open()、SqlConnection.Close()和開啟/關閉一個“物理串連”的關係。 

    Data Provider在收到串連請求時建立串連的完整過程是：先串連池裡建立新的串連（即“邏輯串連”），然後建立該“邏輯串連”對應的“物理串連”。建立“邏輯串連”一定伴隨著建立“物理串連”。Data Provider關閉一個串連的完整過程是先關閉“邏輯串連”對應的“物理串連”然後銷毀“邏輯串連”。銷毀“邏輯串連”一定伴隨著關閉“物理串連”。SqlConnection.Open()是向Data Provider請求一個串連，Data Provider不一定需要完成建立串連的完整過程，可能只需要從串連池裡取出一個可用的串連就可以；SqlConnection.Close()是請求關閉一個串連，Data Provider不一定需要完成關閉串連的完整過程，可能只需要把串連釋放回串連池就可以。 

    下面以一個例子來說明。本文例子都使用Console Application。我們使用作業系統的效能監控器來比較使用串連池與否，資料庫的“物理串連”數量的不同。因為效能監控器至少每一秒採集一次資料，為方便觀察效果，代碼中Open和Close串連後都Sleep一秒。 

SqlConnection con = new SqlConnection("server = .; database = northwind;pooling = false;trusted_connection = true"); for(int i = 0;i < 10;i++) { try { con.Open(); System.Threading.Thread.Sleep(1000); } catch(Exception e){Console.WriteLine(e.Message);} finally { con.Close(); System.Threading.Thread.Sleep(1000); } }

    首先，不使用串連池做測試。以上程式中pooing = false表示不使用串連池，程式使用同一個串連串Open & Close了10次串連，使用效能計數器觀察SQL Server的“物理串連”數量。從下面的鋸齒圖可以看出每執行一次con.Open()，SQLServer的“物理串連”數量都增加一，而每執行一次con.Close()，SQLServer的“物理串連”數量都減少一。由於不使用串連池，每次Close串連的時候Data Provider需要把“邏輯串連”和“物理串連”都銷毀了，每次Open串連的時候Data Provider需要 建立“邏輯串連”和“物理串連”，鋸齒圖因此而成。

圖1

    下面啟用串連池再測試一次。把串連串的pooling參數改為true，另外在for迴圈後加上Console.Read()。 

    從下圖可以看出，從第一次Open到第執行完Console.Read()這段時間內，SQL Server的“物理串連”數量一直保持為1，直到關閉console應用程式的進程後SQL Server的“物理串連”數量才變為0。由於使用了串連池，每次Close串連的時候Data Provider只需把“邏輯串連”釋放回串連池，對應的“物理串連”則保持開啟的狀態。每次Open串連的時候，Data Provider只需從串連池取出一個“邏輯串連”，這樣就可以使用其對應“物理串連”而不需建立新的“物理串連”，直線圖因此而成。

圖2

    在ADO.NET 1.1下使用效能計數器觀察串連池有關計數器需要注意兩個bug。 

    （1）當應用程式進程關閉後，計數器“SqlClient: Current # pooled connections”和“SqlClient: Current # connection pools”不會減為0，所以每重新運行一次應用程式效能計數器的值在上次的值的基礎上一直累加。這是計數器的錯誤顯示，實際上當應用程式關閉後connection pool和pooled connection就減為0。因為關閉應用程式後把效能監控器也關閉，重啟應用程式後再重新開啟效能監控器就可以看出“SqlClient: Current # pooled connections”和“SqlClient: Current # connection pools”是重新從0開始上升的。 

    （2）用斷點調試的情況下，串連串為"server = .;database = northwind;pooling = true;trusted_connection = true" 的connnection第一次Open的時候“SqlClient: Current # pooled connections”就從0變為2。但根據串連串參數的意義，只Open了一個connection，“SqlClient: Current # pooled connections”應該從0變為1（圖2是在沒有斷點調試的情況下得出的曲線）。這不是計數器顯示錯誤，而是ADO.ENT 1.1本身的bug，因為“User Connections”也隨著“SqlClient: Current # pooled connections”從0變為2。 

    為什麼需要串連池。 

    完成建立/關閉一個串連的完整過程是一個消耗大量資源和時間的一個過程。想象一下一個ASP.NET的系統，裡麵包含大量訪問資料庫的代碼片，系統有大量的使用者同時在使用系統，如果程式每次Open/Close一個串連Data Provider都完成建立/關閉一個串連的完整過程，這樣的系統效能肯定讓人無法接受。 

    Data Provider提供串連池並通過串連池實現“物理串連”重複使用而避免頻繁地建立和關閉“物理串連”，從而大大提高應用系統的效能。圖1描述一個應用的不同Client App使用串連池訪問資料庫，Data Provider負責建立和管理一個或者多個的串連池，每一個串連池裡有一個或者多個串連，池裡的串連就是“邏輯串連”。串連池裡有N個串連表示該串連池與資料庫之間有N個“物理串連”。增加一個串連，串連池與資料庫的“物理串連”就增加一個，減少一個串連，串連池與資料庫的“物理串連”就減少一個。

圖3

   Data Provider為每個進程管理該進程的串連池，一個進程可以有一個或者多個串連池。Data Provider是根據什麼來決定是建立新的串連池還是使用已有的串連池呢。根據資料庫連接串。怎樣才算是相同的串連串。串連串的字元完全相同。答案為是但也不是。 

    筆者看過有些文章說不一定兩個串連串的字元完全相同才算是相同的串連池，例如 "server = .;database = northwind;user = sa;password = sqlserver"與 "server = .;database = northwind; password = sqlserver; user = sa”是相同的串連串。但筆者測試過，Data Provider為以上兩個串連串建立兩個串連池，證明它們並不是相同的串連串。其實，筆者認為，對於“兩個串連串參數相同但順序不同”，“兩個串連串只差一個空格”是否是相同的串連串等問題不需要理會，因為保證兩個串連池的字元完全相同是沒有難度的事。 

    如果你需要相同的串連串，首先你保證兩個串連串每一個字元都相同，但這還不能保證Data Provider只為你建立一個串連池。因為如果你使用Windows認證，那麼即使使用相同的串連串"server = .;database = northwind;trusted_connection = true”也有可能建立多個串連池。Windows認證意味著串連資料庫使用的資料庫使用者是運行開啟資料庫連接Open()的目前使用者，如果運行該代碼的使用者不是固定的話，那麼即使每次都使用相同字元的串連串也會產生多個串連池。 

    串連池建立後直到它所屬的進程結束才會被銷毀。 

    3、一個串連池裡有多少個串連 

    明白了怎麼區分不同的串連池後，下面我們來看看一個串連池裡有多少個串連。一個串連池裡的串連數不是靜態數量，它會隨著串連池的不同狀態而改變。這就涉及串連池建立的時候有多少個串連，什麼時候串連會減少，什麼時候會增加，串連數的上限是多少等問題。 

    首先來看看能影響串連池裡串連數的串連串參數，如下表所示。

參數	預設值	描述
Min Pool Size	0	串連池一旦建立後，池裡串連數量的最小值。
Max Pool Size	100	串連池裡串連數量的最大值。
Connection Lifetime	0	每當一個串連使用完後釋放回串連池，如果目前時間減去該串連建立的時間的值大於這個參數設定的值（秒），該串連被銷毀。0表示lifetime沒有上限。
Connection Timeout	15	串連請求停止請求併產出錯誤前等待的時間。當池的串連數達到Max Pool Size而且全部被佔用，串連請求需要等待“被佔用的”串連被釋放回串連池，如果等待超過指定的時間還沒有串連被釋放就拋出InvalidOperationException。

  3．1增加串連  

    一旦串連池被建立，就立即建立由Min Pool Size指定數量的串連。如果只有一個串連被佔用，那麼其他的串連（如果Min Pool Size大於1）為池裡“可用的”串連。如果某進程有串連請求而且請求的串連的串連串與該進程的某個串連池的串連串相同（如果進程裡的所有串連池的串連串都不匹配被請求的串連就需要建立新的串連池），那麼如果該串連池裡有“可用的”串連就從串連池裡取出一個“可用的”的串連使用，如果沒有“可用的”串連就建立新的串連。一旦程式運行串連的Close或者Dispose方法後，“被佔用的”串連被釋放回串連池變為“可用的”串連。需要區分串連池裡“串連的數量”與“‘可用的’串連數量”。“串連的數量”指串連池裡包括“被佔用的”串連與“可用的”串連的數量。 

    如果Max Pool Size已經達到而且所有串連都被佔用，新的串連請求需要等待。如果有被佔用的串連釋放回串連池，那麼請求得到該串連；如果請求等待超過Connection Timeout的時間，程式會拋出InvalidOperationException。 

    3．2減少串連 

    兩種情況下串連池裡的串連會減少。 

    （1）每當一個串連使用完後釋放回串連池，如果目前時間減去該串連建立的時間的值大於Connection Lifetime設定的值（秒），該串連被銷毀。Connection Lifetime是用於叢集資料庫環境下。例如一個應用系統的中介層訪問一個由3台伺服器組成的叢集資料庫，該系統運行一段時間後探索資料庫的負荷太大而需要增加第4台資料庫伺服器。如果不設定Connection Lifetime，你會發現新增加的伺服器很久都得不到串連而原來3台伺服器的負荷一點都沒減少。這是因為中介層的串連一直都不會銷毀而建立新的串連的可能性很小（除非出現增加伺服器之後資料庫的並發訪問量超過增加前的並發最大值）。 

    注意：Connection Lifetime很容易讓人產生誤解。不要認為Connection Lifetime決定了一個串連的存留時間。因為只有串連被釋放回串連池的時刻（Close串連之後）才會檢查Connection Lifetime值是否達到而決定是否銷毀串連，而串連在空閑或者正在使用的時候並不會檢查Connection Lifetime。這意味著絕大多數情況下串連從建立到銷毀經過的時間比Connection Lifetime大。另外，如果Min Pool Size為N (N > 0),那麼串連池裡有N個串連不受Connection Lifetime影響。這N個串連會一直在池裡直到串連池被銷毀。 

    （2）當發現某個串連對應的“物理串連”斷開（這種串連稱為“死串連”），例如資料庫已經被shutdown、網路中斷、SQL Server的串連進程被kill、Oracle的串連會話被kill，該串連被銷毀。“死串連”出現後不是立刻被發現，直到該串連被佔用來訪問資料庫的時候才會被發現。 

    注意：如果執行Open()方法時候Data Provider只需從串連池取出已有的串連，那麼Open()並沒有訪問資料庫，所以這時候“死串連”還不能被發現。 

    下面以一個例子詳細解釋一個串連池從建立起到進程結束串連數的變化情況。

 

string connectionString = "server = .;database = northwind;user = sa; password = sqlserver;min pool size = 2;max pool size = 5; connection lifetime = 20;connection timeout = 10"; SqlConnection[] connections = new SqlConnection[7]; for(int i = 0;i < connections.Length;i++) connections[i] = new SqlConnection(connectionString); …Open connection[0],8秒後Open connection[1] …8秒後Close connection[0],10秒後Open connection[0] …5秒後Open connection[2]、[3]、[4],每隔兩秒開啟一個 Console.WriteLine("Now the Max Pool Size is reached and we try to open connection[5]./r/n"); for(int i = 0;i < 2;i++) { try {connections[5].Open();} catch(InvalidOperationException e) { if(i == 1) return; Console.WriteLine("Can't open connection[5]./r/n" + e.Message); connections[4].Close(); Console.WriteLine("/r/nTry to open connection[5] again."); continue; } } Console.WriteLine("connection[5] is open."); foreach(SqlConnection con in connections) { if(con.State == ConnectionState.Open) { con.Close(); Console.WriteLine("A connection is released back to the pool."); System.Threading.Thread.Sleep(5000); } }

     使用效能監控器觀察,得到圖4所示結果。我們觀察.NET CLR Data的“SqlClient: Current # connection pools”、“SqlClient: Current # pooled connections”以及Sql Server: General Statistic的User Connections計數器。

圖4

    由於Min Pool Size = 2，所以open connection[0]的時候串連池裡就建立了兩個串連。之後open connection[1]、close connection[0]、open connection[0]這段時間裡串連池串連數保持為2，因為open串連的並發數量都沒超過2。接著，相繼open connection[2]、[3]、[4]，因為每次請求串連的時候串連池裡都沒有“可用的”串連，所以每請求一個串連串連數量就增加1，一直攀升到Max Pool Size(5)。這時候connection[0]、[1]的存留時間已經超過Connection Lifetime，但由於它們還沒有被Close，所以還會繼續生存。接著嘗試再請求串連，這時候因為Max Pool Size已達而池裡所有串連都被佔用，所以第一次嘗試失敗。進行第二次嘗試前先close connection[4]，這樣就有一個串連被釋放回串連池，第二次嘗試成功。最後close所有開啟的connection，每隔5秒close一個，所有connection被close的時候它們的存留時間都大於Connection Lifetime，但由於Min Pool Size = 2，所以只有3個connection被銷毀。 

    另外強調兩點： 

    （1）可用看出增加/減少一個串連池的串連，User Connections（即“物理串連”）隨著增加/減少一個。（為方便觀察，先用Sql Query Analyzer開啟一個使用者串連） 

    （2）由於使用相同串連串，所以由始至終只有一個串連池。

4、串連泄漏

    前面說過，串連被開啟後需要執行Close或者Dispose方法後才會釋放回串連池。如果一個串連已經離開其代碼有效範圍，但還沒被Close或者Dispose，該串連就被泄漏了。所謂泄漏的串連就是代碼中已經不再使用某個串連但該串連卻還沒有被釋放回串連池。下面代碼中，每執行一次Method()就泄漏一個串連，第11次執行的時候就會拋出InvalidOperationException，因為最大串連數已達而且所有串連都已經被佔用。 

 

private void Method() { string conString = "server = .;database = northwind;user = sa; password = sqlserver;max pool size = 10"; SqlConnection con = new SqlConnection(conString); con.Open(); }

    如果一個應用系統裡存在會泄漏串連的代碼，系統運行一段時間後串連就泄漏殆盡。即使把Max Pool Size設得很大也解決不了問題，因為單是一直存在太多的資料庫連接已經讓人不能容忍，況且這些是不能使用的“物理串連”。 

    要避免串連的泄漏，請注意下面幾點： 

    （1）除非使用CommandBehavior.CloseConnection作ExecuteReader參數，否則Close DataReader不會Close關聯的串連。在多層結構的系統中，如果中介層向表現層返回DataReader，那麼必須使用CommandBehavior.CloseConnection作ExecuteReader參數，這樣當表現層執行DataReader的Close方法時就會Close串連，不然表現層想幫你也有心無力。 

    （2）執行DataAdapter的Fill和Update方法時，如果串連沒有開啟，那麼DataAdapter自動會開啟串連，執行完操作後自動關閉串連；但如果串連已經開啟，DataAdapter執行完操作後不會幫你關閉串連，你需要自己負責關閉串連。 

    5、處理“死串連” 

    “可用的”串連一定能訪問資料庫。不一定。 

    在前面“減少串連”的部分提過，在資料庫被shutdown、網路中斷、資料庫連接進程/會話被kill情況下串連池會產生“死串連”。“死串連”指串連池裡某個串連對應的“物理串連”已經斷開，但ClientApp執行Open方法時候可以從串連池取得該串連，直到執行資料庫操作Data Provider才發現該串連是“死串連”。注意區分“死串連”和泄漏的串連。 

    “死串連”是“邏輯串連”，是“可用的”串連，但該“邏輯串連”對應的“物理串連”已經不存在；泄漏的串連指“物理串連”存在而對應的“邏輯串連”實際沒有被佔有但被標識為“被佔用”而導致該“邏輯串連”不能被使用。 

    發現“死串連”後Data Provider會銷毀該串連並拋出SqlException但不會自動嘗試使用其他串連，即使在ADO.NET 2.0裡也是如此。把exception catch下來，然後提示使用者重新操作不是最好的處理方式。不管微軟為什麼不幫我們嘗試其他串連，我們只能接受現實自己解決。 

    下面例子裡Helper的ExecuteReader把Data Provider拋出的SqlException catch後先把串連置為“無效”，然後再嘗試使用其他串連，如果再嘗試的次數達到預定值還不成功才拋出SqlException。

public class Helper { private static int TimesTry = 0,MaxTry = 5; public static SqlDataReader ExecuteReader(string conStr,CommandType eType, string commandText) { SqlConnection cn = null; SqlDataReader dr = null; SqlCommand cmd = null; try { cn = new SqlConnection(conStr); cmd = new SqlCommand(commandText,cn); cmd.CommandType = eType; cn.Open(); dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); } catch(SqlException e) { if(dr != null) dr.Close(); cn.Close(); System.Threading.Thread.Sleep(2000); if(TimesTry < MaxTry) { dr = ExecuteReader(conStr,eType,commandText); TimesTry++; } else throw e; } return dr; } } string conString = "server = .;database = northwind; user = sa;max pool size = 1;password = sqlserver; Application Name = DeadConnectionExample"; SqlDataReader reader = Helper.ExecuteReader(conString,CommandType.Text, "select * from orders"); reader.Close(); System.Threading.Thread.Sleep(15000); SqlConnection con = new SqlConnection("server = .;database = master; user = sa;password = sqlserver;pooling = false"); con.Open(); SqlCommand cmd = new SqlCommand("SELECT SPID FROM master.dbo.sysprocesses WHERE PROGRAM_NAME = 'DeadConnectionExample'",con); string spid = cmd.ExecuteScalar().ToString(); cmd = new SqlCommand("kill " + spid,con); cmd.ExecuteNonQuery(); con.Close(); System.Threading.Thread.Sleep(5000); reader = Helper.ExecuteReader(conString,CommandType.Text,"select * from orders"); reader.Close();

    Main方法裡，第一次調用Helper.ExecuteReader後建立了串連池並建立了一個串連，接著我們類比串連進程被kill後再調用Helper.ExecuteReader。為類比串連進程被kill，先在master.dbo.sysprocesses查詢program_name為DeadConnectionExample(串連串的Application Name)的SPID，然後kill了該串連進程。當再次調用Helper.ExecuteReader的時候就遇到“死串連”（一定遇到，因為串連池裡只有一個串連）。用效能監控器觀察串連池裡的情況（先開啟SQL Quary Analyzer得到一個User Connection以方便觀測）得到圖5。 

    圖5中串連池數量一直保持為1，因為kill串連進程所用的串連串沒有使用了串連池。kill了串連進程後User Connections（藍線）立刻下降1，而這時候串連池的串連數量（黃線）沒有隨著下降1，這就出現了一個“死串連”。接著，再從串連池取出串連訪問資料庫的時候就拋出SqlException，這時候串連數量下降1，因為這時候Data Provider銷毀“死串連”。接著，嘗試使用其他串連，因為這時候串連池裡串連數量為0，所以需要建立新串連，串連數量和User Connections同時上升1。為方便觀測，在嘗試其他串連前線程sleep了兩秒。 

    當然，如果“死串連”是由於網路中斷、資料庫被shutdown引起，那麼Helper只能最後拋出SqlException。 

    注意：查詢master.dbo.sysprocesses使用的串連串沒有必要使用串連池。 

圖5

6、ADO.NET 2.0效能計數器 
    前面提到的使用效能計數器時候的兩個bug在ADO.NET 2.0中都不會出現。ADO.NET 2.0中廢掉了1.1所用的“.NET CLR Data”的效能物件，新的效能物件是“.NET Data Provider for  Oracle”和“.NET Data Provider for SqlServer”。這兩個效能物件都有14個計數器，這比ADO.NET 1.1能觀察到更多、更深入的串連池資訊。其中本文說到的“被佔用的”串連、“可用的”的串連、“邏輯串連”和“物理串連”在ADO.NET 2.0效能計數器中分別叫Active Connection、Free Connection、Soft Connection、Hard Connection。 

    NumberOfFreeConnections、 NumberOfActiveConnections、 SoftDisconnectsPerSecond和SoftConnectsPerSecond預設在效能監控器是不開啟的，要觀察這些計數器的值需要在程式的 設定檔裡添加下面的配置: 

 

<system.diagnostics> <switches> <add name="ConnectionPoolPerformanceCounterDetail" value="4"/> </switches> </system.diagnostics>

    NumberOfActiveConnectionPoolGroups計數器。前面說過，如果串連串使用Windows認證，那麼不同的Windows使用者有不同的串連池，ADO.NET 2.0中使用NumberOfActiveConnectionPoolGroups把使用Windows認證的相同串連串（字元相同）產生的不同串連池歸為一組。 

    NumberOfActiveConnections, NumberOfFreeConnections計數器。ADO.NET 1.1裡的計數器沒有提供一個串連池裡的串連有多少個是“被佔用的”，有多少個是“可用的”。NumberOfActiveConnections和NumberOfFreeConnections填補了這個空白。這兩個計數器更加“生動”地描述了串連池裡串連的變化情況。圖6是一個串連相繼Open/Close了4次得到的比ADO.NET 1.1更“生動”的曲線。

圖6

    7、總結 

    明白了串連池的運作機制不等於能正確使用串連池，要充分挖掘串連池給應用系統帶來的效能提高，除了避免泄漏串連需要注意的兩點外， 請參考一下建議： 

    （1）確保每次訪問資料庫使用相同的串連串，串連串不要使用Windows認證。 

    （2）到了非開啟不可的時候才開啟串連，串連使用完畢立刻關閉串連。因為過早佔用和過晚釋放串連意味著增加串連池的不必要負荷（需要建立更多的串連以及串連請求需要等待更長時間）。 

    （3）根據應用系統的實際負荷設定適當的Min Pool Size和Max Pool Size。為避免串連請求逾時，如果應用系統的資料庫最大並發訪問數量大於Max Pool Size的預設置100就需要把Max Pool Size設定得更大；但不是越大越好，畢竟資料庫的負荷承受力有限。如果應用系統的資料庫最大並非訪問數量是N，那麼Min Pool Size不要大於N。 

    （4）如果應用系統不是使用叢集資料庫，把Connection Lifetime設定為0。在單資料庫伺服器的環境下沒必要把串連銷毀，因為銷毀後一段時間又需要建立。 

    串連池對應用系統的效能提高起著至關重要的作用，但需要串連池有其適用範圍，它適用於需要頻繁訪問資料庫的應用系統。對於低頻率（例如一天只有幾次）的資料庫訪問應用系統就不必要，因為一直保留一個低使用頻率的“物理串連”不如使用一次就建立一次好。