Processing Time increased after integrated with Spring Framework

[shin779]

MyBatis version

3.5.2 - 3.5.8

MyBatis Spring Adapter version

2.0.1 - 2.0.5

Database vendor and version

H2 1.4.200
IBM DB2 10.5 LUW

Test case or example project

https://github.com/shin779/DBTestMyBatisSpringWithTxMgr

Steps to reproduce

Perform simple select query (e.g. select 1 from dual ) in a massive number (e.g. 10000 times) inside a transation which managed by Spring via mybatis-spring

Expected result

The process time of 1st 1000 queries should similar to 2, 3...nth 1000 queries
(e.g. if 1st to 1000th queries takes 5sec, then 5001th to 6000th should take ~5 sec as well)

Actual result

Process time keep increase in direct proportion unless keep commit / rollbak with certain number:

[kazuki43zoo]

@shin779

Cloud you provide applicationContext.xml?

[shin779]

Dear kazuki,

Thanks for your reply and please find the updated project for the applicationContext.xml.

Many thanks.

[kazuki43zoo]

@shin779 Thx! I will investigate at later.

[shin779]

Dear @kazuki43zoo,

Thanks for your review!

We digged deeper and find that the process times increase when using transation with MyBatis Spring Adapter:

https://mybatis.org/spring/transactions.html#programmatic

We perform 10000 queries inside a transation and the process time increase higher and higher unless we create another transation (commit / rollback then get a new one) after certain number of queries.

We tried to use Spring transation manager without MyBatis and the process time will not increase.

Although we can low the process time by switching the localCacheScope from STATEMENT to SESSION, that may cause dirty read which we would like to prevent.

We understand there is mechanism and tasks behind MyBatis that may take more time to perform query (proxying, reflect and mapping...etc), but we are wondering if such jobs will make the same query takes more and more time to finish.

P.S. We used MyBatis 2.3.5, Mybatis Spring (1.2.2) and Spring 3.2.2 with transation manager in previous project without such issue. Is it related to the changes between SqlSession and SqlMapClient?

[kazuki43zoo]

@shin779 Thanks you for your reporting!
I've tried the new example. This issue will be occurred using together with DBCP 1.4. As a result of checking with the profiler, it take a lot of time closing the statement at DBCP. At least, I think this issue not belong the mybatis-spring module.

I've tried using together with DBCP 2.9, this issue has been fixed. Could you use a newer other connection pool implementation instead of JDBC 1.4?

@harawata

This behavior did not occur using together with JdbcTemplate(The SQL mapper provided by Spring Framework) . May there is an unknown potential implementation issues in mybatis core module. WDYT?

[shin779]

Dear @kazuki43zoo

Thanks for the testing! We replace DBCP with DBCP2 2.9 and it works like a charm!

There is serval improvement in DBCP2 including the upgrade of common pooling, and common-pool2 seems have a better implementation in object pooling and we wondering if it is related to our issue.

Just like you measured and it seems works fine when using JdbcTemplate, but upgrade dbcp2 did the work.

Thanks again for your work and wish you a happy new year.

[yang7-dev]

good