Ado. NET Getting Started Tutorial (v) The database connection pool

Off Topic

Through the previous chapters of the study, I do not know that we have a certain understanding of ADO has not. Aside from the quality of the article, it must be sure that I write every article with my heart. I spent a lot of time on typography and content selection. I hope that through this series of articles, whether novice or veteran, on the ADO can be harvested. If you feel that it is helpful, I hope to get your recommendation and attention, let me know your affirmation to me. If people think I am not good at writing, I am also willing to listen to criticism, let us progress together.

Summary

What I'm going to talk about today is the database connection pool. To tell you the truth, I mean a great pear. Because the connection pool is relatively difficult to understand compared to other chapters. I would like to use the most popular words to explain to you, speak thoroughly but is not a very easy thing. However, connection pooling is a very important point of knowledge, especially when deploying multi-user programs. Therefore, not only do I have to speak, but also to speak thoroughly. With this article, you will understand how connection pooling is using the connection pooling to improve the performance of your application.

Directory

• What is a connection pool?
• How connection pooling Works
• Talk about several very important properties of the connection pool
• Connection pool exceptions and handling methods
• Ways to monitor connection status
• Basic principles for efficient use of connection pooling

1. What is a connection pool?

In the previous article, "What you must know about the connection object of ADO," I have already emphasized that establishing a database connection is a very time consuming (consuming resource) thing. this is because connecting to the database server involves a few lengthy processes: establishing physical channels (such as sockets or named pipes), initial handshake with the server, parsing connection string information, authenticating the connection by the server, running checks to register in the current transaction, and so on. We do not care why there is such a mechanism, there is always the truth of it. Since a new connection is so painful, why not reuse existing connections?

In fact, ADO. NET has provided us with an optimization method called connection pooling. A connection pool is one such container: it holds a certain number of physical connections to the database server. Therefore, when we need to connect to the database server, we simply go to the pool (container) to take out an idle connection, instead of creating a new connection. In this way, we can significantly reduce the overhead of connecting to the database, thus improving the performance of the application.

PS: originally made 2 pictures to describe the connection pool, helpless company equipped with monitoring software, can not upload, so only wait for the next time to upload.

2. How connection pooling works 2.1 Create a connection pool

It is necessary to note that connection pooling is distinguished by category. that is, the same time the same application domain can have multiple different types of connection pools. So, how does the connection pool identify the distinction? In detail, it is identified by the process, application domain, connection string, and the Windows identity (when using integrated security) to form a signature to identify the distinction. For the same application domain, however, the distinction is typically identified only by the connection string. When a connection is opened, a new connection pool is created if the type signature of the connection does not match the existing connection pool type. Instead, a new connection pool is not created.

A typical instance of creating a connection:

Creating a Connection Object 1
using (SqlConnection conn1 =

             New SqlConnection (  "datasource= (local); Integrated security=sspi;initial catalog=northwind"))    
{        
    Conn1. Open ();       
}


Creating a Connection Object 2
using (SqlConnection conn2 =

           New SqlConnection (  "datasource= (local); Integrated security=sspi;initial catalog=pubs"))   
{       
    Conn2. Open ();      
}


Creating a Connection Object 3
using (SqlConnection CONN3 =

         New SqlConnection (  "datasource= (local); Integrated security=sspi;initial catalog=northwind"))  
{        
    Conn3. Open ();      

In the above example, I have created three SqlConnection objects, but only two connection pools are required for management. Careful friend, may have already found conn1 and conn3 the same connection string, so you can share a connection pool, and conn2 and Conn1 and conn3, so you need to create a new connection pool.

2.2 Allocating idle connections

When a user creates a connection request or calls open for a connection object, the connection pool manager first needs to find a connection pool of the matching type based on the type signature of the connection request, and then tries to allocate an idle connection. the details are as follows:

• If an idle connection is available in the pool, the connection is returned.
• If the connections in the pool are exhausted, create a new connection to add to the pool.
• If the connection in the pool has reached the maximum number of connections, the request enters the wait queue until an idle connection is available.

2.3 Removing invalid connections

Invalid connection, that is, connection to the database server is not properly connected. The number of connections that are stored to the database server is limited for connection pooling. Therefore, for invalid connections, if not removed in time, the pool space will be wasted. In fact, you don't have to worry, the connection pool manager has handled these issues well for us. If the connection is idle for a long time, or if the connection to the server is detected to be disconnected, the connection pool manager removes the connection from the pool.

2.4 Recycling of used connections

when we finish using a connection, we should close or release the connection in a timely manner so that the connection can be reused in the pool. we can close the connection by connection the close or Dispose method of the object, or through the using statement of C #.

3. Talk about a few very important attributes

the behavior of the connection pool can be controlled by a connection string , consisting mainly of four important attributes:

• Connection Timeout: connection request Wait time-out. The default is 15 seconds, in seconds.
• Max Pool Size: The maximum number of connections in the connection pool. The default is 100.
• min Pool Size: The minimum number of connections in the connection pool. The default is 0.
• Pooling: Whether connection pooling is enabled. ADO. NET is enabled for connection pooling by default, so you need to set Pooling=false manually to disable connection pooling.

Let's look at an example to understand the properties of a connection pool. The code is as follows:

SqlConnectionStringBuilder connstr = new SqlConnectionStringBuilder ();
            Connstr.datasource = @ ". \SQLExpress";
            Connstr.initialcatalog = "Master";
            Connstr.integratedsecurity = true;

            Connstr.pooling = true; Open Connection Pool
         connstr.minpoolsize = 0; Set the minimum number of connections to 0
            Connstr.maxpoolsize = 50; Set the maximum number of connections to 50             
            Connstr.connecttimeout = 10; Set the time-out to 10 seconds

            using (SqlConnection conn = new SqlConnection (connstr.connectionstring))
            {
                ;//todo
            }

4. Connection pool exception and processing method

A "connection leak" issue is often raised when a user opens a connection without proper or timely shutdown. The leaked connection remains open until the Dispose method is called, and the connection is closed and released by the garbage collector (GC). Unlike ADO, ADO. NET requires a manual shutdown of the used connections. an important misconception is that connections are closed when the connection object is outside the local scope. in fact, when you go out of scope, you are releasing only the connection object and not the connection resource. Well, let's take a look at an example.

Using System;
Using System.Collections.Generic;
Using System.Linq;
Using System.Text;
Using System.Data;
Using System.Data.SqlClient;

Namespace ConnectionPool
{
Class Program
{
static void Main (string[] args)
{
SqlConnectionStringBuilder connstr = new SqlConnectionStringBuilder ();
Connstr.datasource = @ ". \SQLExpress";
Connstr.initialcatalog = "Master";
Connstr.integratedsecurity = true;

Connstr.maxpoolsize = 5;//Set maximum connection pool to 5
Connstr.connecttimeout = 1;//Set timeout time is 1 seconds

SqlConnection conn = null;
for (int i = 1; i <=; ++i)
{
conn = new SqlConnection (connstr.connectionstring);
Try
{
Conn. Open ();
Console.WriteLine ("Connection{0} is linked", i);
}
catch (Exception ex)
{
Console.WriteLine ("\ n exception information: \n{0}", ex. Message);
Break
}
}

Console.read ();

}
}
}

To make the results more obvious, I specifically set the maximum number of connections to 5 and the timeout time to 1 seconds. After running, the following results are quickly obtained.

From the above results we are clearly aware that there is an exception to the connection. We already know that the maximum number of connections for a connection pool is 5, and when you create a 6th connection, you wait for the connection to expire because the number of connections in the connection pool has reached the maximum number and there are no idle connections. The above connection exception occurs when the timeout period is exceeded. Therefore, I must emphasize again that the used connections should be properly closed and released as soon as possible.

5. Methods for monitoring the connection state of SQL Server (1) through the Activity Monitor

First step: Open the Mssms Manager and click the Activity Monitor icon.

Step Two: in the Open Activity Monitor view, click the Processes tab.

Step three: run #4 Connection pool exception and The example in the processing method, you can see the open 5 connections, as shown in.

(2) using T-SQL statements

Similarly, we can monitor the connection status by executing the system stored procedure sp_who.

EXEC sp_who

The following results can be obtained:

6. Basic Principles for efficient use of connection pooling

Using a good connection pool will greatly improve the performance of your application. On the contrary, if it is used improperly, then the harm is not a benefit. In general, the following principles should be followed:

• Request a connection at the latest time, and release the connection at the earliest.
• Closes the related user-defined transaction first when the connection is closed.
• Ensure and maintain at least one open connection in the connection pool.
• Try to avoid the creation of pool fragments. This includes pool fragmentation resulting from integrated security, and pool fragmentation that results from using many databases.

tip: pool fragmentation is a common problem in many WEB applications, and applications may create a number of pools that will not be released until the process exits. In this way, a large number of connections are opened, consuming a lot of memory, resulting in degraded performance.

Ado. NET Getting Started Tutorial (v) The database connection pool