Java执行SQL存储过程无异常挂起问题求助

First, let's break down the key clues from your problem: the hang occurs either at PreparedStatement.execute() or ResultSet.next(), no exceptions are thrown, it's mostly happening during nightly tests (hard to reproduce), and you're dealing with large datasets + multiple ResultSets from a stored procedure. Here's how to diagnose and fix this:

1. Fix the Stored Procedure Call Mechanism #

You're using PreparedStatement to execute a stored procedure via EXEC procname ?—this is not the ideal approach. JDBC has a dedicated CallableStatement for stored procedure invocations, which has better support for handling multiple ResultSets and timeout behaviors, especially with complex procedures.

Refactored Code Example: #

final String sql = "{call procname(?)}"; // Use standard call syntax
final CallableStatement callableStmt = connection.connection.prepareCall(sql);
callableStmt.setString(1, phrase);
callableStmt.setQueryTimeout(30); // Verify if your driver supports this for stored procs
boolean resultsRemaining = callableStmt.execute();

// Rest of your ResultSet handling logic follows...

Why this helps: Many JDBC drivers don't properly enforce setQueryTimeout() on PreparedStatement when executing stored procedures. CallableStatement is designed for this use case and may resolve the unresponsive timeout issue.

2. Optimize ResultSet & Metadata Handling #

Your current code calls resultSet.getMetaData() inside a loop over LearnableAttribute.values(), which is redundant and could introduce unnecessary database roundtrips or lockups. Plus, you can optimize column lookup to avoid redundant checks:

Optimized ResultSet Processing: #

while (resultsRemaining) {
    final ResultSet resultSet = callableStmt.getResultSet();
    final ResultSetMetaData metaData = resultSet.getMetaData(); // Fetch once per ResultSet
    final int columnCount = metaData.getColumnCount();
    // Pre-map column names to indices for faster lookup
    Map<String, Integer> columnNameToIndex = new HashMap<>();
    for (int i = 0; i < columnCount; i++) {
        columnNameToIndex.put(metaData.getColumnName(i + 1).toUpperCase(), i + 1);
    }

    final boolean resultsEmpty = !resultSet.next();
    for (LearnableAttribute la : LearnableAttribute.values()) {
        final String dbName = la.getDbName().toUpperCase();
        Integer columnIndex = columnNameToIndex.get(dbName);
        if (columnIndex != null) {
            mad.map.put(la, resultsEmpty ? null : resultSet.getString(columnIndex));
        }
    }

    resultsRemaining = callableStmt.getMoreResults();
    resultSet.close(); // Close immediately after processing
}

Key improvements:

• Fetch metadata once per ResultSet instead of per attribute
• Pre-cache column names to indices to avoid repeated loops
• Close ResultSet right after processing to free up database resources faster

3. Address JDBC Driver & Database-Specific Timeout Limitations #

Even with setQueryTimeout(), some drivers (e.g., older SQL Server or Oracle drivers) don't enforce timeouts for stored procedures that run longer than the set limit. Here's what to do:

• Check driver documentation: Confirm if your driver supports query timeouts for stored procedures. If not, add a timeout directly inside your stored procedure (e.g., SET LOCK_TIMEOUT in SQL Server, ALTER SESSION SET QUERY_TIMEOUT in Oracle).
• Upgrade your JDBC driver: Outdated drivers often have bugs related to large datasets and network handling, especially under low-resource conditions (like nightly server backups or traffic spikes).
• Enable driver logging: Turn on debug-level logging for your JDBC driver (e.g., for MySQL, set logger=com.mysql.cj.log.Slf4JLogger; for SQL Server, enable logLevel=FINER). This will help you see if the hang is due to network stalls or unprocessed database responses.

4. Add a Safety Net with Thread-Level Timeouts #

Since JDBC timeouts can be unreliable for long-running procedures, wrap your database call in a thread with an explicit timeout to prevent infinite hangs:

ExecutorService executor = Executors.newSingleThreadExecutor();
Future<Mad> executionFuture = executor.submit(() -> {
    // Your stored procedure execution logic here
    return mad;
});

try {
    // Set a timeout longer than your expected maximum runtime (e.g., 2 hours)
    Mad result = executionFuture.get(2, TimeUnit.HOURS);
} catch (TimeoutException e) {
    // Cancel the hanging task and clean up resources
    executionFuture.cancel(true);
    callableStmt.cancel(); // Attempt to cancel the database query
    connection.connection.close(); // Force close the connection
    // Log the timeout and trigger a retry or alert
} finally {
    executor.shutdown();
}

5. Diagnose Nightly-Specific Issues #

Since the problem occurs mostly at night, investigate these angles:

• Database maintenance tasks: Check if your DB runs nightly backups, index rebuilds, or statistics updates that could lock tables or consume resources, slowing down your stored procedure.
• Network latency: Nighttime might have network throttling or unstable connections between your app and database. Enable network monitoring (e.g., TCP dumps) during nightly tests to spot drops or delays.
• Connection pool health: If using a connection pool (e.g., HikariCP, Apache DBCP), ensure:
  • validationQuery is set (e.g., SELECT 1) to check connection validity before use
  • testOnBorrow or testWhileIdle is enabled to prune stale connections that might hang when used
  • Idle connection timeouts are aligned with your database's connection timeout settings (e.g., MySQL's wait_timeout)

内容的提问来源于stack exchange，提问作者Kyle Seid