因数据库连接池“污染”，引发的诡异故障排查

温卫斌 2022-02-25 12:51:09

作者介绍

温卫斌，就职于中国农业银行研发中心，目前负责云原生应用平台设计与开发，主要关注于分布式、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 connection
        Connection 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 connection
        Connection 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 connection
        Connection 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。

 

 
2、通过DB Proxy中间件连接MySQL

 

由于Proxy端收到已经是SET 类SQL，没有了MySQL JDBC驱动进行自动重置，后端的连接管理要更复杂，需要在性能与状态正确性、连接的隔离和复用之间寻求平衡。

 

对于数据库连接，如果采用和数据库完全一致的模型，那么很容易有以下推导：

有状态->独占的->连接不复用共享（不使用连接池）-> 应用与Proxy的连接：Proxy与数据库连接是1: 1->数据库连接数限制->DB Proxy性能上不去。

 

图片

 

连接独占模型

 

作为DB的统一接入端和网关，而连接性能是其核心关键的一个设计指标，而提升Proxy性能->后端连接必须进行复用->应用与Proxy的连接：Proxy与数据库连接是n :1。

 

图片

 

连接复用模型

 

 
3、连接管理模型

 

在实际创景中并不是所有使用连接是都会执行SET SQL，而且也不是所有SET都会影响SQL执行的正确性（例如SET CHARACTER SET），因此可以通过SQL解析识别可能传染导致不正确的SET，然后再释放回连接之前进行重置（重置失败的，则进行隔离与关闭），这样一方面避免了SET SQL导致的连接污染，另一方面也保证了大多数场景连接的复用，增加Proxy前端连接能力，从而提升Proxy的整体性能。

 

作者丨温卫斌
来源丨网址：https://www.jianshu.com/p/c416466bde51
dbaplus社群欢迎广大技术人员投稿，投稿邮箱：editor@dbaplus.cn
热门文章
最新评论
访客 2024年04月08日

如果字段的最大可能长度超过255字节，那么长度值可能…

访客 2024年03月04日

只能说作者太用心了，优秀

访客 2024年02月23日

感谢详解

访客 2024年02月20日

一般干个7-8年（即30岁左右），能做到年入40w-50w；有…

访客 2023年08月20日

230721

活动预告
Copyright © 广州新炬网络技术有限公司 All rights reserved. 粤ICP备2024180830号-3