Sign up a test

MySQL/InnoDB comes with features that support data consistency like transactions, transaction isolation levels and locking. We have found two things to be very important in understanding and working effectively with these features:

• Reading the description of the features in the MySQL documentation carefully (it sounds obvious, but reading the fine print is vital) ;

• Spinning quick tests to exercise these features in scenarios that mimic real life.

In this section, we will focus on the latter point.

One convenient way to set up a MySQL server is to use a docker container on your local machine. This avoids polluting your local machine by installing MySQL directly. The MySQL team publishes a set of official images into the Docker Hub repository along with instructions on how to use them. Another option is to use a cloud platform like AWS, Azure or GCP to access a managed MySQL server instance; however, this may involve additional costs.

Let’s exemplify by means of testing the difference between the READ COMMITTED and REPEATABLE READ isolation levels.

We begin by creating a Docker container that contains the latest MySQL version (8.0.26 as we write this blog post). This is accomplished by issuing the below command in a terminal window.

Once the container is successfully created, you can launch a bash session on the container using:

At the bash prompt, launch the ‘mysql’ command line client and type your password when asked for.

We basically created a database LOANS that contains a table LOANACCOUNT with two accounts and a table SAVINGSACCOUNT with one account. Let’s set the isolation level to READ COMMITTED, start a transaction, and read the content of the LOANACCOUNT table:

Right now, we leave the transaction uncommitted, open a new bash console, start a MySQLclient and use the LOANS database to issue an update.

We go back to the initial console and query the SAVINGSACCOUNT table.

We remark that we are able to see in our first transaction the changes made in the second terminal (the amount was changed from 300 to 300 + 30 = 330). Let’s go to the second MySQL client session and modify the LOANACCOUNT table as well:

In the first console, read again the LOANACCOUNT table. It is changed too:

In conclusion, we can see that if a transaction uses the READ COMMITTED isolation level, the reading results are influenced by the other concurrent transactions.

We repeat the experiment, but this time using the REPEATABLE READ isolation level. COMMIT/ROLLBACK transaction in the first MySQL client session, delete the tables’ content, initialise them again, and type:

In the second MySQL client instance, we modify SAVINGSACCOUNT and LOANACCOUNT as before:

Back in the first MySQL client instance, we read:

This time, no change. The values within the in progress transaction were not impacted by the changes performed after the start of the transaction in the second MySQL client session. So, REPEATABLE READ provides a better isolation for a transaction, and we were able to experience first-hand what that means.

Locking

Another important lesson that we want to share is that when dealing with consistency issues, sticking with simple and well-known solutions usually pays off. If you want to ensure that your database updates are consistent, one common solution is to lock your data for any new change. That prevents anomalies like lost or inconsistent data that can occur because of a race between transactions. We discuss two main types of locking, optimistic and pessimistic, and which one is the best depends on your scenario.

Optimistic locking