应用程序数据库连接超时时间解析

jimmy 2019年02月28日 1,523次浏览

应用程序数据库连接超时时间解析

介绍

应用程序如果需要连接数据库,会牵扯到很多超时时间概念。对数据库连接进行合理的超时时间设置能够避免一些奇奇怪怪的问题。
超时时间主要包含获取连接connection的超时时间,事务transaction的超时时间,statement的超时时间,socket的超时时间。

connection的超时时间

首先要理解什么是数据库的connection,数据库的一个connection就是一个tcp连接,那么connection超时时间就是说建立tcp握手需要的时间,是客户端(也就是我们自己的应用程序)和数据库服务器直接建立tcp连接的时间。我们以HikariCP数据源,mysql的jdbc驱动为例来查看一下如何设置connection timeout, 这个timeout是如何生效的。

public HikariDataSource dataSource() {
        Properties props = new Properties();
        props.setProperty("jdbcUrl", "jdbc:mysql://localhost:13306/test?characterEncoding=utf8&useSSL=false");
        props.setProperty("username", "root");
        props.setProperty("password", "password");
        HikariConfig config = new HikariConfig(props);
        config.setConnectionTimeout(17000);

        HikariDataSource ds = new HikariDataSource(config);
        return ds;
    }

上面是建立HikariCP数据源的代码,其中有一条 config.setConnectionTimeout(17000) 是设置数据库获取连接的超时时间是1.7s。由于数据源会缓存connection,对于数据来说这个connection timeout时间是应用从数据源中获取一个connection最大的等待时间,超过这个时间以后,应用的线程将不会等待,直接报错,无可用连接。

这个是对数连接池来说的,数据连接池会的链接怎么来?数据连接池也会调用java原生的驱动程序去获取连接,那么这个connection timeout指数据库连接池和数据库服务器建立tcp连接最大等待时间,如果我们没有用数据库连接池,那么这个时间就是应用和数据库服务器建立tcp连接所最大的等待时间。

再看一下,数据库连接池是如何把这个时间传递给jdbc驱动程序的,以mysql为例。
定位到HikariCP的HikariPool,看如下代码,应用获取connection的时候,会默认使用我们在配置连接池时设置的connectionTimeout。

  public Connection getConnection() throws SQLException
   {
      return getConnection(connectionTimeout);
   }

   public Connection getConnection(final long hardTimeout) throws SQLException
   {
      suspendResumeLock.acquire();
      final long startTime = currentTime();

      try {
         long timeout = hardTimeout;
         do {
            PoolEntry poolEntry = connectionBag.borrow(timeout, MILLISECONDS);
            if (poolEntry == null) {
               break; // We timed out... break and throw exception
            }

            final long now = currentTime();
            if (poolEntry.isMarkedEvicted() || (elapsedMillis(poolEntry.lastAccessed, now) > aliveBypassWindowMs && !isConnectionAlive(poolEntry.connection))) {
               closeConnection(poolEntry, poolEntry.isMarkedEvicted() ? EVICTED_CONNECTION_MESSAGE : DEAD_CONNECTION_MESSAGE);
               timeout = hardTimeout - elapsedMillis(startTime);
            }
            else {
               metricsTracker.recordBorrowStats(poolEntry, startTime);
               return poolEntry.createProxyConnection(leakTaskFactory.schedule(poolEntry), now);
            }
         } while (timeout > 0L);

         metricsTracker.recordBorrowTimeoutStats(startTime);
         throw createTimeoutException(startTime);
      }
      catch (InterruptedException e) {
         Thread.currentThread().interrupt();
         throw new SQLException(poolName + " - Interrupted during connection acquisition", e);
      }
      finally {
         suspendResumeLock.release();
      }
   }

如果超过这个时间就会抛出throw new SQLException(poolName + " - Interrupted during connection acquisition", e);异常。

当HikariCP需要新建连接的时候在DriverDataSource中会调用getConnection方法。

@Override
   public Connection getConnection(final String username, final String password) throws SQLException
   {
      final Properties cloned = (Properties) driverProperties.clone();
      if (username != null) {
         cloned.put("user", username);
         if (cloned.containsKey("username")) {
            cloned.put("username", username);
         }
      }
      if (password != null) {
         cloned.put("password", password);
      }

      return driver.connect(jdbcUrl, cloned);
   }

这个方法会调用数据库原生的驱动获取连接。比如mysql驱动MysqlDataSource中的

protected java.sql.Connection getConnection(Properties props) throws SQLException {
        String jdbcUrlToUse = null;

        if (!this.explicitUrl) {
            jdbcUrlToUse = getUrl();
        } else {
            jdbcUrlToUse = this.url;
        }

        //
        // URL should take precedence over properties
        //
        ConnectionUrl connUrl = ConnectionUrl.getConnectionUrlInstance(jdbcUrlToUse, null);
        if (connUrl.getType() == null) {
            throw SQLError.createSQLException(Messages.getString("MysqlDataSource.BadUrl", new Object[] { jdbcUrlToUse }),
                    SQLError.SQL_STATE_CONNECTION_FAILURE, null);
        }
        Properties urlProps = connUrl.getConnectionArgumentsAsProperties();
        urlProps.remove(PropertyDefinitions.DBNAME_PROPERTY_KEY);
        urlProps.remove(PropertyDefinitions.HOST_PROPERTY_KEY);
        urlProps.remove(PropertyDefinitions.PORT_PROPERTY_KEY);
        urlProps.stringPropertyNames().stream().forEach(k -> props.setProperty(k, urlProps.getProperty(k)));

        return mysqlDriver.connect(jdbcUrlToUse, props);
    }

后面会调用到MysqlaSocketConnection的

@Override
    public void connect(String hostName, int portNumber, Properties props, PropertySet propSet, ExceptionInterceptor excInterceptor, Log log,
            int loginTimeout) {

        // TODO we don\'t need both Properties and PropertySet in method params

        try {
            this.port = portNumber;
            this.host = hostName;
            this.propertySet = propSet;
            this.exceptionInterceptor = excInterceptor;

            this.socketFactory = createSocketFactory(propSet.getStringReadableProperty(PropertyDefinitions.PNAME_socketFactory).getStringValue());
            this.mysqlSocket = this.socketFactory.connect(this.host, this.port, props, loginTimeout);

            int socketTimeout = propSet.getIntegerReadableProperty(PropertyDefinitions.PNAME_socketTimeout).getValue();
            if (socketTimeout != 0) {
                try {
                    this.mysqlSocket.setSoTimeout(socketTimeout);
                } catch (Exception ex) {
                    /* Ignore if the platform does not support it */
                }
            }

            this.mysqlSocket = this.socketFactory.beforeHandshake();

            InputStream rawInputStream;
            if (propSet.getBooleanReadableProperty(PropertyDefinitions.PNAME_useReadAheadInput).getValue()) {
                rawInputStream = new ReadAheadInputStream(this.mysqlSocket.getInputStream(), 16384,
                        propSet.getBooleanReadableProperty(PropertyDefinitions.PNAME_traceProtocol).getValue(), log);
            } else if (propSet.getBooleanReadableProperty(PropertyDefinitions.PNAME_useUnbufferedInput).getValue()) {
                rawInputStream = this.mysqlSocket.getInputStream();
            } else {
                rawInputStream = new BufferedInputStream(this.mysqlSocket.getInputStream(), 16384);
            }

            this.mysqlInput = new FullReadInputStream(rawInputStream);
            this.mysqlOutput = new BufferedOutputStream(this.mysqlSocket.getOutputStream(), 16384);
        } catch (IOException ioEx) {
            throw ExceptionFactory.createCommunicationsException(propSet, null, 0, 0, ioEx, getExceptionInterceptor());
        }
    }

通过this.mysqlSocket = this.socketFactory.connect(this.host, this.port, props, loginTimeout);继续走到 StandardSocketFactory(StandardSSLSocketFactory)中

public Socket connect(String hostname, int portNumber, Properties props, int loginTimeout) throws SocketException, IOException {

        this.loginTimeoutCountdown = loginTimeout;

        if (props != null) {
            this.host = hostname;

            this.port = portNumber;

            String localSocketHostname = props.getProperty(PropertyDefinitions.PNAME_localSocketAddress);
            InetSocketAddress localSockAddr = null;
            if (localSocketHostname != null && localSocketHostname.length() > 0) {
                localSockAddr = new InetSocketAddress(InetAddress.getByName(localSocketHostname), 0);
            }

            String connectTimeoutStr = props.getProperty(PropertyDefinitions.PNAME_connectTimeout);

            int connectTimeout = 0;

            if (connectTimeoutStr != null) {
                try {
                    connectTimeout = Integer.parseInt(connectTimeoutStr);
                } catch (NumberFormatException nfe) {
                    throw new SocketException("Illegal value \'" + connectTimeoutStr + "\' for connectTimeout");
                }
            }

            if (this.host != null) {
                InetAddress[] possibleAddresses = InetAddress.getAllByName(this.host);

                if (possibleAddresses.length == 0) {
                    throw new SocketException("No addresses for host");
                }

                // save last exception to propagate to caller if connection fails
                SocketException lastException = null;

                // Need to loop through all possible addresses. Name lookup may return multiple addresses including IPv4 and IPv6 addresses. Some versions of
                // MySQL don\'t listen on the IPv6 address so we try all addresses.
                for (int i = 0; i < possibleAddresses.length; i++) {
                    try {
                        this.rawSocket = createSocket(props);

                        configureSocket(this.rawSocket, props);

                        InetSocketAddress sockAddr = new InetSocketAddress(possibleAddresses[i], this.port);
                        // bind to the local port if not using the ephemeral port
                        if (localSockAddr != null) {
                            this.rawSocket.bind(localSockAddr);
                        }

                        this.rawSocket.connect(sockAddr, getRealTimeout(connectTimeout));

                        break;
                    } catch (SocketException ex) {
                        lastException = ex;
                        resetLoginTimeCountdown();
                        this.rawSocket = null;
                    }
                }

                if (this.rawSocket == null && lastException != null) {
                    throw lastException;
                }

                resetLoginTimeCountdown();

                return this.rawSocket;
            }
        }

        throw new SocketException("Unable to create socket");
    }

在 this.rawSocket.connect(sockAddr, getRealTimeout(connectTimeout)); 这里通过java原生的Socket的connect方法,第二个参数传递出去来,设置connect的超时时间。

事务transaction的超时时间

事务的超时时间是应用来控制的,和具体的数据库没有关系,数据库没有事务超时时间这个参数,这里以经常使用的spring为例子来分析下事务的超时时间是什么。

事务时间是从事务开始,到事务提交之间的时间,spring的事务超时时间配置是通过注解 @Transactional(timeout=10)来设置,也可在通过xml里面的TransactionManage来指定,单位是s。

一个transaction可以包含多次数据库操作,也就是会有多个statement,那这个事务的执行时间就是所有statement的执行时间之和,同一个statement也可能和数据库交互多次,比如批量获取ResultSet的操作。spring实现事务的超时机制比较特殊是通过Statement的超时机制来实现的,Statement的超时机制是有jdbc驱动来实现的。下面以一个简单的例子来说明spring的事务超时时间控制机制。

@Transactional(timeout = 15,propagation = Propagation.REQUIRED)
    public void getUsers(){
        jdbcTemplate.update("update  users set name=\'xx\' where id=\'1\' ");
        try {
            log.info("sleep 5");
            Thread.sleep(5000);
        } catch (InterruptedException e) {
            e.printStackTrace();
        }
        jdbcTemplate.update("update  users set name=\'yy\' where id=\'2\' ");
    }

这里我们写来一个测试方法,service层更新用户信息操作,在执行id='1'的update操作的时候,会取得事务的超时时间,并设置到statement上面,这里会设置为15s(忽略了其他耗时)在执行第二个update的时候,会计算前面已经使用过的时间,这里是 10s-5s(忽略了其他耗时),那么第二个update执行的时候会将statement超时时间设置为10s。

在jdbcTemplate的setQueryTimeout有一段说明如下,也可以看出transaction超时时间存在后jdbcTemplate的超时时间无效。
Any timeout specified here will be overridden by the remaining transaction timeout when executing within a transaction that has a timeout specified at the transaction level.

spring jpa可以通过如下方式来设置事务的超时时间

<bean id="txManager" class="org.springframework.orm.jpa.JpaTransactionManager">
    <property name="entityManagerFactory" ref="xxxx" />
    <property name="dataSource" ref="dataSource" />
    <property name="defaultTimeout" value="60" />
</bean>

Statement的超时时间

statement的超时时间通过satement.setQueryTimeout方法来设置,如果我们使用spring的jdbctemplate也可以通过jdbcTemplate.setQueryTimeout(10);
来设置,其最终通过DataSourceUtils的applyTimeout方法设置到来statement上面。

public static void applyTimeout(Statement stmt, @Nullable DataSource dataSource, int timeout) throws SQLException {
		Assert.notNull(stmt, "No Statement specified");
		ConnectionHolder holder = null;
		if (dataSource != null) {
			holder = (ConnectionHolder) TransactionSynchronizationManager.getResource(dataSource);
		}
		if (holder != null && holder.hasTimeout()) {
			// Remaining transaction timeout overrides specified value.
			stmt.setQueryTimeout(holder.getTimeToLiveInSeconds());
		}
		else if (timeout >= 0) {
			// No current transaction timeout -> apply specified value.
			stmt.setQueryTimeout(timeout);
		}
	}

有一点要注意的是如果transaction和statement的timeout都设置来,会以transaction计算的时间为准。比如下面代码最终的超时时间是15s而不是10s。

@Transactional(timeout = 15,propagation = Propagation.REQUIRED)
    public void getUsers(){
        jdbcTemplate.setQueryTimeout(10);
        jdbcTemplate.update("update  users set name=\'xx\' where id=\'1\' ");
    }

如果statement超时来会看到如下错误

com.mysql.cj.jdbc.exceptions.MySQLTimeoutException: Statement cancelled due to timeout or client request
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:106) ~[mysql-connector-java-6.0.6.jar:6.0.6]
	at com.mysql.cj.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:1983) ~[mysql-connector-java-6.0.6.jar:6.0.6]
	at com.mysql.cj.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:1936) ~[mysql-connector-java-6.0.6.jar:6.0.6]
	at com.mysql.cj.jdbc.StatementImpl.executeQuery(StatementImpl.java:1422) ~[mysql-connector-java-6.0.6.jar:6.0.6]
	at com.zaxxer.hikari.pool.ProxyStatement.executeQuery(ProxyStatement.java:111) ~[HikariCP-3.3.1.jar:na]
	at com.zaxxer.hikari.pool.HikariProxyStatement.executeQuery(HikariProxyStatement.java) [HikariCP-3.3.1.jar:na]

如果你使用的是jpa可以通过 javax.persistence.query.timeout 设置queryTimeout

    <bean id="entityManagerFactoryCur"
          class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
        <property name="dataSource" ref="dataSourceCurOrder"/>
        <property name="jpaVendorAdapter" ref="hibernateJpaVendorAdapter"/>
        <property name="packagesToScan" value="com.xxx.db.cur.entity"/>
        <property name="jpaProperties">
            <props>
                <!-- 命名规则 My_NAME->MyName -->
                <prop key="hibernate.physical_naming_strategy">org.springframework.boot.orm.jpa.hibernate.SpringPhysicalNamingStrategy</prop>
                <prop key="hibernate.ejb.entitymanager_factory_name">online_order</prop>
                <prop key="javax.persistence.query.timeout">9</prop>
            </props>
        </property>
    </bean>

如果通过JdbcTemplate可通过queryTimeout来设置statement超时时间

<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
    <property name="dataSource" ref="dataSource"></property>
    <property name="queryTimeout" value="6"></property>
</bean>

mysql jdbc驱动 通过 com.mysql.jdbc.StatementImpl 里面的CancelTask 新建一个connection 来向mysql服务器发送cancel statement 的请求,cancelStmt.execute("KILL QUERY " + CancelTask.this.connectionId);

socket timeout

socket是tcp层的概念,所以这个超时时间最终会从过数据源传递到jdbc驱动程序,再传递到Socket套接字,并设置到socket上面,在jdbc的驱动MysqlaSocketConnection中

@Override
    public void connect(String hostName, int portNumber, Properties props, PropertySet propSet, ExceptionInterceptor excInterceptor, Log log,
            int loginTimeout) {

        // TODO we don\'t need both Properties and PropertySet in method params

        try {
            this.port = portNumber;
            this.host = hostName;
            this.propertySet = propSet;
            this.exceptionInterceptor = excInterceptor;

            this.socketFactory = createSocketFactory(propSet.getStringReadableProperty(PropertyDefinitions.PNAME_socketFactory).getStringValue());
            this.mysqlSocket = this.socketFactory.connect(this.host, this.port, props, loginTimeout);

            int socketTimeout = propSet.getIntegerReadableProperty(PropertyDefinitions.PNAME_socketTimeout).getValue();
            if (socketTimeout != 0) {
                try {
                    this.mysqlSocket.setSoTimeout(socketTimeout);
                } catch (Exception ex) {
                    /* Ignore if the platform does not support it */
                }
            }

            this.mysqlSocket = this.socketFactory.beforeHandshake();

            InputStream rawInputStream;
            if (propSet.getBooleanReadableProperty(PropertyDefinitions.PNAME_useReadAheadInput).getValue()) {
                rawInputStream = new ReadAheadInputStream(this.mysqlSocket.getInputStream(), 16384,
                        propSet.getBooleanReadableProperty(PropertyDefinitions.PNAME_traceProtocol).getValue(), log);
            } else if (propSet.getBooleanReadableProperty(PropertyDefinitions.PNAME_useUnbufferedInput).getValue()) {
                rawInputStream = this.mysqlSocket.getInputStream();
            } else {
                rawInputStream = new BufferedInputStream(this.mysqlSocket.getInputStream(), 16384);
            }

            this.mysqlInput = new FullReadInputStream(rawInputStream);
            this.mysqlOutput = new BufferedOutputStream(this.mysqlSocket.getOutputStream(), 16384);
        } catch (IOException ioEx) {
            throw ExceptionFactory.createCommunicationsException(propSet, null, 0, 0, ioEx, getExceptionInterceptor());
        }
    }

通过this.mysqlSocket.setSoTimeout(socketTimeout);设置。 这个时间是读取socket没有读取到数据的等待时间,也就说如果客户端在timeout时间内还没有收到服务器端响应的数据,就会打印Read timed out错误。mysql超时的详细错误如下所示。

com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure
The last packet successfully received from the server was 8,022 milliseconds ago.  The last packet sent successfully to the server was 3,005 milliseconds ago.
	at com.mysql.cj.jdbc.exceptions.SQLError.createCommunicationsException(SQLError.java:590) ~[mysql-connector-java-6.0.6.jar:6.0.6]
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:57) ~[mysql-connector-java-6.0.6.jar:6.0.6]
	at com.mysql.cj.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:1983) ~[mysql-connector-java-6.0.6.jar:6.0.6]
	at com.mysql.cj.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:1936) ~[mysql-connector-java-6.0.6.jar:6.0.6]
	at com.mysql.cj.jdbc.StatementImpl.executeQuery(StatementImpl.java:1422) ~[mysql-connector-java-6.0.6.jar:6.0.6]
	at com.zaxxer.hikari.pool.ProxyStatement.executeQuery(ProxyStatement.java:111) ~[HikariCP-3.3.1.jar:na]
	at com.zaxxer.hikari.pool.HikariProxyStatement.executeQuery(HikariProxyStatement.java) [HikariCP-3.3.1.jar:na]

Caused by: java.net.SocketTimeoutException: Read timed out
	at java.net.SocketInputStream.socketRead0(Native Method) ~[na:1.8.0_144]
	at java.net.SocketInputStream.socketRead(SocketInputStream.java:116) ~[na:1.8.0_144]
	at java.net.SocketInputStream.read(SocketInputStream.java:171) ~[na:1.8.0_144]
	at java.net.SocketInputStream.read(SocketInputStream.java:141) ~[na:1.8.0_144]
	at com.mysql.cj.core.io.ReadAheadInputStream.fill(ReadAheadInputStream.java:101) ~[mysql-connector-java-6.0.6.jar:6.0.6]
	at com.mysql.cj.core.io.ReadAheadInputStream.readFromUnderlyingStreamIfNecessary(ReadAheadInputStream.java:144) ~[mysql-connector-java-6.0.6.jar:6.0.6]
	at com.mysql.cj.core.io.ReadAheadInputStream.read(ReadAheadInputStream.java:174) ~[mysql-connector-java-6.0.6.jar:6.0.6]
	at java.io.FilterInputStream.read(FilterInputStream.java:133) ~[na:1.8.0_144]
	at com.mysql.cj.core.io.FullReadInputStream.readFully(FullReadInputStream.java:58) ~[mysql-connector-java-6.0.6.jar:6.0.6]
	at com.mysql.cj.mysqla.io.SimplePacketReader.readHeader(SimplePacketReader.java:60) ~[mysql-connector-java-6.0.6.jar:6.0.6]
	at com.mysql.cj.mysqla.io.TimeTrackingPacketReader.readHeader(TimeTrackingPacketReader.java:48) ~[mysql-connector-java-6.0.6.jar:6.0.6]
	at com.mysql.cj.mysqla.io.MultiPacketReader.readHeader(MultiPacketReader.java:51) ~[mysql-connector-java-6.0.6.jar:6.0.6]
	at com.mysql.cj.mysqla.io.MysqlaProtocol.readPacket(MysqlaProtocol.java:521) ~[mysql-connector-java-6.0.6.jar:6.0.6]

有关SO_KEEPALIVE的三个参数详细解释如下:
tcp_keepalive_intvl,保活探测消息的发送频率。默认值为75s。发送频率tcp_keepalive_intvl乘以发送次数tcp_keepalive_probes,就得到了从开始探测直到放弃探测确定连接断开的时间,大约为11min。
tcp_keepalive_probes,TCP发送保活探测消息以确定连接是否已断开的次数。默认值为9(次)。
tcp_keepalive_time,在TCP保活打开的情况下,最后一次数据交换到TCP发送第一个保活探测消息的时间,即允许的持续空闲时间。默认值为7200s(2h)

HakariCP设置数据库参数事例

public HikariDataSource dataSource() {
        Properties props = new Properties();
        props.setProperty("jdbcUrl", "jdbc:mysql://localhost:13306/test?characterEncoding=utf8&useSSL=false");
        props.setProperty("username", "root");
        props.setProperty("password", "password");

        HikariConfig config = new HikariConfig(props);
        //设置connection的最大连接数,就是tcp的连接数
        config.setMaximumPoolSize(10);
        //设置连接时最大等待时间为1.7s
        config.setConnectionTimeout(17000);
        config.setPoolName("poll-online");

        Properties properties = new Properties();
        //设置socketTimeout超时时间为3s
        properties.setProperty("socketTimeout","3000");
        //设置tcpkeepalive为true,这个心跳发送时间是2个小时,基本没用,修改只能通过修改系统参数来设置。一般都是应用层自己实现心跳逻辑。
        properties.setProperty("tcpKeepAlive","true");
        config.setDataSourceProperties(properties);
        HikariDataSource ds = new HikariDataSource(config);

        return ds;
    }

有些参数记不住可以到com.mysql.cj.core.conf.PropertyDefinitions这个类里面搜一下,里面有常量定义。