Subject: Help Needed: Transaction Timeout and SQL Query Execution Issue (Including Logs)

[Tedd Thorn]

I'm reaching out for assistance with a perplexing issue we're encountering in our project, and I'm hoping someone here might be able to provide some insight or guidance.

Problem Description: We're using Java 11, WildFly 20.0.1, and MS-SQL in our project. Recently, we've been facing an issue where users encounter the error java.sql.SQLTimeoutException: The query has timed out when attempting to open certain windows for editing information, such as Clients or State, in our web application. This error occurs specifically when loading the audit.jsp file, which is used in four similar windows as an iframe. However, the error only appears in two of these windows, despite the functionality being the same.

Error Details: Here's a snippet of the stacktrace we're seeing:

javaCopy code

[Stacktrace Snippet] public static ArrayList load(Connection conn, String query, Class modelClass, PagingContext pc) throws SQLException { try (Statement stmt = conn.createStatement(); ResultSet rset = stmt.executeQuery(query.toUpperCase())) { return load(rset, modelClass, pc); } }

Additional Observations:

• Executing the problematic SQL queries manually through MS SQL Server works without any issues.
• 
  
• Strangely, this problem is isolated to the client's production and UAT environments. We haven't encountered it on our localhost or UAT.
• 
  
• We attempted a solution suggested in a similar thread but to no avail.

Request for Assistance: We're seeking any insights, suggestions, or experiences that might help us pinpoint and resolve this issue. If anyone has encountered a similar problem or has ideas on what could be causing it, we would greatly appreciate your input.

Logs: To provide further context, I'm attaching relevant log entries from our system. Please find the log snippets attached to this email.

Conclusion: Thank you in advance for any assistance you can provide. Please let me know if you need any additional information to help diagnose the problem.

Best regards

[Tedd Thorn]

Error Details: Here's a snippet of the stacktrace we're seeing:

public static ArrayList load(Connection conn, String query, Class modelClass, PagingContext pc) throws SQLException {
        try (Statement stmt = conn.createStatement(); ResultSet rset = stmt.executeQuery(query.toUpperCase())) {
            return load(rset, modelClass, pc);
        }
    }

вторник, 6 февраля 2024 г. в 17:29:03 UTC+1, Tedd Thorn:

[Michael Musgrove]

I'm guessing here, but if you are using JTA with more than one resource then it sounds like you need to set a longer timeout via the WildFly config file: https://docs.wildfly.org/30/wildscribe/subsystem/datasources/xa-data-source/index.html#attr-xa-resource-timeout.

[Michael Musgrove]

There's also a query timeout property on the datasource: https://docs.wildfly.org/30/wildscribe/subsystem/datasources/data-source/ExampleDS/index.html#attr-query-timeout
And if you aren't using JTA then you could still experiment by enabling jta on your datasource and use the attr-xa-resource-timeout property that I mentioned (https://docs.wildfly.org/30/wildscribe/subsystem/datasources/data-source/ExampleDS/index.html#attr-jta) - but I'd start out by looking at the attr-query-timeout property.