MySQL 5.7.12 + Druid 连接池“只读事务”异常深度剖析(Cannot execute statement in a READ ONLY transaction)

一、故障现象在 MySQL 5.7.12 环境下使用 Druid 连接池的应用偶尔会抛出以下异常Cannot execute statement in a READ ONLY transaction诡异特征偶发性出现并非每次操作都复现conn.isReadOnly()返回false但 MySQL 实际报错只读手工执行SET SESSION TRANSACTION READ WRITE后可恢复无法在数据库客户端中手工稳定复现二、底层原理三层状态机撕裂问题的本质是Druid 连接池、JDBC 驱动、MySQL 服务器三者之间的会话状态不一致。2.1 JDBC 驱动的“短路”逻辑MySQL Connector/J 驱动有一个性能优化参数useLocalSessionState默认为true其核心代码如下// ConnectionImpl.setReadOnly() 简化逻辑publicvoidsetReadOnly(booleanreadOnly)throwsSQLException{if(this.useLocalSessionState){// 关键如果内存状态与新状态一致直接返回不发任何包if(readOnlythis.isReadOnly()){return;// ← 问题根源跳过 SQL 发送}}// 只有状态不一致时才真正发送 SQLthis.session.sendCommand(SET SESSION TRANSACTION (readOnly?READ ONLY:READ WRITE));}核心问题当useLocalSessionStatetrue时setReadOnly()调用不一定会真正发送 SQL 到 MySQL。2.2 故障完整时序推演┌─────────────────────────────────────────────────────────────────────────────┐ │ 阶段一连接被污染 │ ├─────────────────────────────────────────────────────────────────────────────┤ │ 应用A: conn.setReadOnly(true) │ │ ↓ JDBC 驱动发送SET SESSION TRANSACTION READ ONLY │ │ ↓ │ │ MySQL 会话状态tx_read_only 1只读 │ │ JDBC 内存状态this.readOnly true │ │ Druid 记录状态holder.isUnderlyingReadOnly() true │ └─────────────────────────────────────────────────────────────────────────────┘ ┌─────────────────────────────────────────────────────────────────────────────┐ │ 阶段二连接回收与“伪重置” │ ├─────────────────────────────────────────────────────────────────────────────┤ │ Druid 回收连接尝试重置conn.setReadOnly(false) │ │ ↓ JDBC 检查当前内存状态 true目标 false → 不一致 │ │ ↓ 正常发送SET SESSION TRANSACTION READ WRITE │ │ ↓ │ │ MySQL 会话状态tx_read_only 0读写【恢复正常】 │ │ JDBC 内存状态this.readOnly false │ └─────────────────────────────────────────────────────────────────────────────┘ ┌─────────────────────────────────────────────────────────────────────────────┐ │ 阶段三致命跳过问题发生的瞬间 │ ├─────────────────────────────────────────────────────────────────────────────┤ │ 某个时机网络波动 / Druid 后台管理线程 / 下次获取连接时的校验 │ │ Druid 再次调用 conn.setReadOnly(false) │ │ ↓ JDBC 检查当前内存状态 false目标 false → 一致 │ │ ↓ 【致命跳过】不发送任何 SQL 到 MySQL │ │ ↓ │ │ MySQL 会话状态tx_read_only 0还是 1可能存在状态残留 │ │ MySQL 5.7.12 的 tx_read_only 状态在事务结束后仍保留 │ │ 实际 MySQL 状态可能仍为只读但 JDBC 认为已经是读写 │ └─────────────────────────────────────────────────────────────────────────────┘ ┌─────────────────────────────────────────────────────────────────────────────┐ │ 阶段四连接复用导致异常 │ ├─────────────────────────────────────────────────────────────────────────────┤ │ 应用B 获取该连接执行 UPDATE 操作 │ │ ↓ │ │ MySQL 检测到会话仍为只读状态 → 返回错误 │ │ Cannot execute statement in a READ ONLY transaction │ │ ↓ │ │ 而 conn.isReadOnly() 返回 falseJDBC 内存状态为 false │ │ 应用层完全无法感知问题所在 │ └─────────────────────────────────────────────────────────────────────────────┘2.3 为什么手工无法复现手工通过 MySQL 客户端操作时不存在 JDBC 驱动的状态缓存层每次SET SESSION都会真实执行因此无法复现此问题。复现需要同时满足JDBC 驱动开启useLocalSessionStatetrue默认Druid 连接池的连接复用机制特定的时序条件Druid 重复调用setReadOnly(false)MySQL 5.7.12 的tx_read_only状态残留特性三、解决方案3.1 根本解决方案关闭 JDBC 状态缓存核心配置设置 MySQL JDBC 驱动参数useLocalSessionStatefalse⚠️注意useLocalSessionState是MySQL JDBC 驱动的参数不是 Druid 的属性。Druid 只是将其透传给底层连接。3.2 Spring Boot Druid 正确配置示例方式一JDBC URL 中指定推荐spring:datasource:# 关键在 URL 中添加 useLocalSessionStatefalseurl:jdbc:mysql://localhost:3306/test?useLocalSessionStatefalseusername:rootpassword:123456type:com.alibaba.druid.pool.DruidDataSourcedruid:initial-size:5max-active:20min-idle:5test-on-borrow:truevalidation-query:SELECT 1default-read-only:false方式二通过 connectionProperties 指定spring:datasource:url:jdbc:mysql://localhost:3306/testusername:rootpassword:123456type:com.alibaba.druid.pool.DruidDataSourcedruid:# 关键通过 connectionProperties 传递驱动参数connection-properties:useLocalSessionStatefalseinitial-size:5max-active:20test-on-borrow:truevalidation-query:SELECT 1default-read-only:false方式三Java Config 配置ConfigurationpublicclassDataSourceConfig{BeanpublicDataSourcedataSource(){DruidDataSourcedataSourcenewDruidDataSource();dataSource.setUrl(jdbc:mysql://localhost:3306/test);dataSource.setUsername(root);dataSource.setPassword(123456);// 核心设置 useLocalSessionStatefalseJDBC 驱动参数dataSource.setConnectionProperties(useLocalSessionStatefalse);// 可选增强配置dataSource.setConnectionInitSqls(Arrays.asList(SET SESSION TRANSACTION READ WRITE));dataSource.setDefaultReadOnly(false);dataSource.setTestOnBorrow(true);dataSource.setValidationQuery(SELECT 1);returndataSource;}}方式四application.properties# 方式AURL 中添加 spring.datasource.urljdbc:mysql://localhost:3306/test?useLocalSessionStatefalse # 方式BconnectionProperties 中添加 spring.datasource.druid.connection-propertiesuseLocalSessionStatefalse # 通用配置 spring.datasource.usernameroot spring.datasource.password123456 spring.datasource.typecom.alibaba.druid.pool.DruidDataSource spring.datasource.druid.default-read-onlyfalse spring.datasource.druid.test-on-borrowtrue spring.datasource.druid.validation-querySELECT 13.3 完整配置示例生产推荐spring:datasource:url:jdbc:mysql://localhost:3306/test?useLocalSessionStatefalseuseSSLfalsecharacterEncodingutf8username:rootpassword:123456type:com.alibaba.druid.pool.DruidDataSourcedruid:# 连接池基础配置initial-size:10max-active:50min-idle:10max-wait:60000# 核心禁用 JDBC 状态缓存connection-properties:useLocalSessionStatefalse# 连接校验增强可靠性test-on-borrow:truetest-while-idle:truevalidation-query:SELECT 1# 强制初始化状态default-read-only:falseconnection-init-sqls:-SET SESSION TRANSACTION READ WRITE# 回收策略time-between-eviction-runs-millis:60000min-evictable-idle-time-millis:3000003.4 配置对比表配置方式示例说明❌ 错误druidDataSource.setUseLocalSessionState(false)Druid 没有此属性✅ 正确jdbc:mysql://host/db?useLocalSessionStatefalseURL 参数传递✅ 正确connection-properties: useLocalSessionStatefalse连接属性传递四、原理总结┌─────────────────────────────────────────────────────────────────┐ │ 问题根因 │ ├─────────────────────────────────────────────────────────────────┤ │ useLocalSessionStatetrue默认 │ │ ↓ │ │ JDBC 驱动缓存会话状态重复调用 setReadOnly(false) 被“短路” │ │ ↓ │ │ 不发 SQL 到 MySQLMySQL 实际状态与 JDBC 内存状态撕裂 │ │ ↓ │ │ 连接复用时执行写入操作 → READ ONLY transaction 异常 │ └─────────────────────────────────────────────────────────────────┘ ┌─────────────────────────────────────────────────────────────────┐ │ 解决方案 │ ├─────────────────────────────────────────────────────────────────┤ │ useLocalSessionStatefalse │ │ ↓ │ │ 强制 JDBC 驱动每次 setReadOnly() 都发送真实 SQL 到 MySQL │ │ ↓ │ │ MySQL 会话状态与 JDBC 期望状态实时同步 │ │ ↓ │ │ 彻底解决三层状态机不一致问题 │ └─────────────────────────────────────────────────────────────────┘五、性能影响评估评估项结论额外网络开销每次setReadOnly()增加一次网络往返实际影响业务中setReadOnly()调用频率极低影响可忽略吞吐量影响生产经验表明通常 3%收益彻底消除偶发性只读事务异常稳定性大幅提升六、一句话总结在 Druid 连接池中配置useLocalSessionStatefalse通过 JDBC URL 或connectionProperties传递强制 MySQL JDBC 驱动每次状态变更都真实通知 MySQL彻底解决三层状态机不一致导致的“只读事务”异常。