Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool

如果應用程式遇到了下面錯誤資訊，那么意味著連接池（connection pool）的連接數量由于一些原因導致其超過了Max Pool Size引數的限制，

英文錯誤資訊：

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

中文錯誤資訊：

超時時間已到，超時時間已到，但是尚未從池中獲取連接，出現這種情況可能是因為所有池連接均在使用，并且達到了最大池大小，

在介紹這個錯誤前，我們必須搞清楚一些概念，后續再展開分析這個問題出現的原因，以及出現后如何解決問題，

連接池（Connection Pool）

對于共享資源，有一個很著名的設計模式：資源池(resource pool)，該模式正是為解決資源頻繁分配、釋放所造成的問題，資料庫連接池（connection pool）的基本思想就是為資料庫連接建立一個“緩沖池”，預先在緩沖池中放入一定數量的連接，當需要建立資料庫連接時，只需要從緩沖池中取出一個連接，使用完畢后再放回去，資料庫連接池負責分配、管理和釋放資料庫連接，它允許應用程式重復使用一個現有的資料庫連接，而不是再重新建立一個，避免重復多次的打開資料庫連接而造成的性能的下降問題和系統資源的浪費問題，

連接池相關引數

由于訪問資料庫的驅動很多，不同驅動的連接池引數等可能有所差異，具體以實際情況為準，我們以ADO.NET為例，來分析一個連接資料庫的連接配置，providerName="System.Data.SqlClient"， 這里沒有設定Max Pool Size、Pooing等引數，那么其實都是取其對應的默認值，其中pooling引數默認情況下為true，Max Pool Size值為100

<add name="xxxx" connectionString="Data Source = 192.168.xxx.xxx;Initial Catalog=xxx;User ID=xxxx;Password=xxxx;MultipleActiveResultSets=true;" providerName="System.Data.SqlClient" /> 

錯誤出現的原因：

If we try to obtain connections more than max pool size, then ADO.NET waits for Connection Timeout for the connection from the pool. If even after that connection is not available, we get the following exception.

"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"

The timeout expired. The timeout expired 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

When you open an SQL Connection object, it always takes from an available pool of connections. When you close the connection, asp.net will release the connection to the pool of connections so that next connection object can use it.If you open connections with out closing them and when the pool reaches maximum connections, it will throw the specified error. Make sure you are not opening connection inside loop, if you open connection make sure you are closing it immedietly after you execute the query.

錯誤的原因其實就是連接池中的連接被用完了，后面的請求建立資料庫連接時，連接池中沒有可用的資源（連接），那么就分配到等待連接池分配的佇列中，而其等待的時間超過了引數“Connection Timeout”的值，就拋出這個例外資訊，

錯誤的解決方案：

1：業務量突然暴增，當前并發請求連接資料的數量超過了連接池的最大連接數（默認情況下，最大連接數是100），出現這種情況時，必須調整資料庫連接字串引數Max Pool Size值為一個合適的值，這種情況一般較少見，

“Connection Pooling and the "Timeout expired" exception FAQ“中這篇文章中有代碼模擬這種情況，這里就不做展開了，有興趣的自己模擬一下即可，

  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();

      }

2： leaking connections問題導致，

其實這里個人理解為資料庫連接沒有正常關閉，有些是代碼邏輯問題導致，有些是沒有正確處理例外問題，

案例1：

這個案例來自“Connection Pooling and the "Timeout expired" exception FAQ“中，它模擬的是一種邏輯例外問題，如果開發人員這樣寫代碼的話，那么即使出現例外，但是資料庫連接永遠不會釋放（sqlconnection1.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)

    }

}

解決方法：

    我們要保證每次呼叫連接的同時都在使用過后通過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_

      }

案例2：

案例2來自官方檔案“Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool”，其實我也見過幾起類似這樣的案例，有現成的例子，就沒必要自己構造這樣的案例，

static void Main(string[] args)

        {

            string connString = @"Data Source=<your server>;Initial Catalog=Northwind;Integrated Security=True; Max Pool Size=20; Connection Timeout=10";

            try

            {

                for (int i = 0; i < 50; i++)

                {

                    // Create connection, command and open the connection

                    SqlConnection sc = new SqlConnection(connString);

                    SqlCommand sCmd = new SqlCommand("SELECT * FROM Shippers", sc);

                    sc.Open();

                    // Print info

                    Console.WriteLine("Connections open: {0}", i.ToString());

                    // This will cause the error to show.

                    SqlDataReader sdr = sCmd.ExecuteReader();

                    sdr.Close();

                    // Replacing the two rows above with these will remove the error

                    //SqlDataReader sdr = sCmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);

                    //sdr.Close();

                    // -- or --

                    // Explicity close the connection

                    //sc.Close();

                }

                    // -- or --

                    // Run all in a Using statement (in this case, replace the whole for loop with the loop below.).

                    //for (int i = 0; i < 50; i++)

                    //{

                    //    using (SqlConnection sc = new SqlConnection(connString))

                    //    {

                    //        SqlCommand sCmd = new SqlCommand("SELECT * FROM Shippers", sc);

                    //        sc.Open();

                    //        Console.WriteLine("Conns opened " + i.ToString());

                    //        SqlDataReader sdr = sCmd.ExecuteReader();

                    //        sdr.Close();

                    //    }

                    //}

            }

            catch (Exception e)

            {

                Console.WriteLine(e);

            }

        }

3：通過visual Studio中的sql debugging 來打開或關閉連接的， 這個參考“Connection Pooling and the "Timeout expired" exception FAQ中”的詳細介紹，個人倒是沒有遇到過這種情況，

個人的一些經驗體會，遇到這些問題后，我們首先應該監控資料庫的連接數量資訊，類似下面這樣的SQL，根據實際情況來調整（例如，有些程式的IIS部署在某個應用服務器上，我們通過hostname基本上就能定位）

SELECT  hostname ,

        COUNT(*) AS connection_sum

FROM    sys.sysprocesses

GROUP BY hostname

ORDER BY 2 DESC;

SELECT  loginame ,

        COUNT(*) AS connection_sum

FROM    sys.sysprocesses

GROUP BY loginame

ORDER BY 2 DESC;

然后要跟開發人員協作檢查連接資料庫的配置資訊（連接字串設定），例如Max Pool Size的大小設定，然后就是最麻煩的問題，怎么定位到root cause呢？ 這就需要開發人員去檢查了，已經脫離了DBA的掌控了，個人經驗（不做開發多年了，經驗都過時了），如果突然出現這個問題，并且有源代碼版本控制管理，最好找出最近的修改部分，進行細致的檢查驗證，基本上就能定位到問題根源了，但是能否做到細致檢查，因人而異，這個往往最難掌控，跟個人的態度、經驗、能力有很大關系，

參考資料：

https://docs.microsoft.com/zh-cn/archive/blogs/angelsb/connection-pooling-and-the-timeout-expired-exception-faq

https://blogs.msdn.microsoft.com/spike/2008/08/25/timeout-expired-the-timeout-period-elapsed-prior-to-obtaining-a-connection-from-the-pool/

標籤：SQL Server

上一篇：分頁查詢sql

下一篇：Sqlserver 新建查詢頁，資料庫中表存在，為什么查詢的時候陳述句有紅色波浪線