Database Connection Pool [translation 〕

Original article: http://www.codeproject.com/KB/dotnet/ADONET_ConnectionPooling.aspx

1: Merge database connections

Establishing a connection to the database server consumes a lot of system resources. If a system needs to query a database service, you must first establish a connection to the database and then perform the query.
Can you feel that when you use the same query on the client to submit the query information to the server, the returned results will get faster and faster. There is a mechanism from the server to return data to the client to produce the above results, and this mechanism is to establish a connection between ado
We usually store the connection information to the database in the configuration file, and use it to frequently open and close the connection to the database in the system. In this way, no matter how big or how small data is accessed, we use the same connection.
Ado. Net in IIS has a technology called connection pool, which is a good solution to the above problems. When the applicationProgramWhen a request is made for the first time and the system closes the database connection. Ado. Net does not completely destroy the connection, but puts the connection object in the connection pool and retains its reference. When a request occurs, ADO. Net calls out the connection from the connection pool and applies it to this query. This can improve the efficiency.

2: create a connection pool

The connection pool is closely related to the connection string. A connection pool is determined by processes, application domains, and connection strings. (Process,Domain,Connection string)
When we use ADO. Net to send a query to the database, ADO. Net matches the connection object in the connection pool with the specified connection string in the same process and application domain. If the connection is found and available, the connection is used for this request. Otherwise, a new connection is established and the connection is added to the connection pool. When you add a connection to a connection pool, the maximum number of connections in the connection pool cannot be exceeded.
We can useThe close () or dispose () method destroys the connection immediately after exploitation, instead of waitingGC to release the resources consumed by the connection.

3: delete and clear the connection pool

When the application is uninstalled, the connection pool is also destroyed. In ourIn the ASP. NET System, the connection pool is created during the first request and destroyed When IIS is restarted. The connection pool depends on our IIS rather than our development environment. Therefore, you cannot disable Dev to destroy the connection pool.
Two Methods for clearing the connection pool are added in ado2.0.Clearallpools,Clearpool

4: control the connection pool through the connection string

The following table lists the connection pool settings in the connection string.

Name	Default	Description
Connection lifetime	0	When a connection is returned to the pool, its creation time is compared with the current time, and the connection is destroyed if that time span (in seconds) exceeds the value specifiedConnection lifetime. A value of zero (0) causes pooled connections to have the maximum connection timeout.
Connection timeout	15	Maximum time (in SECs) to wait for a free connection from the tool
Enlist	'True'	WhenTrue, The pooler automatically enlists the connection in the creation thread's current transaction context. recognized values areTrue,False,Yes, AndNo. Set enlist = "false" to ensure that connection is not context specific.
Max pool size	100	The maximum number of connections allowed in the pool.
Min pool size	0	The minimum number of connections allowed in the pool.
Pooling	'True'	WhenTrue,SqlconnectionObject is drawn from the appropriate pool, or if it is required, is created and added to the appropriate pool. recognized values areTrue,False,Yes, AndNo.
Incr pool size	5	Controls the number of connections that are established when all the connections are used.
Decr pool size	1	Controls the number of connections that are closed when an excessive amount of established connections are unused.

In addition to the above list, there is also a need to note. We try to use userid and pqssword in the connection string to establish a connection, so that a customer can use the connection established by other customers. Otherwise, a connection is established for each customer.

5. Example of a connection string

Initial catalog = northwind; Data Source = localhost; connection timeout = 30; user id = myuser; Password = password; min pool size = 20; Max pool size = 200; incr pool size = 10; decr pool size = 5;

6: view the connection pool created with ADO. net

In Ms sqlserver, open the query analyzer to executeExec sp_who
In Oracle, open a client tool similar to PL/SQL to executeSelect * from V $ session where program is not null

7: Common exceptions in the connection pool

Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and Max pool size was reached
Problem description: The above exception occurs when the connection pool exceeds the maximum value. Generally, the maximum connection pool value is 100. When we get a connection that exceeds the maximum value, ADO. net waits for the connection pool to return a connection and times out. This will throw the above exception.
Solution: The first thing we need to do is to close the connection immediately after we use the connection. If the connection is not closed, the connection will be saved to the connection pool and GC will be known for destruction. In this case, you think the connection pool has not reached the maximum value, but the connection pool has actually reached the maximum value.
Next, we can connect to Max pool size = N; to dynamically expand connections in the connection pool Max Quantity.
Closing the connection pool
A transport-level error has occurred when sending the request to the server. (provider: Shared Memory provider, error: 0-shared memory provider :)
Problem description: Ado. Net assumes that a certain connection exists in the database, but all connections are lost due to database restart.
Solution: for Oracle, If you access the database using ODP or net, you should add Validate connection = true Or Validcon = true
For Ms sqlserver, you can use Sqlconnection. clearpool static method to clear the connection pool. This method is available in Sqlclient, both exist in oracleclient.
for Ms sqlserver in. net1.1
A: recreate the connection pool and destroy useless connection pools
B: catch exceptions and refresh the connection loop until ADO.