Read the source code of mybatis: pooleddatasource of database connection pool

Time:2021-12-8

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 agent, the reuse of original connection objects and the isolation between database connections under multithreading 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 intercepted from the official 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 each time it is requested. Although a little 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, using connection pool is not important. This configuration is very suitable for this situation. Unpooled data sources only need to configure the following five attributes:

  • Driver – this is the fully qualified name of a JDBC driven Java class (not a data source class that may be included in a JDBC Driver).
  • URL – this is the JDBC URL address of the database.
  • Username – the user name to log in to the database.
  • Password – password to log in to the database.
  • Defaulttransactionisolationlevel – the default connection transaction isolation level.
  • Defaultnetworktimeout – the default network timeout in milliseconds to wait for the database operation to complete. Check the API documentation for Java. SQL. Connection #setnetworktimeout() for more information.

Optionally, you can also pass properties to the database driver. Just prefix the attribute name with “driver.” for example:

  • driver.encoding=UTF8

This will pass the encoding property with the value of utf8 to the database driver through the drivermanager. Getconnection (URL, driverproperties) method.

POOLED

The implementation of this data source uses the concept of “pool” to organize JDBC connection objects, avoiding the necessary initialization and authentication time when creating new connection instances.
This processing method is very popular and can enable concurrent web applications to respond to requests quickly.

In addition to the above mentioned attributes under unpooled, 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: 10
  • Poolmaximumidleconnections – the number of idle connections that may exist at any time.
  • Poolmaximumcheckouttime – the time that connections in the pool are checked out before being forcibly returned. The default value is 20000 milliseconds (i.e. 20 seconds)
  • Pooltimetowait – this is an underlying setting. If it takes a long time to obtain a connection, the connection pool will print the status log and try to obtain a connection again (to avoid continuous failure in case of misconfiguration and no log printing). The default value is 20000 milliseconds (i.e. 20 seconds).
  • Poolmaximumlocalbadconnectiontolerance – this is a low-level setting for bad connection tolerance that applies to every thread trying to get a connection from the cache pool. If the thread gets a bad connection, the data source allows the thread to try to get a new connection again, but the number of retries should not exceed the sum of poolmaximumidleconnections and poolmaximumlocalbadconnectiontolerance. Default value: 3 (added to 3.4.5)
  • Poolpingquery – a probe query sent to the database to verify that the connection is working properly and ready to accept the request. The default is “no Ping query set”, which will cause most database drivers to return appropriate error messages when errors occur.
  • 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 is false.
  • Poolpingconnectionsnotusedfor – configure the frequency of poolpingquery. It can be set as the database connection timeout to avoid unnecessary detection. The default value is 0 (that is, all connections are detected at every moment – of course, it is only applicable when poolpingenabled is true).

JNDI

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

  • initial_ Context – this attribute is used to find the context in the initialcontext (that is, initialcontext. Lookup (initial_context)). This is an optional attribute. If it is ignored, data will be found directly from initialcontext_ Source property.
  • data_ Source – this is the context path that references the location of the data source instance. Initial is provided_ When the context is configured, it will be found in the returned context. If it is not provided, it will be found directly in initialcontext.

Similar to other data source configurations, attributes can be passed directly to initialcontext by adding the prefix “env.”. For example:

  • env.encoding=UTF8

This will pass the encoding attribute with the value of utf8 to its constructor when initializcontext is instantiated.

Third party data sources

You can use third-party data sources 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, such as the following code necessary to insert 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();
  }
}

In order to make it work, remember to add corresponding properties to each setter method you want mybatis to call in the configuration file. The following is an example of connecting 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 begins with<dataSource type="POOLED">Configuration as an example to explore the implementation principle of pooleddatasource.

2.1 implementation principle of pooleddatasource

Constructor

The pooleddatasource constructor is as follows. You can see that when creating a pooleddatasource object, an unpooleddatasource object will be created.
Meanwhile, when instantiating the pooleddatasource object, a poolstate instance will be created.

private final PoolState state = new PoolState(this); //  Used to store database connection objects

  private final UnpooledDataSource dataSource; //  Used to create database connection objects
  private int expectedConnectionTypeCode; //  Database connection ID, hash value of URL + username + password string

  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 saved in the unpooleddatasource object.

Read the source code of mybatis: pooleddatasource of database connection pool

Why save an unpooleddatasource object in a pooleddatasource object?
This is to use unpooleddatasource to establish a connection to the database.
For example, when MySQL driver is used, a socket connection will be established to 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); //  Using the database driver package, create the connection object
    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 the poolstate:

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

  protected PooledDataSource dataSource;

  protected final List<PooledConnection> idleConnections = new ArrayList<>();   //  Idle connection
  protected final List<PooledConnection> activeConnections = new ArrayList<>(); //  Active connection
  protected long requestCount = 0;            //  Number of requests
  protected long accumulatedRequestTime = 0;  //  Total request time
  protected long accumulatedCheckoutTime = 0; //  Total check-out time (removing connections from the pool is called check-out)
  protected long claimedOverdueConnectionCount = 0;               //  Number of connections declared expired
  protected long accumulatedCheckoutTimeOfOverdueConnections = 0; //  Total number of expired connections
  protected long accumulatedWaitTime = 0;     //  Total waiting time
  protected long hadToWaitCount = 0;          //  Number of times to wait
  protected long badConnectionCount = 0;      //  Bad connections

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

Instead of storing the original connection object, such as com.mysql.cj.jdbc.connectionimpl, poolstate stores the pooledconnection object.

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

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

  1. Change the connection closing behavior of connection #close to return the connection to the connection pool.
  2. Before using the connection each time, 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 {// is equivalent to a tool class

  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;  //  Original class - database connection
  private final Connection proxyConnection; //  Proxy class - database connection
  private long checkoutTimestamp; //  Timestamp checked out from connection pool
  private long createdTimestamp;  //  Created timestamp
  private long lastUsedTimestamp; //  Last used timestamp
  private int connectionTypeCode;
  private boolean valid; //  Is the connection 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, pooleddatasource) {// pass in the original class and get the proxy class
    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 dynamic agent
  }
  
  /**
   * 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, object [] args) throws throwable {// proxy method
    String methodName = method.getName();
    If (close. Equals (methodname)) {// the behavior of closing the connection is changed to putting it back into the connection pool
      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(); //  Check the connection before using it
      }
      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() {// verify whether the connection is valid
    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 pooleddatasource class:

//A probe query sent to the database to verify that the connection is working properly and ready to accept the request.
protected String poolPingQuery = "NO PING QUERY SET";  
//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 is false.
protected boolean poolPingEnabled;     
//Configure the frequency of poolpingquery. It can be set as the database connection timeout to avoid unnecessary detection. The default value is 0 (that is, all connections are detected at every moment - of course, it is only applicable when poolpingenabled is 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 was not closed.
  2. Configure poolpingenabled to true in mybatis XML.
  3. The time since the last use of the connection 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) {// verify whether the connection is valid
    boolean result = true;

    try {
      result = ! conn.getRealConnection().isClosed(); //  Verify that the database connection session is closed 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 // check the connection if configured
        &&Conn.gettimeelapsedsincelastuse() > poolpingconnectionsnotusedfor) {// the time since the last connection was used is greater than the connection check frequency
      try {
        if (log.isDebugEnabled()) {
          log.debug("Testing connection " + conn.getRealHashCode() + " ...");
        }
        Connection realConn = conn.getRealConnection();
        try (Statement statement = realConn.createStatement()) {
          statement.executeQuery(poolPingQuery).close(); //  Send a simple statement to check whether the connection is valid
        }
        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()); //  Connection check failed
        try {
          conn.getRealConnection().close(); //  Try closing the connection
        } catch (Exception e2) {
          // ignore
        }
        result = false;
        if (log.isDebugEnabled()) {
          log.debug("Connection " + conn.getRealHashCode() + " is BAD: " + e.getMessage());
        }
      }
    }
    return result;
  }

The time stamp (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

Take out the database connection from the pool. The code flow is as follows:

  1. The while loop is used to take out connections from the database connection pool (this operation is called check-out). It is necessary to obtain connections at the beginning of each cyclePoolState stateObject lock.
  2. Detect the free connection set and active connection set in poolstate and obtain the connection object from them. There are several cases:
    2.1 if the free connection set is not empty, take out a connection from it.
    2.2 if the free connection set is empty and the active connection set is not full, a new connection is established by using the database driver package and packaged as a pooledconnection object (generating a dynamic agent).
    2.3 if the free connection set is empty and the active connection set is full, you need to check the oldest connection:

    2.3.1 if the connection has timed out (the check-out time of the proxy object is greater than poolmaximumcheckouttime, but the original connection may still exist), mark the proxy object pooledconnection as invalid and encapsulate the original connection as a new pooledconnection object.
     2.3.2 if the connection does not time out, the current check-out thread enters waiting.
  3. Come to this step to explain that the pooledconnection object is checked out from poolstate successfully. You need to check whether the connection is valid:
    3.1 if the connection is valid, set the relevant timestamp, store it in the active connection set, and end 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 (number of free connections + bad connections tolerance threshold), 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) {// loop check out connections
      Synchronized (state) {// every time you cycle, you need to re acquire the lock!
        If (! State. Idleconnections. Isempty()) {// if the free connections collection is not empty, the connections (all valid) will be taken from it
          // Pool has available connection
          conn = state.idleConnections.remove(0); //  Remove and return
          if (log.isDebugEnabled()) {
            log.debug("Checked out connection " + conn.getRealHashCode() + " from pool.");
          }
        } else {
          //Pool does not have available connection // if the free connection collection is empty, check the active connection collection
          If (state. Activeconnections. Size() < poolmaximumactiveconnections) {// if the active connections collection is not full, a new database connection is established
            // Can create new connection
            conn = new PooledConnection(dataSource.getConnection(), this); //  Using the database driver package, create the connection object, and then wrap it as a proxy object
            if (log.isDebugEnabled()) {
              log.debug("Created connection " + conn.getRealHashCode() + ".");
            }
          } else {
            //Cannot create new connection // if the free connection collection is empty and the active connection collection is full, you need to process expired active connections
            PooledConnection oldestActiveConnection = state.activeConnections.get(0);
            long longestCheckoutTime = oldestActiveConnection.getCheckoutTime();
            If (longestcheckouttime > poolmaximumcheckouttime) {// for the earliest connection put in the active connection collection, if its check-out time has timed out (that is, it has been out of the pool for too long)
              // Can claim overdue connection
              state.claimedOverdueConnectionCount++;
              state.accumulatedCheckoutTimeOfOverdueConnections += longestCheckoutTime;
              state.accumulatedCheckoutTime += longestCheckoutTime;
              state.activeConnections.remove(oldestActiveConnection); //  Remove from active connection collection
              if (!oldestActiveConnection.getRealConnection().getAutoCommit()) {
                try {
                  oldestActiveConnection.getRealConnection().rollback();
                } catch (SQLException e) {
                  /*
                     Just log a message for debug and continue to execute the following. // rollback fails, and nothing happens
                     statement like nothing happened.
                     Wrap the bad connection with a new pooledconnection, this will help // wrap the bad connection as a new pooledconnection object
                     To not interrupt current executing thread and give current thread a // the thread currently executing the task will not be interrupted. The thread can then fetch other valid connections from the connection pool
                     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. // at the end of this loop, the bad connection will be set as null
                   */
                  log.debug("Bad connection. Could not roll back");
                }
              }
              conn = new PooledConnection(oldestActiveConnection.getRealConnection(), this); //  It needs to be identified as a bad connection later! How to identify? Via pooledconnection #isvalid
              conn.setCreatedTimestamp(oldestActiveConnection.getCreatedTimestamp());
              conn.setLastUsedTimestamp(oldestActiveConnection.getLastUsedTimestamp());
              oldestActiveConnection.invalidate(); //  Set to invalid
              if (log.isDebugEnabled()) {
                log.debug("Claimed overdue connection " + conn.getRealHashCode() + ".");
              }
            } else {
              //Must wait // the active collection is full without timeout. You can only wait for other threads to return the active connection
              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); //  Wait until it times out or wakes up by another thread (see pooleddatasource#pushconnection). Then enter the next while loop
                state.accumulatedWaitTime += System.currentTimeMillis() - wt;
              } catch (InterruptedException e) {
                break;
              }
            }
          }
        }
        If (conn! = null) {// after getting the connection through various methods, you need to check whether the connection is valid
          // 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)); //  Set the hash value of the connection ID: URL + username + password string
            conn.setCheckoutTimestamp(System.currentTimeMillis()); //  Set the check-out time. Note that here is the timestamp taken from the database connection pool! Not the time to establish a connection with the database!
            conn.setLastUsedTimestamp(System.currentTimeMillis()); //  Set last use time
            state.activeConnections.add(conn); //  Join the active set (1. Move the original connection object from the idle set to the active set; 2. Take out the timeout connection from the active set and put it back into the active set)
            state.requestCount++;
            state.accumulatedRequestTime += System.currentTimeMillis() - t;
          }Else {// if the connection is invalid, enter the next cycle to obtain the connection again, or throw an exception
            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 the number of cycles is greater than (number of idle connections + tolerance threshold of bad connections), throw an exception and stop the cycle
              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 consumption.

org.apache.ibatis.datasource.pooled.PoolState

protected long requestCount = 0;            //  Number of requests
  protected long accumulatedRequestTime = 0;  //  Total request time
  protected long accumulatedCheckoutTime = 0; //  Total check-out time (removing connections from the pool is called check-out)
  protected long claimedOverdueConnectionCount = 0;               //  Number of connections declared expired
  protected long accumulatedCheckoutTimeOfOverdueConnections = 0; //  Total number of expired connections
  protected long accumulatedWaitTime = 0;     //  Total waiting time
  protected long hadToWaitCount = 0;          //  Number of times to wait
  protected long badConnectionCount = 0;      //  Bad connections

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

org.apache.ibatis.datasource.pooled.PooledDataSource

protected int poolMaximumActiveConnections = 10; //  Number of active (in use) connections that can exist at any time
  protected int poolMaximumIdleConnections = 5;    //  The number of idle connections that may exist at any time
  protected int poolMaximumCheckoutTime = 20000;   //  The time that connections in the pool were checked out before being forced back. Default: 20000 milliseconds (i.e. 20 seconds)
  protected int poolTimeToWait = 20000;            //  This is an underlying setting. If it takes a long time to obtain a connection, it will print a status log to the connection pool and try to obtain a connection again (to avoid continuous failure and no log printing in case of misconfiguration)
  protected int poolMaximumLocalBadConnectionTolerance = 3; //  This is a low-level setting for bad connection tolerance, which applies to every thread trying to get a connection from the cache pool. If the thread gets a bad connection, the data source allows the thread to try to get a new connection again, but the number of retries should not exceed the sum of poolmaximumidleconnections and poolmaximumlocalbadconnectiontolerance

PooledDataSource#pushConnection

Return the connection to the database connection pool.

Code flow:

  1. Gets the poolstate object lock.
  2. Remove pooledconnection from the active connection collection and check if the connection is valid.
  3. If the connection is valid, judge whether the idle collection 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, close the connection and reuse it no longer.

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 used up and set to the invalid state to avoid affecting other threads during reuse.

protected void pushConnection(PooledConnection conn) throws SQLException {

    synchronized (state) {
      state.activeConnections.remove(conn); //  Remove from active connection collection
      If (conn.isvalid()) {// verify whether the connection is valid. If it is valid, proceed to the next step
        If (state. Idleconnections. Size() < poolmaximumidleconnections & & conn.getconnectiontypecode() = = expectedconnectiontypecode) {// if the free connection collection is not full and the connection IDs are consistent (URL + username + password), you need to add the free connection collection
          state.accumulatedCheckoutTime += conn.getCheckoutTime(); //  Accumulate the total check-out time (record the total time of connections from out of the pool to in the pool) (when valid connections are taken out of the pool, the check-out time stamp will be recorded)
          if (!conn.getRealConnection().getAutoCommit()) {
            conn.getRealConnection().rollback(); //  Roll back the previous transaction to avoid affecting the next use
          }
          PooledConnection newConn = new PooledConnection(conn.getRealConnection(), this); //  Generate a new pooledconnection object for the original connection
          state.idleConnections.add(newConn); //  Join the free connection set (note that this is not to move the old pooledconnection from the active set to the free set)
          newConn.setCreatedTimestamp(conn.getCreatedTimestamp());
          newConn.setLastUsedTimestamp(conn.getLastUsedTimestamp());
          conn.invalidate(); //  Set the old pooledconnection object as invalid, because the user can get this instance directly to avoid using this instance to operate the database in the future
          if (log.isDebugEnabled()) {
            log.debug("Returned connection " + newConn.getRealHashCode() + " to pool.");
          }
          state.notifyAll(); //  Wake up the thread waiting to get the database connection. See pooleddatasource#popconnection. After being awakened, only one thread will obtain the object lock.
        }Else {// if the free connection collection is full or the connection IDs are inconsistent, close the connection
          state.accumulatedCheckoutTime += conn.getCheckoutTime();
          if (!conn.getRealConnection().getAutoCommit()) {
            conn.getRealConnection().rollback();
          }
          conn.getRealConnection().close(); //  Close the connection and no longer reuse
          if (log.isDebugEnabled()) {
            log.debug("Closed connection " + conn.getRealHashCode() + ".");
          }
          conn.invalidate();
        }
      }Else {// invalid connection, cumulative count
        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. Create pooleddatasource connection pool object when parsing mybatis-config.xml configuration file.
  2. When opening a sqlsession database session, create a jdbctransaction transaction transaction object, and use jdbctransaction to maintain access to the database connection pool.
  3. In a session, jdbctransaction will only obtain one connection from the connection pool. Within the scope of the session, the operations of reading and writing to the database are completed through the connection.
  4. When a sqlsession database session is closed, the connection is returned to the database connection pool.

Data source configuration resolution

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")); //  Instantiate transaction factory
          DataSourceFactory dsFactory = dataSourceElement(child.evalNode("dataSource")); //  Instantiate database connection pool factory
          DataSource dataSource = dsFactory.getDataSource(); //  From the database connection pool factory, get the data source object. An environment tag has only one data source!
          Environment.Builder environmentBuilder = new Environment.Builder(id)
              .transactionFactory(txFactory)
              .dataSource(dataSource);
          configuration.setEnvironment(environmentBuilder.build()); //  Register the transaction factory and data source object into the configuration object
          break;
        }
      }
    }
  }

Data source initialization

Because 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); //  Write the database connection information in the configuration file to the datasource property in the datasourcefactory
      return factory;
    }
    throw new BuilderException("Environment declaration requires a DataSourceFactory.");
  }

The pooleddatasourcefactory class is 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:

Read the source code of mybatis: pooleddatasource of database connection pool

Open session

When a 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); //  Instantiate the transaction transaction object through the transaction factory
      final Executor executor = configuration.newExecutor(tx, execType); //  Instantiate the executor executor object, execute SQL through it, and support plug-in extension
      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();
    }
  }

Because the configuration is<transactionManager type="JDBC">, what you get here is the jdbctransactionfactory, so create the jdbctransaction transaction object.

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

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

Official description of transaction manager:

There are two types of transaction managers in mybatis (that is, type = “[jdbc|managed]):

  • JDBC – this configuration directly uses JDBC’s commit and rollback facilities, which rely on connections obtained from data sources to manage transaction scopes.
  • Managed – this configuration does little. 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 the 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.

Connection acquisition

When executing an SQL query, the database connection object connection is obtained from the pooleddatasource of the database connection pool.

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(); //  From the transaction object, get the connection object
    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) {// get connections from the connection pool only when it is empty
      openConnection();
    }
    return connection;
  }

In fact, the connection is obtained from the data source object pooleddatasource. What you get 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();
  }

Connection return

When you close a 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, object [] args) throws throwable {// proxy method
    String methodName = method.getName();
    If (close. Equals (methodname)) {// the behavior of closing the connection is changed to putting it back into the connection pool
      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(); //  Check the connection before using it
      }
      return method.invoke(realConnection, args);
    } catch (Throwable t) {
      throw ExceptionUtil.unwrapThrowable(t);
    }

  }

3. Test cases

3.1 verify the validity of the connection after closing the session

After closing sqlsession, the database connection object appears as “expired”.
In fact, at this time, only the proxy object of the database connection is invalid, 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 {
    //Establish session
    SqlSession sqlSession = sqlSessionFactory.openSession();
    Connection connection = sqlSession.getConnection();

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

    //Close session
    sqlSession.close();

    //Assert invalid connection
    connection.isValid(1000);
}

The 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 [[email protected]]
valid = true
2021-08-24 22:45:53,293 [main] DEBUG [org.apache.ibatis.transaction.jdbc.JdbcTransaction] - Resetting autocommit to true on JDBC Connection [[email protected]]
2021-08-24 22:45:53,294 [main] DEBUG [org.apache.ibatis.transaction.jdbc.JdbcTransaction] - Closing JDBC Connection [[email protected]]
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 of connection effectiveness before use

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

  1. Check out the database connection from the connection pool. 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 made invalid 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 cases are as follows:

Modify the mybatis-config.xml configuration topoolPingQueryModify it to the wrong statement to simulate the failure of checking the original connection Ping to the database.

<environment id="development">
    <transactionManager type="JDBC"/>
    <!--  The implementation of pooled data source uses the concept of "pool" to organize JDBC connection objects, avoiding the necessary initialization and authentication time when creating new connection instances. -- >
    <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();
}

Unexpectedly, there are two different results when executing multiple times:

Case 1: check out connection failures from the database connection pool for many times (Ping fails every time, 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.

Case 2: the first time a connection is obtained from the connection pool, the Ping fails, and it is discarded as a bad connection. Re establish a new connection, and then verify whether the new connection is valid. Note that the Ping check for the new connection passed here, 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 [[email protected]]
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:18640 [main] trace [com. Sumkor. Mapper. Studentmapper. Selectbyprimarykey] - < = = row: 2, Daming, 13821378271, [email protected] , 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 = 'Daming'}
2021-08-25 00:13:18,643 [main] DEBUG [org.apache.ibatis.transaction.jdbc.JdbcTransaction] - Resetting autocommit to true on JDBC Connection [[email protected]]
2021-08-25 00:13:18,644 [main] DEBUG [org.apache.ibatis.transaction.jdbc.JdbcTransaction] - Closing JDBC Connection [[email protected]]
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 distinct results?

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

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

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

amongconn.getTimeElapsedSinceLastUse() > poolPingConnectionsNotUsedForIt limits that the Ping check can only be sent when the time from 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 time the new connection was usedlastUsedTimestampIs the creation time of the connection.

As long as the creation time of the connection occurs within the same millisecond as the check time,System.currentTimeMillis() - lastUsedTimestampThe calculation result of is 0, soconn.getTimeElapsedSinceLastUse() > poolPingConnectionsNotUsedForGet false, so that the Ping check will not be sent to the database.

Since the creation time and the inspection time may or may not occur in the same millisecond (depending on the machine performance and whether a breakpoint is set), two different execution results will occur.

Generally speaking, as long as the correct Ping statement is configured, there is no need to check the connection within the same millisecond after establishing a connection to the database.

3.3 detection timeout verification

Set the active connection collection size of the database connection pool to 1, and only one thread is allowed to use the database connection at a time.
Set the maximum check-out time of database connection to 1 second. If the connection is taken out from the connection pool for more than 1 second and is not returned, it is considered that the check-out has timed out.

Open two threads, obtain the connection successively, and observe the results.

/**
 *Verification checkout timeout
 */
@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); //  The capacity of the active connection collection is 1
    pooledDataSource.setPoolMaximumCheckoutTime(1000);   //  The maximum detection time is 1 second

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

    //Thread one, check it out and don't return it for a long time
    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);

                //After hibernating for 5 seconds, thread 2 is allowed to obtain the connection
                Thread.sleep(5000);
                startLatch.countDown();

                //Continue to sleep for 1 second, and then obtain the connection. It is found that thread 2 is set as invalid
                Thread.sleep(1000);
                sqlSession.getConnection().isValid(1000);

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

    //Thread 2: check out thread 1 after a period of time
    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);
                //At this time, if the free connection set is empty and the active connection set is full, you need to interpret the connections in the active connection set and check out timeout:
                //1. Time out and void the connection;
                //2. No timeout, waiting for release
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (sqlSession != null) {
                    sqlSession.close();
                }
                endLatch.countDown();
            }
        }
    }, "thread_02");

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

    endLatch.await();
}

You can see:

  1. thread_ 02 in thread_ 01 after checking out the database connection for a period of time, get the connection from the connection pool. It is found that the only connection in the active connection collection has timed out.
  2. Therefore, thread_ 02 will void the current pooledconnection, encapsulate the original connectionimpl as a new pooledconnection for use, and complete the database query.
  3. After that, thread_ 01 wants to continue the operation of the original pooledconnection and finds that it has been thread_ 02 is set to invalid, and then an exception is thrownConnection is invalid

The 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 [[email protected]]
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:32401 [thread_01] trace [com. Sumkor. Mapper. Studentmapper. Selectbyprimarykey] - < = = row: 1, Xiao Ming, 13821378270, [email protected] , 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 = 'Xiaoming'}
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:37416 [thread_02] trace [com. Sumkor. Mapper. Studentmapper. Selectbyprimarykey] - < = = row: 2, Daming, 13821378271, [email protected] , 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 = 'Daming'}
2021-08-24 22:48:37,418 [thread_02] DEBUG [org.apache.ibatis.transaction.jdbc.JdbcTransaction] - Resetting autocommit to true on JDBC Connection [[email protected]]
2021-08-24 22:48:37,418 [thread_02] DEBUG [org.apache.ibatis.transaction.jdbc.JdbcTransaction] - Closing JDBC Connection [com.mysql.cj.j[email protected]]
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 [[email protected]]
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 object, and the pooledconnection object is stored in the connection pool.
  2. Pooledconnection object will generate a dynamic proxy for the original connection object, such as com.mysql.cj.jdbc.connectionimpl.
  3. Each connection obtained from the connection pool is actually a proxy object. After the proxy object returns to the connection pool, a new proxy object will be generated for the original connection object for next use. The old proxy object will be set to invalid and can no longer be used.
  4. In this way, the life cycle of the connection object of the agent is limited to the sqlsession range to ensure that there will be no problem of multiple threads operating the same database connection after the session is closed. The original connection object can be reused many times in the connection pool to avoid repeatedly establishing a connection to the database.
  5. When obtaining and returning threads from the connection pool, you need to obtain the synchronized lock, so as to achieve thread safety.

Author: sumkor
Link: https://segmentfault.com/a/11…