How to make Hibernate not to rollback when an exception occurs

Question

The following SQL if run in MSSQL will insert the 1st and 3rd rows successfully:

BEGIN TRAN
INSERT ... -- valid data
INSERT ... -- invalid data (e.g. over column width)
INSERT ... -- valid data
COMMIT

Even though the second row fails within the transaction, you can still see the two rows with some valid data after the commit in the table.

However, when trying something similar in Hibernate, it rollbacks the whole transaction. Is there a way to tell Hibernate not to rollback on failed rows and commit the rest as same as how MSSQL does it?

e.g.

EntityTransaction transaction = em.getTransaction();
transaction.begin();
em.persist(new MyEntity("good"));
em.persist(new MyEntity("too long"));
em.persist(new MyEntity("good"));
transaction.commit();

Answer 1

This is not possible within the same transaction. Hibernate simply doesn't allow this. An error in a statement leads to an exception, which Hibernate cannot recover from. From the manual:

If the JPA EntityManager or the Hibernate-specific Session throws an exception, including any JDBC SQLException, you have to immediately rollback the database transaction and close the current EntityManager or Session.

Certain methods of the JPA EntityManager or the Hibernate Session will not leave the Persistence Context in a consistent state. As a rule of thumb, no exception thrown by Hibernate can be treated as recoverable. Ensure that the Session will be closed by calling the close() method in a finally block.

Now this is a restriction (design decision) of Hibernate and not of the underlying JDBC or database stack. So what you want is perfectly possible using JDBC directly. If it is really important for you to get that behaviour, you might consider using JDBC calls for this section of the code. There you can do it exactly like in the SQL client: open transaction, issue statements, catching any exceptions manually and "ignoring" them, and at the end committing the transaction.

Example code:

Session session = em.unwrap(Session.class);
session.doWork(connection -> {
    // manual commit mode
    connection.setAutoCommit(false);

    executeInsertIgnoringError(connection, new Object[]{123, null, "abc"});
    executeInsertIgnoringError(connection, new Object[]{....});
    ...

    connection.commit();
});



private void executeInsertIgnoringError(Connection connection, Object[] values) {
    try (PreparedStatement stmt = 
           connection.prepareStatement("INSERT INTO MY_ENTITY VALUES (?, ?, ?, ...)")) {
        for (int i = 0; i < values.length; i++) {
            // PreparedStatement is indexed from 1
            stmt.setObject(i+1, values[i]);
        }
        stmt.executeUpdate();
    } catch (Exception e) {
        log.warn("Error occurred, continuing.");
    }
}

Answer 2

The way i did it is to divide your logic into diferent functions, and open the transaction inside the persisting function instead of the main one.

The main problem I see in your code is that you're defining a block transaction insead of opening a transaction for each operation.

Here's my snippet:

persistEntity(new MyEntity("good"));
persistEntity(new MyEntity("bad"));
persistEntity(new MyEntity("good"));

...

private void persistEntity(MyEntity entity){
    EntityTransaction transaction = em.getTransaction();
    transaction.begin();
    em.persist(entity);
    transaction.commit();    
}

This way it will rollback just for the bad entity and keep going with the other. You can also add a try catch inside the persistEntity method, if you want to log the exception.

Fun fact, If you're using Spring you could create another @Component for the persist operations and only add @Transactional to the persisting method, this way you don't have to manage the transactions yourself.

Answer 3

Don't do so, that is idiomatically wrong, at first just review the real scope of your transactions.

Answer 4

You could write the code to run one statement at a time with autocommit on and not use @Transactional... Then perhaps catch any exceptions and throw them away as you go. But pretty much everything in that sentence is troublesome to even think about as a responsible developer and it would affect your entire app. Flavius's post would be a little more granular in doing something similar with explicitly smaller transactions and is a good way to go about it too.

As others have been commenting it's not a long term great plan and goes against so many ways to write programs correctly and the benefits and purpose of transactions. Perhaps if you plan to only use this as a one off data ingestion plan you could but again be very wary of using these patterns in a production grade app.

Having been sufficiently alarmed, you can read more about auto commit here and also be sure to read through the post links on why you probably shouldn't use it.

Spring JPA - No transaction set autocommit 'true'

Answer 5

You can do that by adding below property in hibernate config xml file

<property name="hibernate.connection.autocommit" value="true"/>

If you could use @Transactional annotation then

@Transactional(dontRollbackOn={SQLException.class, NOResultException.class})

Then I would suggest one some change in your code. It's better if you add your entities in a loop and catch exception on each transaction.