mysql - Native query executeUpdate seems to commit data in Spring transaction -

my usecase following: when creating application user (entitymanager.persist) have create db user , grant him privileges (that's why need hibernate nativequery).

i have spring @transactional method calls dao both calls:

@transactional public integer createcompany(company company) throws exception {     companydao.createreportuser(reportuser user);     ... } 

my dao method looks this:

getem().persist(companyreportsuser); getem().createnativequery("create user user1@localhost identified :password").setparameter("password", password).executeupdate(); getem().createnativequery("grant select on appdb.v_company user1@localhost").executeupdate(); //several grants 

now, first line executeupdate() executed can see persisted companyreportsuser in database along db user (user1@localhost).

all nativequeries executed , commited 1 one. since commited, cannot rolled back. there no auto-commit parameter set anywhere in configuration assume 'false' found in hibernate docs.

1. i've tested @transactional behavior without native queries , works supposed (transaction rolled when throw runtimeexception , no data inserted database)

2. when debugging i've seen persist operation delays execution when invoked in running transaction.

3. native query seems create , execute preparedstatement (at least didn't find queue of kind.

4. i suppose might not interaction between hibernate native query , spring transaction, i've took time reading spring , hibernate docs regarding transactions , native queries , did not found me.

5. maybe there better way create database user , grant privileges native queries (although didn't find any)

below 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 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

after digging deeper how transactions work in mysql found answer:

problem in specific statements inside native sql query.

from mysql documentation:

13.3.3 statements cause implicit commit

• data definition language (ddl) statements define or modify database objects (...create table, drop database...)

• statements implicitly use or modify tables in mysql database (create user, drop user, , rename user..., grant, revoke, ...)

• ...

more details here:

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

i've decided split action 2 parts:

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

and supply user tool/action reinvoke second part in case when goes wrong.

other solution migrating other rdbms supports transactions around ddl operations.