"Mybatis Hand Rolling Column" Chapter 6: Data Source Pooling Technology Implementation

Author: Xiao Fu Ge Blog: https://bugstack.cn - 手写Mybatis系列文章

Precipitate, share, grow, and let yourself and others gain something! 😄

I. Introduction

码农，只会做不会说？

Have you noticed? In fact, a large part of the code farmers only know how to write code and not speak. When it comes to debriefing, defense, sharing, and reporting, it is difficult to be fluent, high-level, and in-depth, and to incorporate a part of the interesting interest to allow the audience to better accept and understand the information you want to convey.

Then why can't it be said after it has been done? Because doing is only executed under the established goal and established route, but why the goal was determined, why the route was formulated, the horizontal reference and comparison, and the vertical in-depth design are not formed in the mind of an executor. Deduction of the process, more Most of the time it just executes. Therefore, it is difficult to express a complete thing.

Therefore, only when you have experienced enough and rich enough experience can you have better expression skills and on-the-spot response skills, and you can more clearly convey the information you want to express.

2. Goals

In the previous chapter, we parsed the data source configuration information in XML, and used Druid to create a data source to complete the database operation. But in fact, Mybatis has its own data source implementation, including the implementation of UnpooledDataSource without pooling and the implementation of PooledDataSource with pooling.

Then in this chapter, we will implement the processing of pooled data sources. Through these implementations, readers can better understand what the configuration properties of data sources in our daily development are intended to be, including: the maximum number of active connections, The number of idle connections, detection time, etc., play a role in the connection pool.

3. Design

First of all, you can understand the pooling technology as a specific implementation of the Flyweight mode. Usually, we need to cache or preheat some resources that require high creation costs and are frequently used. And store these resources in a preheating pool, obtain them from the pool when needed, and use them after use. Through pooling, the cost of resource use can be effectively controlled, including the unified control and management of the number of resources, idle time, and acquisition methods. As shown in Figure 6-1

图 6-1 池化数据源设计

By providing a unified connection pool center, storing data source links, and obtaining links as requested according to the configuration, the number of data source links in the connection pool is created. This includes the maximum idle links and the maximum active links, which are controlled with the creation process.
In addition, because the number of connections in the connection pool is controlled, when the external connection is obtained from the connection pool, if the connection is full, a circular wait will be performed. This is also the daily use of the DB connection pool. If a SQL operation causes a slow query, the entire service will enter a stage of paralysis. Each database-related interface call cannot obtain a link, and the interface query TP99 suddenly increases, and the system starts. Lots of alarms. Can the connection pool be configured to be very large? No, because the connection pool must correspond to the connection pool allocated by the database, so as to avoid the application configuration connection pool exceeding the number of connection pools provided by the database, otherwise there will be a situation where the connection cannot be allocated. The problem caused the database to collapse and cause a chain reaction.

4. Realization

1. Engineering structure

 mybatis-step-04
└── src
    ├── main
    │   └── java
    │       └── cn.bugstack.mybatis
    │           ├── binding
    │           ├── builder
    │           ├── datasource
    │           │   ├── druid
    │           │   │   └── DruidDataSourceFactory.java
    │           │   ├── pooled
    │           │   │   ├── PooledConnection.java
    │           │   │   ├── PooledDataSource.java
    │           │   │   ├── PooledDataSourceFactory.java
    │           │   │   └── PoolState.java
    │           │   ├── unpooled
    │           │   │   ├── UnpooledDataSource.java
    │           │   │   └── UnpooledDataSourceFactory.java
    │           │   └── DataSourceFactory.java
    │           ├── io
    │           ├── mapping
    │           ├── session
    │           │   ├── defaults
    │           │   │   ├── DefaultSqlSession.java
    │           │   │   └── DefaultSqlSessionFactory.java
    │           │   ├── Configuration.java
    │           │   ├── SqlSession.java
    │           │   ├── SqlSessionFactory.java
    │           │   ├── SqlSessionFactoryBuilder.java
    │           │   └── TransactionIsolationLevel.java  
    │           ├── transaction
    │           └── type
    └── test
        ├── java
        │   └── cn.bugstack.mybatis.test.dao
        │       ├── dao
        │       │   └── IUserDao.java
        │       ├── po
        │       │   └── User.java
        │       └── ApiTest.java
        └── resources
            ├── mapper
            │   └──User_Mapper.xml
            └── mybatis-config-datasource.xml

Project source code: https://t.zsxq.com/bmqNFQ7

The core class relationship of the pooled data source, as shown in Figure 6-2

图 6-2 池化数据源核心类关系

In the implementation of Mybatis data source, it includes two parts: the unpooled UnpooledDataSource implementation class and the pooled PooledDataSource implementation class. The pooled implementation class PooledDataSource is used to extend the unpooled UnpooledDataSource. Save the created link in memory, record it as idle link and active link, and use it in different stages.
PooledConnection is a proxy operation for the link. It recycles the closed link through the reflection call of the invoke method, and uses notifyAll to notify the user who is waiting for the link to grab the link.
The other is the implementation of the DataSourceFactory data source factory interface. After the implementation of the non-pooled factory, it is processed in the way of inheritance of the pooled factory. There are not too many complicated operations here. The pooled processing is mainly processed in the PooledDataSource class.

2. No pooling link implementation

For the implementation of database connection pools, it is not necessary to provide pooling technology. For some scenarios, only connection pools without pooling can be used. Then in the process of implementation, the non-pooling implementation and the pooling implementation can be split and decoupled, and only the corresponding data source needs to be configured when needed.

 public class UnpooledDataSource implements DataSource {

    private ClassLoader driverClassLoader;
    // 驱动配置，也可以扩展属性信息 driver.encoding=UTF8
    private Properties driverProperties;
    // 驱动注册器
    private static Map<String, Driver> registeredDrivers = new ConcurrentHashMap<>();
    // 驱动
    private String driver;
    // DB 链接地址
    private String url;
    // 账号
    private String username;
    // 密码
    private String password;
    // 是否自动提交
    private Boolean autoCommit;
    // 事务级别
    private Integer defaultTransactionIsolationLevel;

    static {
        Enumeration<Driver> drivers = DriverManager.getDrivers();
        while (drivers.hasMoreElements()) {
            Driver driver = drivers.nextElement();
            registeredDrivers.put(driver.getClass().getName(), driver);
        }
    }

    private Connection doGetConnection(Properties properties) throws SQLException {
        initializerDriver();
        Connection connection = DriverManager.getConnection(url, properties);
        if (autoCommit != null && autoCommit != connection.getAutoCommit()) {
            connection.setAutoCommit(autoCommit);
        }
        if (defaultTransactionIsolationLevel != null) {
            connection.setTransactionIsolation(defaultTransactionIsolationLevel);
        }
        return connection;
    }

    /**
     * 初始化驱动
     */
    private synchronized void initializerDriver() throws SQLException {
        if (!registeredDrivers.containsKey(driver)) {
            try {
                Class<?> driverType = Class.forName(driver, true, driverClassLoader);
                // https://www.kfu.com/~nsayer/Java/dyn-jdbc.html
                Driver driverInstance = (Driver) driverType.newInstance();
                DriverManager.registerDriver(new DriverProxy(driverInstance));
                registeredDrivers.put(driver, driverInstance);
            } catch (Exception e) {
                throw new SQLException("Error setting driver on UnpooledDataSource. Cause: " + e);
            }
        }
    }
    
}

The implementation of the data source link without pooling is relatively simple. The core is that the initializerDriver uses Class.forName and newInstance to create the data source link operation in the initialization driver.
After the connection is created, the link is stored in the driver registrar, so that the link can be directly obtained in subsequent use, and the resource consumption caused by repeated creation is avoided.

3. There is a pooled link implementation

The core of the pooled data source link is the packaging of the non-pooled link, and the corresponding pooling technology implementation is provided, including the operation processing of pushConnection, popConnection, forceCloseAll, and pingConnection.

In this way, when the user wants to obtain a link, it will be obtained from the connection pool, and at the same time, it will be judged whether there is an idle link, the maximum number of active links, and whether it needs to wait for processing or eventually throw an exception.

3.1 Proxy for pooled connections

Since we need to pool the connection, when the link calls some CLOSE methods, it is also necessary to close and restore the link from the pool to allow other users to obtain the link. Then here you need to proxy the connection class to handle the CLOSE method.

See the source code for details : cn.bugstack.mybatis.datasource.pooled.PooledConnection

 public class PooledConnection implements InvocationHandler {

    @Override
    public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
        String methodName = method.getName();
        // 如果是调用 CLOSE 关闭链接方法，则将链接加入连接池中，并返回null
        if (CLOSE.hashCode() == methodName.hashCode() && CLOSE.equals(methodName)) {
            dataSource.pushConnection(this);
            return null;
        } else {
            if (!Object.class.equals(method.getDeclaringClass())) {
                // 除了toString()方法，其他方法调用之前要检查connection是否还是合法的,不合法要抛出SQLException
                checkConnection();
            }
            // 其他方法交给connection去调用
            return method.invoke(realConnection, args);
        }
    }
    
}

Implement the InvocationHandler#invoke method through PooledConnection, wrapping the proxy link, so that the specific calling method can be controlled.
In the invoke method, in addition to the control of the CLOSE method, after excluding the methods of Object such as toString, there are other methods that really need to be processed by the DB link.
Then there is a data source recycling operation dataSource.pushConnection(this) for the CLOSE method; there is a specific implementation method, which is processed in the pooled implementation class PooledDataSource.

3.2 pushConnection recycling link

See the source code for details : cn.bugstack.mybatis.datasource.pooled.PooledDataSource

 protected void pushConnection(PooledConnection connection) throws SQLException {
    synchronized (state) {
        state.activeConnections.remove(connection);
        // 判断链接是否有效
        if (connection.isValid()) {
            // 如果空闲链接小于设定数量，也就是太少时
            if (state.idleConnections.size() < poolMaximumIdleConnections && connection.getConnectionTypeCode() == expectedConnectionTypeCode) {
                state.accumulatedCheckoutTime += connection.getCheckoutTime();
                if (!connection.getRealConnection().getAutoCommit()) {
                    connection.getRealConnection().rollback();
                }
                // 实例化一个新的DB连接，加入到idle列表
                PooledConnection newConnection = new PooledConnection(connection.getRealConnection(), this);
                state.idleConnections.add(newConnection);
                newConnection.setCreatedTimestamp(connection.getCreatedTimestamp());
                newConnection.setLastUsedTimestamp(connection.getLastUsedTimestamp());
                connection.invalidate();
                logger.info("Returned connection " + newConnection.getRealHashCode() + " to pool.");
                // 通知其他线程可以来抢DB连接了
                state.notifyAll();
            }
            // 否则，空闲链接还比较充足
            else {
                state.accumulatedCheckoutTime += connection.getCheckoutTime();
                if (!connection.getRealConnection().getAutoCommit()) {
                    connection.getRealConnection().rollback();
                }
                // 将connection关闭
                connection.getRealConnection().close();
                logger.info("Closed connection " + connection.getRealHashCode() + ".");
                connection.invalidate();
            }
        } else {
            logger.info("A bad connection (" + connection.getRealHashCode() + ") attempted to return to the pool, discarding connection.");
            state.badConnectionCount++;
        }
    }
}

In the processing of PooledDataSource#pushConnection data source recycling, the core is to determine whether the link is valid, and to perform the relevant idle link verification, to determine whether to recover the connection to the idle idle link list, and to notify other threads to preempt it.
If there are enough free links now, the recycled link will be rolled back and closed. connection.getRealConnection().close();

3.3 popConnection to get the link

See the source code for details : cn.bugstack.mybatis.datasource.pooled.PooledDataSource

 private PooledConnection popConnection(String username, String password) throws SQLException {
    boolean countedWait = false;
    PooledConnection conn = null;
    long t = System.currentTimeMillis();
    int localBadConnectionCount = 0;
    while (conn == null) {
        synchronized (state) {
            // 如果有空闲链接：返回第一个
            if (!state.idleConnections.isEmpty()) {
                conn = state.idleConnections.remove(0);
                logger.info("Checked out connection " + conn.getRealHashCode() + " from pool.");
            }
            // 如果无空闲链接：创建新的链接
            else {
                // 活跃连接数不足
                if (state.activeConnections.size() < poolMaximumActiveConnections) {
                    conn = new PooledConnection(dataSource.getConnection(), this);
                    logger.info("Created connection " + conn.getRealHashCode() + ".");
                }
                // 活跃连接数已满
                else {
                    // 取得活跃链接列表的第一个，也就是最老的一个连接
                    PooledConnection oldestActiveConnection = state.activeConnections.get(0);
                    long longestCheckoutTime = oldestActiveConnection.getCheckoutTime();
                    // 如果checkout时间过长，则这个链接标记为过期
                    if (longestCheckoutTime > poolMaximumCheckoutTime) {
                        state.claimedOverdueConnectionCount++;
                        state.accumulatedCheckoutTimeOfOverdueConnections += longestCheckoutTime;
                        state.accumulatedCheckoutTime += longestCheckoutTime;
                        state.activeConnections.remove(oldestActiveConnection);
                        if (!oldestActiveConnection.getRealConnection().getAutoCommit()) {
                            oldestActiveConnection.getRealConnection().rollback();
                        }
                        // 删掉最老的链接，然后重新实例化一个新的链接
                        conn = new PooledConnection(oldestActiveConnection.getRealConnection(), this);
                        oldestActiveConnection.invalidate();
                        logger.info("Claimed overdue connection " + conn.getRealHashCode() + ".");
                    }
                    // 如果checkout超时时间不够长，则等待
                    else {
                        try {
                            if (!countedWait) {
                                state.hadToWaitCount++;
                                countedWait = true;
                            }
                            logger.info("Waiting as long as " + poolTimeToWait + " milliseconds for connection.");
                            long wt = System.currentTimeMillis();
                            state.wait(poolTimeToWait);
                            state.accumulatedWaitTime += System.currentTimeMillis() - wt;
                        } catch (InterruptedException e) {
                            break;
                        }
                    }
                }
            }
            // 获得到链接
            if (conn != null) {
                if (conn.isValid()) {
                    if (!conn.getRealConnection().getAutoCommit()) {
                        conn.getRealConnection().rollback();
                    }
                    conn.setConnectionTypeCode(assembleConnectionTypeCode(dataSource.getUrl(), username, password));
                    // 记录checkout时间
                    conn.setCheckoutTimestamp(System.currentTimeMillis());
                    conn.setLastUsedTimestamp(System.currentTimeMillis());
                    state.activeConnections.add(conn);
                    state.requestCount++;
                    state.accumulatedRequestTime += System.currentTimeMillis() - t;
                } else {
                    logger.info("A bad connection (" + conn.getRealHashCode() + ") was returned from the pool, getting another connection
                    // 如果没拿到，统计信息：失败链接 +1
                    state.badConnectionCount++;
                    localBadConnectionCount++;
                    conn = null;
                    // 失败次数较多，抛异常
                    if (localBadConnectionCount > (poolMaximumIdleConnections + 3)) {
                        logger.debug("PooledDataSource: Could not get a good connection to the database.");
                        throw new SQLException("PooledDataSource: Could not get a good connection to the database.");
                    }
                }
            }
        }
    }

    return conn;
}

popConnection to get the link is a while infinite loop operation. Only when the link is obtained, an exception will be thrown out of the loop. If you read these exception codes carefully, is it the exception you encountered when you were doing some development?
The process of acquiring the link will use synchronized for locking, because all threads need to be locked in case of resource competition. In the locked code block, it is returned by judging whether there are still free links. If there is no, it will judge whether the number of active connections is sufficient, and if it is not enough, it will be created and returned. There will also be a process in which the active link has been cyclically waited, and an exception will be thrown if it cannot be obtained at the end.

4. Data Source Factory

The data source factory consists of two parts, namely no pooling and pooling. The project with pooling inherits the non-pooling factory, because in the implementation class of the Mybatis source code, it can reduce the properties of the reflection method of the unified packaging of Properties. deal with. Since we haven't developed this logic for the time being, we just simply get attributes and pass parameters, so it can't reflect how convenient such inheritance is. Readers can refer to the source code for understanding. Source class: UnpooledDataSourceFactory

4.1 Poolless chemical plant

See the source code for details : cn.bugstack.mybatis.datasource.unpooled.UnpooledDataSourceFactory

 public class UnpooledDataSourceFactory implements DataSourceFactory {

    protected Properties props;

    @Override
    public void setProperties(Properties props) {
        this.props = props;
    }

    @Override
    public DataSource getDataSource() {
        UnpooledDataSource unpooledDataSource = new UnpooledDataSource();
        unpooledDataSource.setDriver(props.getProperty("driver"));
        unpooledDataSource.setUrl(props.getProperty("url"));
        unpooledDataSource.setUsername(props.getProperty("username"));
        unpooledDataSource.setPassword(props.getProperty("password"));
        return unpooledDataSource;
    }

}

Simply wrap getDataSource to get data source processing and pass the necessary parameters. In the Mybatis source code, this part is stored and obtained by means of a lot of reflection field processing.

4.2 Youchi Chemical Factory

See the source code : cn.bugstack.mybatis.datasource.pooled.PooledDataSourceFactory

 public class PooledDataSourceFactory extends UnpooledDataSourceFactory {

    @Override
    public DataSource getDataSource() {
        PooledDataSource pooledDataSource = new PooledDataSource();
        pooledDataSource.setDriver(props.getProperty("driver"));
        pooledDataSource.setUrl(props.getProperty("url"));
        pooledDataSource.setUsername(props.getProperty("username"));
        pooledDataSource.setPassword(props.getProperty("password"));
        return pooledDataSource;
    }

}

The implementation of the pooled data source factory is also relatively simple. It just inherits the UnpooledDataSourceFactory's ability to share properties and instantiates the pooled data source.

5. Added type alias registrar

After we have newly developed two data sources and corresponding factory implementation classes, we need to configure them in Configuration, so that we can obtain and instantiate corresponding implementation classes according to different data source types when parsing XML.

See the source code for details : cn.bugstack.mybatis.session.Configuration

 public class Configuration {

    // 类型别名注册机
    protected final TypeAliasRegistry typeAliasRegistry = new TypeAliasRegistry();

    public Configuration() {
        typeAliasRegistry.registerAlias("JDBC", JdbcTransactionFactory.class);

        typeAliasRegistry.registerAlias("DRUID", DruidDataSourceFactory.class);
        typeAliasRegistry.registerAlias("UNPOOLED", UnpooledDataSourceFactory.class);
        typeAliasRegistry.registerAlias("POOLED", PooledDataSourceFactory.class);
    }

}

In the construction method Configuration, two data sources, UNPOOLED and POOLED, are added to the type register, which is convenient for subsequent use when parsing XML and processing data sources using the XMLConfigBuilder#environmentsElement method.

5. Test

1. Prepare in advance

1.1 Create library table

Create a database named mybatis and create a table user and add test data in the library, as follows:

 CREATE TABLE
    USER
    (
        id bigint NOT NULL AUTO_INCREMENT COMMENT '自增ID',
        userId VARCHAR(9) COMMENT '用户ID',
        userHead VARCHAR(16) COMMENT '用户头像',
        createTime TIMESTAMP NULL COMMENT '创建时间',
        updateTime TIMESTAMP NULL COMMENT '更新时间',
        userName VARCHAR(64),
        PRIMARY KEY (id)
    )
    ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
insert into user (id, userId, userHead, createTime, updateTime, userName) values (1, '10001', '1_04', '2022-04-13 00:00:00', '2022-04-13 00:00:00', '小傅哥');

1.2 Configure the data source

 <environments default="development">
    <environment id="development">
        <transactionManager type="JDBC"/>
        <dataSource type="DRUID">
            <property name="driver" value="com.mysql.jdbc.Driver"/>
            <property name="url" value="jdbc:mysql://127.0.0.1:3306/mybatis?useUnicode=true"/>
            <property name="username" value="root"/>
            <property name="password" value="123456"/>
        </dataSource>
    </environment>
</environments>

Configure data source information through mybatis-config-datasource.xml , including: driver, url, username, password
Here, the configuration of dataSource is modified to DRUID in the previous chapter, UNPOOLED and POOLED are tested and verified. These two data sources are also the data sources that we implement in this chapter.

1.3 Configure Mapper

 <select id="queryUserInfoById" parameterType="java.lang.Long" resultType="cn.bugstack.mybatis.test.po.User">
    SELECT id, userId, userName, userHead
    FROM user
    where id = #{id}
</select>

The configuration content of Mapper has been configured in the analysis and learning in the previous chapter, and this chapter has made simple adjustments.

2. Unit testing

 @Test
public void test_SqlSessionFactory() throws IOException {
    // 1. 从SqlSessionFactory中获取SqlSession
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsReader("mybatis-config-datasource.xml"));
    SqlSession sqlSession = sqlSessionFactory.openSession();
    
    // 2. 获取映射器对象
    IUserDao userDao = sqlSession.getMapper(IUserDao.class);
    
    // 3. 测试验证
    for (int i = 0; i < 50; i++) {
        User user = userDao.queryUserInfoById(1L);
        logger.info("测试结果：{}", JSON.toJSONString(user));
    }
}

In the test without pooling and with pooling, the basic unit test class does not need to be changed, and the SqlSession is still obtained through the SqlSessionFactory to obtain the mapping object and execute method calls. In addition, 50 query calls are added here, which is convenient for verifying the creation and acquisition of the connection pool and waiting.
The change lies in the adjustment of the dataSource data source type in mybatis-config-datasource.xml dataSource type="POOLED/UNPOOLED"

2.1 No pooling test

 <dataSource type="UNPOOLED"></dataSource>

Test Results

 11:27:48.604 [main] INFO  cn.bugstack.mybatis.test.ApiTest - 测试结果：{"id":1,"userHead":"1_04","userId":"10001","userName":"小傅哥"}
11:27:48.618 [main] INFO  cn.bugstack.mybatis.test.ApiTest - 测试结果：{"id":1,"userHead":"1_04","userId":"10001","userName":"小傅哥"}
11:27:48.622 [main] INFO  cn.bugstack.mybatis.test.ApiTest - 测试结果：{"id":1,"userHead":"1_04","userId":"10001","userName":"小傅哥"}
11:27:48.632 [main] INFO  cn.bugstack.mybatis.test.ApiTest - 测试结果：{"id":1,"userHead":"1_04","userId":"10001","userName":"小傅哥"}
11:27:48.637 [main] INFO  cn.bugstack.mybatis.test.ApiTest - 测试结果：{"id":1,"userHead":"1_04","userId":"10001","userName":"小傅哥"}
11:27:48.642 [main] INFO  cn.bugstack.mybatis.test.ApiTest - 测试结果：{"id":1,"userHead":"1_04","userId":"10001","userName":"小傅哥"}
11:27:48.649 [main] INFO  cn.bugstack.mybatis.test.ApiTest - 测试结果：{"id":1,"userHead":"1_04","userId":"10001","userName":"小傅哥"}
...

The unpooled connection pool operation will continuously establish new links with the database and execute SQL operations. In this process, as long as the database still has links that can be linked, a link can be created.

2.2 Pooling test

 <dataSource type="POOLED"></dataSource>