How to Diagnose and Fix MySQL Slow Queries Caused by Deadlocks

Process Overview

In a Docker‑based test environment, service A called service B via Dubbo RPC and encountered a timeout. Debugging revealed that a simple SQL statement was taking more than 1000 ms. The database process list showed a deadlock and many threads stuck in sending data state.

Using Druid to Monitor SQL

We enabled Alibaba's Druid connection pool to monitor SQL execution. The monitoring UI displayed the average, slowest, and error counts for each statement. Normal queries run within 10 ms (30 ms for large data), so the observed 1000 ms was unacceptable.

Remote Monitoring with JMC

JMC (Java Mission Control) was used to attach to the Tomcat JVM via JMX. By adding the following JVM arguments to catalina.sh:

# In tomcat's conf/catalina.sh add:
-Dcom.sun.management.jmxremote=true
-Dcom.sun.management.jmxremote.port=8888
-Dcom.sun.management.jmxremote.ssl=false
-Dcom.sun.management.jmxremote.authenticate=false
-XX:+UnlockCommercialFeatures -XX:+FlightRecorder

JMC captured CPU, memory, and thread metrics, showing no obvious JVM issues. However, the I/O tab indicated that about 80 % of time was spent in I/O, with many TCP socket connections, prompting a deeper MySQL investigation.

MySQL Investigation

We examined InnoDB lock behavior. Using the MySQL client we ran: show processlist; Several threads were in Sending data state, and the info column displayed a query that generated primary‑key IDs:

select next_value into ret_val from `xxx` where table_name=tableName for update;

and the accompanying update statement:

update `xxx` set current_value=current_value+step, next_value=next_value+step where table_name=tableName;

The SELECT ... FOR UPDATE placed an exclusive lock on the sequence table, and the table lacked an index on table_name. Since InnoDB row locks are index‑based, concurrent queries using the same index key caused lock conflicts and a deadlock.

To resolve the issue we killed the blocking threads and added an index on table_name. After the index was created, the deadlock disappeared and query latency returned to normal.

Conclusion

When encountering MySQL performance problems, after ruling out application code, check for database deadlocks, examine the process list for blocked threads, and investigate the info field to pinpoint the offending SQL. Adding appropriate indexes and clearing blocked sessions can quickly restore performance.