Database basics-database connection pool problems

In JDBC-based database application development, database connection management is a difficult issue, because it is an important factor that determines the performance of the application.
Based on a thorough analysis of database connections, this paper proposes and implements an efficient connection management policy to achieve high-performance data development.
Database applications become relatively easy. In particular, the two difficulties in connection management are deeply analyzed: transactions and multithreading.
A design-based solution is provided. This section describes how to use Java for database-related application development.
JDBC to interact with the database. One of the key concepts is Connection, which is a class in Java,
Represents a channel. The data application can access the data from the database. For a simple database application,
Because the database access is not frequent. In this case, you can create a new connection when you need to access the database, and close it after you use it,
This will not bring significant performance overhead. However, for a complex database application, the situation is completely different. Frequent establishment,
Closing the connection will greatly reduce the system performance, because the use of the connection has become a bottleneck of the system performance. The methods provided in this article can be effectively implemented.
Solve this problem. In this method, a reasonable and effective connection management policy is proposed to avoid random and irregular connection usage.
The core idea of this strategy is connection reuse. By establishing a database connection pool and a set of connection usage management policies
The connection can be efficiently and securely reused, avoiding the overhead of frequent establishment and shutdown of database connections. In addition
The connection is encapsulated to facilitate the use of database applications for connections (especially for transaction processing) and improve the development efficiency.
The existence of this encapsulation layer isolates the processing logic of the application and the access logic of the specific database, making reuse of the application possible.
The problem arises when I participate in the development of a network management system, which inevitably involves dealing with databases. At the beginning
The usage of database connections is a simple policy that is established when necessary and closed when used up,
This is in line with the XP (eXtreme Programming) slogan: "Do the Simplest Thing that cocould Possibly Work ".
Indeed, it worked very well at the beginning. As the project progresses and the database access frequency changes, the problem is exposed.
Simply obtaining and disabling database connections will greatly affect the system performance. This impact is caused by frequent creation of Database Resource Manager processes.
Create and destroy those connection objects. At this point, it is necessary to refactoring the database access method, because we do need
To improve the system performance. The solution shows that the root cause of the problem is the inefficient management of connected resources.
There is a well-known design pattern for shared resources: resource pools. This mode is designed to solve the problems caused by frequent resource allocation and release.
Applying this mode to the database connection management field is to establish a database connection pool and provide a set of efficient connection allocation and usage policies,
The ultimate goal is to achieve efficient and secure reuse of connections.

3.1. The first step to establish a connection pool is to establish a static connection pool. Static means that connections in the pool are allocated during system initialization.
And cannot be disabled at will. Java provides many container classes for us to easily build connection pools, such as Vector and Stack.
During system initialization, create a connection according to the configuration and place it in the connection pool. All the connections used later are obtained from the connection pool.
To avoid the overhead caused by arbitrary connection establishment and closure (of course, we cannot avoid the overhead caused by Java's Garbage Collection ).

3.2. With this connection pool, we can provide a set of custom allocation and release policies. When a customer requests a database connection
First, check whether there is any idle connection in the connection pool. If there is an idle connection, connect
The connection is allocated to the customer and processed accordingly. The specific processing policy will be detailed in key topics. The main processing policy is to mark the connection as allocated.
If there is no idle connection in the connection pool, find a suitable connection to the customer in the allocated connection pool (the selection policy will be in the key topics ).
In this case, the connection is reused among multiple customers.
When you release a database connection, you can perform different processing based on whether the connection is reused. If the connection has no user
Connection Pool, rather than being closed. It can be seen that this policy ensures the effective reuse of database connections. 3.3 configuring the Policy Database Connection Pool
How many connections need to be placed? What should I do after the connection is exhausted? In this case, a Configuration Policy is configured. Generally, the configuration policy is as follows:
Application requirements of a connection pool, the number of connections in an initial connection pool, and the maximum number of connections that a connection pool can expand. This solution is
Implemented according to this policy. Key topics This section will detail the key details in the above solutions, and these key policies ensure the number
Efficient and secure data library connection reuse.

4.1. The allocation and release strategies in section 3.2 of reference records are very important for the effective reuse of connections. We also adopt a very famous method.
Design Mode: Reference Counting ). This mode is widely used in resource reuse.
, We apply this method to the distribution and release of connections. Each database connection retains a reference count to record the use of this connection.
Number of users. In specific implementation, we adopt a two-pole connection pool, a free pool, and a use pool. The storage in the idle pool has not been allocated for use.
Once a connection is allocated, it is put into the use pool and the reference count is increased. There is a great benefit in doing so,
This allows us to use connections efficiently, because once all connections in the idle pool are allocated, we can use the corresponding policies from the pool
Select a connection that is already in use for reuse, instead of randomly pulling out a connection for reuse. The policy can be selected as needed,
The strategy we adopt is relatively simple: Reuse the connection with the smallest reference count. Java's object-oriented features allow us to flexibly choose different strategies
(Provide an abstract interface shared by different policies. Each specific policy implements this interface, so that the processing logic of the policy and
Implement logical separation ).
4.2. Transaction processing is all about using database connections for common database access. For transaction processing, the situation becomes more complex.
Because transactions require atomic guarantee, database operations must comply with the "All-Nothing" principle, that is, either All operations or
Do nothing. If we simply adopt the above method of connection reuse, a problem will occur, because there is no way to control multiple
Database operation methods may be performed on multiple connections, and these connections may be reused by other non-transaction methods.
Connection itself provides support for transactions. You can explicitly call commit by setting the AutoCommit attribute of Connection to false.
Or the rollback method. However, to reuse connections securely and efficiently, a corresponding transaction support mechanism must be provided. We
The explicit transaction support method is used, and each transaction excludes a connection. This method can greatly reduce the complexity of transaction processing.
Miscellaneous (if a transaction does not exclusively occupy a connection, it is necessary to ensure the atomicity of the transaction and avoid reusing other operations unrelated to the transaction,
Basically impossible, unless the Connection class is developed by you), and it will not impede the reuse of the Connection, because it belongs to all the database operations of the transaction.
All operations are completed through this connection, and transaction methods reuse some other database methods. The Connection Management Service provides an explicit
The transaction start, end (commit or rollback) statement, and a transaction registry, used to register the transaction initiator and the connection used by the transaction
This table isolates the transaction and our connection management sections, because the table is based on the actual call conditions during running.
Status, dynamically generated. The connections used by transactions cannot be reused during the transaction operation. When the user needs to use the transaction method, the Connection Manager is called first.
The beginTrans method provided by the Service,
The main process of this method is as follows (pseudo code description ):
Public void beginTrans (){
... Conn = getIdleConnectionFromPoll ();
UserId = getUserId ();
RegisterTrans (userId, conn );
...}
In our implementation, the user identity is identified by the user's thread. All subsequent accesses to the database are indicated
Table, which is completed using allocated connections. When the transaction ends, the corresponding table items are deleted from the registry. How to deal with nested transactions?
We still use reference records, but the reference records here refer to the "nested hierarchy". The details are not described in detail.

4.3 encapsulation we can see from the above discussion that common database methods and transaction methods differ in connection usage (distribution and release,
For ease of use, the same operation interface is provided externally. we encapsulate the connection: normal connection and transaction connection. Here, we use
Java's powerful object-oriented feature: polymorphism. Both normal connections and transaction connections implement a DbConnection interface, which is defined in the interface
Methods are implemented according to their own characteristics, so that the connection processing is very consistent. 4.4 concurrency Problems
If our connection management service is more universal, we must consider the multi-threaded environment, that is, the concurrency issue. In a multi-threaded environment, we
Ensure the consistency of connection management data and connection internal data. Fortunately, Java provides good support for this.
(Synchronized keyword), so we can easily make connection management thread security. 5. Conclusion This article provides a basic connection pipe
Framework, which uses a number of widely used design patterns (resource pools, reference records, etc.) to enable efficient and secure reuse of database connections
Possible. Of course, there are still some issues that have not been taken into account, such as: joint management of different types of databases is not implemented; timing detection is not provided
And query the connection status. In addition, the packaging of connection management is more rough than some commercial systems, but the underlying foundation is consistent.
Therefore, I believe that these commercial products will have a better understanding of the relevant functions.