Don’t break production: learn about MySQL locks
One web application I used to maintain was using MySQL 5.6 as the main database for a couple of years, but we decided to take a leap and upgrade to MySQL 8.0 to benefit from various improvements: performance, extended online DDL operations, JSON support, …
After weeks of testing, once everything looked good, we rolled out the upgrade and served all our production database traffic from MyQSL 8.0. We closely monitored for any issues in the following weeks: everything was running smoothly, and we could finally put the upgrade behind us! 🥳
This is n ot until a few more weeks that it all shattered. About 1.5 months after releasing the upgrade, our service got disrupted after running a database migration during a regular deployment: our main production replica seemed stuck with thousands of queries hanging indefinitely, causing our webservers to hang as well, and leaving us with no choice other than to reboot the replica. Two days later, while we were still troubleshooting the root cause, the same incident happened: our replica went nuts after running migrations during a deployment.
We immediately connected the incident to the upgrade of MySQL to 8.0, but it was unclear to us why exactly. In particular, we have been running MySQL 8.0 in production, and performed a few migrations, for more than 1.5 months without any issue. So, what is causing this, and why now?
