C# 串連池（Connection Pool）的一些個人見解

原文標題：關於ADO.Net串連池（Connection Pool）的一些個人見解
一下是原文：
建立池串連可以顯著提高應用程式的效能和可縮放性。SQL Server .NET Framework 資料提供者自動為 ADO.NET 用戶端應用程式提供串連池（MSDN）。
Opening a database connection is a resource intensive and time consuming operation. Connection pooling increases the performance of Web/windows applications by reusing active database connections instead of creating a new connection with every request. Connection pool manager maintains a pool of open database connections. When a new connection requests come in, the pool manager checks if the pool contains any unused connections and returns one if available. If all connections currently in the pool are busy and the maximum pool size has not been reached, the new connection is created and added to the pool. When the pool reaches its maximum size all new connection requests are being queued up until a connection in the pool becomes available or the connection attempt times out.
Connection pooling behavior is controlled by the connection string parameters. Please look into MSDN documents in the reference link if you want to know further information.
 
前面是關於串連池知識的一些基本介紹，下面的內容是重點，也是個人見解。因為沒有這方面的文檔資料參考或者佐證，所以請各位仔細思考和討論（我不想誤導大家）。
下面分2種情況進行討論。
1，Client端的windows form application通過ADO.Net直接存取後台Database。
 
查看大圖
SqlServer DirectConnection

我認為在這種情況下，每一個Client端和Database之間都存在一個串連池。通過設定ConnectiongString的Max Pool Size和Min Pool Size屬性來驗證。
如Max Pool Size = 5, Min Pool Size = 3, 通過SQL Server的SP_WHO2可以檢測導如下結果：
啟動1個Client端的Windows form application：application和SQL Server之間存在3個connection。
啟動2個Client端的Windows form application：application和SQL Server之間存在6個connection。
啟動3個Client端的Windows form application：application和SQL Server之間存在9個connection。
 
由此可見，每一個Client端和SQL Server之間都存在一個串連池，否則9個connection已經超出了Max Pool Size的設定。
 
2，Client端的application不直接通過ADO.Net來訪問後台Database，而是通過IIS Server來同後台Database Server打交道。
至少有如下2種情況：
（1）Client通過IE訪問部署在IIS中的web application，web application中的Data Access Class進一步訪問後台Database Server.
（2）Client端的windows form application訪問部署在IIS中的Remote Object，Remote Object進一步訪問後台Database Server.
 
查看大圖
SqlServer InDirectConnection
下面進行同樣的測試：通過設定ConnectiongString的Max Pool Size和Min Pool Size屬性來驗證。
如Max Pool Size = 5, Min Pool Size = 3, 通過SQL Server的SP_WHO2可以檢測導如下結果：
啟動1個Client端的Web form application：application和SQL Server之間存在3個connection。
啟動2個Client端的Web form application：application和SQL Server之間存在3個connection。
啟動3個Client端的Web form application：application和SQL Server之間存在3個connection。
調用由IIS承載的Remote Object，結果類似。只是在未啟動Client端之前，發現在Remote Object與Database Server之間已經存在一個connection。
由此可見，對同一個application而言，IIS Server與Database Server之間只有存在一個串連池，與Client端的多少沒有關係。
3，串連池小節
根據上面的測試結果，顯然第二種情況更有利於減少無用的串連數量，提高Database Server的效能。關於.Net Remoting技術及其效能問題，可以參考如下的Reference串連，這裡就不討論了。
另外，本文是個人關於串連池的一些見解，如果觀點有不正確之處，希望不要誤導各位。歡迎各位在此發表意見。同意的說贊同，不同意的請提出您的看法。謝謝。
Reference Links:
(1) MSDN, ms-help://MS.VSCC.2003/MS.MSDNQTR.2003FEB.2052/cpguide/html/cpconconnectionpoolingforsqlservernetdataprovider.htm
(2) Microsoft .NET Remoting：技術概述, http://www.microsoft.com/china/MSDN/library/NetFramework/default.mspx
(3) 效能比較：.NET Remoting 與 ASP.NET Web 服務, http://www.microsoft.com/china/msdn/archives/library/dnbda/html/bdadotnetarch14.asp
========================原文結束，開可以參考另外一篇文章：NET 串連池救生員
原文的一些補充觀點：
串連的建立和擷取：
Q: 如果我最大串連池是5個，那麼，在我的程式中，每次訪問，我都會使用New一個串連，然後Open，使用以後，然後Close，那麼在每次New的時候，會不會建立串連，。
A: 在每次New的時候，如果connection pool中有閒置串連，則不會建立新串連，否則會建立。
如果超過最多串連數，還能發出New的請求，但是該請求會放入隊列，直到connection pool中有空閑串連或串連請求逾時。
串連的效率問題：
Q: 要是可以對串連進行控制的話，應該怎麼控制，很簡單，我現在每次使用資料庫訪問時，都會New並close，我是擔心這樣的效率是不是會很低啊，想在串連上提高效率，要怎麼操作呢。
A: 使用connection pool時，New並不一定會建立新的串連，如果connection pool中有空閑串連，直接拿來就用。
close也只是將串連放入connection pool，供後續請求使用。因此不會有效率問題。
串連的存活時間：
當串連返回到池中時，將對它的建立時間和目前時間進行比較，如果時間間隔超過由 Connection Lifetime 指定的值（以秒為單位），則會毀壞該串連。在聚集配置中可以使用它來強制在運行伺服器和剛聯機的伺服器之間達到Server Load Balancer。
如果值為零 (0)，則將使池串連具有最大的逾時期限。預設值為0
SqlConnection.Close和.Dispose的比較：
sqlconnection.close和.dispose的相同地方是：
dispose肯定調用了close，所以close裡面有做的事情，dispose都包括
dispose還做了其他的資源的釋放，這樣在GC第一次回收的時候，省卻了dispose的步驟，加快了記憶體資源的回收速度。
如果沒有調用dispose，GC將在第一次回收先做dispose。
如果程式頻繁做new sqlconnection()，然後很快就做close並且不再使用這個connection（例如一些com+/asp.net/web service的程式），而且又不做dispose，那麼隨著這個程式被多次調用，被分配但未能儘快釋放的系統資源（通常是記憶體）會有很多，GC被迫回收的次數也相應增多，系統的整體效率就會變低（GC回收的時候是所有.net程式都暫停，等GC回收跑完才可以繼續，造成其他程式也受到影響）。所以對於這種情況，做dispose好。
但如果其他情況，例如Winforms的client，那麼沒有所謂。
最近發現本站有時候會出現串連不上伺服器的現象，通過sp_who查看資料庫連接時，發現網站應用程式程式佔用了比較多的串連數，當串連數達到一定數量後，網站就會出現串連不上資料庫的錯誤。那麼可以肯定，網站出現錯誤的原因，是資料庫連接沒有及時釋放的緣故——或者是開闢了太多的串連。網站的資料庫訪問邏輯沒有集中在一個資料庫訪問類中，而是在用到的時候建立，用完了關閉。那麼這種情況是否跟上文講的第一種情況相同呢。上文的第一種情況應該是針對winform應用程式來說的。但是本站的程式沒有想上文說的第二種情況，把資料訪問集中在一個資料訪問類中。這個問題有待實踐來驗證。
 來源：http://blogs.msdn.com/angelsb/archive/2004/08/25/220333.aspx

作者：angelsb


System.InvalidOperationException: Timeout expired.  The timeout period elapsed prior to obtaining a connection from the pool.   This may have occurred because all pooled connections were in use and max pool size was reached.

Timeout expired 異常是個很棘手的異常，想必幾乎每個人都碰到過。有時可真是對它咬牙切齒，拿它沒辦法。 angelsb這篇文章很好，希望對大家有用。我也是看到他講得很好，才翻譯過來的，水平有限，請多多指教.


System.InvalidOperationException: Timeout expired.  The timeout period elapsed prior to obtaining a connection from the pool.   This may have occurred because all pooled connections were in use and max pool size was reached.

哎!在另一個進程中,又出現了串連池已滿的問題,這是個最讓人頭痛卻又是最常出現的串連池問題之一.原因是在開發過程中很少碰到這個頭痛的問題,但在部署APP到用戶端時,卻總是不經意地跑出來了.我想,我應該花些許時間對這個問題進行一次完整的總結吧.

發生的本質是什麼?

我們來認真看一下可能會發生這種異常的兩種情況

1) 你使用了超過最大的串連池串連數(預設的最大串連數是100)

在大部分應用程式中,這種情況是很少出現的. 畢竟當你使用串連池時,100個並行串連是一個非常大的數字.根據我的經驗,會造成這種異常的原因的最大可能,應該是在一個純種下開啟了100個串連. 程式碼

SqlConnection[] connectionArray = new SqlConnection[101];
    for (int i = 0; i <= 100; i++)
    {
        connectionArray[i] = new SqlConnection("Server=.//SQLEXPRESS ;Integrated security=sspi;connection timeout=5");
        connectionArray[i].Open();
    }

解決方案:如果你確定你將會使用超過100個並行串連(在同一連接字串上),你可以增加最大串連數.

2) 串連泄漏

我個人認為的串連泄漏定義是你開啟了一個串連但你沒有在你的代碼中執行close()或dispose().這範圍不僅僅是你忘記了在connection後串連後使用dispose()或close()對期進行關閉,還包括一些你已經在相關connection後寫好了close()卻根本沒有起作用的情況.我們來看看下面的代碼: 程式碼

using System;
using System.Data;
using System.Data.SqlClient;

public class Repro
{
    public static int Main(string[] args)
    {
        Repro repro = new Repro();
        for (int i = 0; i <= 5000; i++)
        {
            try{ Console.Write(i+" ");    repro.LeakConnections(); }
            catch (SqlException){}
        }

        return 1;
    }
    public void LeakConnections()
    {    
        SqlConnection sqlconnection1 = new SqlConnection("Server=.//SQLEXPRESS ;Integrated security=sspi;connection timeout=5");
        sqlconnection1.Open();
        SqlCommand sqlcommand1 = sqlconnection1.CreateCommand();
        sqlcommand1.CommandText = "raiserror ('This is a fake exception', 17,1)";
        sqlcommand1.ExecuteNonQuery();  //this throws a SqlException every time it is called.
        sqlconnection1.Close(); //We are calling connection close, and we are still leaking connections (see above comment for explanation)
    }
}

這就是一個典型的例子,將這段代碼複製到visual Studio中,在 sqlconnection1.close()中設定一個斷點,編譯時間可以看到他永遠沒有執行,因為ExecuteNonQurery拋出了一個異常.之後你應該可以看到恐怖的逾時異常了. 在我的機子上,大約有170個串連被開啟. 我曾想讓其在每次調用的時候將異常拋出來達到降低連線逾時出現的機率,但當你考慮到將其部署到一個ASP.NET的應用程式的時候,任何一個泄漏都將讓你處於麻煩之中.

3)你是通過visual Studio中的sql debugging 來開啟或關閉串連的

這是一個眾所周知的Bug,可以看一下下面這個連結
http://support.microsoft.com/default.aspx?scid=kb;en-us;830118



如何在ADO.NET2.0中判斷是否是串連泄漏

在1.0或1.1中,我們很難去判斷是否是串連泄漏,至多可以通過一些效能指標或諸如此類的工作去實現.但在ADO.NET2.0中,如果你注意到NumberOfReclaimedConnections這個玩藝兒,就可以知道你的應用程式是否是串連泄漏了.


時刻注意修複相關的連接字串

修改相關的連接字串可以讓你暫時翻譯”逃過”一些異常,這是非常誘人的.特別是在一個高效能消耗時,修改它就顯示更為必要了.


這裡是一些讓你的應用程式能”運行良好”的非正常行為(搬起石頭砸自己的腳)

不要把Poooling=False

坦白的說,如果你將pooling設為關閉狀態,你當然不會再碰到逾時異常,可怕的是你的應用程式效能將大大降低,而你的串連仍然處於泄漏狀態.

不要把Connection LifeTime=1

這不是一個能清除異常的方法,但它可能是最接近的一個解決方案.你想告訴我們的是將所有的串連超過一秒鐘的串連都通通拋棄(正常的生命週期結束應該是在connetcio.close()後).我個人認為這種方法上關閉串連池沒什麼兩樣.除非你是在使用資料庫的叢集,否則你不應設定串連周期來達到目的.


不要將 Connection TimeOut=40000


非常愚蠢的選擇,你這是在告訴我們在拋出一個逾時異常之前,你在無限地等待一個串連轉變為可用的.幸虧在ASP.NET中將會在三分鐘之後取消一個進程.


不要將Max PoolSize=4000;
如果你將串連池的最大數設定到足夠大的時候,你最終會將這異常停止.但在另一方面,你將佔用了你的應用程式中才是真正需要的巨大的串連資源,這種做法只能飲鳩止渴.



解決方案:

你需要保證你每次調用串連的同時都在使用過後通過close()或dispose()對其執行了關閉.最簡單的辦法就是使用using,將你的串連泄漏方法修改成如下面的代碼樣式:

public void DoesNotLeakConnections()
    {    
        Using (SqlConnection sqlconnection1 = new SqlConnection("Server=.//SQLEXPRESS ;Integrated security=sspi;connection timeout=5")) {
            sqlconnection1.Open();
            SqlCommand sqlcommand1 = sqlconnection1.CreateCommand();
            sqlcommand1.CommandText = "raiserror ('This is a fake exception', 17,1)";
            sqlcommand1.ExecuteNonQuery();  //this throws a SqlException every time it is called.
            sqlconnection1.Close(); //Still never gets called.
                  } // Here sqlconnection1.Dispose is _guaranteed_
    }



FAQ：

Q:為什麼要這樣做

A:使用using結構等同於Try/…/Finally{ <using object>.Dispose() ) 即使當ExecuteNonQuery會拋出一個執行錯誤時,我們都可以保證finally模組將會執行



Q:我上面的代碼中如果沒有異常拋出的話,我可以使用close()或dispose()嗎

A:我們毫無顧忌地用他們中的任意一個,或兩個同時使用.在一個已經close或dipose()的串連中使用close()或dispose()是不會影響的

Q:Close()和Dispose()有什麼不同,我應該用哪一個好?
A:它們做的是同一件事,你可以調用他們中的任意一個,或兩個同時使用.

Q:你所說的"practically the same thing”是什麼意思?
A:Dispose()將會通過sqlConnection來清理相關的串連,之後執行close().它們沒有什麼本質的區別,你可以通過reflector來證明這點

Q:與close()相比,connection.dispose()會將串連些移除嗎?
A:不會

---------------------------------------------------------------

我的分享：

針對"Timeout expired"這個異常，我也查閱了很多資料。在國內我們很多project都會採用MS提供的sqlhelper這個封裝類。因為這個類中有本身的缺陷所致，所以出現的"Timeout expiered"異常機率大。我在國外的一篇文章中看到的解決方案是：

將SqlHelper中的cmd.CommandTimeout="你要設定的秒數"加上去，重新編譯.


if (trans != null)
cmd.Transaction = trans;

cmd.CommandType = cmdType;
cmd.CommandTimeout = 240;


通過搜尋，我找到了相關的一個代碼.
這個代碼是摘自國人的某位同行的,感謝
http://blog.csdn.net/long2006sky/archive/2007/07/09/1683459.aspx

eg:

**//// <summary>
    /// 執行查詢語句，返回DataTable
    /// </summary>
    /// <param name="SQLString">查詢語句</param>
    /// <param name="commTime">設定查詢Timeout</param>
    /// <returns>用於複雜查詢</returns>
    public static DataTable GetDataTable(string SQLString,int commTime)
    ...{
        string connectionString = System.Configuration.ConfigurationManager.AppSettings["connectionString"];
        using (System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(connectionString))
        ...{
            DataTable dt = new DataTable();
            try
            ...{
                connection.Open();
                System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter();
                System.Data.SqlClient.SqlCommand comm = new System.Data.SqlClient.SqlCommand(SQLString, connection);
                comm.CommandTimeout = commTime;
                da.SelectCommand = comm;
                da.Fill(dt);
            }
            catch (System.Data.SqlClient.SqlException ex)
            ...{
                throw new Exception(ex.Message);
            }
            return dt;
        }
    }

asp.net如何設定資料庫連接池的數量? 使用一組名稱-值對以連結字串的形式配置連結池。例如，可以配置池是否有效（預設是有效），池的最大、最小容量，用於打
開連結的排隊請求被阻斷的時間。下面的樣本字串配置了池的最大和最小容量。

"Server=(local); Integrated Security=SSPI; Database=Northwind;
Max Pool Size=75; Min Pool Size=5"

摘要


串連池允許應用程式從串連池中獲得一個串連並使用這個串連，而不需要為每一個串連請求重建立立一個串連。一旦一個新的串連被建立

並且放置在串連池中，應用程式就可以重複使用這個串連而不必實施整個資料庫連接建立過程。

當應用程式請求一個串連時，串連池為該應用程式分配一個串連而不是重建立立一個串連；當應用程式使用完串連後，該串連被歸還給串連

池而不是直接釋放。

如何?串連池

確保你每一次的串連使用相同的連接字串（和串連池相同）；只有連接字串相同時串連池才會工作。如果連接字串不相同，應用程式

就不會使用串連池而是建立一個新的串連。

優點

        使用串連池的最主要的優點是效能。建立一個新的資料庫連接所耗費的時間主要取決於網路的速度以及應用程式和資料庫伺服器的

（網路）距離，而且這個過程通常是一個很耗時的過程。而採用資料庫連接池後，資料庫連接請求可以直接通過串連池滿足而不需要為該請

求重新串連、認證到資料庫伺服器，這樣就節省了時間。

缺點

       資料庫連接池中可能存在著多個沒有被使用的串連一直串連著資料庫（這意味著資源的浪費）。

技巧和提示

1．   當你需要資料庫連接時才去建立串連池，而不是提前建立。一旦你使用完串連立即關閉它，不要等到垃圾收集器來處理它。

2．   在關閉資料庫連接前確保關閉了所有使用者定義的事務。

3．   不要關閉資料庫中所有的串連，至少保證串連池中有一個串連可用。如果記憶體和其他資源是你必須首先考慮的問題，可以關閉所有的連

接，然後在下一個請求到來時建立串連池。

串連池FAQ

1．   何時建立串連池。

當第一個串連請求到來時建立串連池；串連池的建立由資料庫連接的串連字元創來決定。每一個串連池都與一個不同的連接字串相關。

當一個新的串連請求到來時如果連接字串和串連池使用的字串相同，就從串連池取出一個串連；如果不相同，就建立一個串連池。

2．   何時關閉串連池。

當串連池中的所有串連都已經關閉時關閉串連池。

3．   當串連池中的串連都已經用完，而有新的串連請求到來時會發生什麼。

當串連池已經達到它的最大串連數目時，有新的串連請求到來時，新的串連請求將放置到串連隊列中。當有串連釋放給串連池時，串連池將

新釋放的串連分配給在隊列中排隊的串連請求。你可以調用close和dispose將串連歸還給串連池。

4．   我應該如何允許串連池。

對於.NET應用程式而言，預設為允許串連池。（這意味著你可以不必為這件事情做任何的事情）當然，如果你可以在SQLConnection對象的連

接字串中加進Pooling=true;確保你的應用程式允許串連池的使用。

5．   我應該如何禁止串連池。
ADO.NET預設為允許資料庫連接池，如果你希望禁止串連池，可以使用如下的方式：
1) 使用SQLConnection對象時，往連接字串加入如下內容：Pooling=False;
2) 使用OLEDBConnection對象時，往連接字串加入如下內容：OLE DB Services=-4;