ADO.NET数据连接

【IT168 技术文档】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

    2、有多少个连接池 

    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。在单数据库服务器的环境下没必要把连接销毁，因为销毁后一段时间又需要建立。 


    连接池对应用系统的性能提高起着至关重要的作用，但需要连接池有其适用范围，它适用于需要频繁访问数据库的应用系统。对于低频率（例如一天只有几次）的数据库访问应用系统就不必要，因为一直保留一个低使用频率的“物理连接”不如使用一次就建立一次好。

[源:http://tech.it168.com/db/s/2006-10-18/200610181013413.shtml]

1