How do I set read isolation level on a MySQL 8.0 connection

đź’¬ Queries and Resources
1

  1. My goal: To be able to connect to MySQL 8 database with read isolation level set to READ-COMMITTED.

  2. Issue: I have tried various combinations of key + value pairs in the “Connection options” section of the database connection configuration screen, but I cannot get the isolation level to be set to the value I want. Instead all queries run via the connection are using the database’s default level of REPEATABLE-READ.

  3. Steps I've taken to troubleshoot: Tried various Connection option key / value pair combinations, such as key of transaction_isolation and value of READ-COMMITTED.

    Note: I cannot change the default global isolation level for this database, so that is not an option for me.

    Any help you can offer would be much appreciated!

    Thanks,

    Paul

4

Hey Paul and team,

Thanks for reaching out to Retool support. My name is John and I will be helping out with these database questions.

From my understanding of what you're trying to do, set MySQL’s session transaction isolation level to READ-COMMITTED for a Retool database/resource connection, is not currently supported through the “Connection options” field.

Retool’s MySQL connector doesn't apply transaction_isolation or similar connection parameters when establishing a session so the database should continue to use the server’s default (REPEATABLE-READ).

When researching the topic to better understand, I came across this Medium article which mentions you should be able to set this via the SQL statement itself:

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT ...

https://medium.com/@sumit-s/understanding-isolation-levels-in-mysql-baeb1b741863

:white_check_mark: Would you mind giving that a try and letting me know if that helps get the task at hand done? If not, I can happily file a feature request to get something like this into the product in future releases of Retool :grinning_face:

John | Retool Support

5

Hi John,

Thanks for getting back to me on this and for confirming that what we’re trying to do is not currently supported.

We have been able to set it at a SQL statement level successfully, however, that relies upon Retool app creators to know to do this, and so it’s less reliable and harder for us to enforce consistently across all Retool apps. We wanted to enforce it at the connection level so that it happens by default for all queries.

For sure it would be appreciated if you would consider adding this as a feature request. It would really help us in being able to widen our use of Retool.

Thanks,

Paul

6

Absolutely Paul!

I just put in a feature request to get this added to the product in the manner you are hoping for. I shared this thread in there too so the developers can come back here and ask any clarifying questions if they need in the future.

Let us know if we can mark this as resolved as we wait for that feature to eventually come into the product and you use the workaround for the time being :grinning_face: :white_check_mark:

1 Like
7

Hi John,

Thanks for putting in the request. Yeah we can marked this as resolved for now.

Will this thread be notified if / when the feature does get added?

Thanks,

Paul

8

Great question @Paul_Dailly,

While we do not have any automated way to do that as of yet, I am following the internal Linear for it which has data on this original request. Once that gets closed out I should get notified and I can manually come back here and let you know. :grinning_face:

Regards,
John | Retool Support