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?

Answer 1

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.