Understanding Database Connection

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

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

The database 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 pool, activate a connection to process your request. 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 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

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 connection with the min pool size in a short period of time, 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 of concurrent requests is greater than this minimum value, a connection will be created, when the number of concurrent requests exceeds the Max pool size, the request enters the waiting queue and waits for idle connections. If the request reaches the application or database limit, it is not processed yet, 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: After database operations are completed, close () or dispose () is called explicitly to close the connection. The following example shows how to verify whether your application has a connection that is not closed. I did these tests based on this requirement.

 

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. Well, that article has nothing to do with you.

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

The first is the normal program:

 using (SqlDataReader reader = Maticsoft.DBUtility.DbHelperSQL.ExecuteReader("select top 1 * from protuct order by id desc"))            {                while (reader.Read())                {                    Response.Write(reader["Name"].ToString());                }            } using (SqlDataReader reader2 = Maticsoft.DBUtility.DbHelperSQL.ExecuteReader("select top 1 * from users order by userid desc"))            {                while (reader2.Read())                {                    Response.Write(reader2["userName"].ToString());                }            }

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:

 SqlDataReader reader3 = Maticsoft.DBUtility.DbHelperSQL.ExecuteReader("select top 1 * from users order by userid asc");            while (reader3.Read())            {                Response.Write(reader3["userName"].ToString());            } SqlDataReader reader4 = Maticsoft.DBUtility.DbHelperSQL.ExecuteReader("select top 1 * from protuct2 order by id asc");            while (reader4.Read())            {                Response.Write(reader4["Name"].ToString());            } SqlDataReader reader5 = Maticsoft.DBUtility.DbHelperSQL.ExecuteReader("select top 1 * from protuct order by id desc");            while (reader5.Read())            {                Response.Write(reader5["Name"].ToString());            } SqlDataReader reader6 = Maticsoft.DBUtility.DbHelperSQL.ExecuteReader("select top 1 * from users order by userid desc");            while (reader6.Read())            {                Response.Write(reader6["userName"].ToString());            }

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, I can use sp_who to check that there are four connections. The connection status is sleeping and wait command, which is greater than the min pool size. This is abnormal. If I click it again, what do you guess will happen? 8 ?? More than 4 ?? After a normal refresh, there are still four results ???? After the connection is completed, the connection pool will be automatically returned. If no close request is returned, the connection will survive. However, when there are operation requests, they can still be activated to perform the operation. Wow, isn't it great that you close the connection explicitly. 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 scope of work, waiting for it to be recycled and recycled, when the recycle manager finds that the object implements idipose (), it will activate the object again, call its dispose (), and then become junk 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 have been activated to process other requests at this time, and may have been explicitly closed by subsequent requests, or Microsoft may have used some mechanism to make the above concerns 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 view it again through sp_who, the result was shocked. At this time, the sleeping connection reached dozens. if many people access the connection pool at the same time, the connection pool will soon be full and unstable, it will soon be magnified many 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 it is confusing that I set this value to 1. The redundant connections generated by abnormal Code are not destroyed in 1 second, but are waiting for an uncertain time, this may be the time when garbage collection is approaching. In normal code cases, set this value to 60. explicitly called connections that exceed the min pool size will be closed immediately, connections within the min pool 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.