Talking about the principle of database connection pool __ Database

talking about the principle of database connection pool

Hong Jongjun

This time we take a technological evolution to talk about the process of database connection pooling technology and its rationale, and the most popular open source database connection pool jar package today.

I. How do we perform database operations in the early stages ?

1. Principle: In general, the process of accessing a database by a Java application is:

① loading the database driver;

② database connection through JDBC;

③ Access database, execute SQL statement;

④ Disconnect the database.

2. Code

Query All Users

       public void Findallusers () {
              ///1, loading SQL Server driver Object
              drivermanager.registerdriver (new SQLServerDriver ());             
              2. Establish database connection via JDBC
              Connection con =drivermanager.getconnection ("jdbc:sqlserver://192.168.2.6:1433;d Atabasename=customer "," sa "," 123 ");            
              3. Create status
              Statement State =con.createstatement ();           
              4. Query the database and return results
              ResultSet result =state.executequery ("SELECT * from users");           
              5. Output query result
              while (Result.next ()) {
                     System.out.println (result.getstring ("email"));
              }            
              6, disconnect the database connection
              result.close ();
              State.close ();
              Con.close ();
        }

3. Analysis

In the process of program development, there are many problems: first, each Web request to establish a database connection. Establishing a connection is a time-consuming activity that takes 0.05s~1s times each time, and the system allocates memory resources. This time for one or several database operations, you may not feel how much overhead the system has. But for today's web applications, especially large e-commerce sites, there are hundreds of people or even thousands of people online is very normal. In this case, the frequent database connection operation is bound to occupy a lot of system resources, the response speed of the site must be reduced, serious or even cause a server crash. is not alarmist, this is restricting the development of some e-commerce website technology bottleneck problem. Second, for each database connection, you have to disconnect after use. Otherwise, if the program fails to shut down, it will cause a memory leak in the database system and will eventually have to restart the database. Also, this development does not control the number of connection objects being created, and system resources are allocated without consideration, such as too many connections, which can lead to memory leaks and server crashes.

The above user query case, if there are 1000 people access, there will be continuous database connection, disconnection operation:

Through the above analysis, we can see that "database connection" is a scarce resource, in order to protect the normal use of the site, it should be properly managed. In fact, after we query the database, if we do not close the connection, but temporarily stored up, when others use, the connection to their use. It avoids the time consuming of establishing a database connection and a disconnected operation. The principle is as follows:

two. Technology Evolved database connection pool

As can be seen from the above analysis, the root of the problem lies in the inefficient management of database connection resources. We know that there is a well-known design pattern for shared resources: resource pools (resource pool). This model is to solve the problem caused by the frequent allocation of resources ﹑ release. To solve the above problems, the database connection pooling technology can be adopted. The basic idea of a database connection pool is to establish a "buffer pool" for database connections. A certain number of connections are pre-placed in the buffer pool, and when a database connection needs to be established, simply take one out of the buffer pool and put it back when you are finished. 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 in the database through the connection pool management mechanism ﹑ usage, provide the basis for system development ﹑ test and performance adjustment.

We try to develop a connection pool ourselves to provide database connectivity services for the query business above:

① writing class implements DataSource interface

② creates 10 connections at a time in the class constructor, saves the connection in LinkedList

③ implementation getconnection Returns a connection from the LinkedList

④ provides methods for putting connections back into the connection pool

1. Connection Pool Code

    public class myDataSource implements DataSource {//list---implement stack structure privatelinkedlist<conn
 
              ection> datasources = new linkedlist<connection> (); Initialize number of connections Publicmydatasource () {//create 10 connections at a time for (int i = 0; I &lt ; 10;
                               i++) {try {//1, load SQL Server driver object
                               Drivermanager.registerdriver (New SQLServerDriver ());
                                  2. Establish database connection via JDBC Connection con =drivermanager.getconnection (
                               "Jdbc:sqlserver://192.168.2.6:1433;databasename=customer", "sa", "123");
                            3. Join the connection pool in datasources.add (con);
                            } catch (Exception e) {e.printstacktrace ();
          }
                     }    } @Override publicconnection getconnection () throws SQLException { Remove a connection from the connection pool Finalconnection conn = Datasources.removefirst ();
              Delete the first connection to return returnconn; }//Put the connection back into the connection pool publicvoid releaseconnection (Connection conn) {DataSource
                     S.ADD (conn); }
       }

2. Using connection pooling to reconstruct our user query function

       Query all users public
       void Findallusers () {
              ///1, use connection pooling to establish database connection
              myDataSource DataSource = new myDataSource ();
              Connection Conn =datasource.getconnection ();        
              2. Create status
              Statement State =con.createstatement ();           
              3. Query the database and return results
              ResultSet result =state.executequery ("SELECT * from users");           
              4. Output query result
              while (Result.next ()) {
                     System.out.println (result.getstring ("email"));
              }            
              5, disconnect the database connection
              result.close ();
              State.close ();
              6, return the database connection to the connection pool
              datasource.releaseconnection (conn);
        }

This is the principle of database connection pooling, which greatly provides the utilization of database connections and reduces the overhead of memory throughput. We are in the development process, there is no need to care about the database connection problem, there is a database connection pool to help us deal with, this time relieved. But the problem with connection pooling is more than that, let's see what else needs to be considered.

three. There are more issues to consider in connection pooling

1, concurrency problems

In order for the connection Management Service to be the most versatile, you must consider a multithreaded environment, which is a concurrency problem. This is a relatively good problem to solve because the Java language itself provides support for concurrency management, and using the Synchronized keyword ensures that threads are synchronized. Use the method to precede the class method with the Synchronized keyword, such as:

publicsynchronized Connection getconnection ()

2, multi-database server and multi-user

For large enterprise applications, it is often necessary to connect different databases at the same time (such as connecting Oracle and Sybase). How to connect a different database. Our strategy is to design a connection pool management class that conforms to the singleton pattern, reading a resource file when the unique instance of the connection pool management class is created, where the resource file holds information such as the URL address of multiple databases. Creates instances of multiple connection pool classes, each of which is a connection pool for a particular database, based on the information provided by the resource file. The connection pool management class instance takes a name for each connection pool instance and manages the different connection pools with different names.

In the case of multiple users with different names and passwords for the same database, it is also possible to process the resource file by setting up multiple database connection information in the resource file with the same URL address but with a different user name and password.

3. Transaction processing

We know that transactions are atomic, which requires that the operation of the database conform to the "all-all-nothing" principle, which is to either do the whole set of SQL statements or do nothing at all.

In the Java language, the connection class itself provides support for transactions, which can be implemented by setting the Autocommit property of connection to false and then explicitly invoking a commit or Rollback method. However, for efficient connection reuse, it is necessary to provide the corresponding transaction support mechanism. Each transaction can be implemented with a single 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 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 the free pool for management of connections. A connection that has been created but not yet allocated is stored in a free pool at the time of creation. Whenever a user requests a connection, the system first checks that there are no idle connections in the free pool. If there is the longest established (through the container in the order of the implementation) of the connection assigned to him (the actual connection is the validity of the judgment, if available to assign to the user, if it is not available to delete the connection from the free pool, re-detect whether the free pool is still connected) If not, check whether the currently open connection pool reaches the maximum number of connections allowed for the connection pool (maxconn) if not, create a new connection and wait a certain amount of time (timeout) if it is already reached. If a connection is freed within the waiting time, the connection can be assigned to the waiting user, and a null value is returned if the wait time exceeds the scheduled time of timeout. The system only counts the connections that are already being used, and then returns them to the free pool when it is finished. For the state of idle connections, a dedicated thread timing detection can be created, which can cost a certain amount of overhead, but ensures a faster response time. You can also take a method that does not open up specialized threads, just before the allocation is detected.

5, the connection pool configuration and maintenance

How many connections should be placed in the connection pool in order to make the system perform optimally. The system can take control of connections in the connection pool by setting the minimum number of connections (minconn) and the maximum number of connections (maxconn). 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 the connection pool, depending on the amount of access to the system, you can find the best point by repeated testing.

How to ensure the minimum number of connections in the connection pool. There are both dynamic and static strategies. Dynamic means that the connection pool is detected 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 is replenished to ensure the proper operation of the connection pool. Static is found when idle connection is insufficient to check again.

Four. There is a mature open source connection pool for our use in real development

Understanding the principle of the connection pool is possible, there is no need to write it all over again, that will take a lot of time, and performance and stability is not necessarily meet the requirements. In fact, there are a number of popular third-party database connection pool jar packages available for us to use. Such as:

1.Apache COMMONS-DBCP Connection Pool

Download: http://commons.apache.org/proper/commons-dbcp/

2.C3P0 Database Connection Pool

Download: http://sourceforge.net/projects/c3p0/

There are many introductory articles on these connection pools on the web, which are no longer being expanded.

Reprint Please specify source: http://blog.csdn.net/shuaihj/article/details/14223015