Non-atomic behaviour Eclipselink- DB transactions half committed

Question

We are seeing strange behaviour in our DB transactions- they are not behaving atomically. We use MySQL 5.6.25 Innodb, Eclipselink 2.5.2 as JPA provider and HikariCP 2.6.2 as the connection pool.

This problem surfaces when Eclipselink fails to acquire a connection from the pool during a entityManager.flush call. For sometime, we were swallowing this exception because entry to a particular table was being made on best-effort basis- a sort of audit mode you can say. However,this led to the case where only a part of the transaction was committed- out of 5, only 1,2 or 3 entries were persisted.

To be sure, here are is the flow of events

    tx.begin();
    em.persist(entity1);
    try{
        em.persist(entity2);
        em.flush(); ---> this is where connection acquisition fails.
    } catch(Throwable tx){
     //do nothing, except log.
    }
    em.persist(entity3);
    em.flush();
    em.persist(entity4);
    em.flush();
    em.persist(entity5);
    em.flush();

    em.persist(entity6);
    tx.commit();

We are seeing transactions committed till entity3,entity4,entity5, when connection acquisition again fails at some point in the later flushes.

Can anyone point to how exactly this is happening?

different mysql backends have different behaviour, some not have ACID transactions. What You use (innodb, myisam etc) — Jacek Cz, Sep 30 '17 at 16:08
You cannot catch an exception from flush and then continue with the transaction. — Chris, Sep 30 '17 at 16:31
Actually, when I'm trying to re-create the situation locally with a slightly different scenario (I'm killing connections from MySQL, instead of making the pool occupied which happens in production), then yes, it's not allowing the transaction to continue. But it's happening in production, and when we allowed the exception to propagate then the discrepancy went away. Transactions committed in full or did not. — user20507, Sep 30 '17 at 16:41
I wasn't clear. JPA does not support continuing with the EntityManager if there are any persistence exceptions, especially a problem with the connection. Your code needs to change. — Chris, Sep 30 '17 at 16:58

score 0 · Answer 1 · answered Oct 02 '17 at 07:59

The main problem you face is that the connection is not available. Exceptions of that kind must lead to a rollback of the transaction. Catching those transactions unhandled will change the behaviour of the transaction. The exception during the first em.flush() also obliterates the first em.persist(entity1) which you did not want to lose.

So the solution is to add em.flush() before the try, to make sure, that persisting of entity1 either is guaranteed or leads to an exception which will lead to rollback of the complete transaction.

I would not recommend this kind of solution though.

If persisting of entity2 is optional then normally you can to do that in an extra transaction which means the system will need (for a short time) an additional db-connection for that.

How to create an extra transaction? In Ejb you use the REQUIRES_NEW annotated methods. I am not sure what kind of TransactionManagement you are using here, but I am quite sure that there should be the possibility to create a kind of separate transactions (not to be confused by nested transactions!!).

Non-atomic behaviour Eclipselink- DB transactions half committed

1 Answers1