How to use Hibernate Session.doWork(...) for savepoints / nested transactions?

Question

I'm using JavaEE/JPA managed transactions with Oracle DB und Hibernate and need to achieve some kind of nested transaction. As far as I've learned such thing is not supported out of the box but I should be able to use savepoints for that purpose.

As suggested by https://mcmap.net/q/112611/-getting-database-connection-in-pure-jpa-setup I've tried the following:

@Transactional(value = TxType.REQUIRES_NEW)
public boolean doImport(Import importer, Row row) throws ImportRowFailedException {
    // stripped code ...
    // We need to try different possibilities from which one may succeed...
    // ...former failures must be rolled back!
    for (Possibility poss : importer.getPossibilities()) {
        if (this.tryPossibility(poss, row)) break;
    }
    // stripped code ...
}

public boolean tryPossibility(Possibility possibility, Row row) {
    try {
        Session session = this.em.unwrap(Session.class);
        session.doWork(new Work() {
            @Override
            public void execute(Connection connection) throws SQLException {
                Savepoint before = connection.setSavepoint();
                if (!possibility.importRow(row)) {
                    connection.rollback(before);
                    throw new ImportRowFailedException();
                }
            }
        });
    }
    catch (ImportRowFailedException ex) {
        return false;
    }
    return true;
}

Upon connection.rollback(before) I get the following exception:

Caused by: java.sql.SQLException: IJ031040: Connection is not associated with a managed connection: org.jboss.jca.adapters.jdbc.jdk8.WrappedConnectionJDK8@40a6a460
    at org.jboss.jca.adapters.jdbc.WrappedConnection.lock(WrappedConnection.java:164)
    at org.jboss.jca.adapters.jdbc.WrappedConnection.rollback(WrappedConnection.java:883)

How must I deal with that?

Rapacious

Answer 1

The initial java.sql.SQLException: IJ031040 seems related to a specific outcome during our import. It later has been replaced with another java.sql.SQLException that forbid the rollback for managed transactions. But I could finally solve the problem by issuing native SQL statements:

// Mark the current state as SAVEPOINT...
Session session = this.em.unwrap(Session.class);
session.doWork(new Work() {
    @Override
    public void execute(Connection connection) throws SQLException {
        connection.prepareStatement("SAVEPOINT TRY_POSSIBILITY").executeUpdate();
    }
});

// 
// Do all the risky changes... verify... decide...
// 

// Rollback to SAVEPOINT if necessary!
session.doWork(new Work() {
    @Override
    public void execute(Connection connection) throws SQLException {
        connection.prepareStatement("ROLLBACK TO SAVEPOINT TRY_POSSIBILITY").executeUpdate();
    }
});

This allows a "nested Transaction" within the larger one and solved my problems.

Rapacious

Answer 2

The problem is that most JDBC Drivers don't really support nested transactions. In your case, you are using JBoss which provides the JTA transaction manager but still relies on the XA ResoureManagers provided by the JDBC providers, as explained in this forum thread.

So, you are better off using doReturningWork instead of doWork and execute the SQL SAVEPOINT commands, like this:

public boolean tryPossibility(Possibility possibility, Row row) {
    Session session = this.em.unwrap(Session.class);
    
    return session.doReturningWork(connection -> {
        connection.prepareStatement("SAVEPOINT IMPORT_ROW").executeUpdate();
        
        boolean importHasSuceed = possibility.importRow(row);
        
        if (!importHasSuceed) {
            connection.prepareStatement("ROLLBACK TO SAVEPOINT IMPORT_ROW").executeUpdate();
        }
        
        return importHasSuceed;
    });
}

Aha