MySQL Troubleshooting

Chapter 2. You Are Not Alone: Concurrency Issues

MySQL is rarely deployed in a single-user environment. Usually it handles many connection threads doing different jobs for different people at the same time. These parallel connections can access the same databases and tables, so it is hard to know what the state of the database was when a particular connection had a problem.

This chapter describes issues caused by parallel execution. Unlike the troubleshooting scenarios shown in Chapter 1, this chapter covers more complex situations where you may experience problems without knowing which particular query caused them.

One of the symptoms of a concurrency issue is a sudden slowdown of a well-optimized query. The slowdown may not happen consistently, but even a few random occurrences should signal you to check for concurrency problems.

Concurrency can even affect the slave SQL thread. I explicitly mention this to correct a possible misconception. One might think that because the slave SQL is single-threaded,^[7] it doesn’t require any troubleshooting techniques related to concurrency. This is actually not so: replication can be affected by concurrency on both the master and the slave. This chapter therefore contains a section devoted to replication.

Note

Let’s agree on some terms to start with. For each connected client, the MySQL server creates a separate thread. I will use the words thread, connection, or connection thread to refer a thread that serves a client connection. I will ...