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

user_173689 0
Viewed 2838

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://stackoverflow.com/a/7626387/173689 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?

savepoints transactions oracle hibernate java
2 Answers

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;
    });
}
user_10251180
Related