Within JTA transaction (using container managed transaction), executeUpdate mehtod for explicit Query does immediate commit

Question

Within JBOSS 7.1 AS, I'm using container managed transaction. For each request, I do several entity updates. Most of the entities use "insert, merge, refresh" methods from EntityManager to manage the updates. However, there is one entity that uses explicit Query to do "executeUpdate" on the DB (see below for the code snippet). This sql update is immediately commited to the DB and it is not aligned with container managed transaction (like other entity updates). Is there anyway align explicit sql update (the one below) with container-managed-transaction? I'm trying to get rollback to work and this sql update is not being rolledback. All other entity updates and inserts are working fine except this one. Thanks for all your help.

code snippet:

entityManager.createQuery 
 ( "UPDATE Balance a SET a.balanceValue = :newValue WHERE a.balanceId =:balanceId AND a.balanceValue = :currentValue" ) .setParameter("balanceId", cb.getBalanceId()) .setParameter("currentValue", cb.getBalanceValue()).setParameter("newValue", newAmt).executeUpdate();

Additional code: (Code below is using Bean-managed transaction, but i get same behaviour for CMT as well)

            ut.begin();
        ChargingBalance bal2 = entityManager.find(ChargingBalance.class, 13);
        bal2.setResetValue((new Date()).getTime());
        String UPDATE_BALANCE_AND_EXPIRYDATE_EQUAL = "UPDATE ChargingBalanceValue a"
                + "  SET a.balanceValue = :newValue "
                + "  WHERE a.balanceId = :balanceId";

        Query query = entityManager.createQuery(UPDATE_BALANCE_AND_EXPIRYDATE_EQUAL)
                .setParameter("balanceId", 33)
                .setParameter("newValue", 1000l);

        /*The executeUpdate command gets committed to DB before ut.commit is executed */
        query.executeUpdate();

        /* This below only commits changes on ResetValue */
        ut.commit();

        ut.begin();
        ChargingBalance bal = entityManager.find(ChargingBalance.class, 23);
        bal.setResetValue(1011l);

        query = entityManager.createQuery(UPDATE_BALANCE_AND_EXPIRYDATE_EQUAL)
                .setParameter("balanceId", 33)
                .setParameter("newValue", 2000l);
        query.executeUpdate();

        /* This rollback doesn't rollback changes executed by executeUpdate, but it rollbacks ResetValue change */
        ut.rollback();

Try to add some relevant code too, "I do several entity updates. Most of the entities use "insert, merge, refresh", from this not clear how you update entity & how you concluded that most entities use insert, merge, refresh etc. — Nayan Wadekar
I have added some additional code. I'm using Hibernate implementation of JPA 2.0. org.hibernate:hibernate-entitymanager:4.2.7.SP1 — Kumaran Sivasubramaniam
Any help would be much appreciated. I'm stuck on this issue for a while. — Kumaran Sivasubramaniam

Nayan Wadekar Nayan Wadekar · Accepted Answer · 2015-02-24T12:01:28

The executeUpdate command gets committed to DB before ut.commit is executed

It might have probably flushed changes into the database, but not committed as you were in BMT.

You can try roll back & verify if it is really committed & is within transaction.
This below only commits changes on ResetValue

When you execute native or JPQL/HQL query, it will make changes directly into the database & EntityManager might not be aware of those changes.

Therefore managed entities aren't refreshed implicitly by EntityManager & might contain outdated/stale data.
You can go through documentation for more details, below is the exerpt.

JPQL UPDATE queries provide an alternative way for updating entity objects. Unlike SELECT queries, which are used to retrieve data from the database, UPDATE queries do not retrieve data from the database, but when executed, update the content of specified entity objects in the database.

Updating entity objects in the database using an UPDATE query may be slightly more efficient than retrieving entity objects and then updating them, but it should be used cautiously because bypassing the EntityManager may break its synchronization with the database. For example, the EntityManager may not be aware that a cached entity object in its persistence context has been modified by an UPDATE query. Therefore, it is a good practice to use a separate EntityManager for UPDATE queries.

Within JTA transaction (using container managed transaction), executeUpdate mehtod for explicit Query does immediate commit

1 Answers