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" />

Your transactions fails! Did you enable transaction manager in spring context? And your log is normal, mine is similar, except of rollbacks. — madhead
– madhead, Commented Nov 25, 2011 at 15:54
It's just a select and it has no @transactional annotation. It seems that everything is being treated as a transaction. — Gonzalo
– Gonzalo, Commented Nov 29, 2011 at 17:29
I mean native DB level: 27 Query rollback. It rollbacks transaction. Are your Inserts or Updates working correctly? — madhead
– madhead, Commented Nov 29, 2011 at 18:04
Yes, when I use a @Transactional in a method it turns for commit or rollback if an exception is thrown. So inserts, updates and selects on a transaction work. I think somehow I'm adding overhead with that rollback on every "simple" select — Gonzalo
– Gonzalo, Commented Nov 29, 2011 at 20:12

Matt Sgarlata · Accepted Answer · 2011-12-20 20:51:05Z

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.

edited Dec 20, 2011 at 20:51

answered Nov 25, 2011 at 18:01

Matt Sgarlata

1,8471 gold badge17 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Gonzalo Over a year ago

I've added the transaction manager. I use @transactional annotations when I need a transaction but in this case, there's no transaction going on.

Matt Sgarlata Over a year ago

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.

Gonzalo Over a year ago

Still getting the same transaction rollback and autocommit. I get a commit also now with readonly=true

Matt Sgarlata Over a year ago

Please see my updated response: 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.

Gonzalo Over a year ago

I use aspectj transactions. Check last part of my post. Transactions are working perfectly. Only problem is the amount of queries. I've seen that if the connection to the db is readonly, the rollback doesn't happens. But it never matters for the readonly flag of the transaction. I think that that only makes hibernate no to dirty check objects and collections.

|

Collectives™ on Stack Overflow

Hibernate queries on database

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related