Hibernate Entity manager auto flush before query and commit changes to DB in transaction

Question

I am using Hibernate 3.6.0 with JPA 2 on Jboss AS 6.0.0 final. In an EJB of mine, there's a method which updated entity values and do some query on it. The whole method is running in a BMT transaction. If anything fails, all changes should be rollback and not committed to DB.

The Database is mySql.

Before running JPA query, JPA will auto flush the changed states to DB to prevent any stale data from returning. However, within my method, the auto-flush directly update and commits the changes to DB and even something went wrong afterwards, the changes are not rollback. So I would like to ask if there's wrong configuration in my set up or this is a bug or something.

EJB

@Stateless(mappedName = "MyManagementBean")
    @Local
    @TransactionManagement(TransactionManagementType.BEAN)


    public class MyManagement implements MyManagementLocal,MyManagementRemote {

        @PersistenceUnit(unitName="MyEjb") EntityManagerFactory emf;
        @Resource UserTransaction utx;
        @Resource SessionContext ctx;

        /**
         * Default constructor. 
         */
        public MyManagement () {
            // TODO Auto-generated constructor stub
        }

        public void dosomething(String id) throws Exception
        {

            try {
                utx.begin();    
                em = emf.createEntityManager();

                Myline line = em.find(Myline.class, id);

                line.setStatus("R");

            Stromg q += " from Myline as line ";             
                //auto flush apply here and directly committed to DB...
            Iterator iter = em.createQuery(q).getResultList().iterator();

                em.flush();
                utx.commit();// changes should only commit after this
            }
            catch (Exception e) {
                e.printStackTrace();
                if (utx != null) utx.rollback();
                throw e; // or display error message
            }
            finally {
                em.close();
            }       
        } 
}

persistence.xml

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.0" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">
    <persistence-unit name="MyEjb" transaction-type="JTA">
        <provider>org.hibernate.ejb.HibernatePersistence</provider>
        <jta-data-source>java:MyDS</jta-data-source>
        <class>com.quincy.entity.MyLine</class>

        <properties>
            <property name="hibernate.connection.defaultNChar" value="true"/>
            <property name="hibernate.dialect" value="org.hibernate.dialect.MySQLMyISAMDialect"/>
            <property name="hibernate.ejb.cfgfile" value="META-INF/hibernate.cfg.xml"/>
        </properties>
    </persistence-unit>
</persistence>

hibernate.cfg.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
        "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
        "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
    <session-factory>


        <property name="transaction.manager_lookup_class">org.hibernate.transaction.JBossTransactionManagerLookup</property>


        <!-- Echo all executed SQL to stdout -->
        <property name="show_sql">true</property>       

        <property name="hibernate.max_fetch_depth">3</property>


    </session-factory>
</hibernate-configuration>

mysql-ds.xml

<datasources>

<local-tx-datasource>
    <jndi-name>MyDS</jndi-name>
    <connection-url>jdbc:mysql://10.10.150.57:3306/myds</connection-url>
    <driver-class>com.mysql.jdbc.Driver</driver-class>
    <user-name>user</user-name>
    <password>pwd</password>
    <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.MySQLExceptionSorter</exception-sorter-class-name>
    <metadata>
       <type-mapping>mySQL</type-mapping>
    </metadata>
  </local-tx-datasource>
</datasources>

Upon further investigating, I found out that when ever there's a flush, the dirty changes are written and committed to DB directly. If I remove the flush(), everything works fine. However, there are system triggered flush before query and I think it's necessary.

It seems to me that the db is auto-commited. I have tried to set the property hibernate.connection.autocommit to false but the problem just persist and a EJB warning of violating spec is prompted.

UPDATE: The cause should come from mysql. As if I switch to mssql server, the problem goes away.I also tried mysql with xa-datasource , still no luck...

Solution 1

Problem resolved. The cause is that tables in the mysql is using MyISAM engine by default and tables using this engine do not support transaction. Switching tables to innoDB make thing works. Hope this would be useful for anyone, so they will not waste as much time as I did. :(

Solution 2

What is set as the entityManager FlushModeType

void setFlushMode(FlushModeType flushMode) 
Set the flush mode that applies to all objects contained in the persistence context.

The method takes a enum defined here.

Enum Constant Summary

AUTO
       (Default) Flushing to occur at query execution.



COMMIT
       Flushing to occur at transaction commit.

Also try checking the following

@PersistenceContext(unitName="MyEJB") private EntityManager em

after you declare the Entity manager factory.

Comments

[Luke]

javax persistence api

[Quincy]

If I set the flush mode to COMMIT, the flush wouldn't happen. However, the query returns stale data. The problem right now is the flush is committing changes to DB directly. It is supposed to only flush the changes to DB but not commit.

[Luke]

Can't you execute the query (with data in sync) first, then make your change to the particular line and then commit?

[Quincy]

No. During the query process, there's some kind of checking and may result in failure. I want the changes to rollback in this case. In my understanding, even flush mode is set to AUTO, the changes would only be flushed to be visible to other queries but not directly committed to the DB. So I was wondering if I had done anything wrong with my config. (This is my first time to use JPA API, I was using native hibernate before)

[Quincy]

Upon further investigating, I found out that when ever there's a flush, the dirty changes are written and committed to DB directly. It seems that the em is not really binding to a transaction. However, I still cannot work it out. :/

[Luke]

I'm not 100 % sure what you're trying to acheive so can't really suggest a code solution.

[Quincy]

To simply the problem, is that doing flush() in my case will directly commit changes to db, which is kind of breaking the transaction. And I believe this is not the expected behaviour and look for a solution.

[mmey]

Worked for me as well. To see current engine, use show table status;. To change the engine for a table, use alter table table_name engine=InnoDB;