java Continuos数据源崩溃

Question

我正在开发一个web应用程序，使用JavaServerFaces进行表示，使用JavaServerFaces作为容器，使用MySQL 8作为数据库

我已经部署了MySQL连接器8.0.15（即.jar文件），并为数据源设置了参数，如Wildfly的用户指南所述。 详情如下：

• com。mysql。希杰。jdbc。驱动程序类的驱动程序
• 最小池大小：10
• 最大泳池面积：20
• 泳池补充：真的吗

现在，问题是：最简单的查询的执行非常慢（作为简单查询，我指的是类似（select field from table，其中表有20行）

在所有用例中，经过一定数量的交互后，数据源变为空，解决问题的唯一方法是重新启动容器

我希望在一个while循环中多次执行的查询（因为我需要增加一个时间戳并重新执行查询）一旦涉及144行以上（在一个3行的联接表中）就会崩溃

在这种情况下，执行10分钟后，我收到以下消息：

javax.ejb.EJBTransactionRolledbackException: javax.transaction.RollbackException: WFLYEJB0447:
Transaction 'Local transaction (delegate=TransactionImple < ac, BasicAction: 0:ffffc0a80107:1a1a9a9e:5d85e7db:17 status: ActionStatus.ABORTED >,
owner=Local transaction context for provider JBoss JTA transaction provider)' was already rolled back

下面是堆栈跟踪的一些行（太长了，控制台无法维护所有行）

涉及的最长表格有20万行

下面是EJB声明

@Remote
public interface DatabaseDriver{

...

}

@Stateful
public class DatabaseDriverImpl implements DatabaseDriver {

...

    @Resource(lookup = "java:/MySqlDS")
    DataSource ds;

...

}

下面是崩溃的方法

public List<AggregateResult> getAggregatePrecipitation(String stationSn, Timestamp startDate, Timestamp stopDate, long scaleFactor){

    if(GlobalVariables.debug) {
        System.out.println(TAG + " enter getAggregatePrecipitation");
    }

    //Converto lo scaleFactor da ore a millisecondi
    scaleFactor = scaleFactor * 60 * 60 * 1000;

    //Converto i timestamp passati in millisecondi
    long startMillis = startDate.getTime();
    long stopMillis = stopDate.getTime();

    //Variablie incrementale
    long currStopMillis = startMillis + scaleFactor;

    //Risultato
    float currRes = 0;
    AggregateResult apr = null;
    List<AggregateResult> res = new ArrayList<AggregateResult>();

    Connection connection = null;

    try {

        connection = ds.getConnection();

        if(testConnection(connection)) {

            if(GlobalVariables.verbose) {
                System.out.println(TAG + " execute query");
            }

            while(currStopMillis < stopMillis) {

                startDate = new Timestamp(startMillis);
                stopDate = new Timestamp(currStopMillis);

                CallableStatement cs = connection.prepareCall("{call " + "GET_AGGREGATE_PRECIPITATION(?, ?, ?, ?)}"); 
                cs.setString(1, stationSn);
                cs.setTimestamp(2, startDate);
                cs.setTimestamp(3, stopDate);
                cs.registerOutParameter(4, java.sql.Types.FLOAT);
                cs.execute();

                currRes = cs.getFloat(4);
                apr = new AggregateResult(stopDate, currRes);
                res.add(apr);

                startMillis = currStopMillis;
                currStopMillis += scaleFactor;
            }

            if(GlobalVariables.verbose) {
                System.out.println(TAG + " query executed. Result: ");

                for(AggregateResult a : res) {
                    System.out.println(a);
                }
            }
        }

    }
    //TODO: Gestire l'eccezione
    catch(SQLException e) {
        e.printStackTrace();
    }
    finally {
        connection = null;
    }

    if(GlobalVariables.debug) {
        System.out.println(TAG + " exit getAggregatePrecipitation");
    }

    return res;
}

以及数据库中的存储过程

BEGIN
    set aggregatePrecipitation = (
        select sum(RG.value)
        from datum D, rain_gauge_datum RG, production P 
        where D.id = RG.id and P.ref_datum = D.id and D.data_type = 'RG'
        and P.ref_station = stationSn 
        and D.creation_date >= startDate and D.creation_date <= stopDate
    );
END

记录例外情况：

11:11:36,220 ERROR [org.jboss.as.ejb3] (default task-1) WFLYEJB0487: Unexpected invocation state 0
11:11:36,220 WARN  [com.arjuna.ats.arjuna] (default task-1) ARJUNA012077: Abort called on already aborted atomic action 0:ffffc0a80107:1a1a9a9e:5d85e7db:17
11:11:36,221 ERROR [org.jboss.as.ejb3.invocation] (default task-1) WFLYEJB0034: EJB Invocation failed on component DatabaseDriverImpl for method public abstract java.util.List it.idrologiaUnipa.dao.DatabaseDriver.getAllData(java.lang.String,java.sql.Timestamp,java.sql.Timestamp): javax.ejb.EJBTransactionRolledbackException: javax.transaction.RollbackException: WFLYEJB0447: Transaction 'Local transaction (delegate=TransactionImple < ac, BasicAction: 0:ffffc0a80107:1a1a9a9e:5d85e7db:17 status: ActionStatus.ABORTED >, owner=Local transaction context for provider JBoss JTA transaction provider)' was already rolled back

...

Caused by: javax.transaction.RollbackException: WFLYEJB0447: Transaction 'Local transaction (delegate=TransactionImple < ac, BasicAction: 0:ffffc0a80107:1a1a9a9e:5d85e7db:17 status: ActionStatus.ABORTED >, owner=Local transaction context for provider JBoss JTA transaction provider)' was already rolled back
    at org.jboss.as.ejb3@17.0.1.Final//org.jboss.as.ejb3.tx.CMTTxInterceptor.endTransaction(CMTTxInterceptor.java:95)
    ... 119 more

...

Caused by: javax.transaction.RollbackException: WFLYEJB0447: Transaction 'Local transaction (delegate=TransactionImple < ac, BasicAction: 0:ffffc0a80107:1a1a9a9e:5d85e7db:17 status: ActionStatus.ABORTED >, owner=Local transaction context for provider JBoss JTA transaction provider)' was already rolled back
    at org.jboss.as.ejb3@17.0.1.Final//org.jboss.as.ejb3.tx.CMTTxInterceptor.endTransaction(CMTTxInterceptor.java:95)
    ... 119 more

然后数据源变量变为null

11:51:18,749 ERROR [stderr] (default task-2) java.sql.SQLException: javax.resource.ResourceException: IJ000453: Unable to get managed connection for java:/MySqlDS

...
11:51:18,766 ERROR [stderr] (default task-2) Caused by: javax.resource.ResourceException: IJ000453: Unable to get managed connection for java:/MySqlDS
11:51:18,766 ERROR [stderr] (default task-2)    at org.jboss.ironjacamar.impl@1.4.16.Final//org.jboss.jca.core.connectionmanager.AbstractConnectionManager.getManagedConnection(AbstractConnectionManager.java:690)
11:51:18,766 ERROR [stderr] (default task-2)    at org.jboss.ironjacamar.impl@1.4.16.Final//org.jboss.jca.core.connectionmanager.tx.TxConnectionManagerImpl.getManagedConnection(TxConnectionManagerImpl.java:440)
11:51:18,766 ERROR [stderr] (default task-2)    at org.jboss.ironjacamar.impl@1.4.16.Final//org.jboss.jca.core.connectionmanager.AbstractConnectionManager.allocateConnection(AbstractConnectionManager.java:789)
11:51:18,766 ERROR [stderr] (default task-2)    at org.jboss.ironjacamar.jdbcadapters@1.4.16.Final//org.jboss.jca.adapters.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:151)
11:51:18,766 ERROR [stderr] (default task-2)    ... 132 more
11:51:18,767 ERROR [stderr] (default task-2) Caused by: javax.resource.ResourceException: IJ000655: No managed connections available within configured blocking timeout (30000 [ms])
11:51:18,767 ERROR [stderr] (default task-2)    at org.jboss.ironjacamar.impl@1.4.16.Final//org.jboss.jca.core.connectionmanager.pool.mcp.SemaphoreConcurrentLinkedDequeManagedConnectionPool.getConnection(SemaphoreConcurrentLinkedDequeManagedConnectionPool.java:570)
11:51:18,767 ERROR [stderr] (default task-2)    at org.jboss.ironjacamar.impl@1.4.16.Final//org.jboss.jca.core.connectionmanager.pool.AbstractPool.getTransactionNewConnection(AbstractPool.java:714)
11:51:18,767 ERROR [stderr] (default task-2)    at org.jboss.ironjacamar.impl@1.4.16.Final//org.jboss.jca.core.connectionmanager.pool.AbstractPool.getConnection(AbstractPool.java:613)
11:51:18,767 ERROR [stderr] (default task-2)    at org.jboss.ironjacamar.impl@1.4.16.Final//org.jboss.jca.core.connectionmanager.AbstractConnectionManager.getManagedConnection(AbstractConnectionManager.java:624)
11:51:18,767 ERROR [stderr] (default task-2)    ... 135 more