MySQL still autocommit with Transaction

[Caladan]

Hi everybody,

I'm currrently working with Spring 2.0.6, Hibernate 3.2.2GA and MySQL Java Connector 5.0.4 and have problem with transaction.

It looks like MySQL doesn't take care of autocommit false setted by the transaction manager and directly persist change to my database, here is my config whether I'm in a transaction boundary or not :

I deploy my application on JBoss 4.0.5 using standard MySQL datasource (as specified in JBoss example).

<bean id="myDao" class="com.cdsolutionsinfo.test.MyDaoImpl">
<property name="sessionFactory" ref="sessionFactory"/>
</bean>


<tx:advice id="transactionAdvice" transaction-anager="transactionManager">
<tx:attributes>
<tx:method name="is*" read-only="true" propagation="SUPPORTS"/>
<tx:method name="get*" read-only="true" propagation="SUPPORTS"/>
<tx:method name="find*" read-only="true" propagation="SUPPORTS"/>
<tx:method name="*" propagation="REQUIRED"/>
</tx:attributes>
</tx:advice>

<aop:config>
<aopointcut id="repositoryOperation"
expression="execution(* com.cdsolutionsinfo..*Dao.*(..))" />

<aop:advisor advice-ref="transactionAdvice"
pointcut-ref="repositoryOperation" />
</aop:config>

Now I have a reattach method in my dao to force to reattach element to active session that does following :

public void reattach(MyEntity entity) {
getHibernateTemplate().saveOrUpdate(entity)
}

I also have service layer using the daos, I also define transaction boundaries at this level and then service can make call to multiples dao's services and all dao calls participate in same transaction, I enable debug mode of TransactionManager and TransactionInterceptor, everything look good, except information is directly stored to database and rollbacking transaction doesn't work properly in my service.

public void myService(Entity myEntity) {
// begin to reattach domain object to avoid lazy load problem while
// working with object and children.
entityDao.reattach(myEntity);

// Do any business logic here, if something fail a RuntimeException is
// throwned to force to rollback transaction but too late information
// is already stored to database before giving control to caller method.
}

[titiwangsa]

dbcp has an autocommit option
perhaps you can turn it off.
well that's assuming you are using dbcp.

[Caladan]

dbcp has an autocommit option
perhaps you can turn it off.
well that's assuming you are using dbcp.

I don't use dbcp, I'm using plain datasource from JBoss, I expect datasource handles that (I know it handles that correctly with Oracle, but I need to use MySQL since my Web provider provides MySQL as database engine).

I don't know how dbcp works, is it possible to replace the standard JBoss datasource with the dbcp datasource ?

[ShakaTheRock]

Which kind of MySql tables are you using? Some kinds of tables (e.g. MyISAM) don't implement tranasctions.

[titiwangsa]

yes. you should probably use innodb. also i've noticed something with mysql 4 and mysql 5.
an insertion of a 200 character long string to an 80 colum wide table.
using hibernate + spring's hibernate template.
on mysql 4 the data get truncated.
on mysql 5 the row does not get truncatated.
so a difference in mysql version could be a factor.

[karldmoore]

Not using InnoDB is a common cause of this problem, if it still doesn't work then turn up the logging or add some code to check if you are actually within a transaction and your AOP matching rule is correct.
http://www.springframework.org/docs/...actionIn fo()

[Caladan]

I'm using InnoDB, I enabled debug log for my aop and I know that everything is ok on this side, I also go step by step in TransactionInterceptor to make sure everything was ok...:-(

I'm using MySQL 5.0.27 on my dev environnment, my provider uses MySQL 5.0.18.

I think I'll try with another database like Oracle Express just to find where is the problem either in Hibernate or in database.

[Caladan]

Ok I did a test with simple JDBC Client (I use Squirrell SQL Client) connecting to my database with autocommit setted to false and I execute an update statement to see what append, without committing my execution I'm able to see change directly in MySQL Query Browser connected to the same database, therefore, that means my database isn't transactional, thanks everybody, I'll post my problem on MySQL forum.

[karldmoore]

Thanks for posting back, if you get a solution to this could you post a link back here? I'm sure it would be useful for other people.

[trisberg]

Executing SHOW TABLE STATUS in the Query Browser should give you the information you need to see if the table is actually an InnoDB table