ADO.NET数据连接池剖析

本篇文章起源于在gcr mvp open day的时候和c# mvp张响讨论连接池的概念而来的。因此单独写一篇文章剖析一下连接池。

为什么需要连接池
剖析一个技术第一个要问的是，这项技术为什么存在。
对于每一个到sql server的连接，都需要经历tcp/ip协议的三次握手，身份认证，在sql server里建立连接，分配资源等。而当客户端关闭连接时，客户端就会和sql server终止物理连接。但是，我们做过数据库开发的人都知道，每次操作完后关闭连接是再正常不过的事了，一个应用程序即使在负载不大的情况下也需要不停的连接sql server和关闭连接，同一个应用程序同时也可能存在多个连接。

因此，如果不断的这样建立和关闭连接，会是非常浪费资源的做法。因此ado.net中存在连接池这种机制。在对sql server来说的客户端的应用程序进程中维护连接池。统一管理ado.net和sql server的连接，既连接池保持和sql server的连接，当connection.open()时，仅仅从连接池中分配一个已经和sql server建立的连接，当connection.close(）时，也并不是和sql server物理断开连接，仅仅是将连接进行回收。

因此，连接池总是能维护一定数量的和sql server的连接，以便应用程序反复使用这些连接以减少性能损耗。

重置连接的sys.sp_reset_connection
连接是有上下文的，比如说当前连接有未提交的事务，存在可用的游标，存在对应的临时表。因此为了便于连接重复使用，使得下一个连接不会收到上一个连接的影响，sql server通过sys.sp_reset_connection来清除当前连接的上下文，以便另一个连接继续使用。

当在ado.net中调用了connection.close()时，会触发sys.sp_reset_connection。这个系统存储过程大概会做如下事情:
关闭游标
清除临时对象，比如临时表
释放锁
重置set选项
重置统计信息
回滚未提交的事务
切换到连接的默认数据库
重置trace flag
此外，根据bol上的信息：

"the sp_reset_connection stored procedure is used by sql
server to support remote stored procedure calls in a transaction. this stored
procedure also causes audit login and audit logout events to fire when a
connection is reused from a connection pool."

可以知道不能显式的在sql server中调用sys.sp_reset_connection,此外，这个方法还会触发audit login和audit logout事件。

一个简单的示例
下面我们通过一个简单的示例来看连接池的使用:
首先我分别使用四个连接，其中第一个和第二个连接之间有10秒的等待时间:

string connectionstring = "data source=.\\sql2012;database=adventureworks;uid=sa;pwd=sasasa";
sqlconnection cn1=new sqlconnection(connectionstring);
sqlcommand cmd1=cn1.createcommand();
cmd1.commandtext="select * from dbo.abcd";
cn1.open();
cmd1.executereader();
cn1.close();
response.write("连接关闭时间:"+datetime.now.tolongtimestring()+"<br />");
system.threading.thread.sleep(10000);
sqlconnection cn2=new sqlconnection(connectionstring);
sqlcommand cmd2=cn2.createcommand();
cmd2.commandtext="select * from dbo.abcd";
cn2.open();
cmd2.executereader();
cn2.close();
response.write("连接关闭时间:"+datetime.now.tolongtimestring()+"<br />");
sqlconnection cn3=new sqlconnection(connectionstring);
sqlcommand cmd3=cn3.createcommand();
cmd3.commandtext="select * from dbo.abcd";
cn3.open();
cmd3.executereader();
cn3.close();
response.write("连接关闭时间:"+datetime.now.tolongtimestring()+"<br />");
system.threading.thread.sleep(1500);
sqlconnection cn4=new sqlconnection(connectionstring);
sqlcommand cmd4=cn4.createcommand();
cmd4.commandtext="select * from dbo.abcd";
cn4.open();
cmd4.executereader();
cn4.close();
response.write("连接关闭时间:"+datetime.now.tolongtimestring()+"<br />");

下面我们通过profile截图:
    
我们首先可以看到，每一次close()方法都会触发exec sp_reset_connection
此外，我们在中间等待的10秒还可以看到sp51是不断的，剩下几个连接全部用的是spid51这个连接，虽然ado.net close了好几次，但实际上物理连接是没有中断的。
因此可以看出，连接池大大的提升了效率。