How database connection pooling works

For shared resources, there is a well-known design pattern: resource pools (resource pool). This model is to solve the problem caused by the frequent allocation and release of resources. The basic idea of a database connection pool is to establish a "buffer pool" for database connections. Pre-placed a certain number of connections in the buffer pool, when the need to establish a database connection, only need to remove one from the buffer pool, and then put it back. We can prevent the system from endlessly connecting to the database by setting the maximum number of connection pools. What is more important is that we can monitor the use of database connections through the connection pooling management mechanism to provide the basis for system development, testing, and performance tuning.

Related problem analysis of connection pool:

1, concurrency problems.

In order for the connection Management service to have maximum versatility, it is necessary to consider the multi-threaded environment and concurrency issues. This problem is relatively good because each language itself provides support for concurrency management, such as Java C #, and uses keywords such as synchronized (Java) lock (C #) to ensure thread synchronization.

2, transaction management.

We know that transactions are atomic, which requires that database operations conform to the "all-all-nothing" principle, which is to either do the whole set of SQL statements or do nothing at all. We know that when two threads share a connection connection object, and each has its own transaction to handle, it is a headache for connection pooling, because even if the connection class provides the appropriate transaction support, we still cannot be sure that the database operation corresponds to that transaction. Knowledge because all of our two threads are doing transactional operations. For this we can use each thing to monopolize a connection, although this method is a bit wasteful of connection pool resources but can greatly reduce the complexity of transaction management.

3. Allocation and release of connection pool

The allocation and release of the connection pool has a great impact on the performance of the system. Reasonable allocation and release can increase the reusability of the connection, thus reducing the cost of establishing a new connection and speeding up the user's access speed. You can use a list for the management of a connection. That is, the connections that have been created are placed in the list to be managed uniformly. Each time a user requests a connection, the system checks that there are no connections available for allocation in the list. Assign the most appropriate connection to him if you have one (how to find the most appropriate connection article will be pointed out in key issues); If you throw an exception to the user, the list can be assigned by a thread to manage it specifically. I'll introduce the specific implementation of this thread.

4, the connection pool configuration and maintenance

How many connections should be placed in the connection pool to make the system perform optimally? The system can take parameters such as setting the minimum number of connections (minconnection) and the maximum number of connections (maxconnection) to control connections in the connection pool. For example, the minimum number of connections is the number of connections created by the connection pool when the system starts. If you create too many, the system starts slowly, but the system responds quickly after creation, 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, develop quickly, and set a larger size when the system is actually used, because it will be faster to access the customer. Maximum connections is the maximum number of connections allowed in a connection pool, depending on how much of the system is accessed, which can be obtained by software requirements. How do I ensure the minimum number of connections in a connection pool? There are both dynamic and static strategies. Dynamic is to detect the connection pool at regular intervals, and if the number of connections is found to be less than the minimum number of connections, the corresponding number of new connections will be replenished to ensure the proper operation of the connection pool. Static is found when idle connection is insufficient to check again.

Reference count

The allocation and release strategies are important for efficient multiplexing connections, and the approach we adopt is a well-known design pattern: Reference counting (reference count). This pattern is very widely used in reusing resources, and we apply this method to the release of connection allocations. Each database connection maintains a reference count that is used to record the number of users of the link. In the implementation, we have further packaged the connection class to implement the reference count. Packaged Connection class We provide 2 ways to implement reference counting operations, one is repeat (assigned) one is remove (released back), and then the Repeatnow property is used to determine how much of the current reference, specifically which user refers to the connection, Is enlisted in the connection pool, and finally provides the Isrepeat property to determine whether the connection can use reference counting techniques. Once a connection is assigned, the requester of the connection is registered and the reference count is added, and the information is deleted when it is released and the reference count is reduced once. One of the great benefits of doing this is that we can use the connection efficiently, because once all the connections are allocated, we can use the corresponding strategy to pick out an active connection from the pool, instead of just taking out a connection to reuse.

Connection pooling is the buffering technology used to create and manage database connections, and connections in the buffer pool can be used by any thread that needs them. When a thread needs to use JDBC for a database operation, a connection is requested from the pool. When this link is finished, it will be returned to the connection pool, waiting for other threads to service.

Key benefits of connection pooling:

1) Reduce connection creation time, connection pool connection is ready, can be reused, get access to the database directly, thus reducing the number and time of connection creation.

2) simplified programming mode. When using connection pooling, each individual thread can operate as if it were creating its own JDBC connection, allowing the user to use JDBC programming techniques directly.

3) control the use of resources. If you do not use a connection pool, you need to create a connection each time you access the database, so that the stability of the system is affected by the system's connectivity requirements, and it is easy to generate resource waste and high load exceptions. Connection pooling maximizes performance and allows resource utilization to be controlled at a certain level. The connection pool can control the number of links in the pool, enhancing the stability of the system in a large number of user applications.

How the Connection pool works:

The core idea of the connection pool is the reuse of the connection, by establishing a database connection pool and a set of connection usage, allocation and management policies, so that the connection in the connection pool can be efficiently and safely reused, avoiding the overhead of frequent database connection establishment and shutdown.

The working principle of connection pooling consists of three parts, namely the establishment of connection pool, the use management of connection pool, and the closing of connection pool.

First, the establishment of the connection pool. Generally, when the system is initialized, the connection pool is established according to the system configuration and several connection objects are established in the pool so that it can be obtained from the connection pool when it is used, and connections in the connection pool cannot be created and closed at will, thus avoiding the overhead caused by the connection being arbitrarily established and closed. There are many container classes available in Java that allow you to easily build connection pools, such as Vector,stack.

Second, the management of the connection pool. The connection pooling management policy is the core of the connection pooling mechanism, and the allocation and release of connections within the connection pool have a great impact on the performance of the system. The strategy is:

When a customer requests a database connection, first to see if there is an idle connection in the connection pool, assign the connection to the customer if there is an idle connection, or, if there is no control connection, see if the number of connections currently open has reached the maximum number of connections, such as re-creating a requested customer if not met; Waits at the set maximum wait time and throws an exception to the customer if the maximum wait time is exceeded.

When a customer releases a database connection, it first determines whether the number of references to the connection exceeds the specified value, and if the connection is removed from the connection pool, it is reserved for other customer services. This policy ensures the efficient reuse of database connections and avoids the overhead of frequently establishing system resources for releasing connections.

Third, the connection pool is closed. When the application exits, close all links in the connection pool and release the connection pool-related resources, which is exactly the opposite of creation.

How database connection pooling works