Connection pooling and unstable networks

Connection pooling technology, greatly improve the performance of the application, but if you do not understand the use of connection pooling scenarios and principles, the use of connection pooling,. NET defaults to use connection pooling, most people will choose to use the connection pool default settings, the consequences are often painful, Perhaps you will spend more to find out the unknown problem caused by improper use of the connection pool.

In one project, the improper use of connection pooling has brought a bitter lesson. Generally our application if deployed in a more stable network environment, the default use of connection pooling may not be a problem, if once deployed to an unstable network environment, you have to rack your brains to face the application of unstable generation of torture.

First of all, probably say the principle of the connection pool

The database connection pool allows the application to reuse database connections that already exist in the pool to avoid creating new database connections over and over again. This technology can effectively increase the scalability of the application because a limited database connection can serve a large number of customers. This technology also improves system performance and avoids the overhead of creating new connections in large numbers.

Developing a scalable, high-performance application should minimize the time it takes to establish a connection and keep the database connection to the maximum effective to access data. When a database connection is closed, it is only reclaimed by the connection pool for reuse and is not really released. However, if the connection pool is freed, the database connection will be freed.

Developers should be careful not to rely on garbage collection to free up database connections, because database connections do not shut down when the argument is out of scope, and such a database resource leak causes a connection error to be thrown when a new connection is made.

Establishing a database connection pool

When you open a database connection, a database connection pool is created. The creation of a database connection pool is precisely related to the database connection string (including spaces, capitalization). All connection pools are differentiated according to the connection string. When you create a new database connection, different connection pools are created if the connection strings are not exactly the same.

Once the database connection pool is created, it will always exist until the process is finished. Maintaining an inactive connection pool requires little overhead.

database connections in the connection pool
　　
The connection pool is created based on a unique connection string. While the connection pool is created, the connection pool creates the smallest database connection, and when the connection is not sufficient, the connection pool will add the database connection one at a time until the maximum number of connections is reached, and subsequent connection requests will be added to the request queue. When you call the Close method or Dispose method of a database connection object, the database connection is reclaimed by the database connection pool.

When the database connection is used, call the Close method or the Dispose method to return it to the connection pool. A database connection without an explicit release might not return a connection pool.

When the application establishes a connection, if the connection pool is enabled, a logical connection and a physical connection are maintained in the connection pool.

In an unstable network environment, such as firewalls, network gates (internal and external network deployment applications) physical isolation, the cascade switch in the case of network congestion occurs when the probability of the network is greater

internal and external network system interaction, the emergence of network transient, and then establish network connection is very easy to ora-03113 communication channel file end or ora-03114 can not connect to Oracle, these two errors in the online interpretation of most of the network caused by. For both of these errors, set the connection string Pooling=false if it is not necessary to use the connection pool, because when the connection pool is used, the connection physical connection is interrupted in the connection pool, but the logical connection is still present, and the next time a new request is made, The connection pool is likely to assign a dead connection, and there will be an error above. If ora-03114 appears, you can only reconnect.

The following steps to solve this problem before, a summary written before, but the end is still not a cure, eventually do not use the connection pool no longer error,

After the development of the SMS Center service, an oracle error is often found in the test:ORA-03113, and as soon as this error occurs, the SMS service does not provide services properly. This error is still very regular, that is not the case when the business is busy, only occurs when the service is idle for a long time (typically more than 5 minutes without operation). Initially assumed that Oracle would automatically sever inactive connections, but after viewing Oracle's session log, Oracle did not actively sever the database connection, and the database connection was "inexplicably" disconnected, That is, it is not a client disconnect (because the database connection pool is used in the program, the connection pool closes the inactive connection at certain intervals). Then, to see what the problem is, we monitor the connection at both the database end and the client side (monitoring the database connection on the database, the number of TCP connections on the client, the 1521-port communication used by Oracle). An interesting question was found at this time: when the ORA-03113 error occurs, the database-side session displays a good connection, but a large number of zombie TCP connections are found on the client (the connection is not fully closed and the status is closing). At this point we are guessing that the intermediate network environment may be causing the TCP connection to shut down properly. After consulting the "network Management", learned that the application server and the database server between the network gate, but network management said the gateway will not cut off the connection. After querying the data (see this article: http://www.laoxiong.net/oracle_and_firewall.html), found that our situation and the article is very similar, we suspect that the gateway has a TCP connection removal function ( this need in the implementation, to ascertain the function of the net gate and the net gate can have much impact on the network . After a test, the most positioning to the network gate should be a time to remove the function of the TCP connection (because if the business is more frequent, the database connection is unblocked, as long as the business idle situation will appear this problem).

Problem Solving Ideas:

First step: We intend to start with the connection pool configuration, the biggest effect of these zombie connections on the connection pool is to make the connection pool think that these connections are still unblocked, when the program opens a connection, the connection pool will return a dead connection, at this time the operation on the dead connection will occur ORA-03113 error. Our idea is to shorten the connection time by connecting the pool. Try to shut down the city's idle connections before it is dead and then re-establish the connection ( Note: This will degrade the connection pool performance ). But after testing, this method is not very good, because the time of the connection zombie is not fixed, so can not set a suitable time for the connection pool to close the connection.

Part II: Use the Oracle DCD feature, which is to set the sqlnet.expire_time parameters of the Sqlnet.ora, so that Oracle periodically sends a heartbeat packet to keep the connection alive, but there is a problem with the application of the heartbeat packet Timeout ( TCP's KeepAlive attribute ). So in order for Oracle and client to discover a disconnect early, you need to set the TCP keepalive parameters (Windows needs to set up the registry, Linux needs to set up the appropriate configuration file), which can solve the short connection problem (here Short connection refers to the short connection used by the program). That is, when you need to use the database to request a connection, quickly complete the transaction, and then close the connection, because the connection pool is used, so the connection is not closed, but only returned to the connection pool.

Part III: Short connection problem solved, but long connection still will appear ORA-03113 error (using the long connection is the Oracle AQ polling thread, the thread will keep the AQ queue for a team operation, timeout is 5m, in order to get SMS data from the queue). The test was tested because the database connection was disconnected during the waiting period. So we shorten the time of the team operation to 1m, so that the connection has always been active, so that the probability of ora-03113 error has been minimized.

The Final Solution:

1. Set the keepalive parameters of the operating system using Oracle's DCD domestic (set sqlnet.expire_time parameters).

2. For short connection and long connection using different database connection pool strategy, short connection using database connection pool, and set the connection to keep alive time, this can be the connection pool as early as possible to find dead connections. For a regular connection, the database connection pool is not used, and when a ORA-03113 error occurs that indicates that the connection has expired, the connection is closed and a new connection is requested.

3. Shorten the queue time for Oracle AQ, which can be connected to a regular operation. But then there's the second problem. ORA-25228 error (queue out team timeout). The exception handling of the random last queue is: ORA-03113 closes the connection, requests the connection again, ORA-25228 does not carry on any operation, directly carries on the next team operation.