Understanding Database Connection

Database connection, which can also be used in other words. This is a common database connection.

View sourceprint?

1	Data Source=.;Initial Catalog=test;Persist Security Info=True;User ID=testuser;Password=123456;Min Pool Size=10;Max Pool Size=150;Connection Lifetime=10

Who doesn't understand this? What I want to talk about here is

Min pool size = 10; Max pool size = 150; connection lifetime = 10

Here we will talk about the database connection pool
The connection pool is enabled by default. The above attribute is used to configure the connection pool. This means that when you send a database Request to your page, you do not have to create a database connection, but it may be from an existing connection.
In the pool, activate a connection to process your requests. Similarly, when you call close in your code to explicitly close the database connection, it is not necessarily that the database connection is actually closed, if the current connection pool is
When a connection does not meet the min pool size requirement, it only returns to the connection pool and waits for the next command to activate it.

Pass

View sourceprint?

1	sp_who 'testusers'// Testusers is the user name you specified when establishing the connection

View your current connections

How does the connection pool work according to your configuration ??

When the first connection request arrives, it will create a min pool in a short time.
The size of the specified connection, although the number of concurrent requests may not reach this number. When the number of concurrent requests is lower than this value, no connection will be created or closed in the future, unless you close the application;
When the number is greater than the minimum value, a connection is created, and the number of concurrent requests is greater than the Max pool.
When the maximum size is exceeded, the request enters the waiting queue and waits for idle connections. If the request reaches the application or database limit, the connection timeout exception is returned.

Who doesn't understand this principle? Yes, this is the case when the program is normal. What is the case when the program is abnormal .. Normal programs: all operations on the database are explicitly called after the operation.
Close () or dispose () to close the connection. The following example shows how to verify whether your application has a connection that is not closed. I am also based on this need
Please.

Today's programs are encapsulated for data operations. Yes, operations other than reader such as dataset and datatable are all closed in the database operation class. Well,
Yes, but the reader is closed only after the developer is used up, which may be determined by the quality of the developer. Don't tell me, you don't need reader at all.
It does not matter to you.

Start the experiment with reader. We set the min pool size to 1.

The first is the normal program:

View sourceprint?

01	using (SqlDataReader reader = Maticsoft.DBUtility.DbHelperSQL.ExecuteReader("select top 1 * from protuct order by id desc"))

02

{

03	while (reader.Read())

04

{

05	Response.Write(reader["Name"].ToString());

06

}

07

}

08	using (SqlDataReader reader2 = Maticsoft.DBUtility.DbHelperSQL.ExecuteReader("select top 1 * from users order by userid desc"))

09

{

10	while (reader2.Read())

11

{

12	Response.Write(reader2["userName"].ToString());

13

}

14

}

This program is placed on a page. No matter how many users or users simultaneously request this page, after the request processing is complete, you can view it through sp_who. I know why this is called a normal program. It is in line with normal reasoning, haha.

The following is a problematic program:

View sourceprint?

01	SqlDataReader reader3 = Maticsoft.DBUtility.DbHelperSQL.ExecuteReader("select top 1 * from users order by userid asc");

02	while (reader3.Read())

03

{

04	Response.Write(reader3["userName"].ToString());

05

}

06	SqlDataReader reader4 = Maticsoft.DBUtility.DbHelperSQL.ExecuteReader("select top 1 * from protuct2 order by id asc");

07	while (reader4.Read())

08

{

09	Response.Write(reader4["Name"].ToString());

10

}

11	SqlDataReader reader5 = Maticsoft.DBUtility.DbHelperSQL.ExecuteReader("select top 1 * from protuct order by id desc");

12	while (reader5.Read())

13

{

14	Response.Write(reader5["Name"].ToString());

15

}

16	SqlDataReader reader6 = Maticsoft.DBUtility.DbHelperSQL.ExecuteReader("select top 1 * from users order by userid desc");

17	while (reader6.Read())

18

{

19	Response.Write(reader6["userName"].ToString());

20

}

As you can see, the reader is not explicitly closed. To see the effect, there are a few more.

First, I will refresh the page normally. After the page is loaded, view it through sp_who. There are four connections in the sleeping and wait statuses.
Command, greater than min pool
The size is invalid. If I click it again, what do you guess will happen? 8 ?? More than 4 ?? After a normal refresh, there are still four results ???? The connection pool is automatically returned after the connection is completed,
Requests that are not closed survive, but they can still be activated to execute operations when they are executed. Wow, isn't that great? No explicit close of the connection will not cause any adverse impact?
Ah. Haha, but if you do not close it explicitly, it does not mean you do not close it. the working principle of net garbage collection. We know that any object becomes garbage after it leaves its work scope, waiting for it to be recycled and recycled.
When the recycle manager finds that the object has implemented idipose (), it will activate the object again, call its dispose (), and then become garbage again, and eventually be recycled.
When dispose () is called, it is uncertain. Therefore, redundant connections in the connection pool will be closed at a certain time in the future, which may cause instability of the program, because this connection may already exist
After being activated and processing other requests, the subsequent requests may have been explicitly closed, and so on. Maybe Microsoft has used some mechanism to make the above worries redundant. But the abnormal code has far more impact than this .....

Please note that the previous refresh is normal again and again, that is, after I wait for the page to come out, refresh the page again. Below I force press F5 to refresh the page continuously, and then use sp_who again
Looking at the results, I was surprised. At this time, the number of sleeping connections reached dozens. if many people access the connection pool at the same time, the connection pool will soon be filled up and unstable, it will soon be magnified.
Times.For how to determine whether your application has a connection that is not closed, during peak hours or by simulating a large number of concurrent requests, check if the connection status of sleeping is greater than the min pool size you set, it must exist.

 

Next let's talk about connection lifetime.

Official explanation: After a connection is used, it is released back to the connection pool. If the value of the current time minus the time when the connection is established is greater than the value set by this parameter (in seconds), the connection is destroyed. 0 indicates that there is no upper limit on lifetime.

According to the surface, connections that are not explicitly closed will also be temporarily closed at the time specified by this value. That's great. It's hard to set a smaller lifttime. But confusing
Yes, I set this value to 1. The redundant connections generated by abnormal Code are not destroyed in 1 second, but wait for an uncertain time, this may be the time when garbage collection is coming, and the same normal code
Set this value to 60. connections that are explicitly called beyond the min pool size will be closed immediately.
Connections within the size range remain alive. In this case, this value does not work.

I searched for the search and found that an article mentioned the role:

Function of connection lifetime (Database Cluster)

The experiment is over. Once again, it turns out that the connection must be closed explicitly. It also provides a simple way to check whether there is a closed connection in our program.

Http://www.cnblogs.com/forcertain/archive/2011/01/20/1939808.html