0

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. enter image description here

5
  • They are wasteful in what sense?
    – mustaccio
    Jun 9, 2022 at 18:05
  • They take up the database's time. If I understand correctly this setting is entirely unnecessary in a reader instance.
    – Johnny
    Jun 9, 2022 at 19:48
  • 1
    How much time do you think it takes to set a session variable?
    – mustaccio
    Jun 9, 2022 at 19:51
  • 1
    If you'd asked me without data I'd say no time at all. But Performance Insights says it takes up a lot. I've added a screenshot to my question.
    – Johnny
    Jun 9, 2022 at 19:59
  • 1
    I guess we have different views on what "a lot" means. Looks like it adds whopping 0.002% to the query latency. Personally, I'd find myself a bigger fish to fry.
    – mustaccio
    Jun 9, 2022 at 20:17

1 Answer 1

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.