Thread之数据库连接

多线程下数据库连接对资源和时间相应都有很高要求。数据库连接资源十分昂贵，一般情况下应主动建立数据库连接池，限定池内连接数量，并应对线程尝试连接的时间进行限制。如下例。

模型

main线程

1、控制所有子线程同时尝试获取连接
2、等待所有子线程完成后才统计连接成功获取数量和丢失数量
上述两个功能要求CountDownLatch类进行控制。

子线程

尝试获取连接，提供本线程内的连接成功获取数量和丢失数量。由于main线程内统计数据由所有子线程提供，因此需要采用原子更新型（AtomicInteger）作为数据类型，确保所有子线程对主内存进行CAS操作。

连接池

连接池就是一个双向链表，存储连接

package com.threadpool;

import java.sql.Connection;
import java.util.LinkedList;

public class ConnectionPool {
    private LinkedList<Connection> pool = new LinkedList<>();

    public ConnectionPool(int initSize) {
        if (initSize > 0) {
            for (int i = 0; i < initSize; i++) {
                Connection conn = ConnectionDriver.createConnection();
                pool.addLast(conn);
            }
        }
    }

    // 从连接池中获取连接
    public Connection fetchConnection(long millis) throws InterruptedException {
        synchronized (pool) {
            // 非超时等待
            if (millis <= 0) {
                while (pool.isEmpty()) {
                    pool.wait();
                }
                return pool.removeFirst();
                // 超时等待
            } else {
                long remaining = millis;
                long future = System.currentTimeMillis() + millis;
                while (pool.isEmpty() && remaining > 0) {
                    pool.wait(remaining);
                    remaining = future - System.currentTimeMillis();
                }
                Connection result = null;
                if (!pool.isEmpty()) {
                    result = pool.removeFirst();
                }
                return result;
            }
        }
    }

    // 释放连接到连接池
    public void releaseConnection(Connection conn) {
        if (conn != null) {
            synchronized (pool) {
                pool.addLast(conn);
                pool.notifyAll();
            }
        }
    }
}

使用动态代理模式生成连接

使用动态代理，修改Connection接口的commit方法，调用方法前休眠一段时间。

package com.threadpool;

import java.lang.reflect.InvocationHandler;
import java.lang.reflect.Method;
import java.lang.reflect.Proxy;
import java.sql.Connection;
import java.util.concurrent.TimeUnit;

public class ConnectionDriver {
    static class ConnectionHandler implements InvocationHandler {
        //向方法切入点织入通知
        @Override
        public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
            // commit 方法前休眠100ms
            if (method.getName().equals("commit")) {
                TimeUnit.MILLISECONDS.sleep(100);
            }
            return null;
        }
    }

    //生成连接
    public static final Connection createConnection() {
        return (Connection) Proxy.newProxyInstance(ConnectionDriver.class.getClassLoader(), 
                new Class<?>[] {Connection.class}, new ConnectionHandler());
    }

}

多线程的数据库连接测试

多个线程多次尝试获取数据库连接，每个线程尝试获取数据库连接的次数不变，随着线程数量增加，连接成功率不断下降。

package com.threadpool;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.concurrent.CountDownLatch;
import java.util.concurrent.atomic.AtomicInteger;

public class ConnectionPoolTest {
    // 连接池中10个连接
    static ConnectionPool pool = new ConnectionPool(10);
    // 确保所有线程同时开始尝试获取连接
    static CountDownLatch begin = new CountDownLatch(1);
    static CountDownLatch end;

    public static void main(String[] args) throws InterruptedException {
        // 线程数量，可修改
        int threadCount = 40;
        // 确保所有线程同时结束
        end = new CountDownLatch(threadCount);
        // 尝试次数
        int count = 20;
        // 用于统计变量
        AtomicInteger got = new AtomicInteger();
        AtomicInteger missed = new AtomicInteger();

        for (int i = 0; i < threadCount; i++) {
            Thread thread = new Thread(new ConnectionRunner(count, got, missed), "ConnectionThread-" + i);
            thread.start();
        }
        begin.countDown();
        end.await();
        System.out.println("total number of connection is " + (threadCount * count));
        System.out.println("got connection " + got.get());
        System.out.println("missed connection " + missed.get());
    }

    static class ConnectionRunner implements Runnable {
        int count;
        AtomicInteger got;
        AtomicInteger missed;

        public ConnectionRunner(int count, AtomicInteger got, AtomicInteger missed) {
            this.count = count;
            this.got = got;
            this.missed = missed;
        }

        @Override
        public void run() {
            try {
                begin.await();
            } catch (InterruptedException e) {
                e.printStackTrace();
            }
            while (count > 0) {
                try {
                    Connection connection = pool.fetchConnection(1000);
                    if (connection != null) {
                        try {
                            // 模拟commit方法使用，相当于伪代码
                            connection.createStatement();
                            connection.commit();
                        } catch (SQLException e) {
                            e.printStackTrace();
                        } finally {
                            // 释放锁
                            pool.releaseConnection(connection);
                            got.incrementAndGet();
                        }
                    } else {
                        missed.incrementAndGet();
                    }
                } catch (InterruptedException e) {
                    e.printStackTrace();
                } finally {
                    count--;
                }
            }
            end.countDown();
        }
    }
}