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

大家好，我是考100分的小小码，祝大家学习进步，加薪顺利呀。今天说一说Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool,希望您对编程的造诣更进一步.

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

代码100分

Keyword	Default	Description
Max Pool Size	100	The maximum number of connections that are allowed in the pool. Valid values are greater than or equal to 1. Values that are less than Min Pool Size generate an error.
Min Pool Size	0	The minimum number of connections that are allowed in the pool. Valid values are greater than or equal to 0. Zero (0) in this field means no minimum connections are initially opened. Values that are greater than Max Pool Size generate an error.
Pooling	“true”	When the value of this key is set to true, any newly created connection will be added to the pool when closed by the application. In a next attempt to open the same connection, that connection will be drawn from the pool. Connections are considered the same if they have the same connection string. Different connections have different connection strings. The value of this key can be “true”, “false”, “yes”, or “no”.
PoolBlockingPeriod	Auto	Sets the blocking period behavior for a connection pool. See PoolBlockingPeriod property for details.

Keyword	Default	Description
Max Pool Size	100	池中允许的最大连接数。有效值大于或等于1。小于最小池大小的值会生成错误。
Min Pool Size	0	池中允许的最小连接数。有效值大于或等于0。此字段中的零（0）表示最初没有打开最小值连接。大于最大池大小的值会生成错误。
Pooling	“true”	如果此项的值设置为 true，则在应用程序关闭时，将向池中添加任何新创建的连接。在下一次尝试打开相同的连接时，该连接将从池中提取。如果连接具有相同的连接字符串，则将其视为相同。不同连接具有不同的连接字符串。此键的值可以为 “true”、“false”、“yes” 或 “no”。
PoolBlockingPeriod	Auto	设置连接池的阻塞期行为。有关详细信息，请参阅 PoolBlockingPeriod 属性。

错误出现的原因：

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“中这篇文章中有代码模拟这种情况，这里就不做展开了。有兴趣的自己模拟一下即可。

代码100分  SqlConnection[] connectionArray = new SqlConnection[101];

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

代码100分      {

                  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的掌控了。个人经验（不做开发多年了，经验都过时了），如果突然出现这个问题，并且有源代码版本控制管理，最好找出最近的修改部分，进行细致的检查验证，基本上就能定位到问题根源了。但是能否做到细致检查，因人而异。这个往往最难掌控，跟个人的态度、经验、能力有很大关系。