What if … MySQL’s Repeatable Reads Cause You to Lose Money?

What if … MySQL’s Repeatable Reads Cause You to Lose Money?

Well, let me say if that happens it’s because there is a logic mistake in your application. But you need to know and understand what happens in MySQL to be able to avoid the problem. 

In short, the WHY of this article is to inform you about possible pitfalls and how to prevent them from causing you damage.

Let us start by having a short introduction to what Repeatable reads are about. Given I am extremely lazy, I am going to use (a lot) existing documentation from the MySQL documentation.

Transaction isolation is one of the foundations of database processing. Isolation is the I in the acronym ACID; the isolation level is the setting that fine-tunes the balance between performance and reliability, consistency, and reproducibility of results when multiple transactions are making changes and performing queries at the same time.

InnoDB offers all four transaction isolation levels described by the SQL:1992 standard: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE. The default isolation level for InnoDB is REPEATABLE READ.

• REPEATABLE READThis is the default isolation level for InnoDB. Consistent reads within the same transaction read the snapshot established by the first read. This means that if you issue several plain (nonlocking) SELECT statements within the same transaction, these SELECT statements are consistent also with respect to each other.

• READ COMMITTEDEach consistent read, even within the same transaction, sets and reads its own fresh snapshot.

And about Consistent Non-Blocking reads:

A consistent read means that InnoDB uses multi-versioning to present to a query a snapshot of the database at a point in time. The query sees the changes made by transactions that committed before that point in time, and no changes made by later or uncommitted transactions. The exception to this rule is that the query sees the changes made by earlier statements within the same transaction. This exception causes the following anomaly: If you update some rows in a table, a SELECT sees the latest version of the updated rows, but it might also see older versions of any rows. If other sessions simultaneously update the same table, the anomaly means that you might see the table in a state that never existed in the database.

Ok, but what does all this mean in practice? To understand, let us simulate this scenario:

I have a shop and I decide to grant a bonus discount to a selected number of customers that:

• Have an active account to my shop
• Match my personal criteria to access the bonus

My application is set to perform batch operations in a moment of less traffic and unattended. This includes reactivating dormant accounts that customers ask to reactivate.  

What we will do is to see what happens, by default, and then see what we can do to avoid pitfalls.

The Scenario

I will use three different connections to connect to the same MySQL 8.0.27 instance. The only relevant setting I have modified is the Innodb_timeout that I set to 50 seconds. 

• Session 1 will simulate a process that should activate the bonus feature for the selected customers
• Session 2 is an independent process that reactivates the given list of customers
• Session 3 is used to collect lock information

For this simple test I will use the customer table in the sakila schema modified as below:

As you can see I have added the bonus and activate_bonus fields plus the idx_bonus index.

To be able to trace the locks, these are the threads ids by session:

To collect the lock information:

Ok, ready? Let’s start!

The Run…

While the following steps can be done in a more compressed way, I prefer to do it in a more verbose way, to make it more human-readable.

First, let us set the environment:

Then let see the list of the customers we will modify:

As you can see we have 21 customers fitting our criteria. How much money is involved in this exercise?

This exercise is going to cost me ~242 dollars. Keep this number in mind. What locks do I have at this point?

The answer is none. 

Meanwhile, we have the other process that needs to reactivate the customers:

In this case, the process needs to reactivate 10 users.

All good, right? But before going ahead let’s double-check session 1:

Perfect! My repeatable read still sees the same snapshot. Let me apply the changes:

Wait, what? My list reports 21 entries, but I have modified 31! And if I check the cost:

Well, now the cost of this operation is 375 dollars, not 242. In this case, we are talking about peanuts, but guess what could be if we do something similar on thousands of users. 

Anyhow, let us first:

And cancel the operation.

So what happened; is this a bug?

No, it is not! It is how repeatable reads work in MySQL. The snapshot is relevant to the read operation, but if another session is able to write given also the absence of lock at the moment of reads, the next update operation will touch any value in the table matching the conditions. 

As shown above, this can be very dangerous. But only if you don’t do the right things in the code. 

How Can I Prevent This From Happening?

When coding, hope for the best, plan for the worse, always! Especially when dealing with databases. That approach may save you from spending nights trying to fix the impossible. 

So how can this be prevented? You have two ways, both simple, but both with positive and negative consequences.

1. Add this simple clausole to the select statement: for share
2. Add the other simple clausole to the select statement: for update
3. Use Read-committed

Solution one is easy and clean, with no other change in the code. BUT it creates locks, and if your application is lock sensitive this may be an issue for you.

Solution two is also easy, but a bit more encapsulated and locking. 

On the other hand, solution three does not add locks but requires modifications in the code and it still leaves some space for problems.

Let us see them in detail.

Solution One

Let us repeat the same steps:

Now we check the locks (for brevity I have cut some entries and kept a few as a sample):

This time we can see that the select has raised a few locks all in S (shared) mode. 

For brevity, I am skipping to report exactly the same results as in the first exercise.

If we go ahead and try with session two:

Here we go! Attempting to change the values in the customer table is now on hold waiting to acquire the lock. If the duration exceeds the Innodb_wait_timeout, then the execution is interrupted and the application must have a try-catch mechanism to retry the operation. This last one is a best practice that you should already have in place in your code. If not, well add it now!

At this point, session one can proceed and complete the operations. After that and before the final commit, we will be able to observe:

As you can see we now have two different lock types, the Shared one from the select, and the exclusive lock (X) from the update.

Solution Two

In this case, we just change the kind of lock we set on the select. In solution one we opt for a shared lock, which allows other sessions to eventually acquire another shared lock. Here we go for an exclusive lock that will put all the other operations on hold.

If now we check the locks (again, for brevity I have cut some entries and kept a few as a sample):

In this case, the kind of lock is X as exclusive, so the other operations when requesting a lock must wait for this transaction to complete.

And it will wait for N time where N is either the Innodb_lock_wait_timeout or the commit time from session one.

Note the lock request on hold:

Session two is trying to lock exclusively the idx_bonus but cannot proceed.

Once session one goes ahead, we have:

Now my update matches the expectations and the other operations are on hold.

After session one commits:

Session two is able to complete, BUT it was on hold for 52 seconds waiting for session one. As said, this solution is a good one if you can afford locks and waiting time.

Solution Three

In this case, we will use a different isolation model that will allow session one to see what session two has modified.

Same result as before. Now let us execute commands in session two:

All seems the same, but if we check again in session one:

We now can see all the 31 customers and also the value calculation:

It is reporting the right values.

At this point, we can program some logic in the process to check the possible tolerance and have the process either complete performing the update operations or stop the process and exit.

This as mentioned requires additional coding and more logic.

To be fully honest, this solution still leaves space for some possible interference, but at least allows the application to be informed about what is happening. Still, it should be used only if you cannot afford to have a higher level of locking, but this is another story/article.

Conclusion

When writing applications that interact with an RDBMS, you must be very careful in what you do and HOW you do it. While using data facilitation layers like Object Relational Mapping (ORM), seems to make your life easier, in reality, you may lose control of a few crucial aspects of the application’s interaction. So be very careful when opting for how to access your data.

About the case reported above we can summarize a few pitfalls:

1. First and most important, never ever have processes that may interfere with the one running at the same time. Be very careful when you design them and even more careful when planning their executions.  
2. Use options such as for share or for update in your select statements. Use them carefully to avoid unuseful locks, but use them. 
3. In case you have a long process that requires modifying data, and you need to be able to check if other processes have altered the status of the data, but at the same time, cannot have a long wait time for locks. Then use a mix, setting READ-COMMITTED as isolation, to allow your application to check, but also add things like for share or for update in the select statement, immediately before the DML and the commit. That will allow you to prevent writes while you are finalizing, and also to significantly reduce the locking time. 
4. Keep in mind that long-running processes and long-running transactions can be the source of a lot of pain, especially when using REPEATABLE-READ. Try whenever you can to split the operations into small chunks. 

Finally, when developing, remember that DBAs are friends, and are there to help you to do things at your best. It may seem they are giving you a hard time, but that is because their point of view is different, focusing on data consistency, availability, and durability. But they can help you to save a lot of time after the code is released, especially when you try to identify why something has gone wrong. 

So involve them soon in the design process, use them and let them be part of the process.

References

https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html

https://dev.mysql.com/doc/refman/8.0/en/innodb-consistent-read.html

https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-model.html

Complete the 2021 Percona Open Source Data Management Software Survey

Have Your Say!