Native query executeUpdate seems to commit data in Spring transaction

Question

my usecase is following: When creating an application user (EntityManager.persist) I also have to create a DB user and grant him privileges (that's why I need hibernate nativeQuery).

I have a Spring @Transactional method which calls my DAO with both calls:

@Transactional
public Integer createCompany(Company company) throws Exception {
    companyDao.createReportUser(ReportUser user);
    ...
}

My DAO method looks like this:

getEm().persist(companyReportsUser);
getEm().createNativeQuery("CREATE USER user1@localhost IDENTIFIED BY :password").setParameter("password", password).executeUpdate();
getEm().createNativeQuery("GRANT SELECT ON appdb.v_company TO user1@localhost").executeUpdate();
//several grants

Now, as soon as the first line with executeUpdate() is executed I can see persisted companyReportsUser in database along with DB user (user1@localhost).

All the nativeQueries are executed and immediately commited one by one. Since they are commited, they cannot be rolled back. There is no auto-commit parameter set anywhere in my configuration so I assume it is 'false' as found in Hibernate docs.

1. I've tested @Transactional behavior without native queries and it works as supposed to (transaction is rolled back when I throw an RuntimeException and no data are inserted to database)

2. When debugging I've seen that persist operation delays execution when it is invoked in a running transaction.

3. Native query seems to create and execute a PreparedStatement immediately (at least I didn't find a queue of any kind.

4. I suppose that I might not get the interaction between hibernate native query and Spring transaction, but I've took my time reading Spring and Hibernate docs regarding transactions and native queries and did not found anything that would help me.

5. Maybe there is a better way to create a database user and grant privileges than native queries (although I didn't find any)

Below is my application config:

applicationContext.xml

<tx:annotation-driven transaction-manager="txManager" />

<bean id="txManager" class="org.springframework.orm.jpa.JpaTransactionManager">
    <property name="entityManagerFactory">
        <ref local="entityManagerFactory" />
    </property>
</bean>

<bean id="entityManagerFactory"
    class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
    <property name="dataSource" ref="dataSource" />
    <property name="persistenceUnitName" value="domainPU" />
    <property name="loadTimeWeaver">
        <bean
            class="org.springframework.instrument.classloading.InstrumentationLoadTimeWeaver" />
    </property>
    <property name="jpaVendorAdapter">
        <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter" />
    </property>
</bean>

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
    destroy-method="close">
    <property name="driverClassName" value="com.mysql.jdbc.Driver" />
    <property name="url" value="${db.url}" />
    <property name="username" value="${db.user.name}" />
    <property name="password" value="${db.user.password}" />
    <property name="validationQuery" value="select 1 as dbcp_connection_test" />
    <property name="testOnBorrow" value="true" />
</bean>

persistence.xml

<persistence 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_1_0.xsd" version="1.0">
<persistence-unit name="domainPU" transaction-type="RESOURCE_LOCAL">

    <provider>org.hibernate.ejb.HibernatePersistence</provider>

    <class>com.domain.Entity1</class>
    ....

    <exclude-unlisted-classes>true</exclude-unlisted-classes>

    <properties>
        <property name="hibernate.show_sql" value="true" />
        <property name="hibernate.dialect" value="org.hibernate.dialect.MySQLDialect" />
        <property name="hibernate.jdbc.batch_size" value="100"></property>
        <property name="hibernate.order_inserts" value="true"></property>
        <property name="hibernate.order_updates" value="true"></property>

        <property name="hibernate.c3p0.min_size" value="5"></property>
        <property name="hibernate.c3p0.max_size" value="30"></property>
        <property name="hibernate.c3p0.timeout" value="300"></property>
        <property name="hibernate.c3p0.max_statements" value="100"></property>
        <property name="hibernate.c3p0.idle_test_period" value="${hibernate.c3p0.idle_test_period}"></property>
    </properties>
</persistence-unit>

Libraries used:

• Hibernate 4.1.6.FINAL
• Spring 3.2.2.RELEASE
• mysql-connector-java-5.1.21
• MySQL 5.5

Answer 1

After digging deeper into how transactions work in MySQL I found an answer:

Problem was in the specific statements inside the native sql query.

From MySQL documentation:

13.3.3 Statements That Cause an Implicit Commit

• Data definition language (DDL) statements that define or modify database objects (...CREATE TABLE, DROP DATABASE...)

• Statements that implicitly use or modify tables in the mysql database (CREATE USER, DROP USER, and RENAME USER..., GRANT, REVOKE, ...)

• ...

More details here:

http://dev.mysql.com/doc/refman/5.1/en/implicit-commit.html

I've decided to split that action into two parts:

• ordinary hibernate statements (inside a transaction),
• native query statements

and supply user with a tool/action to reinvoke the second part in case when something goes wrong.

Other solution would be migrating to some other RDBMS that supports transactions around DDL operations.