Mbatis 批量执行报错详解

灰太狼 2022-09-13 11:21 228阅读 0赞

mybatis 批量插入或更新在很多业务场景是一个节约资源和连接数的好的处理方案，但是有时候数据库配置特别是连接超时时间、等待时间等配置的不合理的时候，执行批量操作就可能报如下错误。

一、问题解决：

1、报错的示例：

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
    
    The last packet successfully received from the server was 9 milliseconds ago.  The last packet sent successfully to the server was 18 milliseconds ago.
    	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    	at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
    	at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
    	at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:990)
    	at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3751)
    	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2512)
    	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2683)
    	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2495)
    	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1903)
    	at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1242)
    	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3051)
    	at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:440)
    	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3049)
    	at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.execute(PreparedStatementProxyImpl.java:167)
    	at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:498)
    	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    	at java.lang.reflect.Method.invoke(Method.java:498)
    	at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59)
    	at com.sun.proxy.$Proxy230.execute(Unknown Source)
    	at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:46)
    	at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74)
    	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    	at java.lang.reflect.Method.invoke(Method.java:498)
    	at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63)
    	at com.sun.proxy.$Proxy228.update(Unknown Source)
    	at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:50)
    	at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117)
    	at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:198)
    	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    	at java.lang.reflect.Method.invoke(Method.java:498)
    	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:433)
    	at com.sun.proxy.$Proxy109.update(Unknown Source)
    	at org.mybatis.spring.SqlSessionTemplate.update(SqlSessionTemplate.java:294)
    	at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:62)
    	at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59)
    	at com.sun.proxy.$Proxy214.batchUpdate(Unknown Source)
    	at com.huobi.open.mis.assets.service.sys.impl.rateServiceImpl.batchUpdate(SymbolRateServiceImpl.java:41)
    	at com.huobi.open.mis.assets.task.asyn.rateTask.scheduled(SymbolRateTask.java:276)
    	at com.huobi.open.mis.assets.task.asyn.rateTask.execute(SymbolRateTask.java:66)
    	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    	at java.lang.reflect.Method.invoke(Method.java:498)
    	at com.huobi.open.mis.assets.task.base.ScheduleRunnable.run(ScheduleRunnable.java:44)
    	at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
    	at java.util.concurrent.FutureTask.run$$$capture(FutureTask.java:266)
    	at java.util.concurrent.FutureTask.run(FutureTask.java)
    	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
    	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
    	at java.lang.Thread.run(Thread.java:748)
    Caused by: java.net.SocketException: Broken pipe (Write failed)
    	at java.net.SocketOutputStream.socketWrite0(Native Method)
    	at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:111)
    	at java.net.SocketOutputStream.write(SocketOutputStream.java:155)
    	at java.io.BufferedOutputStream.write(BufferedOutputStream.java:122)
    	at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3732)
    	... 51 common frames omitted

2、解决方案：

这类问题的解决方案一般是多样性的，比如适当配置数据库的连接时间、等待时间、执行时间长一些；同时每次批量执行数据的条数尽量合理一些、或者说少一些，一般不要超过500条；综合解决以上问题。比如：

我们可以通过以下方法查看你的mysql 的默认连接等待时间是多少：

show global variables like 'wait_timeout';

显示：

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAbmFuZGFvMTU4_size_20_color_FFFFFF_t_70_g_se_x_16][]

此查询的时间单位是秒（s）

在wait\_timeout时间内，mysql的连接（connection）处于等待状态，一旦超过这个时间，mysql5就会关闭。但是在我们的项目中的连接池中仍存在合法的connection，当你的程序需要再次访问数据库时，就会出现以上异常，但是再重新访问，又能正常从数据库中获取到数据。

这个时间是可以修改的，在windows中最大可设置为24天，需要修改mysql5的安装文件夹中的my.ini文件。

LInux中最大为365天，需要修改/etc/my.cnf文件。文件中添加一行配置：wait\_timeout=1814400,（大约21天）重启mysql服务之后生效。

或者可以使用sql修改，但重启之后又会恢复成8小时，因此不推荐此方法:

set global wait_timeout=1814400;

但是，即使你修改了时间，系统若长时间不用，一旦超过你设置的时间，再次访问依然会出现上述异常情况。因此，我又找了另一个可以永久解决该问题的办法。在配置文件里配置，永久性的。  
 二、全局排查解决：

按照网上的解决方案，修改了my.cnf中的超时时间：

set wait_timeout=31536000
    set interactive_timeout=31536000

当时解决了问题，但是第二天又不行了，于是我详细排查了一下：

最终还需要设置 net\_read\_timeout和net\_write\_timeout 参数，将这两个参数调大，改为9000，重启数据库，之后就OK了。

数据库查询：

show global variables like "%timeout%";

查到结果：

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAbmFuZGFvMTU4_size_20_color_FFFFFF_t_70_g_se_x_16 1][]

参数详解：

1.  connect\_timeout
    
    连接超时 mysql连接共有6次握手，3次TCP协议这个跟connect\_timeout参数没有关系，另外3次跟connect\_timeout参数有关系，该参数主要是为了防止网络不佳时应用重连导致连接数涨太快，一般默认即可。
2.  delayed\_insert\_timeout
    
    这是为MyISAM INSERT DELAY设计的超时参数，在INSERT DELAY中止前等待INSERT语句的时间
3.  interactive\_timeout
    
    服务器关闭交互式连接前等待活动的秒数。交互式客户端定义为在mysql\_real\_connect()中使用CLIENT\_INTERACTIVE选项的客户端。参数默认值：28800秒（8小时）
4.  lock\_wait\_timeout
    
    锁等待超时时间
5.  net\_read\_timeout / net\_write\_timeout
    
    这个参数只对TCP/IP链接有效，分别是数据库等待接收客户端发送网络包和发送网络包给客户端的超时时间，这是在Activity状态下的线程才有效的参数
6.  slave\_net\_timeout
    
    这是Slave判断主机是否挂掉的超时设置，在设定时间内依然没有获取到Master的回应就人为Master挂掉了
7.  wait\_timeout
    
    服务器关闭非交互连接之前等待活动的秒数。

[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAbmFuZGFvMTU4_size_20_color_FFFFFF_t_70_g_se_x_16]: /images/20220828/17464529217b4896823c74da4aeccc13.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAbmFuZGFvMTU4_size_20_color_FFFFFF_t_70_g_se_x_16 1]: /images/20220828/d38445d46dee45a8b5b69f6de66e1f19.png