因数据库连接池“污染”，引发的诡异故障排查
作者介绍
温卫斌，就职于中国农业银行研发中心，目前负责云原生应用平台设计与开发，主要关注于分布式、Serverless相关领域。
一、诡异问题
最近小伙伴反映了一个很诡异的问题，就是当应用执行完一些SQL后，后续查询的查询SQL有的可以准确返回，而有的返回记录却数只有1个，出现丢数问题。经过排查日志，发现有个SQL在执行前先执行了SET SQL_SELECT_LIMIT=1。
SQL_SELECT_LIMIT 变量用来指定select返回的最大记录数，具体可参见
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_sql_select_limit
应用组反馈并没有直接调用这个SQL，下意识的觉得应该是MySQL驱动自动发送的。查看其源码，很快就定位到是应用方调用的Statement.setMaxRow方法，对应MySQL驱动中com.mysql.cj.jdbc.StatementImpl类。
private boolean executeInternal(String sql, boolean returnGeneratedKeys) throws SQLException {JdbcConnection locallyScopedConn = this.checkClosed();...locallyScopedConn.setSessionMaxRows(maybeSelect ? this.maxRows : -1);...}
进一步看locallyScopedConn.setSessionMaxRows方法
com.mysql.cj.jdbc.ConnectionImpl
public void setSessionMaxRows(int max) throws SQLException {try {synchronized(this.getConnectionMutex()) {if (this.session.getSessionMaxRows() != max) {this.session.setSessionMaxRows(max);this.session.execSQL((Query)null, "SET SQL_SELECT_LIMIT=" + (this.session.getSessionMaxRows() == -1 ? "DEFAULT" : this.session.getSessionMaxRows()), -1, (NativePacketPayload)null, false, this.nullStatementResultSetFactory, (ColumnDefinition)null, false);}}} catch (CJException var6) {throw SQLExceptionsMapping.translateException(var6, this.getExceptionInterceptor());}}
可以看到正是该方法发送了SET SQL_SELECT_LIMIT，正是这个SET语句使得后续该连接执行的SQL查询出来的记录数都只有1条，由于Proxy端的连接是一个连接池，因此下一个用到该连接的SQL也只能查到1条记录，这也是文章开头提到问题的原因所在。
写个测试代码很容易重现：
mysql> select * from user;+----+--------+------+| id | name | age |+----+--------+------+| 1 | h2 | 10 || 2 | mysql | 19 || 3 | oracle | 20 |+----+--------+------+3 rows in set (0.00 sec)
HikariConfig config = new HikariConfig();config.setPoolName("test");config.setJdbcUrl("jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC&allowMultiQueries=true&useAffectedRows=true");config.setUsername("root");config.setPassword("password");config.setMaximumPoolSize(1);config.setMinimumIdle(1);config.setDriverClassName("com.mysql.cj.jdbc.Driver");HikariDataSource hikariDataSource = new HikariDataSource(config);Connection connection1 = hikariDataSource.getConnection();Statement statement1 = connection1.createStatement();statement1.execute("SET SQL_SELECT_LIMIT=1");// statement.setMaxRows(1);ResultSet resultSet1 = statement1.executeQuery("select * from user");System.out.println("After SET SQL_SELECT_LIMIT=1,first query");while (resultSet1.next()) {System.out.println(resultSet1.getString("name"));}resultSet1.close();statement1.close();connection1.close();//release connectionConnection connection2 = hikariDataSource.getConnection();Statement statement2 = connection2.createStatement();ResultSet resultSet2 = statement2.executeQuery("select * from user");System.out.println("After SET SQL_SELECT_LIMIT=1,second query");while (resultSet2.next()) {System.out.println(resultSet2.getString("name"));}resultSet2.close();statement2.close();connection2.close();
执行结果：
After SET SQL_SELECT_LIMIT=1,first queryh2After SET SQL_SELECT_LIMIT=1,second queryh2
不过问题并没有结束，因为正常情况下，我们往往不直接调用SET SQL_SELECT_LIMIT，而是调用Statement.setMaRows，那么这种方式下会出现类似问题吗？
写个测试代码，发现竟然不会。为什么呢？
...HikariDataSource hikariDataSource = new HikariDataSource(config);Connection connection1 = hikariDataSource.getConnection();Statement statement1 = connection1.createStatement();//statement1.execute("SET SQL_SELECT_LIMIT=1");statement1.setMaxRows(1);ResultSet resultSet1 = statement1.executeQuery("select * from user");System.out.println("After setMaxRows,first query");while (resultSet1.next()) {System.out.println(resultSet1.getString("name"));}resultSet1.close();statement1.close();connection1.close();//release connectionConnection connection2 = hikariDataSource.getConnection();Statement statement2 = connection2.createStatement();ResultSet resultSet2 = statement2.executeQuery("select * from user");System.out.println("After setMaxRows,second query");while (resultSet2.next()) {System.out.println(resultSet2.getString("name"));}resultSet2.close();statement2.close();connection2.close();
执行结果：
After setMaxRow,first queryh2After setMaxRow,second queryh2mysqloracle
这是因为当调用Connection.createStatement创建一个新的Statement的时候，因为MySQL驱动会自动比较当前maxRows值与Session的设置是否一样，新建的Statement的maxRows值为-1，跟当前数据库连接的sessionMaxrows不一样，MySQL驱动自动发送SET SQL_SELECT_LIMIT=DEFAULT SQL来重置该变量值。
也可通过测试代码进行验证：
....HikariDataSource hikariDataSource = new HikariDataSource(config);Connection connection1 = hikariDataSource.getConnection();Statement statement1 = connection1.createStatement();statement1.execute("SET SQL_SELECT_LIMIT=1");// statement.setMaxRows(1);ResultSet resultSet1 = statement1.executeQuery("select * from user");System.out.println("After SET SQL_SELECT_LIMIT=1,first query");while (resultSet1.next()) {System.out.println(resultSet1.getString("name"));}resultSet1.close();statement1.close();connection1.close();//release connectionConnection connection2 = hikariDataSource.getConnection();Statement statement2 = connection2.createStatement();statement2.execute("SET SQL_SELECT_LIMIT=DEFAULT");ResultSet resultSet2 = statement2.executeQuery("select * from user");System.out.println("After SQL_SELECT_LIMIT=DEFAULT,second query");while (resultSet2.next()) {System.out.println(resultSet2.getString("name"));}resultSet2.close();statement2.close();connection2.close();
执行结果：
After SET SQL_SELECT_LIMIT=1,first queryh2After SQL_SELECT_LIMIT=DEFAULT,second queryh2mysqloracle
这个问题也让我们再次正视一个问题，数据库连接是有状态的，由于连接池中的连接是复用的，不当的设置会导致连接被“污染”，从而带来很多不容易排查的诡异问题，因为这类SESSION级的设置会在连接的生存期会一直生效。除了SQL_SELECT_LIMIT，常见的还有autocommit、SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ、SET CHARACTER SET等等。
二、连接池带来的数据库连接“污染”，如何解决？
1、通过连接池直连MySQL
通过JDBC方式调用MySQL驱动方法，不要直接使用MYSQL SET类 SQL，这样MySQL驱动可以帮助尽可能降低这种连接污染带来的困惑。尽可能使用top、limit替代SET SQL SELECT_LIMIT。
由于Proxy端收到已经是SET 类SQL，没有了MySQL JDBC驱动进行自动重置，后端的连接管理要更复杂，需要在性能与状态正确性、连接的隔离和复用之间寻求平衡。
对于数据库连接，如果采用和数据库完全一致的模型，那么很容易有以下推导：
有状态->独占的->连接不复用共享（不使用连接池）-> 应用与Proxy的连接：Proxy与数据库连接是1: 1->数据库连接数限制->DB Proxy性能上不去。
连接独占模型
作为DB的统一接入端和网关，而连接性能是其核心关键的一个设计指标，而提升Proxy性能->后端连接必须进行复用->应用与Proxy的连接：Proxy与数据库连接是n :1。
连接复用模型
在实际创景中并不是所有使用连接是都会执行SET SQL，而且也不是所有SET都会影响SQL执行的正确性（例如SET CHARACTER SET），因此可以通过SQL解析识别可能传染导致不正确的SET，然后再释放回连接之前进行重置（重置失败的，则进行隔离与关闭），这样一方面避免了SET SQL导致的连接污染，另一方面也保证了大多数场景连接的复用，增加Proxy前端连接能力，从而提升Proxy的整体性能。