Basic working principle of connection pool

Basic working principle of connection pool

1 , basic concepts and principles

As can be seen from the above analysis, the root of the problem lies in the inefficient management of the database connection resources. We know that for shared resources, there is a well-known design pattern: resource pools (Resource pool). The pattern is to solve the problem of the frequent distribution of resources and the release of the resource. In order to solve the above problems, we can use database connection pool technology. The basic idea of a database connection pool is to create a "buffer pool" for database connections. Put a certain number of connections in the buffer pool beforehand, and when you need to establish a database connection, simply remove one from the buffer pool, and then put it back when you have finished using it. We can prevent the system from endlessly connecting to the database by setting the maximum number of connections to the connection pool. More importantly, we can monitor the number of connections to the database through the connection pooling management mechanism, and provide the basis for system development test and performance adjustment.

2, Server The connection pool that is brought from

There is no method in the JDBC API to provide connection pooling. Some large Web application servers, such as Bea's WebLogic and IBM's WebSphere, provide a mechanism for connection pooling, but there must be a Third-party dedicated class method that supports the use of connection pooling.

Analysis of key problems of connection pool

1 , concurrency Problems

In order to make the connection Management Service have the most universality, it is necessary to consider the multithread environment, that is concurrency problem. This is a relatively good solution because the Java language itself provides support for concurrency management, and using the Synchronized keyword ensures that threads are synchronized. Use the method to directly precede the class method with the Synchronized keyword, such as:

Public synchronized Connection getconnection ()

2, multiple database servers and multiple users

For large enterprise applications, it is often necessary to simultaneously connect different databases, such as Oracle and Sybase. How to connect a different database. The strategy we adopt is to design a connection pool management class that conforms to the singleton pattern, reading a resource file when a unique instance of the connection pool management class is created, where the URL address () for multiple databases is stored in the resource file (). User name (), password (), and other information. such as tx.url=172.21.15.123:5000/tx_it,tx.user=yang,tx.password=yang321. Create multiple instances of the connection pool class based on the information provided by the resource file, each of which is a connection pool for a particular database. The connection pool management class instance takes a name for each connection pool instance and manages the different connection pools by different names.

With multiple users accessing different names and passwords for the same database, you can also use resource files to set up multiple database connection information with the same URL address, but with different user names and passwords, in the resource file.

3, transaction processing

We know that a transaction is atomic, requiring that the operation of the database conform to the "all-all-nothing" principle, that is, to a set of SQL statements, either full or none at all.

In the Java language, the connection class itself provides support for transactions by setting the connection Autocommit property to False and then explicitly invoking the commit or Rollback method. However, in order to efficiently perform connection reuse, the corresponding transaction support mechanism must be provided. Each transaction can be implemented exclusively with one connection, which can greatly reduce the complexity of transaction management.

4, allocation and release of connection pool

The allocation and release of the connection pool have a great effect on the performance of the system. Reasonable allocation and release can increase the reuse of the connection, reduce the cost of establishing new connection, and speed up the user's access.

You can use a free pool for connection management. A connection that has been created but not yet allocated is stored in a free pool at the time of creation. Each time a user requests a connection, the system first checks to see if there is an idle connection in the free pool. If there is a connection to the longest established (by the order of the container to be implemented) assigned to him (actually the first to do the connection is valid judgment, if available, assign to the user, if not available, delete the connection from the free pool, and re-detect if there is a connection to the free pool. If not, check whether the current open connection pool reaches the maximum number of connections allowed by the connection pool (maxconn), and if not, create a new connection and wait a certain amount of time (timeout) if it has been reached. If a connection is released within the waiting time, the connection can be assigned to the waiting user, or null if the wait time exceeds the scheduled time timeout. The system counts only the connections that are already being used, and returns them to the free pool when it is used. For the state of the idle connection, a dedicated thread timing detection can be developed, which will cost a certain amount of overhead, but can ensure a faster response speed. It can also take a method that does not open up specialized threads, just before the distribution is detected.

5, Connection pool configuration and maintenance

How many connections should be placed in the connection pool to make the system perform best. The system can take the setting of the minimum number of connections (minconn) and the maximum number of connections (maxconn) to control connections in the connection pool. The minimum number of connections is the number of connections created by the connection pool at system startup. If you create too many, the system starts slowly, but the system responds quickly when it is created, and if you create too little, the system starts quickly and responds slowly. In this way, you can set a smaller minimum number of connections at development time, and it will be faster to develop, and larger when the system is actually in use, because it will be faster for the client to access. Maximum connection number is the maximum number of connections allowed in the connection pool, the specific settings, depending on the amount of access to the system, through repeated testing, to find the best point.

How to ensure the minimum number of connections in the connection pool. There are both dynamic and static strategies. Dynamic that is, every time the connection pool is detected, if the number of connections is found to be less than the minimum number of connections, then add a corresponding number of new connections to ensure the normal operation of the connection pool. Static is found when the idle connection is not enough to check again.

implementation of connection pooling

1 , Connection pool model

The connection pool discussed in this article includes a connection pool class (Dbconnectionpool) and a connection pooling management class (Dbconnetionpoolmanager). A connection pool class is a "buffer pool" for all connections to a database, which mainly implements the following functions: ① Gets or creates an available connection from the connection pool; ② the connection to the connection pool after use; ③ disconnect all connections and release system resources for connection before system shutdown ④ can also handle invalid connections (connections that were originally registered as available, are no longer available for some reason, such as timeouts, communication problems), and can limit the total number of connections in the connection pool to no less than a predetermined value and no more than a predetermined value.

The connection pool management class is the outer covering class (wrapper) of the connection pool class, which conforms to the singleton pattern, where only one instance of the connection pool management class can be in the system. It is mainly used for the management of multiple connection pool objects and has the following functions: ① load and register the JDBC driver for a particular database; ② create a connection pool object based on the information given by the property file; ③ for the convenience of managing multiple connection pool objects, take a name for each connection pool object, The mapping between the connection pool name and its instance is implemented, and ④ tracks the customer's use of the connection so that the connection is closed to release resources. The introduction of connection pool management classes is designed to facilitate the use and management of multiple connection pools, such as the need for a system to connect to different databases, or to connect to the same database, but because of security issues, different users are required to use different names and passwords.

2, Connection Pool implementation

The main attributes of the connection pool class and connection pool management class and the basic interfaces to implement are given below:

Copy Content to Clipboard

Code:

public class Dbconnectionpool implements timerlistener{
private int checkedout;//Number of connections that have been allocated
Private ArrayList freeconnections=new ArrayList ();
containers, free pools, to store connections that have been created but not yet allocated according to//creation time order
Minimum number of connections in private int minconn;//connection pool
Maximum number of connections allowed in private int maxconn;//connection pool
Private String name;//A name for this connection pool for easy administration
Private String password;//The password required to connect to the database
Private String url;//The address of the database to which you want to create a connection
Private String user;//The user name required to connect to the database
Public Timer timer;//Timer
Public Dbconnectionpool (String name,string url,string user,
String password,int maxconn)//exposed constructors
Public synchronized void freeconnection (Connection con)
After use, return the connection to the free pool
Public synchronized Connection getconnection (long timeout)
Get a connection, timeout is waiting time
Public synchronized void release ()
Disconnect all connections and release occupied system resources
Private Connection newconnection ()
Create a new database connection
Public synchronized void TimerEvent ()
Timer event handler function
}
public class Dbconnectionmanager {
static private Dbconnectionmanager instance;
Unique instance of the connection pool management class
static private int clients;//number of customers
Private ArrayList drivers=new ArrayList ();
containers, storing database drivers
Private HASHMAP pools = new HashMap ();
Access the name of the connection pool object and the connection pool object in name/value form
Static synchronized public Dbconnectionmanager getinstance ()
/** If the unique instance instance has already been created, return the instance directly, otherwise, call the private constructor,
Create a unique instance of the connection pool management class * *
Private Dbconnectionmanager ()
Private constructor, in which initialization function init () is called
public void Freeconnection (String name,connection con)
Frees a connection, name is a connection pool object names
Public Connection getconnection (String name)
A connection is obtained from the connection pool object named name
Public Connection getconnection (String name,long time)
Gets a connection from the connection pool object named name, time is wait
Public synchronized void release ()/Free all resources
private void Createpools (Properties props)
Create one or more connection pools based on the information provided by the property file
private void init ()//Initializes a unique instance of the connection pool management class, which is called by the private constructor
private void Loaddrivers (Properties props)/Load Database driver
}

3, Connection pool use

The connection pool implemented above is applied to the system in the development of the program. The following is an example of a servlet to illustrate the use of connection pooling.

The lifecycle of the servlet is to invoke its initialization (init) method when the servlet is started. Each user request then causes a thread to invoke the service method of the previously established instance. Finally, when the server decides to uninstall a servlet, it first invokes the Destroy method of the servlet.

Depending on the nature of the servlet, we can generate a unique instance of the connection pool management class in the initialization function (which includes creating one or more connection pools). Such as:

Copy Content to Clipboard

Code:

public void Init () throws Servletexception
{
Connmgr=dbconnectionmanager.getinstance ();
}

You can then use connection pooling in the service method through the connection pool name to perform database operations. Finally, release the occupied system resources in the Destroy method, such as:

Copy Content to Clipboard

Code:

public void Destroy () {
Connmgr.release ();
Super.destroy ();
}

Concluding remarks

Database connection management is a difficult problem in the development of database-related applications using JDBC. In many cases, the system resource overhead caused by the chaotic management of the connection becomes the bottleneck that restricts the application efficiency of the large enterprise level. For a wide range of user-accessible Web applications, systems with database connectivity technologies are much better at efficiency and stability than in traditional systems. This article describes the technique of accessing a database using JDBC. The key problem of database connection management based on connection pool technology is discussed and an implementation model is given. This paper presents a basic mode of connection pool management program, which can be used to improve the overall performance of the system, and also make a lot of meaningful extensions.