Database development-Database Connection Pool

Use connection pool
Let's take a look at the sqlconnection string parameters of ado.net.
<Add name = "snetconnectionstring" connectionstring = "Data Source = 192.168.1.10; initial catalog = pandadb; persist Security info = true; user id = sa; Password = 1234; multipleactiveresultsets = true; max pool size = 5; min pool size = 3 "providername =" system. data. sqlclient "/>
We have set the maximum and minimum pools. How can we see the differences between Windows app and web appp?
We can create a web application or Windows app. After the app is enabled, you can use sp_who2 in the SQL analyzer to view the connected client, obviously, if a web application or Windows app starts n connections each time a client starts, it is much larger than the Max pool size = 5, that means that each client of the APP has a connection pool.
Let's take a look at the website. When you are developing a website and configuring it on IIS, it is actually the interaction between IIS and database. You can see it using the connection string above, the connection will never exceed 5. That is to say, there is only one connection pool between IIS and database. No matter how many requests are sent from a few users, this connection pool is used by the IIS app to access the database app.

For details, refer to the following documents

Connecting to the database server usually consists of several steps that take a long time. A physical channel (such as a socket or named pipe) must be established, and a handshake must be performed with the server for the first time. The connection string information must be analyzed and the server must authenticate the connection, check must be run to register in the current transaction, and so on.

In fact, most applicationsProgramUse only one or several different connection configurations. This means that many identical connections will be opened and closed repeatedly during application execution. To minimize the cost of opening a connection, ADO. NET is calledConnection Pool.

The connection pool reduces the number of times new connections need to be opened.Pool ProcessWarranty
The ownership of the physical connection. Manage connections by retaining a set of active connections for each given connection configuration. As long as the user calls
Open, the pool process checks whether there are available connections in the pool. If a pool connection is available, the connection is returned to the caller instead of opening a new connection. The application calls close on the connection.
The pool process returns the connection to the active connection pool instead of closing the connection. After the connection is returned to the pool, it can be reused in the next open call.

You can establish a pool connection only when the same connection is configured. Ado. Net retains multiple pools at the same time, and each pool is configured with one. Connections are divided into multiple pools by connection strings and Windows Identifiers (when using integrated security.

Pool
Connection can greatly improve application performance and scalability. By default, ADO. net
Enable the connection pool. Unless explicitly disabled, the pool process will optimize the connection when the connection is opened or closed in the application. You can also provide several connection string modifiers to control the behavior of the connection pool. For more information
For more information, see "using the connection string keyword to control the connection pool" after this topic ".

Pool creation and allocation

In
When the connection is opened for the first timeAlgorithmCreate a connection pool. This algorithm associates the pool with the connection string in the connection. Each connection pool is associated with different connection strings. When a new connection is opened, if the connection string
Instead of completely matching the existing pool, a new pool will be created. Create a pool connection by process, by application domain, by connection string, and by windows when using integrated security.

In the following C # example, three new sqlconnection objects are created, but only two connection pools are required for management. Note:Initial catalogThe assigned value. The first and second connection strings are different.

Copy code

Using (sqlconnection connection = new sqlconnection (
"Integrated Security = sspi; initial catalog = northwind "))
{
Connection. open ();
// Pool a is created.
}

Using (sqlconnection connection = new sqlconnection (
"Integrated Security = sspi; initial catalog = pubs "))
{
Connection. open ();
// Pool B is created because the connection strings differ.
}

Using (sqlconnection connection = new sqlconnection (
"Integrated Security = sspi; initial catalog = northwind "))
{
Connection. open ();
// The connection string matches pool.
}
 

If minpoolsize is not specified in the connection string or is specified as zero, the connection in the pool will be closed after a period of inactivity. However, if the specified minpoolsize is greater than zero, the connection pool will not be damaged until the appdomain is detached and the process ends. Maintenance of inactive or empty pools only requires minimal system overhead.

Note:
If a fatal error occurs (such as failover or alias change in the Registry), the pool is automatically cleared.

Add connection

The connection pool is created for each unique connection string. After a pool is created, multiple connection objects are created and added to the pool to meet the minimum pool size requirements. The connection is added to the pool as needed, but cannot exceed the specified maximum pool size (default value: 100 ). The connection is released back to the pool when it is closed or disconnected.

If an available connection exists when you request a sqlconnection object, the object is obtained from the pool. The connection must be available, not used, have a matched transaction context or not associated with any transaction context, and have a valid link with the server.

The connection pool process re-allocates connections when the connection is released back to the pool to meet these connection requests. If the maximum pool size is reached and no available connections exist, the request will be queued. Then, the pool process tries to re-establish any connection until the timeout time is reached (the default value is 15 seconds ). If the pool process cannot meet the request before the connection times out, an exception is thrown.

Warning

Me We recommend that you close the connection when using the connection so that the connection can return to the pool. To close the connection, you can use close or dispose of the connection object. In the C # using statement or in the Visual Basic using Open all connections in the statement. Connections that are not explicitly closed may not be added or returned to the pool. For example, if the connection has exceeded the range but is not explicitly closed, the connection is valid only when the maximum pool size is reached, The connection is returned to the connection pool. For more information, see the Visual Basic using Statement (C # reference) or how to: release system resources.

Note:
No To call close or Dispose. In the Terminator, only the unmanaged resources directly owned by the class are released. If the class does not have any unmanaged resources, do not include finalize in the class definition. Method. For more information, see garbage collection.

Remove Connection

The connection pool process periodically scans the connection pool to find unused connections that are not closed through close or dispose, and reestablishes the established connection. If the application does not explicitly close or disconnect it, it may take a long time for the connection pool process to re-establish the connection. Therefore, it is best to ensure that close and dispose are explicitly called in the connection.

If the connection is idle for a long time or the pool process detects that the connection to the server is disconnected, the connection pool process removes the connection from the pool. Note that the disconnected connection can be detected only after you try to communicate with the server. If a connection is no longer connected to the server, it is marked as invalid. Invalid connections are removed from the connection pool only after they are closed or re-established.

For example
If a connection exists with the server that has been removed, the connection pool manager may still remove the connection from the pool even if it does not detect a disconnected connection and marks it as invalid. This is because the connection is checked
Still effective system overhead will cause another round-trip with the server, thus offsetting the advantages of the pool process. In this case, the first attempt to use the connection will detect whether the connection has been disconnected and cause an exception.

Clear pool

Ado. NET 2.0 introduces two new methods to clear the pool: clearallpools and clearpool. Clearallpools clears the connection pool of a given provider and clearpool clears the connection pool associated with a specific connection. If the connection is in use during the call, it will be marked accordingly. When the connection is closed, it will be discarded rather than returned to the pool.

Transaction support

The connection is retrieved from the pool and allocated based on the transaction context. Unless specified in the connection stringEnlist = falseOtherwise, the connection pool ensures that the connection is in the current
Register in the context. If the connection is closed using the registered system. Transactions transaction and returns to the pool, the connection is retained in the pool to use the same
The system. Transactions transaction will return the same connection for the next request of the connection pool. If no connection is available for the transaction, the connection is automatically registered when the connection is opened.

When the connection is closed, it will be released back to the pool and placed into the corresponding sub-parts according to the transaction context. Therefore, even if the distributed transaction is still suspended, you can close the connection without generating an error. In this way, you can submit or stop distributed transactions later.

Use the connection string keyword to control the connection pool

The connectionstring attribute of the sqlconnection object supports the connection string key/value pair and can be used to adjust the behavior of the Connection Pool logic. For more information, see connectionstring.

Pool fragments

Pool fragmentation is a common problem in many web applications. Applications may create a large number of pools that will be released after the process exits. In this way, a large number of connections will be opened, occupying a lot of memory, thus affecting performance.

Pool fragmentation caused by integration Security

Connection
Establish a pool connection based on the connection string and user ID. Therefore, if you use basic authentication on the website or Windows
Each user receives a pool for authentication and integrated secure login. Although this can improve the performance of subsequent database requests of a single user, the user cannot use connections established by other users. In this way
Each user generates at least one connection to the database server. This has side effects on the structure of a specific web application because developers need to measure security and audit requirements.

Because many databases generate pool fragments

Xu
Multiple Internet
Service providers host multiple websites on one server. They may use a single database to confirm form authentication login, and then open a connection with a specific database for the user or user group. The
The connection establishes a pool connection for each user. However, each database connection has an independent pool, which increases the number of connections to the server.

This will also generate a secondary feature for application design.
Function. However, this side effect can be avoided in a relatively simple way without affecting the connection to SQL Server.
Security. Instead of connecting to an independent database for each user or group, connect to the same database on the server and then execute the transact-SQL use
To switch to the required database. BelowCodeSection shows how to create an initial connection to the master database, and then switchDatabasenameThe required database specified in the string variable.

Copy code in Visual Basic

'Assumes that command is a valid sqlcommand object.
Using connectionAs NewSqlconnection (_
"Server = mssql1; uid = xxx; Pwd = xxx; database = Master")
Connection. open ()
Command. executenonquery ("Use"& Databasename)
EndUsing
 

C # copy code

// Assumes that command is a sqlcommand object.
Using(Sqlconnection connection =NewSqlconnection (
"Server = mssql1; uid = xxx; Pwd = xxx; database = Master"))
{
Connection. open ();
Command. executenonquery ("Use"+ Databasename );
}
 

Application role and Connection Pool

After the SQL Server application role is activated by calling the sp_setapprole system stored procedure, the security context of the connection cannot be reset. However, if the pool is enabled, the connection will return to the pool, and an error will occur when the pool connection is reused.

If you are using the SQL Server application role, we recommend that you disable the connection pool for the application in the connection string. For more information, see the Knowledge BaseArticle