# 数据源不懂的配置请使用默认值

By [OutOfToken](https://paragraph.com/@lua) · 2022-01-10

---

### TL;NR:

`请按照HikariCP官方推荐进行配置数据库连接池`

    dataSource.cachePrepStmts=true
    dataSource.prepStmtCacheSize=250
    dataSource.prepStmtCacheSqlLimit=2048
    dataSource.useServerPrepStmts=true
    dataSource.useLocalSessionState=true
    dataSource.rewriteBatchedStatements=true
    dataSource.cacheResultSetMetadata=true
    dataSource.cacheServerConfiguration=true
    dataSource.elideSetAutoCommits=true
    dataSource.maintainTimeStats=false
    

### 背景信息介绍:

公司的项目部门使用我们研发中心的框架进行产品开发,再使用动态数据源进行数据库操作的时候出现两种奇怪的现象:

*   相同的 sql 语句「一种写法是 navicat 格式化后的语句(字符较多), 一种写法是单行压缩的语句(字符较短)」,两种语句在`正确执行SQL语句` `+ java逻辑代码进行处理的时候出现异常` 两个条件下,执行结果是不同的.
    
    *   正确期望 : 出现异常,两种写法语句对应的整个事务回滚,数据库无新增数据.
        
    *   错误现象 :
        
        *   长语句事务不回滚,不符合预期
            
        *   短语句事务回滚,符合预期
            
*   一个mybatis的语句包含多个insert(比如执行三个 insert into tablename values)操作,如果中途的语句出现表名错误、字段名称不存在等原因导致sql执行失败
    
    *   正确期望 : 出现异常,整个事务回滚,数据库无新增数据.
        
    *   错误现象 : 事务是不回滚,数据部分插入成功
        

最小化测试代码请参考:

[https://github.com/HugePages/troubles](https://github.com/HugePages/troubles)

代码时序图:

![主体代码时序图](https://storage.googleapis.com/papyrus_images/7b35542d1650b37fcf73038699da8b033e3cfdbb6b3aa4a6e0cc680164a36df8.png)

主体代码时序图

* * *

### 现象复现 :

#### 📍 执行不同长度的 sql 语句,当 sql 长度小于2048,事务正常回滚

#### 发起请求 [http://127.0.0.1:8080/demo/insert-rollback?type=1&exception=1](http://127.0.0.1:8080/demo/insert-rollback?type=1&exception=1)

![type 为 1](https://storage.googleapis.com/papyrus_images/290fd0c3717e1b6b61cef10329a17b624635179c6f855ee2928d35745e51f340.png)

type 为 1

[http://127.0.0.1:8080/demo/insert-rollback?type=1&exception=1](http://127.0.0.1:8080/demo/insert-rollback?type=1&exception=1)

查看 Wireshark 对应 mysql 协议的监控如图:

![sql 语句执行 insert1 语句(不超过配置项2048长度)](https://storage.googleapis.com/papyrus_images/dc48c5569772c235e061ae2d442d50f57ba793fd0e2404a1f8e34b21b7a4fef5.png)

sql 语句执行 insert1 语句(不超过配置项2048长度)

此时,代码执行符合预期 : 出现异常, spring 的事务管理正常,执行 rollback 操作,数据库没有新数据插入:

![查询数据库结果,数量为0](https://storage.googleapis.com/papyrus_images/6340915e460d3ec82bdf227c08770efcda5c808334c3357bbf33b1bc2ceecca0.png)

查询数据库结果,数量为0

* * *

#### 📍 执行不同长度的 sql 语句,当 sql 太长,导致事务不回滚

#### 发起请求 [http://127.0.0.1:8080/demo/insert-rollback?type=0&exception=1](http://127.0.0.1:8080/demo/insert-rollback?type=0&exception=1)

![type 为 0](https://storage.googleapis.com/papyrus_images/c289792ddfe8151cee2176263feb127607c262d400b7dbd65e3a7a265ad5ece6.png)

type 为 0

查看Wireshark 对应 mysql 协议的监控如图:

![此时的 mysql 协议多了一个请求,Close Statement](https://storage.googleapis.com/papyrus_images/975b8ee0babe412fd041536dd97f9201dc5b72c07f058f53bdb380cae93b1ed7.png)

此时的 mysql 协议多了一个请求,Close Statement

通过 debug,事务也是交由 spring 进行管理,也执行了 rollback 操作,但是最终数据还是插入到数据库中:

![数据插入成功](https://storage.googleapis.com/papyrus_images/ca8ec66fbd6209a3a4f02cf380405ff432a7ea0ec9f41fe3ded9453c20761ef2.png)

数据插入成功

结合上述的现象,只能怀疑是因为数据库sql长度导致,排查hikari的配置项,修改

    spring.datasource.hikari.data-source-properties.useServerPrepStmts=true
    spring.datasource.hikari.data-source-properties.prepStmtCacheSize=250
    spring.datasource.hikari.data-source-properties.prepStmtCacheSqlLimit=200
    

修改为 `prepStmtCacheSqlLimit=2048`,再次重复之前的测试,均进行正常回滚.

![测试不同配置导致的结果分支](https://storage.googleapis.com/papyrus_images/8a43e8fdf043a005426103c009d0c6a8775769106528931f49cb53177fb5fc03.png)

测试不同配置导致的结果分支

* * *

### 原因分析

追踪代码发现 rollback 之前, Statement 已经 close.

![断点执行到 rollbackNoChecks() 时 ,wireshark监控到已经发送了close请求](https://storage.googleapis.com/papyrus_images/4794f121c238c7b2b801cdb4e35248a60fe8155cb99683e4412fef0e74634629.png)

断点执行到 rollbackNoChecks() 时 ,wireshark监控到已经发送了close请求

因为 serverPrepareStatement 进行缓存,但是超过了 sqllimit 的大小, 就调用了realclose方法,进行 statement 的释放

![](https://storage.googleapis.com/papyrus_images/e396001175e6c2932d97b1fe6658ddb1a5733ca4011f9174bc7d578250d70a86.png)

![](https://storage.googleapis.com/papyrus_images/d0bf942e56f148135e8e73b2350f3b9796c591a80f6d3fd68c00000bfb3431ff.png)

**关键步骤:** 发送了 Close() 方法,收到响应,设置 transcationInserver 的 statusFlag 为0

⚠️ 注意,如果执行的 sql 语句存在异常,这个值会在最后抛异常的时候,获取当前事务在服务端状态

![](https://storage.googleapis.com/papyrus_images/aee39cd1953d4a20ffa53a03b7d304fc3eaeca1da3a977ae146d1e0b6eaeaa3b.png)

最后判断是否执行 rollback 的时候, 直接 return //no-op,不发送 rollback 指令给 mysql server

![](https://storage.googleapis.com/papyrus_images/15af9adaece7c34c49788a1fae4ec99de439caf9d2f056754780963eb1464e50.png)

#### 补充代码执行大致流程 @2022-0117

![](https://storage.googleapis.com/papyrus_images/a19e0e906489aacfa1fdabf2ac69bba6945b9efe298ad3299f524abcf361157e.png)

* * *

### 参考资料如下:

**transactions-are-not-getting-rollbacked-with-hikaricp**

[https://stackoverflow.com/questions/50234214/transactions-are-not-getting-rollbacked-with-hikaricp](https://stackoverflow.com/questions/50234214/transactions-are-not-getting-rollbacked-with-hikaricp)

**HikariCP 的 MySQL-Configuration**

[https://github.com/brettwooldridge/HikariCP/wiki/MySQL-Configuration](https://github.com/brettwooldridge/HikariCP/wiki/MySQL-Configuration)

**官方 bug 介绍 ( 网页内搜索 「Bug #20212882, Bug #75209」)**

[https://dev.mysql.com/doc/relnotes/connector-j/5.1/en/news-5-1-40.html](https://dev.mysql.com/doc/relnotes/connector-j/5.1/en/news-5-1-40.html)

**官方文档 (5.3.5 Performance Extensions)**

[https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-connp-props-performance-extensions.html](https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-connp-props-performance-extensions.html)

sql 监控组件 p6spy

[https://github.com/p6spy/p6spy](https://github.com/p6spy/p6spy)

**Connection 和 Transaction**

[https://docstore.mik.ua/orelly/weblinux2/mysql/ch08\_02.htm](https://docstore.mik.ua/orelly/weblinux2/mysql/ch08_02.htm)

#### 扩展阅读:

[Mysql Connector/J 源码分析（普通 Connection）](https://blog.csdn.net/yyb_gz/article/details/108428442?spm=1001.2014.3001.5501)

[如果 MySQL 事务中发生了网络异常？](https://cloud.tencent.com/developer/article/1710665)

### 总结:

    公司的开发框架已经发布4年了, 使用次框架开发的产品、项目近百个从来没有遇到过此类问题.
    
    平时的产品项目都会遵守一些分层、数据库使用规范,所以基本很难触发此问题.这次也是项目的开发在设计分层、接口功能拆分、SQL使用等方案不规范导致,没有严格遵守设计原则和开发规范.
    
    此外,框架研发团队在使用开源组件的时候,尽量减少默认参数配置,把参数配置的能力暴露给框架的使用者,特别是所开发的功能作为团队中最基础的公用服务.
    

![🌚 我猜测开发者直接网上在某些博客拷贝了对应的配置信息.](https://storage.googleapis.com/papyrus_images/3c9cac8e1beb67e3065f3351726b23400f0fbe4ad8183c0e592f0fd5cae465ea.png)

🌚 我猜测开发者直接网上在某些博客拷贝了对应的配置信息.

---

*Originally published on [OutOfToken](https://paragraph.com/@lua/ZMT4YBuerCeWl8PAoV6Z)*