数据库连接及线程池

曾几何时，记住了一句话：“建立数据库连接是一个代价高昂的过程”，也从那时开始，我在构建系统时，一旦建立起了数据库连接，就保存起来，任何要用数据库的地方，都使用这个数据库连接对象进行操作。

       这样的行为，在以前写的单线程程序中，倒也可以接受，但在这次写的多线程程序中，就出现问题了。在这次的程序中，最开始设计时，在工作线程类中，设置了一个SqlConnection的静态成员以接受系统中已经建立起的数据库连接对象。大致的代码如下：

public class NoPoolThread
    {
        public static System.Data.SqlClient.SqlConnection DatabaseConnection = null;
        private System.Threading.Thread WorkThread = null;
        private bool Continue = false;
        public void Start()
        {
            if (WorkThread == null)
            {
                ThreadStart workfun = new ThreadStart(DoWork);
                WorkThread = new Thread(workfun);
                Continue = true;
                WorkThread.Start();
            }
        }
        private void DoWork()
        {
            System.Diagnostics.Debug.Assert(DatabaseConnection != null);
            while (Continue)
            {
                //省略了使用数据库连接对象进行操作的代码
                Thread.Sleep(300);
            }// end while
        }
        public void Stop()
        {
            if (WorkThread != null)
            {
                Continue = false;
            }
        }
    }

 

在主程序中，使用下面的代码来启动工作线程

凭借以往的经验，满心期望着：NoPoolThread你给我好好干吧，成功就在眼前。殊不知，异常马上就出现，基本上是说：“已有打开的与此命令相关联的 DataReader，必须首先将它关闭。ExecuteNonQuery 要求已打开且可用的连接。连接的当前状态为已关闭”。

      

然后把threads数组长度改成1，即又恢复到只有一个线程使用数据库连接对象时，就又恢复正常。

      

       这样就说明了，在多个线程中使用同一个SqlConnection对象进行数据库操作的想法是不现实的也是行不通的。

 

       于是，就大着胆子，冒着“巨大的代价”，进行修改，在任何使用SqlConnection的地方，都临时创建的SqlConnection对象一个对象，但是要创建对象，需要连接字符串啊，怎么来呢，不想重新写一遍，就用现成的、已经传到线程对象里面的哪个数据库连接对象里面的连接字符串吧，于是修改代码如下：

private void DoWork()
{
     System.Diagnostics.Debug.Assert(DatabaseConnection != null);
     SqlConnection connection = new SqlConnection(DatabaseConnection.ConnectionString); 

     while (Continue)
     {
        //使用connection对象
       Thread.Sleep(300);
      }// end while

      connection.Close();
}

3.2 SqlConnection的连接

       这样修改的结果，每个线程使用自己的SqlConnection对象对数据库进行操作，使各个线程之间互不影响。根据测试，程序可以顺畅运行，而且在性能上没有明显损失，这多少有些以为，于是继续查找资料。由于使用的时SqlConnection对象，所以就以它为线索，首先从MSDN开始，MSDN给出的解释有如下内容：

Ø         SqlConnection 对象表示与 SQL Server 数据源的一个唯一的会话。对于客户端/服务器数据库系统，它等效于到服务器的网络连接。SqlConnection 与 SqlDataAdapter 和 SqlCommand 一起使用，可以在连接 Microsoft SQL Server 数据库时提高性能。对于所有第三方 SQL 服务器产品以及其他支持 OLE DB 的数据源，请使用 OleDbConnection。

Ø         当创建 SqlConnection 的实例时，所有属性都设置为它们的初始值。有关这些值的列表，请参见 SqlConnection 构造函数。

Ø         如果 SqlConnection 超出范围，则不会将其关闭。因此，必须通过调用 Close 或 Dispose 显式关闭该连接。它们在功能上是等效的。如果将连接池值 Pooling 设置为 true 或 yes，则也会释放物理连接。还可以打开 using 块内部的连接，以确保当代码退出 using 块时关闭该连接。

Ø         注意：若要部署高性能应用程序，则必须使用连接池。在使用用于 SQL Server 的 .NET Framework 数据提供程序时，不必启用连接池，因为提供程序会自动对此进行管理，不过您可以修改某些设置。

 

       从上面的解释来看，我修改后的代码，无意间启用了数据库连接池——因为除非你在连接字符串里面明确禁用连接池功能，否则默认的数据库连接是从连接池中获得连接的。

      

3.3 连接池的相关概念

这里，既然提及了连接池，自己在这方面的认识又很模糊，于是就自己给自己了一个学习连接池的机会。

连接池及ADO.NET

连接到数据库服务器通常由几个需要很长时间的步骤组成。必须建立物理通道（例如套接字或命名管道），必须与服务器进行初次握手，必须分析连接字符串信息，必须由服务器对连接进行身份验证，必须运行检查以便在当前事务中登记，等等。——这就是建立数据库连接的代价，但仍然只是定性的描述。

 

实际上，大多数应用程序仅使用一个或几个不同的连接配置。这意味着在执行应用程序期间，许多相同的连接将反复地打开和关闭。为了使打开的连接成本最低，ADO.NET 使用称为连接池的优化方法。

 

连接池是一种在打开数据存储区的连接时提高应用程序性能的机制，可以显著提高应用程序的性能和可缩放性。使用连接池减少新连接需要打开的次数。

 

池进程保持物理连接的所有权。通过为每个给定的连接配置保留一组活动连接来管理连接。只要用户在连接上调用 Open，池进程就会检查池中是否有可用的连接。如果某个池连接可用，会将该连接返回给调用者，而不是打开新连接。应用程序在该连接上调用 Close 时，池进程会将连接返回到活动连接池集中，而不是真正关闭连接。连接返回到池中之后，即可在下一个 Open 调用中重复使用。

 

只有配置相同的连接可以建立池连接。ADO.NET 同时保留多个池，每个配置一个池。连接由连接字符串以及 Windows 标识（在使用集成的安全性时）分为多个池。

 

池连接可以大大提高应用程序的性能和可缩放性，考虑一个访问SQL Server数据库的典型ASP.NET或WebServices应用程序。客户端应用程序每次需要查询数据库时，就会在服务器端代码中进行往返，以打开SqlConnection来执行查询。在许多此类应用程序中，这一代码以相同凭据一次又一次地连接到相同数据库。理论上，这意味着客户端应用程序每次需要执行查询时，服务器端代码需要执行三个操作——登录到数据库(需要检查所提供的凭据)、执行查询、然后注销。连接池可以真正地提高此类应用程序的性能——通过将内部连接存储在池中，并在以后进行重复利用，就不再因为登录数据库以及从中注销而降低性能。对SqlConnection对象的Open和Close方法的调用可以短时间内返回，从而可以提高代码的性能和响应速度(请参见3.1图)。

图3.1 典型ASP.NET或WebServices应用程序中的连接池

默认情况下，ADO.NET 中启用连接池。除非显式禁用，否则，连接在应用程序中打开和关闭时，池进程将对连接进行优化。还可以提供几个连接字符串修饰符来控制连接池的行为。有关更多信息，请参见MSDN中的“使用连接字符串关键字控制连接池”。

 

在调用SqlConnection对象的Close方法时，SQL Client .NET数据提供程序并不实际关闭内部连接。相反，数据提供程序将该内部连接存储到一个池中，以便在以后再次使用。甚至在SqlConnection对象被处理之后，该内部连接也保留在池中。如果在以后使用相同连接字符串和凭据调用SqlConnection对象的Open方法，将会再次使用同一内部连接与数据库进行通信。

 

因此，微软建议在使用完连接时一定要关闭或断开连接，以便连接可以返回池。要关闭连接，可以使用 Connection 对象的 Close 或 Dispose 方法，也可以通过在 C# 的 using 语句中或在 Visual Basic 的 Using 语句中打开所有连接。不是显式关闭的连接可能不会添加或返回到池中。例如，如果连接已超出范围但没有显式关闭，则仅当达到最大池大小而该连接仍然有效时，该连接才会返回到连接池中。不是显式关闭的连接可能无法返回池。例如，如果连接已超出范围但没有显式关闭，则仅当达到最大池大小而该连接仍然有效时，该连接才会返回到连接池中。参见下面的示例。

 

另外，如果你希望确认是否真正再次利用了同一内部连接，可以使用.NET Reflection中的功能以可编程方式访问私有InnerConnection属性的内容。以下代码(其需要对System.Reflection命名空间的引用)在Using代码块中打开一个SqlConnection，并存储SqlConnection的InnerConnection属性的值。通过利用Using代码块，在该代码块的末尾隐式处理了SqlConnection。此代码在Using代码块中打开另一个SqlConnection，并存储SqlConnection的InnerConnection属性的值。最后，此代码对比InnerConnection属性的内容，确认它们实际上为同一对象。

string strConn = @"Data Source=.\SQLExpress;Integrated Security=True;";

PropertyInfo propInnerConn;

propInnerConn = typeof(SqlConnection).GetProperty("InnerConnection",

                                BindingFlags.NonPublic | BindingFlags.Instance);

object objInnerConn1, objInnerConn2;

using (SqlConnection cn = new SqlConnection(strConn))

{

    cn.Open();

    objInnerConn1 = propInnerConn.GetValue(cn, null);

    cn.Close();

}

 

using (SqlConnection cn = new SqlConnection(strConn))

{

    cn.Open();

    objInnerConn2 = propInnerConn.GetValue(cn, null);

    cn.Close();

}

 

Console.WriteLine(objInnerConn1 == objInnerConn2);

 

两个SqlConnection对象是在不同的Using代码块中创建的，所以其资源将在每个Using代码块的末尾被清除。InnerConnection属性的内容及其所封装的物理连接没有被处理，而是存储在池中，以便在以后被再次利用。

 

注意    如果在连接字符串中禁用了连接池(稍后将解释如何禁用)，将会看到此内部连接不能被重复利用。

 

连接池的创建和清除

       在初次打数据库开连接时——例如调用SqlConnection.Open方法时，池进程将根据完全匹配算法创建连接池，该算法将池与连接中的连接字符串关联。每个连接池与不同的连接字符串关联。打开新连接时，如果连接字符串并非与现有池完全匹配，将创建一个新池。按进程、按应用程序域、按连接字符串以及（在使用集成的安全性时）按 Windows 标识来建立池连接。

       ADO.NET 2.0 引入了两种新的方法来清除池：ClearAllPools 和 ClearPool。ClearAllPools 清除给定提供程序的连接池，ClearPool 清除与特定连接关联的连接池。如果在调用时连接正在使用，将进行相应的标记。连接关闭时，将被丢弃，而不是返回池中。

 

连接的添加和移除

       连接池是为每个唯一的连接字符串创建的。当创建一个池后，将创建多个连接对象并将其添加到该池中，以满足最小池大小的要求。连接根据需要添加到池中，但是不能超过指定的最大池大小（默认值为 100）。连接在关闭或断开时释放回池中。

在请求 SqlConnection 对象时，如果存在可用的连接，将从池中获取该对象。连接要可用，必须未使用，具有匹配的事务上下文或未与任何事务上下文关联，并且具有与服务器的有效链接。

连接池进程通过在连接释放回池中时重新分配连接，来满足这些连接请求。如果已达到最大池大小且不存在可用的连接，则该请求将会排队。然后，池进程尝试重新建立任何连接，直到到达超时时间（默认值为 15 秒）。如果池进程在连接超时之前无法满足请求，将引发异常。

 

       连接池进程定期扫描连接池，查找没有通过 Close 或 Dispose 关闭的未用连接，并重新建立找到的连接。如果应用程序没有显式关闭或断开其连接，连接池进程可能需要很长时间才能重新建立连接，所以，最好确保在连接中显式调用 Close 和 Dispose。

如果连接长时间空闲，或池进程检测到与服务器的连接已断开，连接池进程会将该连接从池中移除。注意，只有在尝试与服务器进行通信之后才能检测到断开的连接。如果发现某连接不再连接到服务器，则会将其标记为无效。无效连接只有在关闭或重新建立后，才会从连接池中移除。

如果存在与已消失的服务器的连接，那么即使连接池管理程序未检测到已断开的连接并将其标记为无效，仍有可能将此连接从池中取出。这种情况是因为检查连接是否仍有效的系统开销将造成与服务器的另一次往返，从而抵消了池进程的优势。发生此情况时，初次尝试使用该连接将检测连接是否曾断开，并引发异常。

 

上述连接的添加和移除，全都是由后台运行的池进程管理的。

 

禁用连接池

您可能不希望使用连接池。例如，如果正在使用一个直接与数据库进行通信的简单Windows应用程序，那么可能希望禁用连接池。在采用这一架构时，各个客户端应用程序需要自己的连接。在启用连接池时，每个应用程序的连接被放入池中，如果在清除连接池之前重新打开该连接，将重复利用放入池中的连接。所以，如果应用程序频繁重复使用连接，那么在启用连接池的情况下，对SqlConnection.Open的调用将会更快速地返回。但是，这种方法将会导致在任意给定时刻存在许多活动的数据库连接。禁用连接池将会降低任意时刻的活动数据库连接数目，但这样会强制所有对SqlConnection.Open的调用都建立一个新的数据库连接。

 

如果希望禁用连接池，可以通过向连接字符串中添加Pooling=False，逐个连接地禁用连接池。幸运的是，在ADO.NET 2.0中不再需要记忆诸如此类的属性。如果存在疑问，可以检查SqlConnectionStringBuilder类的选项。在这个类中可以找到一个Pooling属性，其取值为Boolean类型。默认情况下，此值被设置为True。将该值设置为False将会禁止将该连接放入池中。因此，在调用SqlConnection对象的Close方法时，将会关闭与数据库的实际连接。

注意    在“偶尔进行连接”的Windows应用程序中，使用连接池可能很有帮助，具体取决于应用程序。如果应用程序希望定期重新连接到数据库，则可以发挥连接池的作用，将与数据库的物理连接保持打开状态，至少暂时如此。如果在从池中删除该物理连接之前，应用程序尝试重新连接到该数据库，则连接池逻辑(pooling logic)将会重新使用与该数据库的物理连接。

 

查看数据库连接

       在使用了数据库连接池的情况下，那么建立到数据库上的连接到底怎么样呢，这可以使用Windows的性能查看器来看。在运行SqlServer的服务器上，使用【管理工具】→【性能】就可以调出性能查看器，然后添加计数器：1、在性能对象下拉框中选择：SQLServer：General Statistics，然后选择User Connections计数器，这样就可以看到连接到SQL Server上的数据库连接数了。

 

       为此，做了一个测试函数，该函数不断的建立数据库连接，读取userinfo表的总函数，然后返回，代码如下。

private int GetNextAge()

        {

            string sql = "SELECT  COUNT(username) FROM   userinfo";

 

            int nextage = -1;

 

            try

            {

                SqlConnection connection = new SqlConnection(ConnectString);

                connection.Open();                              

 

                SqlDataAdapter da = new SqlDataAdapter(sql, connection);

                DataSet ds = new DataSet();

                da.Fill(ds);

 

                if (ds.Tables.Count != 0 &&

                    ds.Tables[0].Rows.Count != 0)

                {

                    string nextagevalue = ds.Tables[0].Rows[0][0].ToString();

                    nextage = Int32.Parse(nextagevalue);

                }

                //下面一句标记为：【关闭语句】可以注释掉下面一句观看情况

                connection.Close();

 

            }

            catch (Exception e)

            {

                Console.WriteLine(e.Message);

                Console.WriteLine(e.StackTrace);

            }

 

            return nextage + 1;

        }

 

下面是上面代码的运行中对性能计数器的截图，同时，也把进程的CPU、内存占用情况也列了出来。

① 及时关闭连接，不启用连接池。

图3.2：及时关闭连接，不使用连接池——开始循环调用

图3.3：及时关闭连接，不使用连接池——结束循环调用

② 及时关闭连接，启用连接池

图3.4：及时关闭连接，启用连接池——开始循环调用

图3.5：及时关闭连接，启用连接池——结束循环调用

③ 不及时关闭连接，不启用连接池。

图3.6：不及时关闭连接，不启用连接池——开始循环调用

图3.7：不及时关闭连接，不启用连接池——结束循环调用

④ 不及时关闭连接，启用连接池。

图3.8：不及时关闭连接，启用连接池——开始循环调用

图3.9：不及时关闭连接，启用连接池——结束循环调用

从上面的测试可以看出

Ø         不使用连接池，如果及时关闭连接，那么数据库连接数还有可能比使用线程池时低。这是因为，池进程可能并没有真正释放一些链接导致的。

Ø         使用线程池时，CPU都保持在一个较低的水平

SqlConnection connection = null;

NoPoolThread[] threads = new NoPoolThread[99];

private void button1_Click(object sender, EventArgs e)
{
     connection = new SqlConnection(GetConnectString());
     connection.Open();
     NoPoolThread.DatabaseConnection = connection;

     for ( int i=0; i<threads.Length; i++)
     {
         threads[i] = new NoPoolThread();
         threads[i].Start();
     }
}