Massive number of "SET autocommit=0/1" queries in MySQL

Question

I'm running some load tests on our system and I notice a massive number of "SET autocommit=0" and "SET autocommit=1" queries being executed. Something around 25,000 within 1 minute. I'm trying to figure out what is causing this and how to get rid of it.

We use the following technologies:

MySQL
Hibernate
Hikari
Spring
Tomcat

I have tried the following but it did not seem to help:

"SET autocommit = 0" in MySQL
Added the elideSetAutoCommits property in the db connection URL. "jdbc:mysql://localhost/db_name?useUniCode=true&characterEncoding=UTF-8&pinGlobalTxToPhysicalConnection=true&elideSetAutoCommits=true"

Could someone point me towards what might be causing these queries?

just a guess: can it be that Hibernate issues these "set autocommit" before and after a transaction? You can perhaps try to find out, debugging the application and noting which commands exactly arrive on the MySQL connection. — John Donn

Nicolas Filotto Nicolas Filotto · Accepted Answer · 2016-09-27T08:15:01

Could someone point me towards what might be causing these queries?

Your queries are the consequence of Connection#setAutoCommit(boolean) which is used to switch from the default mode that is auto-commit mode to transactional mode in order to insert/update/delete/read data within a transaction.

The common code is :

// Switch to transactional mode which actually triggers a SET autocommit = 0
con.setAutoCommit(false);
try {
    // Some operations on the db
    con.commit();
} finally {
    // Switch back to auto-commit mode which actually triggers a SET autocommit = 1
    con.setAutoCommit(true);
}

Here is a good link that explains how transactions work in JDBC.

If you know that your pool of connections will always be used to get connections in transactional mode, you can set the default mode in the configuration of Hikari thanks to the parameter autoCommit to set to false this way the connections will be already in transactional mode such that it won't be needed anymore to modify the mode.

This property controls the default auto-commit behavior of connections returned from the pool. It is a boolean value. Default: true

More details about the configuration of Hikari here.

Massive number of "SET autocommit=0/1" queries in MySQL

1 Answers