Read MyBatis source code: PooledDataSource database connection pool

Sumkor
中文

In MyBatis, the PooledDataSource data source is used as the connection pool object, and the PooledConnection object is stored in the connection pool. Through dynamic proxy, the reuse of original connection objects and the isolation between database connections under multiple threads are realized.

1. Data source configuration

In the mybatis-config.xml configuration file, you can configure the data source by setting the dataSource tag.

<environments default="development">
  <environment id="development">
    <transactionManager type="JDBC">
      <property name="..." value="..."/>
    </transactionManager>
    <dataSource type="POOLED">
      <property name="driver" value="${driver}"/>
      <property name="url" value="${url}"/>
      <property name="username" value="${username}"/>
      <property name="password" value="${password}"/>
    </dataSource>
  </environment>
</environments>

The following configuration instructions are taken from the document-XML configuration-environments

The dataSource element uses the standard JDBC data source interface to configure the resources of the JDBC connection object.

There are three built-in data source types (that is, type="[UNPOOLED|POOLED|JNDI]"):

UNPOOLED

The implementation of this data source opens and closes the connection every time it is requested. Although a bit slow, it is a good choice for simple applications that do not require high database connection availability.
Performance depends on the database used. For some databases, the use of connection pools is not important, and this configuration is very suitable for this situation. The data source of UNPOOLED type only needs to configure the following 5 attributes:

  • driver – This is the fully qualified name of the Java class of the JDBC driver (not the data source class that may be included in the JDBC driver).
  • url-This is the JDBC URL address of the database.
  • username-the username for logging in to the database.
  • password-the password for logging in to the database.
  • defaultTransactionIsolationLevel-The default connection transaction isolation level.
  • defaultNetworkTimeout-the default network timeout (unit: milliseconds) to wait for the database operation to complete. Check the API documentation of java.sql.Connection#setNetworkTimeout() for more information.

As an option, you can also pass attributes to the database driver. Just add the "driver." prefix to the attribute name, for example:

  • driver.encoding=UTF8

This will pass the encoding property whose value is UTF8 to the database driver through the DriverManager.getConnection(url, driverProperties) method.

POOLED

The realization of this kind of data source uses the concept of "pool" to organize JDBC connection objects, avoiding the necessary initialization and authentication time when creating a new connection instance.
This processing method is very popular and enables concurrent Web applications to respond quickly to requests.

In addition to the attributes under UNPOOLED mentioned above, there are more attributes to configure the data source of POOLED:

  • poolMaximumActiveConnections-the number of active (in use) connections that can exist at any time, default value: 10
  • poolMaximumIdleConnections-the number of idle connections that may exist at any time.
  • poolMaximumCheckoutTime-before being forced to return, the pool connection is checked out (checked out) time, default value: 20000 milliseconds (ie 20 seconds)
  • poolTimeToWait – This is a low-level setting. If it takes a long time to get a connection, the connection pool will print the status log and try to get a connection again (to avoid failure and not print the log in case of misconfiguration), the default value: 20000 Milliseconds (that is, 20 seconds).
  • poolMaximumLocalBadConnectionTolerance-This is a low-level setting about the tolerance of bad connections, which acts on every thread that tries to get a connection from the buffer pool. If this thread acquires a bad connection, then the data source allows this thread to try to reacquire a new connection, but the number of retry attempts should not exceed the sum of poolMaximumIdleConnections and poolMaximumLocalBadConnectionTolerance. Default value: 3 (added in 3.4.5)
  • poolPingQuery-a detection query sent to the database to verify that the connection is working properly and is ready to accept requests. The default is "NO PING QUERY SET", which will cause most database drivers to return appropriate error messages when they fail.
  • poolPingEnabled-Whether to enable detection query. If enabled, you need to set the poolPingQuery property to an executable SQL statement (preferably a very fast SQL statement), the default value: false.
  • poolPingConnectionsNotUsedFor-Configure the frequency of poolPingQuery. It can be set to be the same as the database connection timeout period to avoid unnecessary detection. The default value is 0 (that is, all connections are detected every moment-of course only applicable when poolPingEnabled is true).

JNDI

This data source is implemented in order to be used in containers such as EJB or application servers. The container can configure the data source centrally or externally, and then place a JNDI context data source reference. This data source configuration requires only two attributes:

  • initial_context – This attribute is used to find the context in the InitialContext (ie, initialContext.lookup(initial_context)). This is an optional attribute. If omitted, the data_source attribute will be directly searched from the InitialContext.
  • data_source – This is the context path that refers to the location of the data source instance. When the initial_context configuration is provided, it will be searched in the context returned by it, and when it is not provided, it will be searched directly in the InitialContext.

Similar to other data source configuration, attributes can be passed directly to InitialContext by adding the prefix "env.". for example:

  • env.encoding=UTF8

This will pass the encoding property of UTF8 to its constructor when the InitialContext is instantiated.

Third-party data source

You can use a third-party data source implementation by implementing the interface org.apache.ibatis.datasource.DataSourceFactory:

public interface DataSourceFactory {
  void setProperties(Properties props);
  DataSource getDataSource();
}

org.apache.ibatis.datasource.unpooled.UnpooledDataSourceFactory can be used as a parent class to build a new data source adapter. For example, the following code is necessary to insert a C3P0 data source:

import org.apache.ibatis.datasource.unpooled.UnpooledDataSourceFactory;
import com.mchange.v2.c3p0.ComboPooledDataSource;

public class C3P0DataSourceFactory extends UnpooledDataSourceFactory {

  public C3P0DataSourceFactory() {
    this.dataSource = new ComboPooledDataSource();
  }
}

To make it work, remember to add corresponding properties to each setter method that you want MyBatis to call in the configuration file. The following is an example that can connect to a PostgreSQL database:

<dataSource type="org.myproject.C3P0DataSourceFactory">
  <property name="driver" value="org.postgresql.Driver"/>
  <property name="url" value="jdbc:postgresql:mydb"/>
  <property name="username" value="postgres"/>
  <property name="password" value="root"/>
</dataSource>

2. Source code analysis

This section takes the <dataSource type="POOLED"> as an example to explore the implementation principle of PooledDataSource.

2.1 The realization principle of PooledDataSource

Constructor

The constructor of PooledDataSource is as follows, you can see that when the PooledDataSource object is created, an UnpooledDataSource object is created.
At the same time, when the PooledDataSource object is instantiated, an instance of PoolState is created.


  private final PoolState state = new PoolState(this); // 用于存储数据库连接对象

  private final UnpooledDataSource dataSource; // 用于创建数据库连接对象
  private int expectedConnectionTypeCode; // 数据库连接标识,url+username+password 字符串的哈希值

  public PooledDataSource() {
    dataSource = new UnpooledDataSource();
  }

  public PooledDataSource(UnpooledDataSource dataSource) {
    this.dataSource = dataSource;
  }

  public PooledDataSource(String driver, String url, String username, String password) {
    dataSource = new UnpooledDataSource(driver, url, username, password);
    expectedConnectionTypeCode = assembleConnectionTypeCode(dataSource.getUrl(), dataSource.getUsername(), dataSource.getPassword());
  }

  public PooledDataSource(String driver, String url, Properties driverProperties) {
    dataSource = new UnpooledDataSource(driver, url, driverProperties);
    expectedConnectionTypeCode = assembleConnectionTypeCode(dataSource.getUrl(), dataSource.getUsername(), dataSource.getPassword());
  }

  public PooledDataSource(ClassLoader driverClassLoader, String driver, String url, String username, String password) {
    dataSource = new UnpooledDataSource(driverClassLoader, driver, url, username, password);
    expectedConnectionTypeCode = assembleConnectionTypeCode(dataSource.getUrl(), dataSource.getUsername(), dataSource.getPassword());
  }

  public PooledDataSource(ClassLoader driverClassLoader, String driver, String url, Properties driverProperties) {
    dataSource = new UnpooledDataSource(driverClassLoader, driver, url, driverProperties);
    expectedConnectionTypeCode = assembleConnectionTypeCode(dataSource.getUrl(), dataSource.getUsername(), dataSource.getPassword());
  }

Database connection-original object

The connection address, user name and password information of the database are stored in the UnpooledDataSource object.

PooledDataSource

In the PooledDataSource object, why save an UnpooledDataSource object?
This is to use UnpooledDataSource to establish a connection to the database.
For example, in the case of using the MySQL driver, a Socket connection will be established to the MySQL server and a com.mysql.cj.jdbc.ConnectionImpl connection object will be returned.

org.apache.ibatis.datasource.unpooled.UnpooledDataSource#getConnection()
org.apache.ibatis.datasource.unpooled.UnpooledDataSource#doGetConnection(java.lang.String, java.lang.String)
org.apache.ibatis.datasource.unpooled.UnpooledDataSource#doGetConnection(java.util.Properties)

  private Connection doGetConnection(Properties properties) throws SQLException {
    initializeDriver();
    Connection connection = DriverManager.getConnection(url, properties); // 利用数据库驱动包,创建连接对象
    configureConnection(connection);
    return connection;
  }

Database connection-proxy object

PoolState in PooledDataSource is an internal class used to store database connection objects and record statistics.

The size of the database connection pool is determined by the capacity of the two collections in PoolState:

  • In the idle connection set, the connections that are not used and can be used directly are stored.
  • In the active connection collection, the connections that are in use are stored.
public class PoolState {

  protected PooledDataSource dataSource;

  protected final List<PooledConnection> idleConnections = new ArrayList<>();   // 空闲的连接
  protected final List<PooledConnection> activeConnections = new ArrayList<>(); // 活动的连接
  protected long requestCount = 0;            // 请求次数
  protected long accumulatedRequestTime = 0;  // 总请求时间
  protected long accumulatedCheckoutTime = 0; // 总的检出时间(从池中取出连接,称为检出)
  protected long claimedOverdueConnectionCount = 0;               // 声明为已过期的连接数
  protected long accumulatedCheckoutTimeOfOverdueConnections = 0; // 总的已过期的连接数
  protected long accumulatedWaitTime = 0;     // 总等待时间
  protected long hadToWaitCount = 0;          // 要等待的次数
  protected long badConnectionCount = 0;      // 坏的连接次数

  public PoolState(PooledDataSource dataSource) {
    this.dataSource = dataSource;
  }
}  

PoolState does not store primitive connection objects, such as com.mysql.cj.jdbc.ConnectionImpl, but PooledConnection objects.

The dynamic proxy of JDK is used here. Every time a PooledConnection is created, a dynamic proxy is created for the original connection object.

The purpose of using proxy is to change the behavior of Connection:

  1. Change the connection closing behavior Connection#close to return the connection to the connection pool.
  2. Before each use of the connection, check whether the PooledConnection#valid property is valid (just check whether the proxy object is valid, not the original connection).

org.apache.ibatis.datasource.pooled.PooledConnection

class PooledConnection implements InvocationHandler { // 相当于一个工具类

  private static final String CLOSE = "close";
  private static final Class<?>[] IFACES = new Class<?>[] { Connection.class };

  private final int hashCode;
  private final PooledDataSource dataSource;
  private final Connection realConnection;  // 原始类-数据库连接
  private final Connection proxyConnection; // 代理类-数据库连接
  private long checkoutTimestamp; // 从连接池中检出的时间戳
  private long createdTimestamp;  // 创建的时间戳
  private long lastUsedTimestamp; // 上一次使用的时间戳
  private int connectionTypeCode;
  private boolean valid; // 连接是否有效

  /**
   * Constructor for SimplePooledConnection that uses the Connection and PooledDataSource passed in.
   *
   * @param connection
   *          - the connection that is to be presented as a pooled connection
   * @param dataSource
   *          - the dataSource that the connection is from
   */
  public PooledConnection(Connection connection, PooledDataSource dataSource) { // 传入原始类,获取代理类
    this.hashCode = connection.hashCode();
    this.realConnection = connection;
    this.dataSource = dataSource;
    this.createdTimestamp = System.currentTimeMillis();
    this.lastUsedTimestamp = System.currentTimeMillis();
    this.valid = true;
    this.proxyConnection = (Connection) Proxy.newProxyInstance(Connection.class.getClassLoader(), IFACES, this); // JDK 动态代理
  }
  
  /**
   * Required for InvocationHandler implementation.
   *
   * @param proxy
   *          - not used
   * @param method
   *          - the method to be executed
   * @param args
   *          - the parameters to be passed to the method
   * @see java.lang.reflect.InvocationHandler#invoke(Object, java.lang.reflect.Method, Object[])
   */
  @Override
  public Object invoke(Object proxy, Method method, Object[] args) throws Throwable { // 代理方法
    String methodName = method.getName();
    if (CLOSE.equals(methodName)) { // 将关闭连接的行为,改为放回连接池
      dataSource.pushConnection(this);
      return null;
    }
    try {
      if (!Object.class.equals(method.getDeclaringClass())) {
        // issue #579 toString() should never fail
        // throw an SQLException instead of a Runtime
        checkConnection(); // 使用连接之前,先检查
      }
      return method.invoke(realConnection, args);
    } catch (Throwable t) {
      throw ExceptionUtil.unwrapThrowable(t);
    }

  }
  
  private void checkConnection() throws SQLException {
    if (!valid) {
      throw new SQLException("Error accessing PooledConnection. Connection is invalid.");
    }
  }
  
  public boolean isValid() { // 校验连接是否有效
    return valid && realConnection != null && dataSource.pingConnection(this);
  }

PooledDataSource#pingConnection

During the use of PooledDataSource, the PooledConnection#isValid method will be called to check whether the connection is valid.

Properties related to connection checking in the PooledDataSource class:

// 发送到数据库的侦测查询,用来检验连接是否正常工作并准备接受请求。
protected String poolPingQuery = "NO PING QUERY SET";  
// 是否启用侦测查询。若开启,需要设置 poolPingQuery 属性为一个可执行的 SQL 语句(最好是一个速度非常快的 SQL 语句),默认值:false。
protected boolean poolPingEnabled;     
// 配置 poolPingQuery 的频率。可以被设置为和数据库连接超时时间一样,来避免不必要的侦测,默认值:0(即所有连接每一时刻都被侦测 — 当然仅当 poolPingEnabled 为 true 时适用)。
protected int poolPingConnectionsNotUsedFor;     

When the following conditions are met, the SQL statement configured by poolPingQuery will be sent to the database.

  1. The database connection is not closed.
  2. Configure poolPingEnabled to true in MyBatis XML.
  3. The time since the last connection was used is greater than the connection check frequency.

org.apache.ibatis.datasource.pooled.PooledDataSource#pingConnection

  /**
   * Method to check to see if a connection is still usable
   *
   * @param conn
   *          - the connection to check
   * @return True if the connection is still usable
   */
  protected boolean pingConnection(PooledConnection conn) { // 校验连接是否有效
    boolean result = true;

    try {
      result = !conn.getRealConnection().isClosed(); // 校验数据库连接会话是否已关闭 eg. com.mysql.cj.jdbc.ConnectionImpl.isClosed
    } catch (SQLException e) {
      if (log.isDebugEnabled()) {
        log.debug("Connection " + conn.getRealHashCode() + " is BAD: " + e.getMessage());
      }
      result = false;
    }

    if (result && poolPingEnabled && poolPingConnectionsNotUsedFor >= 0         // 配置了需要检查连接
        && conn.getTimeElapsedSinceLastUse() > poolPingConnectionsNotUsedFor) { // 距离上一次使用连接的时间,大于连接检查频率
      try {
        if (log.isDebugEnabled()) {
          log.debug("Testing connection " + conn.getRealHashCode() + " ...");
        }
        Connection realConn = conn.getRealConnection();
        try (Statement statement = realConn.createStatement()) {
          statement.executeQuery(poolPingQuery).close(); // 发送简单语句,检查连接是否有效
        }
        if (!realConn.getAutoCommit()) {
          realConn.rollback();
        }
        result = true;
        if (log.isDebugEnabled()) {
          log.debug("Connection " + conn.getRealHashCode() + " is GOOD!");
        }
      } catch (Exception e) {
        log.warn("Execution of ping query '" + poolPingQuery + "' failed: " + e.getMessage()); // 连接检查失败
        try {
          conn.getRealConnection().close(); // 尝试关闭连接
        } catch (Exception e2) {
          // ignore
        }
        result = false;
        if (log.isDebugEnabled()) {
          log.debug("Connection " + conn.getRealHashCode() + " is BAD: " + e.getMessage());
        }
      }
    }
    return result;
  }

The timestamp (in milliseconds) of the last connection used is recorded in the PooledConnection object.

org.apache.ibatis.datasource.pooled.PooledConnection#getTimeElapsedSinceLastUse

  /**
   * Getter for the time since this connection was last used.
   *
   * @return - the time since the last use
   */
  public long getTimeElapsedSinceLastUse() {
    return System.currentTimeMillis() - lastUsedTimestamp;
  }

PooledDataSource#popConnection

Remove the database connection from the pool, the code flow:

  1. The while loop is used to fetch the connection from the database connection pool (this operation is called checkout), and the PoolState state object lock needs to be acquired at the beginning of each loop.
  2. Detect the idle connection set and active connection set in PoolState, and obtain the connection object from it. There are several situations:
    2.1 If the set of idle connections is not empty, a connection is taken from it.
    2.2 If the set of idle connections is empty and the set of active connections is not full, use the database driver package to establish a new connection and package it as a PooledConnection object (generate dynamic proxy).
    2.3 The idle connection set is empty, and the active connection set is full, you need to check the earliest connection:

     2.3.1 如果该连接已超时(代理对象的检出时间大于 poolMaximumCheckoutTime,但是原始连接可能还存活),此时将代理对象 PooledConnection 标记为失效,将原始连接封装为新的 PooledConnection 对象。
     2.3.2 如果该连接未超时,则当前的检出线程进入等待。
  3. At this step, it means that the PooledConnection object is successfully checked out from PoolState, and you need to check whether the connection is valid:
    3.1 If the connection is valid, set the relevant timestamp and store it in the active connection set, ending the while loop.
    3.2 If the connection is invalid, re-enter the while loop.
    3.3 The number of times to re-enter the while loop is limited, and cannot exceed (the number of idle connections + the threshold for tolerance of bad connections), otherwise an exception will be thrown.

The complete code is as follows:

org.apache.ibatis.datasource.pooled.PooledDataSource#popConnection

  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()) { // 空闲连接集合非空,则从中取出连接(都是有效的)
          // Pool has available connection
          conn = state.idleConnections.remove(0); // 移除并返回
          if (log.isDebugEnabled()) {
            log.debug("Checked out connection " + conn.getRealHashCode() + " from pool.");
          }
        } else {
          // Pool does not have available connection // 空闲连接集合为空,则需要检查活跃连接集合
          if (state.activeConnections.size() < poolMaximumActiveConnections) { // 活跃连接集合未满,则建立新的数据库连接
            // Can create new connection
            conn = new PooledConnection(dataSource.getConnection(), this); // 利用数据库驱动包,创建连接对象,再包装为代理对象
            if (log.isDebugEnabled()) {
              log.debug("Created connection " + conn.getRealHashCode() + ".");
            }
          } else {
            // Cannot create new connection // 空闲连接集合为空,且活跃连接集合已满,则需要处理过期的活跃连接
            PooledConnection oldestActiveConnection = state.activeConnections.get(0);
            long longestCheckoutTime = oldestActiveConnection.getCheckoutTime();
            if (longestCheckoutTime > poolMaximumCheckoutTime) { // 对于活跃连接集合中最早放入的连接,如果它的检出的时间已超时(也就是说从池中出来太久了)
              // Can claim overdue connection
              state.claimedOverdueConnectionCount++;
              state.accumulatedCheckoutTimeOfOverdueConnections += longestCheckoutTime;
              state.accumulatedCheckoutTime += longestCheckoutTime;
              state.activeConnections.remove(oldestActiveConnection); // 从活跃连接集合移除
              if (!oldestActiveConnection.getRealConnection().getAutoCommit()) {
                try {
                  oldestActiveConnection.getRealConnection().rollback();
                } catch (SQLException e) {
                  /*
                     Just log a message for debug and continue to execute the following  // 回滚失败,当作无事发生
                     statement like nothing happened.
                     Wrap the bad connection with a new PooledConnection, this will help         // 将坏连接包装为一个新的 PooledConnection 对象
                     to not interrupt current executing thread and give current thread a         // 不会中断当前执行任务的线程,该线程后续可以从连接池中,取出其他的有效连接
                     chance to join the next competition for another valid/good database
                     connection. At the end of this loop, bad {@link @conn} will be set as null. // 本次循环最后,会把坏连接置为空
                   */
                  log.debug("Bad connection. Could not roll back");
                }
              }
              conn = new PooledConnection(oldestActiveConnection.getRealConnection(), this); // 后续需要识别为坏连接!怎么识别?通过 PooledConnection#isValid
              conn.setCreatedTimestamp(oldestActiveConnection.getCreatedTimestamp());
              conn.setLastUsedTimestamp(oldestActiveConnection.getLastUsedTimestamp());
              oldestActiveConnection.invalidate(); // 设为无效
              if (log.isDebugEnabled()) {
                log.debug("Claimed overdue connection " + conn.getRealHashCode() + ".");
              }
            } else {
              // Must wait // 活跃集合已满,且都未超时,只能等待其他线程归还活跃连接
              try {
                if (!countedWait) {
                  state.hadToWaitCount++;
                  countedWait = true;
                }
                if (log.isDebugEnabled()) {
                  log.debug("Waiting as long as " + poolTimeToWait + " milliseconds for connection.");
                }
                long wt = System.currentTimeMillis();
                state.wait(poolTimeToWait); // 等待直到超时,或者被其他线程唤醒(见 PooledDataSource#pushConnection)。接着进入下一次 while 循环
                state.accumulatedWaitTime += System.currentTimeMillis() - wt;
              } catch (InterruptedException e) {
                break;
              }
            }
          }
        }
        if (conn != null) { // 通过各种方式拿到连接之后,需要检查连接是否有效
          // ping to server and check the connection is valid or not
          if (conn.isValid()) {
            if (!conn.getRealConnection().getAutoCommit()) {
              conn.getRealConnection().rollback();
            }
            conn.setConnectionTypeCode(assembleConnectionTypeCode(dataSource.getUrl(), username, password)); // 设置连接标识:url+username+password 字符串的哈希值
            conn.setCheckoutTimestamp(System.currentTimeMillis()); // 设置检出时间,注意,这里是从数据库连接池中取出的时间戳!而不是与数据库建立连接的时间!
            conn.setLastUsedTimestamp(System.currentTimeMillis()); // 设置最后一次使用时间
            state.activeConnections.add(conn); // 加入活跃集合(1. 把原连接对象从空闲集合移动到活跃集合;2. 从活跃集合中取出超时连接,又放回活跃集合)
            state.requestCount++;
            state.accumulatedRequestTime += System.currentTimeMillis() - t;
          } else { // 连接无效,则进入下一次循环重新获取连接,或者抛异常
            if (log.isDebugEnabled()) {
              log.debug("A bad connection (" + conn.getRealHashCode() + ") was returned from the pool, getting another connection.");
            }
            state.badConnectionCount++;
            localBadConnectionCount++;
            conn = null;
            if (localBadConnectionCount > (poolMaximumIdleConnections + poolMaximumLocalBadConnectionTolerance)) { // 本次循环次数 大于(空闲连接数 + 坏连接忍受阈值),则抛异常不再循环
              if (log.isDebugEnabled()) {
                log.debug("PooledDataSource: Could not get a good connection to the database.");
              }
              throw new SQLException("PooledDataSource: Could not get a good connection to the database.");
            }
          }
        }
      }

    }

    if (conn == null) {
      if (log.isDebugEnabled()) {
        log.debug("PooledDataSource: Unknown severe error condition.  The connection pool returned a null connection.");
      }
      throw new SQLException("PooledDataSource: Unknown severe error condition.  The connection pool returned a null connection.");
    }

    return conn;
  }

In the process of checking out the connection, PoolState will be used to record some total time-consuming.

org.apache.ibatis.datasource.pooled.PoolState

  protected long requestCount = 0;            // 请求次数
  protected long accumulatedRequestTime = 0;  // 总请求时间
  protected long accumulatedCheckoutTime = 0; // 总的检出时间(从池中取出连接,称为检出)
  protected long claimedOverdueConnectionCount = 0;               // 声明为已过期的连接数
  protected long accumulatedCheckoutTimeOfOverdueConnections = 0; // 总的已过期的连接数
  protected long accumulatedWaitTime = 0;     // 总等待时间
  protected long hadToWaitCount = 0;          // 要等待的次数
  protected long badConnectionCount = 0;      // 坏的连接次数

In the PooledDataSource object, the capacity of the idle connection collection, the active connection collection, and some maximum time limits are set.

org.apache.ibatis.datasource.pooled.PooledDataSource

  protected int poolMaximumActiveConnections = 10; // 在任意时间可存在的活动(正在使用)连接数量
  protected int poolMaximumIdleConnections = 5;    // 任意时间可能存在的空闲连接数
  protected int poolMaximumCheckoutTime = 20000;   // 在被强制返回之前,池中连接被检出的时间。默认值:20000 毫秒(即 20 秒)
  protected int poolTimeToWait = 20000;            // 这是一个底层设置,如果获取连接花费的相当长的时间,它会给连接池打印状态日志,并重新尝试获取一个连接(避免在误配置的情况下一直失败且不打印日志)
  protected int poolMaximumLocalBadConnectionTolerance = 3; // 这是一个关于坏连接容忍度的底层设置,作用于每一个尝试从缓存池获取连接的线程。如果这个线程获取到的是一个坏的连接,那么这个数据源允许这个线程尝试重新获取一个新的连接,但是这个重新尝试的次数不应该超过 poolMaximumIdleConnections 与 poolMaximumLocalBadConnectionTolerance 之和

PooledDataSource#pushConnection

Return the connection to the database connection pool.

Code flow:

  1. Acquire the PoolState object lock.
  2. Remove PooledConnection from the set of active connections and check whether the connection is valid.
  3. If the connection is valid, judge whether the free set is full:
    3.1 If the free set is not full, encapsulate the original connection as a new PooledConnection object and add it to the free set.
    3.2 If the free set is full, the connection is closed and no longer reused.

It can be seen that each time the database connection is returned, the original com.mysql.cj.jdbc.ConnectionImpl connection object is actually returned, while the proxy object generated by PooledConnection is lost when it is used up, and is set to an invalid state to avoid repetition. Time affects other threads.

  protected void pushConnection(PooledConnection conn) throws SQLException {

    synchronized (state) {
      state.activeConnections.remove(conn); // 从活跃连接集合中移除
      if (conn.isValid()) { // 校验连接是否有效,若有效则进入下一步
        if (state.idleConnections.size() < poolMaximumIdleConnections && conn.getConnectionTypeCode() == expectedConnectionTypeCode) { // 空闲连接集合未满,并且连接标识一致(url+username+password),则需要加入空闲连接集合
          state.accumulatedCheckoutTime += conn.getCheckoutTime(); // 累加总的检出时间(记录连接从出池到入池的总时间)(有效的连接从池中取出时,会记录检出时间戳)
          if (!conn.getRealConnection().getAutoCommit()) {
            conn.getRealConnection().rollback(); // 把之前的事务回滚,避免对下次使用造成影响
          }
          PooledConnection newConn = new PooledConnection(conn.getRealConnection(), this); // 为原始连接生成新的 PooledConnection 对象
          state.idleConnections.add(newConn); // 加入空闲连接集合(注意这里不是把旧的 PooledConnection 从活跃集合移动到空闲集合)
          newConn.setCreatedTimestamp(conn.getCreatedTimestamp());
          newConn.setLastUsedTimestamp(conn.getLastUsedTimestamp());
          conn.invalidate(); // 将旧的 PooledConnection 对象设为失效,因为用户可以直接拿到这个实例,避免后续仍使用这个实例操作数据库
          if (log.isDebugEnabled()) {
            log.debug("Returned connection " + newConn.getRealHashCode() + " to pool.");
          }
          state.notifyAll(); // 唤醒等待获取数据库连接的线程,见 PooledDataSource#popConnection。被唤醒后只有一个线程会获得对象锁。
        } else { // 空闲连接集合已满,或者连接标识不一致,则关闭连接
          state.accumulatedCheckoutTime += conn.getCheckoutTime();
          if (!conn.getRealConnection().getAutoCommit()) {
            conn.getRealConnection().rollback();
          }
          conn.getRealConnection().close(); // 关闭连接,不再复用
          if (log.isDebugEnabled()) {
            log.debug("Closed connection " + conn.getRealHashCode() + ".");
          }
          conn.invalidate();
        }
      } else { // 连接无效,累加计数
        if (log.isDebugEnabled()) {
          log.debug("A bad connection (" + conn.getRealHashCode() + ") attempted to return to the pool, discarding connection.");
        }
        state.badConnectionCount++;
      }
    }
  }

2.2 Usage process of PooledDataSource

The usage process of PooledDataSource is as follows:

  1. When parsing the mybatis-config.xml configuration file, create a PooledDataSource connection pool object.
  2. When opening the SqlSession database session, create a JdbcTransaction transaction object, and use JdbcTransaction to maintain access to the database connection pool.
  3. In a session, JdbcTransaction will only get one connection from the connection pool. Within the scope of this session, all operations to read and write to the database are completed through this connection.
  4. When closing the SqlSession database session, return the connection to the database connection pool.

Data source configuration analysis

When using SqlSessionFactoryBuilder to parse the mybatis-config.xml configuration file, the environments tag will be parsed.

The call chain is as follows:

org.apache.ibatis.session.SqlSessionFactoryBuilder#build(Reader, String, java.util.Properties)
org.apache.ibatis.builder.xml.XMLConfigBuilder#parse()
org.apache.ibatis.builder.xml.XMLConfigBuilder#parseConfiguration(org.apache.ibatis.parsing.XNode)

  private void environmentsElement(XNode context) throws Exception {
    if (context != null) {
      if (environment == null) {
        environment = context.getStringAttribute("default");
      }
      for (XNode child : context.getChildren()) {
        String id = child.getStringAttribute("id");
        if (isSpecifiedEnvironment(id)) {
          TransactionFactory txFactory = transactionManagerElement(child.evalNode("transactionManager")); // 实例化事务工厂
          DataSourceFactory dsFactory = dataSourceElement(child.evalNode("dataSource")); // 实例化数据库连接池工厂
          DataSource dataSource = dsFactory.getDataSource(); // 从数据库连接池工厂中,获取数据源对象。一个 environment 标签只有一个数据源!
          Environment.Builder environmentBuilder = new Environment.Builder(id)
              .transactionFactory(txFactory)
              .dataSource(dataSource);
          configuration.setEnvironment(environmentBuilder.build()); // 将事务工厂、数据源对象注册到 Configuration 对象中
          break;
        }
      }
    }
  }

Data source initialization

Since the configuration is <dataSource type="POOLED"> , the PooledDataSourceFactory object is obtained by XML parsing.

org.apache.ibatis.builder.xml.XMLConfigBuilder#dataSourceElement

  private DataSourceFactory dataSourceElement(XNode context) throws Exception {
    if (context != null) {
      String type = context.getStringAttribute("type"); // eg. "POOLED"
      Properties props = context.getChildrenAsProperties();
      DataSourceFactory factory = (DataSourceFactory) resolveClass(type).getDeclaredConstructor().newInstance();
      factory.setProperties(props); // 将配置文件中的数据库连接信息,写入 DataSourceFactory 中的 DataSource 属性
      return factory;
    }
    throw new BuilderException("Environment declaration requires a DataSourceFactory.");
  }

The contents of the PooledDataSourceFactory class are as follows, a PooledDataSource object will be created in the constructor:

public class PooledDataSourceFactory extends UnpooledDataSourceFactory {

  public PooledDataSourceFactory() {
    this.dataSource = new PooledDataSource();
  }

}

PooledDataSourceFactory inheritance system:

PooledDataSourceFactory

Open conversation

When the SqlSession session is opened, the transaction object Transaction is created from the transaction factory and the DataSource object is passed to it.

SqlSession sqlSession = sqlSessionFactory.openSession();

org.apache.ibatis.session.defaults.DefaultSqlSessionFactory#openSession
org.apache.ibatis.session.defaults.DefaultSqlSessionFactory#openSessionFromDataSource

  private SqlSession openSessionFromDataSource(ExecutorType execType, TransactionIsolationLevel level, boolean autoCommit) {
    Transaction tx = null;
    try {
      final Environment environment = configuration.getEnvironment();
      final TransactionFactory transactionFactory = getTransactionFactoryFromEnvironment(environment);
      tx = transactionFactory.newTransaction(environment.getDataSource(), level, autoCommit); // 通过事务工厂,实例化 Transaction 事务对象
      final Executor executor = configuration.newExecutor(tx, execType); // 实例化 Executor 执行器对象,通过它来执行 SQL,支持插件扩展
      return new DefaultSqlSession(configuration, executor, autoCommit);
    } catch (Exception e) {
      closeTransaction(tx); // may have fetched a connection so lets call close()
      throw ExceptionFactory.wrapException("Error opening session.  Cause: " + e, e);
    } finally {
      ErrorContext.instance().reset();
    }
  }

Since the configuration is <transactionManager type="JDBC"> , here is the JdbcTransactionFactory, so the JdbcTransaction transaction object is created.

org.apache.ibatis.transaction.jdbc.JdbcTransactionFactory#newTransaction

  @Override
  public Transaction newTransaction(DataSource ds, TransactionIsolationLevel level, boolean autoCommit) {
    return new JdbcTransaction(ds, level, autoCommit);
  }

Official instructions on the transaction manager (transactionManager):

There are two types of transaction managers in MyBatis (that is, type="[JDBC|MANAGED]"):

  • JDBC-This configuration directly uses the commit and rollback facilities of JDBC, which relies on the connection obtained from the data source to manage the scope of the transaction.
  • MANAGED-This configuration does almost nothing. It never commits or rolls back a connection, but lets the container manage the entire life cycle of the transaction (such as the context of a JEE application server).

If you are using Spring + MyBatis, there is no need to configure the transaction manager, because the Spring module will use its own manager to override the previous configuration.

Get connected

When executing a SQL query, the database connection object Connection is obtained from the database connection pool PooledDataSource.

Student student01 = sqlSession.selectOne("selectByPrimaryKey", 1);

The call chain is as follows:

org.apache.ibatis.session.defaults.DefaultSqlSession#selectOne
org.apache.ibatis.session.defaults.DefaultSqlSession#selectList
org.apache.ibatis.executor.SimpleExecutor#doQuery
org.apache.ibatis.executor.SimpleExecutor#prepareStatement
org.apache.ibatis.executor.BaseExecutor#getConnection

  protected Connection getConnection(Log statementLog) throws SQLException {
    Connection connection = transaction.getConnection(); // 从事务对象中,获取连接对象
    if (statementLog.isDebugEnabled()) {
      return ConnectionLogger.newInstance(connection, statementLog, queryStack);
    } else {
      return connection;
    }
  }

The JdbcTransaction object is singleton in a session! Therefore, the same database connection is used in the same session.

org.apache.ibatis.transaction.jdbc.JdbcTransaction#getConnection

  protected Connection connection;

  @Override
  public Connection getConnection() throws SQLException {
    if (connection == null) { // 为空的时候,才从连接池中获取连接
      openConnection();
    }
    return connection;
  }

In fact, the connection is obtained from the data source object PooledDataSource, and what is obtained here is a proxy object.

org.apache.ibatis.transaction.jdbc.JdbcTransaction#openConnection
org.apache.ibatis.datasource.pooled.PooledDataSource#getConnection

  @Override
  public Connection getConnection() throws SQLException {
    return popConnection(dataSource.getUsername(), dataSource.getPassword()).getProxyConnection();
  }

Connect return

When closing the database session, return the database connection to the connection pool.

sqlSession.close();

The call chain is as follows:

org.apache.ibatis.session.defaults.DefaultSqlSession#close
org.apache.ibatis.executor.BaseExecutor#close
org.apache.ibatis.transaction.jdbc.JdbcTransaction#close
org.apache.ibatis.datasource.pooled.PooledConnection#invoke

  @Override
  public Object invoke(Object proxy, Method method, Object[] args) throws Throwable { // 代理方法
    String methodName = method.getName();
    if (CLOSE.equals(methodName)) { // 将关闭连接的行为,改为放回连接池
      dataSource.pushConnection(this);
      return null;
    }
    try {
      if (!Object.class.equals(method.getDeclaringClass())) {
        // issue #579 toString() should never fail
        // throw an SQLException instead of a Runtime
        checkConnection(); // 使用连接之前,先检查
      }
      return method.invoke(realConnection, args);
    } catch (Throwable t) {
      throw ExceptionUtil.unwrapThrowable(t);
    }

  }

3. Test cases

3.1 After closing the session, the validity of the connection is verified

After closing SQLSession, the database connection object appears as "invalid".
In fact, only the proxy object of the database connection is invalid at this time, and the original object of the database connection is still valid and has been returned to the database connection pool.

@Test
public void valid() throws SQLException {
    // 建立会话
    SqlSession sqlSession = sqlSessionFactory.openSession();
    Connection connection = sqlSession.getConnection();

    boolean valid = connection.isValid(1000);
    System.out.println("valid = " + valid);

    // 关闭会话
    sqlSession.close();

    // assert 无效的连接
    connection.isValid(1000);
}

The execution results are as follows:

2021-08-24 22:45:52,618 [main] DEBUG [org.apache.ibatis.transaction.jdbc.JdbcTransaction] - Opening JDBC Connection
Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.
2021-08-24 22:45:53,291 [main] DEBUG [org.apache.ibatis.datasource.pooled.PooledDataSource] - Created connection 1847637306.
2021-08-24 22:45:53,292 [main] DEBUG [org.apache.ibatis.transaction.jdbc.JdbcTransaction] - Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@6e20b53a]
valid = true
2021-08-24 22:45:53,293 [main] DEBUG [org.apache.ibatis.transaction.jdbc.JdbcTransaction] - Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@6e20b53a]
2021-08-24 22:45:53,294 [main] DEBUG [org.apache.ibatis.transaction.jdbc.JdbcTransaction] - Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@6e20b53a]
2021-08-24 22:45:53,294 [main] DEBUG [org.apache.ibatis.datasource.pooled.PooledDataSource] - Returned connection 1847637306 to pool.

java.sql.SQLException: Error accessing PooledConnection. Connection is invalid.

3.2 Check the validity of the connection before use

Reading from the source code, we can see that before using the database connection, there are two places to check the validity of the connection:

  1. To check out the database connection from the connection pool, the PooledConnection#isValid method will be called to check whether the connection is valid. At this time, a ping statement will be sent to the database to check whether the original connection is valid.

org.apache.ibatis.datasource.pooled.PooledConnection#isValid

  public boolean isValid() {
    return valid && realConnection != null && dataSource.pingConnection(this);
  }
  1. For connections that have been checked out, the PooledConnection#valid property is checked before each use (the original connection is not checked) to prevent the current PooledConnection from being invalidated by other threads.

org.apache.ibatis.datasource.pooled.PooledConnection#invoke
org.apache.ibatis.datasource.pooled.PooledConnection#checkConnection

  private void checkConnection() throws SQLException {
    if (!valid) {
      throw new SQLException("Error accessing PooledConnection. Connection is invalid.");
    }
  }

The test case is as follows:

Mybatis-config.xml modified configuration, poolPingQuery modified error statement simulation scenarios ping fails to connect check the original database.

<environment id="development">
    <transactionManager type="JDBC"/>
    <!-- POOLED 这种数据源的实现利用“池”的概念将 JDBC 连接对象组织起来,避免了创建新的连接实例时所必需的初始化和认证时间。 -->
    <dataSource type="POOLED">
        <property name="driver" value="${jdbc.driver}"/>
        <property name="url" value="${jdbc.url}"/>
        <property name="username" value="${jdbc.username}"/>
        <property name="password" value="${jdbc.password}"/>
        <property name="poolPingEnabled" value="true"/>
        <property name="poolPingQuery" value="select 1 from abc"/>
        <property name="poolPingConnectionsNotUsedFor" value="0"/>
    </dataSource>
</environment>
@Test
public void ping() {
    SqlSession sqlSession = sqlSessionFactory.openSession();
    Student student = sqlSession.selectOne("selectByPrimaryKey", 2);
    System.out.println("student = " + student);
    sqlSession.close();
}

Surprisingly, there are two different results when executed multiple times:

Case 1: Check out the connection failure multiple times from the database connection pool (each time the ping fails, exceeding the bad connection tolerance threshold), and directly report an error.

2021-08-24 23:47:24,858 [main] DEBUG [org.apache.ibatis.datasource.pooled.PooledDataSource] - PooledDataSource: Could not get a good connection to the database.
org.apache.ibatis.exceptions.PersistenceException:
### Error querying database.  Cause: java.sql.SQLException: PooledDataSource: Could not get a good connection to the database.
### The error may exist in com/sumkor/mapper/StudentMapper.java (best guess)
### The error may involve com.sumkor.mapper.StudentMapper.selectByPrimaryKey
### The error occurred while executing a query
### Cause: java.sql.SQLException: PooledDataSource: Could not get a good connection to the database.

Situation 2: The first time you get a connection from the connection pool, the ping fails, and it is regarded as a bad connection and invalidated, re-establish a new connection, and then verify whether the new connection is valid. Note that the ping check for the new connection is passed, so the query statement is executed normally.

2021-08-25 00:13:17,830 [main] DEBUG [org.apache.ibatis.transaction.jdbc.JdbcTransaction] - Opening JDBC Connection
Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.
2021-08-25 00:13:18,487 [main] DEBUG [org.apache.ibatis.datasource.pooled.PooledDataSource] - Created connection 112797691.
2021-08-25 00:13:18,487 [main] DEBUG [org.apache.ibatis.datasource.pooled.PooledDataSource] - Testing connection 112797691 ...
2021-08-25 00:13:18,516 [main] WARN  [org.apache.ibatis.datasource.pooled.PooledDataSource] - Execution of ping query 'select 1 from abc' failed: Table 'testdb.abc' doesn't exist
2021-08-25 00:13:18,523 [main] DEBUG [org.apache.ibatis.datasource.pooled.PooledDataSource] - Connection 112797691 is BAD: Table 'testdb.abc' doesn't exist
2021-08-25 00:13:18,523 [main] DEBUG [org.apache.ibatis.datasource.pooled.PooledDataSource] - A bad connection (112797691) was returned from the pool, getting another connection.
2021-08-25 00:13:18,550 [main] DEBUG [org.apache.ibatis.datasource.pooled.PooledDataSource] - Created connection 112049309.
2021-08-25 00:13:18,550 [main] DEBUG [org.apache.ibatis.transaction.jdbc.JdbcTransaction] - Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@6adbc9d]
2021-08-25 00:13:18,561 [main] DEBUG [com.sumkor.mapper.StudentMapper.selectByPrimaryKey] - ==>  Preparing: SELECT * FROM student WHERE id = ?
2021-08-25 00:13:18,603 [main] DEBUG [com.sumkor.mapper.StudentMapper.selectByPrimaryKey] - ==> Parameters: 2(Integer)
2021-08-25 00:13:18,638 [main] TRACE [com.sumkor.mapper.StudentMapper.selectByPrimaryKey] - <==    Columns: id, name, phone, email, sex, locked, gmt_created, gmt_modified, delete
2021-08-25 00:13:18,640 [main] TRACE [com.sumkor.mapper.StudentMapper.selectByPrimaryKey] - <==        Row: 2, 大明, 13821378271, xiaoli@mybatis.cn, 0, 0, 2018-08-30 18:27:42, 2018-10-08 20:54:29, null
2021-08-25 00:13:18,643 [main] DEBUG [com.sumkor.mapper.StudentMapper.selectByPrimaryKey] - <==      Total: 1
student = Student{id=2, name='大明'}
2021-08-25 00:13:18,643 [main] DEBUG [org.apache.ibatis.transaction.jdbc.JdbcTransaction] - Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@6adbc9d]
2021-08-25 00:13:18,644 [main] DEBUG [org.apache.ibatis.transaction.jdbc.JdbcTransaction] - Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@6adbc9d]
2021-08-25 00:13:18,644 [main] DEBUG [org.apache.ibatis.datasource.pooled.PooledDataSource] - Testing connection 112049309 ...
2021-08-25 00:13:18,645 [main] WARN  [org.apache.ibatis.datasource.pooled.PooledDataSource] - Execution of ping query 'select 1 from abc' failed: Table 'testdb.abc' doesn't exist
2021-08-25 00:13:18,645 [main] DEBUG [org.apache.ibatis.datasource.pooled.PooledDataSource] - Connection 112049309 is BAD: Table 'testdb.abc' doesn't exist
2021-08-25 00:13:18,645 [main] DEBUG [org.apache.ibatis.datasource.pooled.PooledDataSource] - A bad connection (112049309) attempted to return to the pool, discarding connection.

Why are there two completely different results?

Note that the connection pool of MyBatis sends a ping check condition to the database:

org.apache.ibatis.datasource.pooled.PooledDataSource#pingConnection

result && poolPingEnabled && poolPingConnectionsNotUsedFor >= 0 
        && conn.getTimeElapsedSinceLastUse() > poolPingConnectionsNotUsedFor

Among them, conn.getTimeElapsedSinceLastUse() > poolPingConnectionsNotUsedFor restricts the ping check only when the time since the last use of the connection is greater than the connection check frequency.

org.apache.ibatis.datasource.pooled.PooledConnection#getTimeElapsedSinceLastUse

  public long getTimeElapsedSinceLastUse() {
    return System.currentTimeMillis() - lastUsedTimestamp;
  }

In this example, the last connection time lastUsedTimestamp of the newly created connection is the time when the connection was created.

As long as the creation time of the connection and the check time occur within the same millisecond, the calculation result of System.currentTimeMillis() - lastUsedTimestamp conn.getTimeElapsedSinceLastUse() > poolPingConnectionsNotUsedFor gets false, so the ping check will not be sent to the database.

Since the creation time and the check time may occur in the same millisecond or not in the same millisecond (depending on the performance of the machine and whether the breakpoint is broken), there will be two different execution results.

Generally speaking, as long as the correct ping statement is configured, within the same millisecond after the connection to the database is established, there is no need to check the connection again.

3.3 Checkout timeout verification

Set the active connection collection size of the database connection pool to 1, allowing only one thread to use the database connection at a time.
Set the maximum checkout time of a database connection to 1 second. If the connection is taken out of the connection pool for more than 1 second and is not returned for more than 1 second, the checkout is considered to be timed out.

Start two threads, get the connection one after another, and observe the result.

/**
 * 验证检出超时
 */
@Test
public void timeout() throws InterruptedException {
    Configuration configuration = sqlSessionFactory.getConfiguration();
    Environment environment = configuration.getEnvironment();
    PooledDataSource pooledDataSource = (PooledDataSource) environment.getDataSource();
    System.out.println("pooledDataSource = " + pooledDataSource);

    pooledDataSource.setPoolMaximumActiveConnections(1); // 活跃连接集合的容量为1
    pooledDataSource.setPoolMaximumCheckoutTime(1000);   // 最大检出时间为1秒

    CountDownLatch startLatch = new CountDownLatch(1);
    CountDownLatch endLatch = new CountDownLatch(2);

    // 线程一,检出,很久不归还
    Thread thread01 = new Thread(new Runnable() {
        @Override
        public void run() {
            System.out.println(Thread.currentThread().getName() + " start to open session...");
            try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
                Student student = sqlSession.selectOne("selectByPrimaryKey", 1);
                System.out.println("student = " + student);

                // 休眠5秒之后,才让线程二获取连接
                Thread.sleep(5000);
                startLatch.countDown();

                // 继续休眠1秒,再获取连接,发现被线程二设为已失效
                Thread.sleep(1000);
                sqlSession.getConnection().isValid(1000);

            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                endLatch.countDown();
            }
        }
    }, "thread_01");

    // 线程二,在线程一检出过一段时间之后,再检出
    Thread thread02 = new Thread(new Runnable() {
        @Override
        public void run() {
            SqlSession sqlSession = null;
            try {
                startLatch.await();
                System.out.println(Thread.currentThread().getName() + " start to open session...");
                sqlSession = sqlSessionFactory.openSession();
                Student student = sqlSession.selectOne("selectByPrimaryKey", 2);
                System.out.println("student = " + student);
                // 此时空闲连接集合为空,且活跃连接集合已满,则需要判读活跃连接集合中的连接,是否检出超时:
                // 1. 超时,作废该连接;
                // 2. 未超时,等待释放
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (sqlSession != null) {
                    sqlSession.close();
                }
                endLatch.countDown();
            }
        }
    }, "thread_02");

    thread01.start();
    thread02.start();

    endLatch.await();
}

can be seen:

  1. thread_02 obtained the connection from the connection pool after a period of time after thread_01 checked out the database connection, and found that the only connection in the active connection set has been checked out and timed out.
  2. Therefore, thread_02 will invalidate the current PooledConnection and encapsulate the original ConnectionImpl as a new PooledConnection to use to complete the database query.
  3. After that, thread_01 wanted to continue to operate the original PooledConnection, and found that thread_02 had been invalidated, and then threw the exception Connection is invalid .

The execution results are as follows:

thread_01 start to open session...
2021-08-24 22:48:31,959 [thread_01] DEBUG [org.apache.ibatis.transaction.jdbc.JdbcTransaction] - Opening JDBC Connection
Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.
2021-08-24 22:48:32,306 [thread_01] DEBUG [org.apache.ibatis.datasource.pooled.PooledDataSource] - Created connection 212273522.
2021-08-24 22:48:32,306 [thread_01] DEBUG [org.apache.ibatis.transaction.jdbc.JdbcTransaction] - Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@ca70972]
2021-08-24 22:48:32,310 [thread_01] DEBUG [com.sumkor.mapper.StudentMapper.selectByPrimaryKey] - ==>  Preparing: SELECT * FROM student WHERE id = ?
2021-08-24 22:48:32,361 [thread_01] DEBUG [com.sumkor.mapper.StudentMapper.selectByPrimaryKey] - ==> Parameters: 1(Integer)
2021-08-24 22:48:32,400 [thread_01] TRACE [com.sumkor.mapper.StudentMapper.selectByPrimaryKey] - <==    Columns: id, name, phone, email, sex, locked, gmt_created, gmt_modified, delete
2021-08-24 22:48:32,401 [thread_01] TRACE [com.sumkor.mapper.StudentMapper.selectByPrimaryKey] - <==        Row: 1, 小明, 13821378270, xiaoming@mybatis.cn, 1, 0, 2018-08-29 18:27:42, 2018-10-08 20:54:25, null
2021-08-24 22:48:32,408 [thread_01] DEBUG [com.sumkor.mapper.StudentMapper.selectByPrimaryKey] - <==      Total: 1
student = Student{id=1, name='小明'}
thread_02 start to open session...
2021-08-24 22:48:37,413 [thread_02] DEBUG [org.apache.ibatis.transaction.jdbc.JdbcTransaction] - Opening JDBC Connection
2021-08-24 22:48:37,414 [thread_02] DEBUG [org.apache.ibatis.datasource.pooled.PooledDataSource] - Claimed overdue connection 212273522.
2021-08-24 22:48:37,414 [thread_02] DEBUG [com.sumkor.mapper.StudentMapper.selectByPrimaryKey] - ==>  Preparing: SELECT * FROM student WHERE id = ?
2021-08-24 22:48:37,415 [thread_02] DEBUG [com.sumkor.mapper.StudentMapper.selectByPrimaryKey] - ==> Parameters: 2(Integer)
2021-08-24 22:48:37,416 [thread_02] TRACE [com.sumkor.mapper.StudentMapper.selectByPrimaryKey] - <==    Columns: id, name, phone, email, sex, locked, gmt_created, gmt_modified, delete
2021-08-24 22:48:37,416 [thread_02] TRACE [com.sumkor.mapper.StudentMapper.selectByPrimaryKey] - <==        Row: 2, 大明, 13821378271, xiaoli@mybatis.cn, 0, 0, 2018-08-30 18:27:42, 2018-10-08 20:54:29, null
2021-08-24 22:48:37,417 [thread_02] DEBUG [com.sumkor.mapper.StudentMapper.selectByPrimaryKey] - <==      Total: 1
student = Student{id=2, name='大明'}
2021-08-24 22:48:37,418 [thread_02] DEBUG [org.apache.ibatis.transaction.jdbc.JdbcTransaction] - Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@ca70972]
2021-08-24 22:48:37,418 [thread_02] DEBUG [org.apache.ibatis.transaction.jdbc.JdbcTransaction] - Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@ca70972]
2021-08-24 22:48:37,419 [thread_02] DEBUG [org.apache.ibatis.datasource.pooled.PooledDataSource] - Returned connection 212273522 to pool.
2021-08-24 22:48:38,430 [thread_01] DEBUG [org.apache.ibatis.transaction.jdbc.JdbcTransaction] - Error resetting autocommit to true before closing the connection.  Cause: java.sql.SQLException: Error accessing PooledConnection. Connection is invalid.
2021-08-24 22:48:38,430 [thread_01] DEBUG [org.apache.ibatis.transaction.jdbc.JdbcTransaction] - Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@ca70972]
2021-08-24 22:48:38,430 [thread_01] DEBUG [org.apache.ibatis.datasource.pooled.PooledDataSource] - A bad connection (212273522) attempted to return to the pool, discarding connection.

java.sql.SQLException: Error accessing PooledConnection. Connection is invalid.

4. Summary

  1. In MyBatis, the PooledDataSource data source is used as the connection pool ob
阅读 541

Sumkor's Blog
Java and Everything.

会写点代码

125 声望
1.3k 粉丝
0 条评论
你知道吗?

会写点代码

125 声望
1.3k 粉丝
文章目录
宣传栏