JDBC游标读取失效引发内存溢出问题的排查剖析

JDBC游标读取失效引发内存溢出问题的排查解析

问题呈现

程序采用游标进行分批次读取MySQL的数据，然而程序所在的容器出现了内存溢出（OOM）的情况。

基础信息

MySQL版本：8.0.25
JDBC版本：8.0.25

JDBC的配置情况如下：

connectionProperties=useUnicode=true;autoReconnect=true;defaultFetchSize=800;useServerPrepStmts=false;rewriteBatchedStatements=true;useCompression=true;useCursorFetch=true;allowMultiQueries=true

批量程序出现OOM的日志情况：

问题剖析

获取到dump下来的内存快照后，使用jdk自带的Java visualVM打开，找到右侧最大的对象：
发现java.lang.Object[]是最大的，点击后看到里面存的是ByteArrayRow类型对象，这是数据库的游标对象，表明在查询数据库的过程中内存已经溢出，还没来得及转换成实体类，说明此时游标读失效。

通过查看堆栈上的线程报错信息，显示代码的流程调用的是ClientPreparedStatement类的方法，没有调用ServerPreparedStatement类的方法，是由客户端来执行的，此时属于普通读。

利用游标读的demo进行测试，发现游标读的调用走的是ServerPreparedStatement类的方法（下图第3、4行），然后调用ServerPreparedQuery类的ServerPreparedQuery方法（下图第1行）。

查看源码，ServerPreparedQuery方法中调用了packet.writeInteger(IntegerDataType.INT1,OPEN_CURSOR_FLAG)方法来进行游标读。

ClientPreparedStatement：查询是在客户端进行准备的。这意味着所有的SQL语句处理，包括参数替换，都在客户端完成，然后作为一个整体发送到服务器，只能进行普通读。

ServerPreparedStatement：查询是在服务器端进行准备的。这意味着SQL语句和其参数在服务器上被处理，能够利用服务器的某些优化特性，可以进行普通读、游标读、流式读。

进一步探究，PreparedStatement的具体实现何时确定是ClientPreparedStatement还是ServerPreparedStatement？

在调用Connection.prepareStatement()或Connection.prepareStatement(String sql,
int resultSetType, int
resultSetConcurrency)等方法时，JDBC驱动会依据当前的配置以及数据库服务器的能力来确定使用哪种PreparedStatement实现。

@Override
public java.sql.PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency) throws SQLException {
    synchronized (getConnectionMutex()) {
        checkClosed();

        //
        // FIXME: Create warnings if can't create results of the given type or concurrency
        //
        ClientPreparedStatement pStmt = null;

        boolean canServerPrepare = true;

        String nativeSql = this.processEscapeCodesForPrepStmts.getValue() ? nativeSQL(sql) : sql;

        if (this.useServerPrepStmts.getValue() && this.emulateUnsupportedPstmts.getValue()) {
            canServerPrepare = canHandleAsServerPreparedStatement(nativeSql);
        }

        if (this.useServerPrepStmts.getValue() && canServerPrepare) {
            if (this.cachePrepStmts.getValue()) {
                synchronized (this.serverSideStatementCache) {
                    pStmt = this.serverSideStatementCache.remove(new CompoundCacheKey(this.database, sql));

                    if (pStmt != null) {
                        ((com.mysql.cj.jdbc.ServerPreparedStatement) pStmt).setClosed(false);
                        pStmt.clearParameters();
                    }

                    if (pStmt == null) {
                        try {
                            pStmt = ServerPreparedStatement.getInstance(getMultiHostSafeProxy(), nativeSql, this.database, resultSetType,
                                    resultSetConcurrency);
                            if (sql.length() < this.prepStmtCacheSqlLimit.getValue()) {
                                ((com.mysql.cj.jdbc.ServerPreparedStatement) pStmt).isCacheable = true;
                            }

                            pStmt.setResultSetType(resultSetType);
                            pStmt.setResultSetConcurrency(resultSetConcurrency);
                        } catch (SQLException sqlEx) {
                            // Punt, if necessary
                            if (this.emulateUnsupportedPstmts.getValue()) {
                                pStmt = (ClientPreparedStatement) clientPrepareStatement(nativeSql, resultSetType, resultSetConcurrency, false);

                                if (sql.length() < this.prepStmtCacheSqlLimit.getValue()) {
                                    this.serverSideStatementCheckCache.put(sql, Boolean.FALSE);
                                }
                            } else {
                                throw sqlEx;
                            }
                        }
                    }
                }
            } else {
                try {
                    pStmt = ServerPreparedStatement.getInstance(getMultiHostSafeProxy(), nativeSql, this.database, resultSetType, resultSetConcurrency);

                    pStmt.setResultSetType(resultSetType);
                    pStmt.setResultSetConcurrency(resultSetConcurrency);
                } catch (SQLException sqlEx) {
                    // Punt, if necessary
                    if (this.emulateUnsupportedPstmts.getValue()) {
                        pStmt = (ClientPreparedStatement) clientPrepareStatement(nativeSql, resultSetType, resultSetConcurrency, false);
                    } else {
                        throw sqlEx;
                    }
                }
            }
        } else {
            pStmt = (ClientPreparedStatement) clientPrepareStatement(nativeSql, resultSetType, resultSetConcurrency, false);
        }

        return pStmt;
    }
}

通过debug发现，会走到16行的 canServerPrepare =
canHandleAsServerPreparedStatement(nativeSql);
这表明在jdbc配置useServerPrepStmts=true是生效的，emulateUnsupportedPstmts系统默认值就是true，判断成立。

继续debug，进入canHandleAsServerPreparedStatement方法：

private boolean canHandleAsServerPreparedStatement(String sql) throws SQLException {
    if (sql == null || sql.length() == 0) {
        return true;
    }

    if (!this.useServerPrepStmts.getValue()) {
        return false;
    }

    boolean allowMultiQueries = this.propertySet.getBooleanProperty(PropertyKey.allowMultiQueries).getValue();

    if (this.cachePrepStmts.getValue()) {
        synchronized (this.serverSideStatementCheckCache) {
            Boolean flag = this.serverSideStatementCheckCache.get(sql);

            if (flag != null) {
                return flag.booleanValue();
            }

            boolean canHandle = StringUtils.canHandleAsServerPreparedStatementNoCache(sql, getServerVersion(), allowMultiQueries,
                    this.session.getServerSession().isNoBackslashEscapesSet(), this.session.getServerSession().useAnsiQuotedIdentifiers());

            if (sql.length() < this.prepStmtCacheSqlLimit.getValue()) {
                this.serverSideStatementCheckCache.put(sql, canHandle ? Boolean.TRUE : Boolean.FALSE);
            }

            return canHandle;
        }
    }

    return StringUtils.canHandleAsServerPreparedStatementNoCache(sql, getServerVersion(), allowMultiQueries,
            this.session.getServerSession().isNoBackslashEscapesSet(), this.session.getServerSession().useAnsiQuotedIdentifiers());
}

cachePrepStmts默认值是false，前面的判断不成立，直接走到最后的StringUtils类的canHandleAsServerPreparedStatementNoCache方法。

public static boolean canHandleAsServerPreparedStatementNoCache(String sql, ServerVersion serverVersion, boolean allowMultiQueries,
        boolean noBackslashEscapes, boolean useAnsiQuotes) {

    // Can't use server-side prepare for CALL
    if (startsWithIgnoreCaseAndNonAlphaNumeric(sql, "CALL")) {
        return false;
    }

    boolean canHandleAsStatement = true;

    boolean allowBackslashEscapes = !noBackslashEscapes;
    String quoteChar = useAnsiQuotes ? "\"" : "'";

    if (allowMultiQueries) {
        if (StringUtils.indexOfIgnoreCase(0, sql, ";", quoteChar, quoteChar,
                allowBackslashEscapes ? StringUtils.SEARCH_MODE__ALL : StringUtils.SEARCH_MODE__MRK_COM_WS) != -1) {
            canHandleAsStatement = false;
        }
    } else if (startsWithIgnoreCaseAndWs(sql, "XA ")) {
        canHandleAsStatement = false;
    } else if (startsWithIgnoreCaseAndWs(sql, "CREATE TABLE")) {
        canHandleAsStatement = false;
    } else if (startsWithIgnoreCaseAndWs(sql, "DO")) {
        canHandleAsStatement = false;
    } else if (startsWithIgnoreCaseAndWs(sql, "SET")) {
        canHandleAsStatement = false;
    } else if (StringUtils.startsWithIgnoreCaseAndWs(sql, "SHOW WARNINGS") && serverVersion.meetsMinimum(ServerVersion.parseVersion("5.7.2"))) {
        canHandleAsStatement = false;
    } else if (sql.startsWith("/* ping */")) {
        canHandleAsStatement = false;
    }

    return canHandleAsStatement;
}

canHandleAsServerPreparedStatementNoCache是在不开启缓存的情况下判断是否能使用ServerPreparedStatement。

根据后续反馈，游标读并非一直不生效，只是在运行某个sql的时候不生效，为了隐私，这里将这个sql简化为

select * from t;

由于sql不是以CALL开头而且jdbc的参数allowMultiQueries=true会走到15行的代码，indexOfIgnoreCase方法的作用是在字符串中查找子字符串的位置，忽略大小写，并有选择地跳过由给定标记限定的文本或在注释中的文本。
这行代码的意思是在sql语句中查找;的位置，忽略''符号之间的内容，如果不存在，即返回-1，就允许使用ServerPreparedStatement，否则使用ClientPreparedStatement。经过debug，确实会走到这里。

问题总结

问题发生路径：开启allowMultiQueries=true且当前sql带有分号 ——>

canHandleAsServerPreparedStatementNoCache返回值为false ——>

canHandleAsServerPreparedStatement返回值为false ——>

执行 (ClientPreparedStatement) clientPrepareStatement(nativeSql, resultSetType,
resultSetConcurrency, false)返回ClientPreparedStatement ——>

客户端执行普通读。

使用建议

1. 默认情况下书写SQL时去掉后面的分号；
2. 不要开启allowMultiQueries=true，其默认值为false（默认设置下会影响到需要多语句执行的场景，可根据实际需要临时开启）。

全文完。