ADO.NET 中提供连接池避免 在业务操作中频繁打开,关闭连接。 当客户端释放连接后,连接池并未真正将数据库连接资源释放 , 而是根据连接字符串特征,将资源放到连接池中, 方便下次重用。
因此问题来了,如果连接池连接并未真正释放,那上一次打开连接相关session 设置是否会对下一次重用产生影响。 比如临时表 ,在前一个连接中创建一个临时表,下一次连接临时表还能用么?
举个例子:
如下配置,设置连接池连接量为3 , 分别通过同一组连接串打开数据库操作,如下,执行结果表明 每个子连接均能创建临时表,其中,第一个连接与最后一个连接使用同一个spid , 也是没有问题的。
SqlConnectionStringBuilder connStr = new SqlConnectionStringBuilder(); connStr.DataSource = @"10.20.26.4\MSSQL2008R2"; connStr.InitialCatalog = "SY_TEST"; connStr.IntegratedSecurity = false; connStr.UserID = "sa"; connStr.Password = "123456"; connStr.Pooling = true; //开启连接池 connStr.MinPoolSize = 0; //设置最小连接数为0 connStr.MaxPoolSize = 2; //设置最大连接数为50 connStr.ConnectTimeout = 10; //设置超时时间为10秒 string sourceConnection = connStr.ToString(); using (SqlConnection sqlConn = new SqlConnection(sourceConnection)) { sqlConn.Open(); SqlCommand commandInsert = new SqlCommand("create table #aa(aa int ) ", sqlConn); int result = commandInsert.ExecuteNonQuery(); commandInsert.CommandText = "SELECT @@SPID"; Console.WriteLine("spid:" + Convert.ToString(commandInsert.ExecuteScalar()) + " , 创建临时表"); using (SqlConnection sqlConn1 = new SqlConnection(sourceConnection)) { sqlConn1.Open(); SqlCommand commandInsert1 = new SqlCommand("SELECT @@SPID ", sqlConn1); Console.WriteLine("spid:" + Convert.ToString(commandInsert1.ExecuteScalar()) + " , 创建临时表"); commandInsert1.CommandText = " create table #aa(aa int ) "; commandInsert1.ExecuteNonQuery(); using (SqlConnection sqlConn2 = new SqlConnection(sourceConnection)) { sqlConn2.Open(); SqlCommand commandInsert2 = new SqlCommand("SELECT @@SPID ", sqlConn2); Console.WriteLine("spid:" + Convert.ToString(commandInsert2.ExecuteScalar()) + " , 创建临时表"); commandInsert2.CommandText = " create table #aa(aa int ) "; commandInsert2.ExecuteNonQuery(); } } } using (SqlConnection sqlConn = new SqlConnection(sourceConnection)) { sqlConn.Open(); SqlCommand commandInsert = new SqlCommand("create table #aa(aa int ) ", sqlConn); int result = commandInsert.ExecuteNonQuery(); commandInsert.CommandText = "SELECT @@SPID"; Console.WriteLine("spid:" + Convert.ToString(commandInsert.ExecuteScalar()) + " , 创建临时表"); } Console.ReadKey();
执行结果:
如下,将连接池数量改成 2 , 如下异常表明,连接资源来自连接池.
接下来,设置锁超时时间 , 在第一次连接将超时时间设为 0 , 后面三个连接查询只读超时时间 , 仍然互不影响:
SqlConnectionStringBuilder connStr = new SqlConnectionStringBuilder(); connStr.DataSource = @"10.20.26.4\MSSQL2008R2"; connStr.InitialCatalog = "SY_TEST"; connStr.IntegratedSecurity = false; connStr.UserID = "sa"; connStr.Password = "123456"; connStr.Pooling = true; //开启连接池 connStr.MinPoolSize = 0; //设置最小连接数为0 connStr.MaxPoolSize = 3; //设置最大连接数为50 connStr.ConnectTimeout = 10; //设置超时时间为10秒 string sourceConnection = connStr.ToString(); using (SqlConnection sqlConn = new SqlConnection(sourceConnection)) { sqlConn.Open(); SqlCommand commandInsert = new SqlCommand("set lock_timeout 0;", sqlConn); int result = commandInsert.ExecuteNonQuery(); commandInsert.CommandText = "select @@lock_timeout "; Console.WriteLine("lock_timeout:" + Convert.ToString(commandInsert.ExecuteScalar()) + " , 创建临时表"); using (SqlConnection sqlConn1 = new SqlConnection(sourceConnection)) { sqlConn1.Open(); SqlCommand commandInsert1 = new SqlCommand("select @@lock_timeout", sqlConn1); Console.WriteLine("lock_timeout:" + Convert.ToString(commandInsert1.ExecuteScalar()) + " , 创建临时表"); //commandInsert1.CommandText = " create table #aa(aa int ) "; //commandInsert1.ExecuteNonQuery(); using (SqlConnection sqlConn2 = new SqlConnection(sourceConnection)) { sqlConn2.Open(); SqlCommand commandInsert2 = new SqlCommand("select @@lock_timeout", sqlConn2); Console.WriteLine("lock_timeout:" + Convert.ToString(commandInsert2.ExecuteScalar()) + " , 创建临时表"); //commandInsert2.CommandText = " create table #aa(aa int ) "; //commandInsert2.ExecuteNonQuery(); } } } using (SqlConnection sqlConn = new SqlConnection(sourceConnection)) { sqlConn.Open(); SqlCommand commandInsert = new SqlCommand("select @@lock_timeout", sqlConn); Console.WriteLine("lock_timeout:" + Convert.ToString(commandInsert.ExecuteScalar()) + " , 创建临时表"); } Console.ReadKey();
执行结果:
结论: 连接池中链接重用时 临时表 , session 等信息是隔离的 , 但是涉汲到不同数据库切换,需要显示调用 use db 指令。