Small knowledge, big challenge! This article is participating in the creation activity of “Essential Tips for Programmers”.

This article has participated in the “Digitalstar Project” and won a creative gift package to challenge the creative incentive money.

1. Simple database connection pool

Brief introduction:

Use the wait timeout pattern to construct a simple database connection pool.

The database connection pool supports the following functions

  1. Normal access connection has no timeout period
  2. Timeout gets the connection, after which null is returned
  1. Use the connection
  2. Release the connection
  1. Records The Times of obtaining and trying to obtain connections, and collects statistics on connection pool performance

Timeout wait mode

Before we implement database connection pooling, let’s review the classic wait/notification paradigm from the previous chapter. The three steps are locking, conditional loops, and processing logic. However, regular wait/notification cannot do timeout wait, so we made some minor changes to implement a timeout wait/notification paradigm.

Modification method:

  1. Define the wait time T
  2. Calculate the return time of timeout now() + T
  1. Wait () when the result is not met or the timeout is not reached

Pseudo code:

Public synchronized Object get(long Mills) throws InterruptedException {Long Future = System.currentTimemills  + mills; long remaining = mills; While ((result == null) && Remainig > 0) {wait(remaining); // While (result == null) && remainig > 0) {wait(remaining); remaining = future - System.currentTimeMills; } return result; }Copy the code

Connection pool code and function description

The initial connection pool size is 10, or you can specify the size. The LinkedList queue is used for connection pool management, with the end of the queue inserting connections and the head of the queue fetching connections. Connection ()/ Connection (long) supports normal connection acquisition without timeout period and timeout waiting for connection acquisition. If the connection is not obtained within the specified time, null will be returned. ReleaseConnection (Connection) After a connection is released, the connection returns to the queue and notifies the thread waiting on the pool to acquire the connection.

package com.lizba.p3; import java.sql.Connection; import java.util.LinkedList; / * * * < p > * * < / p > database connection pool * * @ Author: Liziba * @ the Date: 2021/6/17 21:13 */ public class ConnectionPool {/** default ConnectionPool size */ private static final int DEFAULT_SIZE = 10; /** private LinkedList<Connection> pool = new LinkedList<>(); Public ConnectionPool(int initialSize) {// Incorrect parameter default initialization 10 initialSize = initialSize <= 0? DEFAULT_SIZE : initialSize; for (int i = 0; i < initialSize; i++) { pool.addLast(ConnectionDriver.createConnection()); }} /** * Release the connection, return to the connection pool, Public void releaseConnection(Connection Connection) {if (connection! = null) { synchronized (pool) { pool.addLast(connection); // Notify the consumer pool.notifyall (); }}} /** * * * @return * @throws InterruptedException */ Public Connection Connection () throws InterruptedException {return  connection(0); @param mills * @return * @interruptedexception */ Public Connection Connection (Long Mills) throws InterruptedException { synchronized (pool) { if (mills <= 0) { while (pool.isEmpty()) { pool.wait(); } return pool.removeFirst(); } else { long future = System.currentTimeMillis() + mills; long remaining = mills; while (pool.isEmpty() && remaining > 0) { pool.wait(mills); remaining = future - System.currentTimeMillis(); } return pool.isEmpty() ? null : pool.removeFirst(); }}}}Copy the code

Connection driver simulation:

Connection is an interface that we created by dynamic proxy Connection, when performing the Connection method of the commit, through TimeUnit. MILLISECONDS. Sleep (200); Hibernate the thread to simulate the execution of a transaction commit.

package com.lizba.p3; import java.lang.reflect.InvocationHandler; import java.lang.reflect.Method; import java.lang.reflect.Proxy; import java.sql.Connection; import java.util.concurrent.TimeUnit; /** ** <p> ** @author: Liziba * @date: 2021/6/17 16:57 */ public class ConnectionDriver { private static final String COMMIT_OP = "commit"; static class ConnectionHandler implements InvocationHandler { @Override public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {// Commit 200ms if (method.getName().equals(COMMIT_OP)) { TimeUnit.MILLISECONDS.sleep(200); } return null; } } public static final Connection createConnection() { return (Connection) Proxy.newProxyInstance(ConnectionDriver.class.getClassLoader(), new Class<? >[] {Connection.class}, new ConnectionHandler()); }}Copy the code

Client tests:

The client uses multi-threaded simulation to initiate multiple connections to the database, and calculates the number of successful and unsuccessful attempts to acquire threads from the thread pool under different thread pool sizes and client connections by counting The Times of acquiring and not acquiring.

package com.lizba.p3; import java.sql.Connection; import java.sql.SQLException; import java.util.concurrent.CountDownLatch; import java.util.concurrent.atomic.AtomicInteger; /** * <p> * Connection pool test, using CountdownLatch to ensure that connectionThread can execute concurrently * </p> ** @author: Liziba * @date: 2021/6/17 17:51 */ public class PoolTest {/** Init thread pool */ private static ConnectionPool pool = new ConnectionPool(10); */ private static CountDownLatch start = new CountDownLatch(1); */ private static CountDownLatch end; Public static void main(String[] args) throws InterruptedException {// Define the number of threads that obtain connections int threadSize = 10; Int count = 50; end = new CountDownLatch(threadSize); AtomicInteger getConnectionCount = new AtomicInteger(); AtomicInteger notGetConnectionCount = new AtomicInteger(); for (int i = 0; i < threadSize; i++) { Thread t = new Thread(new Runner(count, getConnectionCount, notGetConnectionCount), "connectionThread"); t.start(); } start.countDown(); end.await(); System.out.println(" threadSize "+ (threadSize * count)); System.out.println(" getConnectionCount "+ getConnectionCount); System.out.println(notGetConnectionCount); } static class Runner implements Runnable { private int count; private AtomicInteger getConnectionCount; private AtomicInteger notGetConnectionCount; public Runner(int count, AtomicInteger getConnectionCount, AtomicInteger notGetConnectionCount) { this.count = count; this.getConnectionCount = getConnectionCount; this.notGetConnectionCount = notGetConnectionCount; } @override public void run() {try {start.countDown(); start.await(); } catch (InterruptedException e) { e.printStackTrace(); While (count > 0) {try {Connection Connection = pool.connection(1000); if (connection ! = null) { try { connection.createStatement(); connection.commit(); } finally {// releaseConnection pool.releaseconnection (connection); / / the number of records for getConnectionCount incrementAndGet (); }} else {/ / the number of record did not get to notGetConnectionCount incrementAndGet (); } } catch (InterruptedException | SQLException e) { e.printStackTrace(); } finally { count--; } } end.countDown(); }}}Copy the code

Test result statistics:

Author CPU(AMD Ryzen 5 3600 6-core Processor), memory 16G.

Number of threads Total number of acquisition Success times Failed fetch times Failure ratio
10 500 500 0 0%
20 1000 891 109 10.9%
30 1500 1212 288 19.2%
40 2000 1482 518 25.9%

Conclusion:

When the amount of data resource is fixed, the ratio of connection failure will increase with the increase of the number of concurrent connections. The design of timeout connection here, in the actual development and application process, is beneficial to reduce program blocking time, avoid service unavailability in the case of connection is always unavailable. By returning NULL, programmers can respond to fault tolerance measures or service degradation when designing the system.