A method you don't know to Improve the Performance of spring + hibernate-MySQL

Http://www.iteye.com/topic/494179

In the sprint + hibernate application, you monitor MySQL logs and find a large number of logs as follows:

Java code 158268 query set autocommit = 1 158268 query set autocommit = 1 158268 query set autocommit = 1 158268 query set autocommit = 0 158268 query commit 158268 query set autocommit = 1 158268 query set autocommit = 1 158268 query set autocommit = 1 158268 query set autocommit = 0 158268 query commit

In addition, if you read mysqlreport, you will find that set_option and commt account for almost half of MySQL query !!

Why?
Let's look at it first.
Java code
158268 query set autocommit = 0
158268 query commit
This log is generated because, during Hibernate query, if the requested object is in the cache, Hibernate will still issue two such SQL statements to the database.
Let's take a look.
Java code
158268 query set autocommit = 1
This is because when the connection is put back into the conn pool, the conn pool will restore the autocommit status when the connection is taken out. Generally, the autocommit status in the connection pool is true, so each query is followed by a set autocommit = 1.
In the second case, you can use the jdbc url of the conn pool to set autocommit to true. However, MySQL driver does not seem to support this setting.

However, don't worry. We have an optimal solution, which can not only solve these two problems at once, but also solve the performance impact of read_only transactions.
That is, lazyconnectionceceproxy
This conn proxy is used to issue queries to DB only when necessary. All unnecessary set autocommit = 0 commit statements are not sent to the database.

For more information about the function of this class, see the spring document.

The configuration of example is as follows: Java code <bean id = "datasourcetarget" class = "org. springframework. JDBC. datasource. drivermanagerdatasource "> <property name =" driverclassname "> <value> COM. mySQL. JDBC. driver </value> </property> <property name = "url"> <value> JDBC: mysql: // localhost: 3306/imagedb </value> </property> <property name = "username"> <value> admin </value> </property> <property name = "password"> <value> </property> </bean> <bean id = "datasource" class = "org. springframework. JDBC. datasource. lazyconnectiondatasourceproxy "> <property name =" targetdatasource "> <ref local =" datasource "/> </property> </bean>