记一次坑，Could not retrieve transaction read-only status from server

电玩女神 2023-03-13 02:13 35阅读 0赞

线上的一个定时任务，重刷数据，执行报错。查看了日志，错误摘要如下

[05-09 13:18:34:814] [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.

代码就是很简单的查询，然后更新

@Transactional
    public void methodA(){
        doSelect();
    
        doUpdate();
    }

跟着报错去网上搜，搜到的都是改mysql驱动版本，改mysql隔离级别啥的，都试了还是没用，于是把线上数据拉到本地，重现了bug。发现doSelect()方法执行的很慢，执行完花了20分钟，然后一到修改方法就报java.sql.SQLException: Could not retrieve transaction read-only status from server这个错误了。突然想到会不会跟@Transactional注解有关，于是更改代码

public void methodA(){
        doSelect();
    
        doUpdate();
    }
    
    @Transactional
    public void doUpdate(){
    
    }

完美运行！

我看了@Transactional有关的参数，timeout啥的，还是没找到会出现java.sql.SQLException: Could not retrieve transaction read-only status from server这个错误的原因，但总算能跑通了。

============  应该是找到原因了  ===========

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4MjE0NTM0_size_16_color_FFFFFF_t_70][]

数据库wait\_timeout时间为10分钟，事务开启，从数据库获取到连接，10分钟之后数据库已经主动断开事务保持的连接，事务再次操作数据库抛出异常

资料来源：[https://blog.csdn.net/u013097383/article/details/88581938][https_blog.csdn.net_u013097383_article_details_88581938]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4MjE0NTM0_size_16_color_FFFFFF_t_70]: /images/20230312/10dc328d19de45b0bf89e4f86fcd1cd6.png
[https_blog.csdn.net_u013097383_article_details_88581938]: https://blog.csdn.net/u013097383/article/details/88581938