java Hibernate unnecessary queries on detached objects

Question

I'm inserting, updating and deleting many detached objects with hibernate and a c3p0 connectionpool. The problem is that hibernate does not batch the statements but instead does a

select @@session.tx_read_only

between every session.persist/insert/update/delete(object). Profiling the sql-connection it looks like this:

select @@session.tx_read_only
insert...
select @@session.tx_read_only
insert...
select @@session.tx_read_only
insert...
select @@session.tx_read_only
insert...
select @@session.tx_read_only
insert...
select @@session.tx_read_only

with select @@session.tx_rad_only always returning "0" (of course). It doesn't matter whether i use a stateless or stateful session. The resulting performance is not acceptable and far of any expectation.

My Hibernate Konfiguration:

 <property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
    <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
    <property name="hibernate.connection.url">jdbc:mysql://127.0.0.1:4040/xy?zeroDateTimeBehavior=convertToNull</property>
    <property name="hibernate.connection.username">xy</property>
    <property name="hibernate.connection.password">xy</property>
    <property name="hibernate.connection.autocommit">false</property>
    <property name="hibernate.show_sql">false</property>
    <property name="hibernate.format_sql">false</property>
    <property name="hibernate.use_sql_comments">false</property>
    <property name="hibernate.query.factory_class">org.hibernate.hql.internal.classic.ClassicQueryTranslatorFactory</property>
    <property name="hibernate.connection.provider_class">org.hibernate.service.jdbc.connections.internal.C3P0ConnectionProvider</property>
    <property name="hibernate.c3p0.min_size">5</property>
    <property name="hibernate.c3p0.max_size">20</property>
    <property name="hibernate.c3p0.timeout">300</property>
    <property name="hibernate.c3p0.max_statements">250</property>
    <property name="hibernate.c3p0.idle_test_period">3000</property>
    <property name="hibernate.jdbc.batch_size">250</property>
    <property name="hibernate.connection.release_mode">auto</property>
    <property name="hibernate.order_inserts">true</property>
    <property name="hibernate.order_updates">true</property>
    <property name="hibernate.cache.use_second_level_cache">false</property>
    <property name="hibernate.cache.region.factory_class">org.hibernate.cache.ehcache.EhCacheRegionFactory</property>
    <property name="hibernate.cache.provider_class">org.hibernate.cache.EhCacheProvider</property>
    <property name="hibernate.cache.use_query_cache">true</property>
    <property name="net.sf.ehcache.configurationResourceName">hibernate_ehcache.xml</property>

I'm using:

<dependency>
        <groupId>org.hibernate</groupId>
        <artifactId>hibernate-c3p0</artifactId>
        <version>4.3.5.Final</version>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.31</version>
    </dependency>
    <dependency>
        <groupId>org.hibernate</groupId>
        <artifactId>hibernate-entitymanager</artifactId>
        <version>4.3.4.Final</version>
    </dependency>
    <dependency>
        <groupId>org.hibernate</groupId>
        <artifactId>ejb3-persistence</artifactId>
        <version>1.0.2.GA</version>
    </dependency>
    <dependency>
        <groupId>org.hibernate</groupId>
        <artifactId>hibernate-ehcache</artifactId>
        <version>4.3.4.Final</version>
    </dependency>

I have nearly no expirience with hibernate and it is a good guess i made a huge mistake so please feel free to suggest anything. I switched to hibernate because the ORM functionality and am coming from plain jdbc prepared statements with a blazing fast performance. The MYSQL-Server is in a good configuration-state.

edit1: i'm aware of: Unnecessary queries in Hibernate - MySql I've got no transactional annotations in my entities nor a defined isolationlevel anywhere

edit2: i changed my connectionpool to bonecp - and the problem continues. Seems to be clearly a hibernate copnfiguration issue.

edit3: tried many different things and found maybe a trace of a hint: If I manualy session.flush() every 5 inserts (=size of batch e.g.)[tried the batch-example AGAIN from hibernate], the select @@session.tx_read_only query appears double - every 5 queries. I therefore assume that select @@session.tx_read_only is related to flushing. are there any ways to prevent hibernate from flushing every single insert/update? I tried so far: session.setFlushMode(FlushMode.COMMIT/NEVER/etc) without any change in the behaviour. Maybe I misconfigured anything... what does hibernate trigger to flush every insert? Unique constraint at the tables? hibernate validation framework? complex object-graphs? difficult concurrency? maybe a locking issue (Hibernate isn't sure if someone else locked the tables and doesn't batch but checks every single insert if the table is read only?)?

i found nothing related to this extrem (I assume) flushing bahaviour.

Dhanas chenniappan · Accepted Answer · 2014-09-29 21:00:28Z

8

We solved this issue by just setting useLocalSessionState=true in the connection string.

The below link explain the details of ReadOnly related changes happened from Mysql5.6 and the java connector 5.1.23. http://dev.mysql.com/doc/relnotes/connector-j/en/news-5-1-23.html

answered Sep 29, 2014 at 21:00

Dhanas chenniappan

1761 silver badge8 bronze badges

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

2 Comments

Per Lindberg Over a year ago

I had the same problem, and this setting solved it. The problem appeared when I upgraded to MySql 5.6, and upgraded Connector/J from 5.1.13 to 5.1.36 in order to be able to use a new data type in MySql 5.6. When I switched back to mysql-connector-java-5.1.13-bin.jar the extra "SELECT @@session.tx_read_only" for each INSERT disappeared. Adding useLocalSessionState=true to the connection string also worked with mysql-connector-java-5.1.36-bin.jar ! So, this is not a problem in JPA (or Hibernate). Thanks for the solution!!

Dhanas chenniappan Over a year ago

Great.. I am glad that this solution worked for you.

Community · Accepted Answer · 2017-05-23 12:34:05Z

1

You need to include all those CRUD operations in a single Transaction so all statements are executed in the same DB connection.

You can also enable the following Hibernate configurations:

<property name="hibernate.order_inserts" value="true"/>
<property name="hibernate.order_updates" value="true"/>
<property name="hibernate.jdbc.batch_size" value="50"/>

Those queries don't mean you don't have batching. It's a MySQL thing

Some drivers require special batching reordering directives:

<property name="hibernate.connection.url">jdbc:mysql://host:port/db?rewriteBatchedStatements=true</property

edited May 23, 2017 at 12:34

CommunityBot

11 silver badge

answered Jul 4, 2014 at 7:08

Vlad Mihalcea

156k85 gold badges599 silver badges984 bronze badges

5 Comments

Vlad Mihalcea Over a year ago

To properly batch you need a transaction to at least contain the current batched statements.

Florian Bronder Over a year ago

To be more specific: I open a session, begin transaction, iterate over a list and insert the objects, commit the transaction and close the session which returns to the pool. The inserts are not batched :(

Vlad Mihalcea Over a year ago

If you are using the Identity Id generator, the batching is disabled.

Florian Bronder Over a year ago

I'm using the increment generator with: @GenericGenerator(name = "xy", strategy = "increment") thank you for these hints, please continue so i can check my configuration!

Florian Bronder Over a year ago

checked your update, not a new information as i stated in my question exactly this post.

Florian Bronder · Accepted Answer · 2014-07-26 14:43:09Z

0

I found the flaw in my configuration. I had to change the mysql-connectionsstring from

<property name="hibernate.connection.url">jdbc:mysql://127.0.0.1:4040/xy?zeroDateTimeBehavior=convertToNull</property>

to

<property name="hibernate.connection.url">jdbc:mysql://127.0.0.1:4040/xy?rewriteBatchedStatements=true</property>

this solved my problems.

answered Jul 26, 2014 at 14:43

Florian Bronder

1231 silver badge7 bronze badges

Collectives™ on Stack Overflow

java Hibernate unnecessary queries on detached objects

3 Answers 3

2 Comments

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related