Java MySQL transaction and executeBatch

Question

I have met a problem that I am unable to figure out.

I have the following SQL code snippet, where I insert multiple rows into a table. It is important that no rows are added on an exception.

The code works as intended, but if the rollback part is removed, some rows are added on an exception, even though connection.commit(); is never invoked. Why is that?

My concern is: What happens if my application is killed by the operating system while invoking a batch insert, and thereby the rollback part is never called. My tests shows that nothing is inserted in that case. But I'm am still stumbled on why some rows are inserted on an exception, if the rollback part is removed.

Any hints on this?

    Connection connection = null;
    PreparedStatement stmt = null;

    try {
        connection = DBConnectionFactory.getInstance().getConnection(JDNI);
        connection.setAutoCommit(false);

        stmt = connection.prepareStatement(
                "INSERT INTO " + TABLE_PREFIX + TABLE_OUTBOX + "...");

        // add multiple statements

        stmt.executeBatch();

        connection.commit();

    } catch (Exception ex) {
        try {
            log.debug(ex);
            log.debug("rolling back insert message batch");
            connection.rollback();
            log.debug("rollback success");
        } catch(Exception e) {
            log.debug("unable to rollback", e);
        }
        throw new TranquilityException("could not insert endpoints!!", ex);
    } finally {
        if (stmt != null) {
            try {
                stmt.close();
            } catch (Exception ex) {
                log.warn("Could not close sql statement", ex);
            }
        }
        if (connection != null) {
            try {
                connection.setAutoCommit(true);
                connection.close();
            } catch (Exception ex) {
                log.error("Could not close sql connection", ex);
            }
        }
    }
}

Answer 1

There are two reasons I can see for this to happen:

• The first is that it appears you are using a connection pool. In this case, your connections are re-used. If you return a connection with an uncommitted transaction to the pool and that connection is checked out by another method at a later point, the transaction could be getting committed when that later method is committed.

• The second (and more likely) is that in your finally block, you are setting connection.setAutoCommit(true); When your method completes, that will cause everything in your connection to be flushed. I know you need to do this for the connection pool, but you should make sure your connection is in a completed state before doing this.