MySQL transaction isolation levels

This short article is about my initial lack of knowledge of MySQL transaction isolation levels, something I knew little about until recently when I needed it.

The problem in question here is that I’m responsible for a relatively simple MySQL setup, with a primary database and a reporting database operating on one host (a multi-AZ AWS RDS instance). The reporting database contains a number of views that have a relation to the primary database, yet for various reasons the views take quite a bit of time to ‘render’. The employees using the system rely on the numbers in the reporting database to be accurate and up to date. We used to have two separate servers, yet when moving to AWS we setup the databases on a single, large (i.e. rds.m5.xlarge) AWS instance. We noticed that every time we ran the script to update the reporting database, where a few ‘cache’ tables where filled from the views, the primary database would throw several Lock wait timeout exceeded errors. This needed to be fixed.

My first priority was to optimize the select queries used within the views, though that turned out to be quite some work, yet I then started to think about other options. It turned out that the database put a read lock on all tables involved in the view (if I understood ). I wanted to circumvent that, so that other transactions on the primary database can continue. For our reporting queries a ‘dirty read’ wasn’t an issue, and no write actions were happening on the primary database. That is where I discovered the various ‘transaction isolation levels’ that are present in MySQL. Another, more clear explanation of what ‘isolation levels’ do, can be found here on Wikipedia.

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
    INSERT INTO c_something SELECT * FROM v_something;
    ...
COMMIT;

My new SQL statement

The SQL statement that now updates the ‘cache’ tables (prefixed with c_), sets the isolation level to READ UNCOMMITTED which potentially might operate on an out-of-date version. This allows other queries to still interact with the primary database without any hindrance.

Are there other ways that it can be setup? Yes, absolutely. Though for now this seems to work as I’d expect, if I have understood it correctly. If I haven’t, please help me correct it.

P.S. If you’ve enjoyed this article or found it helpful, please share it, or check out my other articles. I’m on Instagram and Twitter too if you’d like to follow along on my adventures and other writings, or comment on the article.

• ← Previous Post
• Next Post →