A detailed explanation of database connection pool and the implementation of custom connection pool

Implementation principle

When the database connection pool is initialized, a certain number of database connections are created in the connection pool, and the number of these database connections is constrained by the minimum number of database connections. Regardless of whether these database connections are being used, the connection pool will always be guaranteed to have at least so many connections. The maximum number of database connections for a connection pool limits the maximum number of connections that this pool can occupy, and these requests are added to the wait queue when the number of connections requested by the application to the connection pool exceeds the maximum number of connections.
Connection pooling The basic idea is that when the system is initialized, the database connection is stored as an object in memory, and when the user needs to access the database, instead of establishing a new connection, it pulls out an established idle connection object from the connection pool. After use, the user does not close the connection, but instead puts the connection back into the connection pool for use by the next request. Connection pooling is managed by the connection pool itself, while connections are established and disconnected. You can also control the number of initial connections in the connection pool, the upper and lower limits of connections, the maximum number of uses per connection, maximum idle time, and so on, by setting parameters for the connection pool. It is also possible to monitor the number, usage, etc. of database connections through its own management mechanism.

Precautions

1. The minimum number of connections to the database connection pool is the database connection that the connection pool maintains, so if the application has a small amount of database connections, there will be a lot of wasted database connection resources.
2. The maximum number of connections for a database connection pool is the maximum number of connections the connection pool can request, and if the database connection request exceeds this number, subsequent database connection requests are added to the wait queue, which affects subsequent database operations.
3, the maximum number of connections to the specific value of the system to see the amount of access. To undergo constant testing to take a balance
4, Interval time to detect the connection pool, found that less than the minimum number of connections to supplement the corresponding number of new connections
5, the minimum number of connections and the maximum number of connections, the minimum number of connections and the maximum number of connections is too large, then the first connection request will be profitable, then the connection request over the minimum number of connections is equivalent to establishing a new database connection. However, these database connections that are larger than the minimum number of connections will not be released immediately after they are used, and will be placed in the connection pool for reuse or to be freed after an idle timeout.

Database Connection Pool Configuration Properties

At present, there are many kinds of database connection pool, different kinds of basic configuration properties are similar, such as C3P0, Proxool, Ddconnectionbroker, Dbpool, Xapool, Druid, DBCP, here we take dbcp as an example to say the main configuration items:

#最大连接数量: The maximum number of active connections that the connection pool can allocate at the same time, if set to non-positive, which means no limit, the default value is 8maxActive=15#最小空闲连接: The minimum number of connections allowed to remain idle in the connection pool, below which a new connection is created, and if set to 0 is not created, the default value is 0minIdle=5#最大空闲连接: The maximum number of connections allowed to remain idle in the connection pool, excess idle connections will be released, and if set to negative indicates no limit, the default value is 8maxIdle=10#初始化连接数: Number of initial connections created at Connection pool startup, default 0initialSize=5#连接被泄露时是否打印logAbandoned=true#是否自动回收超时连接removeAbandoned=true#超时时间 (in seconds) removeabandonedtimeout=180# Maximum wait time: The maximum time (in milliseconds) that the connection pool waits for a connection to be returned when there is no available connection, and the time to throw an exception if set to-1 means infinite wait, default value infinite maxwait=3000the #在空闲连接回收器线程运行期间休眠的时间值 (in milliseconds). Timebetweenevictionrunsmillis=10000#在每次空闲连接回收器线程 (if any) number of connections checked at run time Numtestsperevictionrun=8#连接在池中保持空闲而不被空闲连接回收器线程minEvictableIdleTimeMillis=10000#用来验证从连接池取出的连接validationQuery=select 1#指明是否在从池中取出连接前进行检验testOnBorrow=true#testOnReturnfalseIndicates whether the test is performed before being returned to the pool Testonreturn=true#设置为true后如果要生效, the Validationquery parameter must be set to a non-empty string Testwhileidle

Example of a custom database connection pool

First look at the definition of the connection pool. It initializes the maximum limit of the connection through a constructor, maintains the connection through a two-way queue, and the caller needs to call the Fetchconnection (long) method to specify how many milliseconds to time out to get the connection, and when the connection is finished, it needs to call Releaseconnection ( Connection) method to put the connection back into the thread pool

 Public classConnectionPool {PrivateLinkedlist<connection> pool =NewLinkedlist<connection>(); /*** Initialize the size of the connection pool *@paramInitialSize*/     PublicConnectionPool (intinitialsize) {        if(InitialSize > 0) {             for(inti = 0; i < initialsize; i++) {pool.addlast (connectiondriver.createconnection ()); }        }    }    /*** Release the connection and put it back in the connection pool *@paramConnection*/     Public voidreleaseconnection (Connection Connection) {if(Connection! =NULL){            synchronized(pool) {//notifications are required after the connection is released so that other consumers can perceive that a connection has been returned in the connection poolPool.addlast (connection);            Pool.notifyall (); }        }    }    /*** Cannot get to the connection within Mills, will return NULL *@paramMills *@return     * @throwsinterruptedexception*/     PublicConnection Fetchconnection (LongMillsthrowsinterruptedexception{synchronized(pool) {//Unlimited Wait            if(Mills <= 0) {                 while(Pool.isempty ()) {pool.wait (); }                returnPool.removefirst (); }Else{                LongFuture = System.currenttimemillis () +Mills; LongRemaining =Mills;  while(Pool.isempty () && remaining > 0) {                    //Wait Timeoutpool.wait (remaining); Remaining= Future-System.currenttimemillis (); } Connection result=NULL; if(!Pool.isempty ()) {Result=Pool.removefirst (); }                returnresult; }        }    }}

Since Java.sql.Connection is an interface, the final implementation is implemented by the database driver provider, considering just an example, we construct a connection with a dynamic proxy, and the proxy implementation of the connection is simply the commit () Hibernate 100 milliseconds when method calls

 Public classConnectiondriver {Static classConnectionhandlerImplementsinvocationhandler{@Override PublicObject Invoke (Object proxy, Method method, object[] args)throwsThrowable {if(Method.equals ("Commit") {TimeUnit.MILLISECONDS.sleep (100); }            return NULL; }    }    /*** Create a connection agent that sleeps 100 milliseconds during commit *@return     */     Public Static FinalConnection createconnection () {return(Connection) Proxy.newproxyinstance (Connectiondriver.class. getClassLoader (),NewClass[] {Connection.class},NewConnectionhandler ()); }}

The following is an example to test the operation of the simple database connection pool, simulating the process of client Connectionrunner acquiring, using, and finally releasing the connection, when it is used, the connection will increase the number of connections acquired, and conversely, will increase the number of non-acquired connections

 Public classConnectionpooltest {StaticConnectionPool pool =NewConnectionPool (10); //ensure all Connectionrunner can start at the same time    StaticCountdownlatch start =NewCountdownlatch (1); //The main thread will wait for all connectionrunner to finish before continuing execution    StaticCountdownlatch end;  Public Static voidMain (string[] args) {//number of threads, you can modify the number of threads to observe        intThreadCount = 10; End=NewCountdownlatch (ThreadCount); intCount = 20; Atomicinteger got=NewAtomicinteger (); Atomicinteger Notgot=NewAtomicinteger ();  for(inti = 0; i < ThreadCount; i++) {thread thread=NewThread (NewConnetionrunner (count, got, Notgot), "Connectionrunnerthread");        Thread.Start ();        } start.countdown (); Try{end.await (); } Catch(interruptedexception e) {e.printstacktrace (); } System.out.println ("Total Invoke:" + (ThreadCount *count)); System.out.println ("Got connection:" +got); System.out.println ("Not got Connection" +notgot); }    Static classConnetionrunnerImplementsRunnable {intcount;        Atomicinteger got;        Atomicinteger Notgot;  PublicConnetionrunner (intcount, Atomicinteger got, Atomicinteger Notgot) {             This. Count =count;  This. got =got;  This. Notgot =Notgot; } @Override Public voidrun () {Try{start.await (); } Catch(Exception ex) {} while(Count > 0) {                Try {                    //gets the connection from the thread pool and returns null if it cannot be obtained within 1000ms//Counts the number of got and the number of non-acquired quantities that the connection obtains separately NotgotConnection Connection = pool.fetchconnection (1); if(Connection! =NULL) {                        Try{connection.createstatement ();                        Connection.commit (); } finally{pool.releaseconnection (connection);                        Got.incrementandget (); }                    } Else{notgot.incrementandget (); }                } Catch(Exception ex) {}finally{Count--;        }} end.countdown (); }    }}

The Countdownlatch class is a synchronous counter that is constructed with an int parameter, which is the initial value of the counter, each time the countdown () method is called, the counter is reduced by 1, the counter is greater than 0 o'clock, and the await () The Countdownlatch method blocks the program from continuing to execute as it is written, and is an inverted-count latch that triggers a specific event when the count is reduced to 0 o'clock. This feature allows the main thread to wait for the end of a child thread. This is guaranteed to make all the Connetionrunner
Execute main to print after execution.

Operation Result:
20 Clients

Total invoke:2000

50 clients

Total invoke:10009991

100 clients

Total invoke:20001842158

With a certain amount of resources (10 connections in the connection pool), the rate at which the client is unable to get a connection is increasing as the client thread increases incrementally. Although the client thread is unable to get the connection in the mode of this time-out acquisition, it ensures that the client thread does not hang on to the operation acquired by the connection, but instead "returns on time" and informs the client that connection acquisition is a problem, which is a self-protection mechanism of the system. The design of the database connection pool can also be reused to other resource-acquired scenarios, and the acquisition of expensive resources, such as database connections, should be timed out.

Reprint: http://blog.csdn.net/fuyuwei2015/article/details/72419975

A detailed explanation of database connection pool and the implementation of custom connection pool