深入理解数据库编程中的超时设置

　　数据库是开发过程中最常用的组件，然而我们经常会遇到各种各样的超时异常，如：
　　connecttimeout：建立数据库连接超时
　　sockettimeout：socket读取超时
　　statementtimeout：单个sql执行超时
　　transactiontimeout：事务执行超时，一个事务中可能包含多个sql
　　getconnectiontimeout：从连接池中获取链接超时
　　读完此文，你将彻底掌握各种超时产生的根本原因，以及对应的解决方案。
　　1connectTimeout与socketTimeout
　　connecttimeout和sockettimeout都属于TCP层面的超时。
　　以mysql为例，我们可以在jdbcurl中指定connectTimeout和socketTimeout。如：jdbc：mysql：localhost：3306db？connectTimeout1000socketTimeout60000
　　其中：
　　connectTimeout：表示的是数据库驱动（mysqlconnectorjava）与mysql服务器建立TCP连接的超时时间。
　　socketTimeout：是通过TCP连接发送数据（在这里就是要执行的sql）后，等待响应的超时时间。
　　mysql驱动（mysqlconnectorjava）在与服务端建立Socket连接时，会将这两个参数设置到socket对象上参见：
　　com。mysql。jdbc。MysqlIO类的构造方法：
　　提示：这里的mysqlConnection类型为java。net。Socket
　　如果这两个参数设置的不够合理，都会导致mysql驱动抛出以下异常：com。mysql。jdbc。exceptions。jdbc4。CommunicationsException：Communicationslinkfailure
　　相信大部分读者对这个异常都不陌生。接下来笔者将分别演示这两个异常是如何产生的，并提出对应的解决方案。
　　1。1connectTimeout
　　下面首先通过一个案例演示如何模拟connectTimeoutTestpublicvoidtestConnectTimeoutthrowsSQLException｛DruidDataSourcedataSourcenewDruidDataSource；dataSource。setInitialSize（5）；dataSource。setUrl（jdbc：mysql：127。0。0。1：3306test？connectTimeout5）；dataSource。setUsername（root）；dataSource。setPassword（yourpassword）；dataSource。setDriverClassName（com。mysql。jdbc。Driver）；dataSource。init；初始化，底层通过mysqlconnectorjava建立数据库连接｝
　　笔者这里将connectTimeout设置为了5ms，表示mysql驱动与服务端建立一个连接最多不能超过5ms。由于这里是与本地（127。0。0。1）数据库建立一个连接，5ms已经足够。然而，如果你是与一个远程数据库建立连接，那么5ms可能无法完成建立一个连接，此时你极有可能会遇到类似以下异常：com。mysql。jdbc。exceptions。jdbc4。CommunicationsException：CommunicationslinkfailureThelastpacketsentsuccessfullytotheserverwas0millisecondsago。Thedriverhasnotreceivedanypacketsfromtheserver。atsun。reflect。NativeConstructorAccessorImpl。newInstance0（NativeMethod）atsun。reflect。NativeConstructorAccessorImpl。newInstance（NativeConstructorAccessorImpl。java：62）。。。Causedby：java。net。SocketTimeoutException：connecttimedoutatjava。net。PlainSocketImpl。socketConnect（NativeMethod）atjava。net。AbstractPlainSocketImpl。doConnect（AbstractPlainSocketImpl。java：350）。。。
　　到这里，我们看到了：
　　CommunicationsException异常，异常的Causedby部分是java。net。SocketTimeoutException：connecttimedout
　　也就是说，建立底层socket连接超时了。这通常意味着我们需要将connectTimeout值调大。
　　这个问题并非无关紧要，特别是在公司有多个数据中心的情况下，尤其需要注意。笔者曾经遇到过有业务开发同学，应用部署在北京，数据库集群在北京和上海都有部署，如下图：
　　上海和北京的一个RTT大概在20ms，而业务同学将connectTimeout设置为10ms。这就是导致，应用与北京的主库建立连接可以成功，但是与上海的从库建立连接总是经常失败，显然问题的解决方案，就是调大connectTimeout的值。需要注意的是，通常建议connectTimeout设置的值是需要大于RTT的，如果设置的刚刚好，很容易因为网络拥堵或者抖动导致出现相同的异常。
　　最后，connectTimeout的默认值为0，驱动层面不设置超时时间，但这并不意味着不会超时。此时将由操作系统来决定超时时间。一些内核参数，如net。ipv4。tcpsynretries可以影响connectTimeout，这里不做深入介绍。
　　1。2socketTimeout
　　sockettimeout是我们实际开发中最容易遇到的另外一个导致CommunicationsException异常的原因，通常是在sql的执行时间超过了sockettimeout设置的情况下出现。例如sockettimeout设置的是3s，但是sql执行确需要5s，那么将会出现异常。
　　sockettimeout异常演示：TestpublicvoidtestSocketTimeoutthrowsSQLException｛org。apache。tomcat。jdbc。pool。DataSourcedatasourceneworg。apache。tomcat。jdbc。pool。DataSource；设置socketTimeout3000，单位是msdatasource。setUrl（jdbc：mysql：localhost：3306test？socketTimeout3000）；
　　datasource。setUsername（root）；datasource。setDriverClassName（com。mysql。jdbc。Driver）；datasource。setPassword（yourpassword）；Connectionconnectiondatasource。getConnection；PreparedStatementpsconnection。prepareStatement（selectsleep（5））；ps。executeQuery；｝
　　在这个案例中，我们模拟了一个慢查询，通过执行selectsleep（5），sleep是mysql提供的函数，其接受一个休眠时间，单位是s，当我们把这个sql发送给mysql时，mysql服务端会休眠5秒后，再返回结果。
　　然而，由于我们在jdbcurl中设置了socketTimeout3000，意味着单条sql最大执行时间不能超过3s。因此运行以上案例，将会抛出类似以下异常：com。mysql。jdbc。exceptions。jdbc4。CommunicationsException：CommunicationslinkfailureThelastpacketsuccessfullyreceivedfromtheserverwas3，080millisecondsago。Thelastpacketsentsuccessfullytotheserverwas3，005millisecondsago。atsun。reflect。NativeConstructorAccessorImpl。newInstance0（NativeMethod）atsun。reflect。NativeConstructorAccessorImpl。newInstance（NativeConstructorAccessorImpl。java：62）atsun。reflect。DelegatingConstructorAccessorImpl。newInstance（DelegatingConstructorAccessorImpl。java：45）atjava。lang。reflect。Constructor。newInstance（Constructor。java：423）。。。
　　Causedby：java。net。SocketTimeoutException：Readtimedoutatjava。net。SocketInputStream。socketRead0（NativeMethod）atjava。net。SocketInputStream。socketRead（SocketInputStream。java：116）。。。
　　这个异常看起来与connectTimeout导致的异常很相似，但是实际却有很大不同。这里我们是执行了一条sql，CausedBy部分的异常提示为Readtimedout，而之前是建立连接时抛出的异常，异常提示为connecttimeout。
　　在异常信息的开始部分，我们看到了详细的错误提示信息：最后一次接收到服务端返回的报文是3080ms之前，最后一次发送报文给服务端是3005ms之前。
　　细心的读者已经发现，3005ms与我们设置的socketTimeout3000如此接近，事实上，你可以认为多出的5ms是系统检测到超过socketTimeout的耗时，之后抛出异常。当然，在实际开发中，系统检测sockettimeout的耗时并不是固定为5ms，每次检测的耗时可能都不同，一般不过超过几十毫秒。
　　另外，socketTimeout是配置在jdbcurl上的，对于所有执行的sql都会有这个超时限制。因此在配置这个值的时候，应该比应用中耗时最长的sql还要稍大一点。
　　socketTimeout默认值也是0，也就是不超时。
　　2statementtimeout
　　sockettimeout统一限制了所有SQL执行的最大耗时，有的时候，我们希望为不同的SQL指定不同的最大超时时间。这可以通过statementtimeout来完成。
　　Statement对象提供了一个setQueryTimeout方法（其子类PreparedStatement继承了这个方法），单位是秒，默认值为0，也就是不超时。以下是一个设置statementtimeout的案例：Connectionconndatasource。getConnection；PreparedStatementpsconn。prepareStatement（selectsleep（5））；ps。setQueryTimeout；设置statementtimeoutps。executeQuery；
　　在这里：
　　我们执行的sql是selectsleep（5），服务端需要休眠5s后才返回，
　　另外，我们设置了sql查询超时queryTimeout为1s
　　由于sql执行耗时超出了1s，因此，执行上述代码片段将抛出类似以下异常：com。mysql。jdbc。exceptions。MySQLTimeoutException：Statementcancelledduetotimeoutorclientrequestatcom。mysql。jdbc。PreparedStatement。executeInternal（PreparedStatement。java：1881）atcom。mysql。jdbc。PreparedStatement。executeQuery（PreparedStatement。java：1962）atsun。reflect。NativeMethodAccessorImpl。invoke0（NativeMethod）atsun。reflect。NativeMethodAccessorImpl。invoke（NativeMethodAccessorImpl。java：62）atsun。reflect。DelegatingMethodAccessorImpl。invoke（DelegatingMethodAccessorImpl。java：43）atjava。lang。reflect。Method。invoke（Method。java：498）atorg。apache。tomcat。jdbc。pool。StatementFacadeStatementProxy。invoke（StatementFacade。java：114）atcom。sun。proxy。Proxy6。executeQuery（UnknownSource）。。。
　　可以看到，提示的异常信息为Statementcancelledduetotimeoutorclientrequest，表示sql由于执行超时而被取消了。
　　通过statementtimeout，我们可以更加灵活的为不同的sql设置不同的超时时间。然而，在实际开发过程中，通常我们都是使用ORM框架，而不会直接使用原生的JDBCAPI，这意味着ORM要对此进行支持。
　　以mybatis为例，其提供了对statementtimeout超时设置的支持。我们可以在元素中，为所有要执行的sql，设置一个默认的statementtimeout。
　　如在mybatisconfig。xml配置默认的statementtimeout：settings！设置sql默认执行超时时间为25秒，如果为提供，则默认值为0，也就是没有限制settingnamedefaultStatementTimeoutvalue25settings
　　或者在mapper映射文件中，指定单个sql的statementtimeout，如！设置sql超时时间为10秒selectidselectPersontimeout10parameterTypeintresultTypehashmapSELECTFROMPERSONWHEREID｛id｝select
　　事实上，mybatis底层也是也只是我们我们配置的值，通过调用Statement。setQueryTimeout方法进行设置。
　　BaseStatementHandlersetStatementTimeout
　　需要注意的是，尽管statementtimeout很灵活，但是在高并发的情况下，会创建大量的线程，一些场景下笔者并不建议使用。原因在于，mysqlconnectorjava底层是通过定时器Timer来实现statementtimeout的功能，也就是说，对于设置了statementtimeout的sql，将会导致mysql创建定时Timer来执行sql，意味着高并发的情况下，mysql驱动可能会创建大量线程。
　　以下是笔者模拟设置statementtimeout之后，通过jstack命令查看的结果。
　　可以看到这里包含了一个名为MysqlStatementCancellationTimer的线程，这就是用于控制sql执行超时的定时器线程。在高并发的情况下，大量的sql同时执行，如果设置了statementtimeout，就会出现需要这样的线程。
　　在mysqlconnectorjava驱动的源码中（这里使用的是5。1。39版本），体现了这个逻辑。在ConnectionImpl类中定义了一个超时Timer
　　com。mysql。jdbc。ConnectionImplgetCancelTimer
　　这里我们看到ConnectionImpl内部，提供了一个名为MySQLStatementCancellationTimer的定时器。
　　在sql执行时，如果设置了statementtimeout，则将sql包装成一个task，通过Timer进行执行：mysql驱动源码里有多处使用到了这个Timer，这里以StatementImpl的executeQuery方法为例进行讲解，包含了以下代码片段：
　　com。mysql。jdbc。StatementImplexecuteQuery
　　可以看到，在指定statementtimeout的情况下，mysql内部会将sql执行操作包装成一个CancelTask，然后通过定时器Timer来运行。Timer实际上是与ConnectionImpl绑定的，同一个ConnectionImpl执行的多个sql，会共用这个Timer。默认情况下，这个Timer是不会创建的，一旦某个ConnectionImpl上执行的一个sql，指定了statementtimeout，此时这个Timer才创建，一直到这个ConnectionImpl被销毁时，Timer才会取消。
　　在一些场景下，如分库分表、读写分离，如果使用的数据库中间件是基于smartclient方式实现的，会与很多库建立连接，由于其底层最终也是通过mysqlconnectorjava创建连接，这种场景下，如果指定了statementtimeout，那么应用中将会存在大量的Timer线程，在这种场景下，并不建议设置。
　　最后，需要提醒的是，sockettimeout是TCP层面的超时，是操作系统层面进行的控制，statementtimeout是驱动层面实现的超时，是应用层面进行的控制，如果同时设置了二者，那么后者必须比前者大，否则statementtimeout无法生效。
　　3transactiontimeout
　　前面提到的的sockettimeout、statementtimeout，都是限制单个sql的最大执行超时。在事务的情况下，可能需要执行多个sql，我们想针对整个事务设置一个最大的超时时间。
　　例如，我们在采用spring配置事务管理器的时候，可以指定一个defaultTimeout属性，单位是秒，指定所有事务的默认超时时间。
　　也可以在Transactional注解上针对某个事务，指定超时时间，如：Transactional（timeout）
　　如果同时配置了，Transactional注解上的配置，将会覆盖默认的配置。
　　transactiontimeout的实现原理可以用以下流程进行描述，假设事务超时为5秒，需要执行3个sql：starttransaction事务超时为5秒sql1statementtimeout设置为5秒执行耗时1s，那么整个事务超时还剩4秒sql2设置statementtimeout设置为4秒执行耗时2秒，整个事务超时还是2秒sql3设置statementtimeout设置为2秒假设执行耗时超过2s，那么整个事务超时，抛出异常
　　这里只是一个简化的流程，但是可以帮助我们了解spring事务超时的原理。从这个流程中，我们可以看到，spring事务的超时机制，实际上是还是通过Statement。setQueryTimeout进行设置，每次都是把当前事务的剩余时间，设置到下一个要执行的sql中。
　　事实上，spring的事务超时机制，需要ORM框架进行支持，例如mybatisspring提供了一个SpringManagedTransaction，里面有一个getTimeout方法，就是通过从spring中获取事务的剩余时间。这里不在继续进行源码分析。
　　4getconnectiontimeout
　　checkconnectiontimeout或者getconnectiontimeout，表示从数据库连接池DataSource中获取链接超时。通DataSource的实现有很多，如druid，c3p0、dbcp2、tomcatjdbc、hicaricp等，不同的连接池，抛出的异常类型不同，但是从异常的名字中，都可以看出是获取链接异常。连接池，底层也是通过mysqlconnectorjava创建连接，只不过在连接上做了一层代理，当关闭的时候，是返回连接池，而不是真正的关闭物理连接，从而达到连接复用。
　　我们通常是需要首先获取到一个连接Connection对象，然后才能创建事务，设置事务超时实现，在事务中执行sql，设置sql的超时时间。因此，要操作数据库，Connection是基础。从连接池中，获取链接超时，是开发中，最常见的异常。
　　通常是因为连接池大小设置的不合理。如何设置合理的线程池大小需要进行综合考虑。
　　这里以sql执行耗时、要支撑的qps为例：
　　假设某个接口的sql执行耗时为5ms，要支撑的最大qps为1000。一个sql执行5ms，理想情况下，一个Connection一秒可以执行200个sql。又因为支持的qps为1000，那么理论上我们只需要5个连接即可。当然，实际情况远远比这复杂，例如，我们没有考虑连接池内部的逻辑处理耗时，mysql负载较高执行sql变慢，应用发生了gc等，这些情况都会导致获取连接时间变长。所以，我的建议是，比理论值，高35倍。
　　最后对以下两种典型情况，进行说明：
　　1应用启动时，出现获取连接超时异常
　　可以通过调大initPoolSize。如果连接池有延迟初始化（lazyinit）功能，也要设置为立即初始化，否则，只有第一次请求访问数据库时，才会初始化连接池。这个时候容易出现获取链接超时。
　　2业务高峰期，出现获取连接超时异常
　　如果是偶然出现，可以忽略。如果出现的较为频繁，可以考虑调大maxPoolSize和minPoolSize。
　　近期发表：
　　异地多活场景下的数据同步之道
　　数据库中间件详解
　　分布式事务概述
　　史上最详细mybatis与spring整合教程
　　源码剖析Mybatis映射器（Mapper）工作原理
　　剖析Spring多数据源
　　Mysql分支选择：PerconaOrMariaDB