求助：生产环境随机出现scrollable result sets are not enabled错误

Hey there, I’ve dealt with similar flaky scrollable result set issues before—random bugs are always the worst! Let’s walk through possible fixes based on your code snippet and common production pitfalls.

First, let’s reference your method for context:

private ScrollableResults scrollableResults() { 
    return configureCriteria()
        .setCacheMode(CacheMode.IGNORE)
        .setFetchSize(batchSize)
        .setFlushMode(FlushMode.MANUAL)
        .setReadOnly(readonly)
        .scroll(ScrollMode.FORWARD_ONLY); 
}

1. Fix JDBC Connection URL Configuration #

Most databases require explicit parameters to enable scrollable result sets. If your connection URL is missing these, the driver might fall back to non-scrollable mode randomly (especially with connection pooling where connections are reused).

• For MySQL: Add useCursorFetch=true and defaultFetchSize=<your-batch-size> to your JDBC URL. Example:

  jdbc:mysql://your-db-host:3306/your-db?useCursorFetch=true&defaultFetchSize=1000
  

• For PostgreSQL: Ensure defaultRowFetchSize is set, and confirm your driver version supports scrollable forward-only results (PostgreSQL’s driver usually handles this well, but older versions might need tweaks).

2. Verify Hibernate Configuration #

Hibernate needs to pass the right flags to the JDBC driver. Add this property to your Hibernate config (e.g., hibernate.cfg.xml or application properties):

hibernate.jdbc.scrollable_resultset=true

Also, double-check that your Hibernate version is compatible with your database driver version—mismatches can cause random behavior like this.

3. Fix Connection Pool Initialization #

If you’re using a connection pool (HikariCP, C3P0, etc.), some connections might not be initialized with the required scrollable result set settings.

• For HikariCP: Add the connection property directly in your pool config:

  hikariDataSource.addDataSourceProperty("useCursorFetch", "true");
  hikariDataSource.addDataSourceProperty("defaultFetchSize", batchSize);
  

• For other pools, use the connection initialization SQL or pool-specific properties to ensure every connection has the necessary settings before being used.

4. Rule Out Context Conflicts #

Your method sets setReadOnly(readonly) and FlushMode.MANUAL—while these shouldn’t directly break scrollable results, it’s worth testing:

• Temporarily remove setReadOnly(readonly) in a staging environment to see if the error stops (some drivers have quirks with read-only connections and scrollable sets).
• Confirm that the batchSize value isn’t being set to an invalid number (e.g., 0 or negative) randomly, which could confuse the driver.

Quick Test Tip #

To narrow down the issue, add logging for the JDBC connection properties when the error occurs. This will help you confirm if the problematic connection is missing the scrollable result set flags.

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