Hibernate queries on database

Question

I'm having a problem with the amount of queries done by hibernate to the database. Here's my query log (using Mysql 5.1) to the database when doing a simple select:

111125  7:18:30
27 Query    SET autocommit=0
27 Query    SELECT @@session.tx_isolation
27 Query    select this_.id as id34_0_, this_.media_id as media3_34_0_, this_.message as message34_0_, this_.user_id as user4_34_0_ from notifications this_
27 Query    rollback
27 Query    SET autocommit=1

I've read a lot about setting autocommit to 0 and then to 1. I know that the default for a connection is 1 and this behaviour cannot be changed. You can run SET autocommit = 0 but the result is the same.

Is there anyway to avoid any of those queries? I don't know why the SELECT @@session.tx_isolation is happening and the rollback. When I use a transaction I get a commit and then a rollback. Not sure why a rollback is always cabled.

Thanks a lot!

My conf: Spring 2.5.6, Hibernate 3.6.0, Mysql 5.1

datasoure.xml:

<bean id="dataSource" destroy-method="close"
    class="org.apache.commons.dbcp.BasicDataSource">
    <property name="driverClassName" value="${jdbc.driverClassName}" />
    <property name="url" value="${jdbc.url}" />
    <property name="username" value="${jdbc.username}" />
    <property name="password" value="${jdbc.password}" />

    <property name="initialSize" value="3" />
    <property name="maxActive" value="20" />
    <property name="minIdle" value="3" />
    <property name="poolPreparedStatements" value="false" />
    <property name="defaultAutoCommit" value="false" />
    <property name="defaultTransactionIsolation" value="4" />
</bean>

Transaction manager definition:

<bean id="transactionManager"
    class="org.springframework.orm.hibernate3.HibernateTransactionManager">
    <property name="sessionFactory" ref="sessionFactory" />
</bean>

UPDATE: Managed to take out the rollback by setting new property

<property name="defaultReadOnly" value="true" />

But the problem now is that you can't make a modification (no matter if I set on the transactional annotation readOnly=false) to the db giving a SQLException. This property sets the connection readonly to true. I'm guessing there's no way to do this with HibernateTemplate.

I use aspectj transaction for inner code weaving.

<aop:aspectj-autoproxy proxy-target-class="true" />

Answer 1

You need to configure transactions for your application. See the Spring 2.5.x documentation on transaction management.

Edit 12/3/11: Even for methods that only do selects, you still have to create a read only transaction in order to remove the extra queries you mentioned in your post. Just write @Transactional(readOnly=true) and you should be good to go.

Edit 12/20/11: You also need make sure transactions are configured correctly. It looks like the configuration you posted may be missing < tx:annotation-driven /> annotation. See section 10.5.1 of the Spring documentation.