IGNITE issue: java.sql.SQLException: Transaction is already completed

Question

I have an Ignite DB with 2 tables created with settings "ATOMICITY=TRANSACTIONAL_SNAPSHOT".

• Table #1 "enforcer": store about 2 millions "command" has STATUS column (PENDING/PROCESSED) and PARTITION column (1-16).
• Table #2: store results of commands. I have 16 JAVA instances of a service, each of them has a different partition number 1-16, and will pick about 100 PENDING commands from Table #1 (filtered by their partition) each round, do some tasks defined in each command and insert the results into Table #2. The STATUS of command will be updated PROCESSED after that.

After some rounds, some of instances cannot pick their commands. Select query throws an exception "java.sql.SQLException: Transaction is already completed". Could someone please help me? I have searched about this kind of exception but there is no result.

Many thanks.

Here is my load command methods.

private String SELECT_SQL = "select ID, PARTITION, SHOP_CODE, REPORT_DATE, " + "ENFORCE_FROM, PROCESS_STATUS, FEE_TYPE, ACTION_TYPE, AMOUNT, HASH_VALUE " + " from stg.shop_kpi_enforcer " + " where ENFORCE_FROM >= ? and PARTITION = ? and PROCESS_STATUS = ? ";

public List<T> load(Date timeOffset, int batchSize, int partition)
        throws Exception {
    List<T> entities;

    String sql = SELECT_SQL + " order by SHOP_CODE, REPORT_DATE, ENFORCE_FROM";
    if (batchSize > 0) {
        sql = sql + " limit " + batchSize;
    }

    PreparedStatement ps = null;
    ResultSet rs = null;
    try {
        ps = this.connectionManager
                .openConnection()
                .prepareStatement(sql);
        ps.setTimestamp(1, new Timestamp(timeOffset.getTime()));
        ps.setInt(2, partition);
        ps.setInt(3, ProcessStatus.PENDING.label());

        rs = ps.executeQuery();
        entities = this.databaseToDomain(rs);
    } catch (Exception e) {
        Sentry.captureException(e);
        log.error(">>> Load enforcers error: "+ e);
        throw e;
    } finally {
        this.connectionManager.close(rs);
        this.connectionManager.close(ps);
    }
    log.info("Load enforcers from Ignite.");
    return entities;
}

There is my actual results:

14:50:42.996 [main] INFO v.g.o.s.s.a.o.IgniteConnectionManager - >>> Connection opened: org.apache.ignite.internal.jdbc.thin.JdbcThinConnection@18f20260 14:50:43.010 [main] ERROR v.g.o.s.s.a.o.IgniteEnforcerRepository - >>> Load enforcers error: java.sql.SQLException: Transaction is already completed.

Answer 1

TRANSACTIONAL_SNAPSHOT aka MVCC feature was in beta state and has been deprecated sine Ignite 2.12. There is no point in using it because of instability, just like in your question.

Consider switching to the recommended TRANSACTIONAL mode. It doesn't support SQL transactions, therefore consider rewriting your code to KV API if required.