数据库事务耗时过长导致Could not retrieve transaction read-only status from server异常

背景 

[11-06 02:02:09:005] [ERROR] - DruidDataSource - discard connection
java.sql.SQLException: Could not retrieve transaction read-only status from server
 
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
 
The last packet successfully received from the server was 1,114,012 milliseconds ago.  The last packet sent successfully to the server was 1 milliseconds ago.
 
Caused by: java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.

最开始只看到Could not retrieve transation read-only status server没看到下面的Communications link failure，以为是在代码中手动设置事务状态为只读或者事务回滚，后来查代码也没发现。
当看到Communications link failure意识到连接异常，但与当前方法连接同一个库的其他方法可以执行成功，所以数据库服务也是正常的。

最终发现原因：事务内部处理逻辑过多，导致大事务执行时间过长，超过了mysql连接的超时时间啊in，导致服务端丢弃连接后，过了很久事务才执行完毕。

问题原因

wait_timeout 设置过小，mysql自动丢弃连接，但是程序端没有超时，造成依然在这个被丢弃的连接上执行语句

解决方案

设置mysql wait_timeout 参数
show global variables like '%timeout%';
SET GLOBAL wait_timeout=86400;
此处的单位应该秒。

　　

相关链接

https://segmentfault.com/a/1190000020125846

https://blog.csdn.net/u013097383/article/details/88581938

https://blog.csdn.net/qq_38214534/article/details/106021187

https://study.sf.163.com/documents/read/service_support/dsc-p-a-0149

 

本篇文章如有帮助到您，请给「翎野君」点个赞，感谢您的支持。

首发链接：https://www.cnblogs.com/lingyejun/p/18498531