Why does my JDBC keep setting session variables?

Question

I'm looking at the Performance Insights for MySQL Aurora on AWS and among the top queries I see:

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

These statements don't appear in my code anywhere, I couldn't find them in the HikariCP implementation, in the MySQL/J driver or the JDBC connection string. I was able to confirm that the last query SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED occurs when a new connection is created. These queries are wasteful since they come from a reader instance. Any ideas on how to trace and stop this?

This is with HikariCP 3.4.5, mysql connector 8.0.11 and MySQL 5.7. Below is a screenshot of what Performance Insights shows. Note that setting session parameters is 4 of the top 5 queries by Average Active Sessions. The wait event in green is wait/io/aurora_respond_to_client, so I guess it's just waiting for the client to confirm receipt.

Answer 1

Not sure why you couldn't find it in the driver code, it's certainly there:

    public void setTransactionIsolation(int level) throws SQLException {
      ...    
            if (shouldSendSet) {
                switch (level) {
                    ...
                    case java.sql.Connection.TRANSACTION_READ_COMMITTED:
                        sql = "SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED";
                    ...
                }

                this.session.execSQL(null, sql, -1, null, false, this.nullStatementResultSetFactory, null, false);
                ...

You can reduce frequency of these calls by setting the alwaysSendSetIsolation property to false.