Here is part two of my MySQL with Diagrams series (Here’s part one – MySQL with Diagrams Part One: Replication Architecture). We are going to explore how MySQL handles thread termination using the KILL command, as visualized in the provided diagram, and provide sample demonstrations to help you better understand.
Many people think they know this topic, but it is unknown or known incorrectly. KILLs are not handled by the thread that runs the KILL command; they are handled by the thread itself, which is killed by another thread. It’s a bit confusing, which is why diagrams work well for this.
The diagram illustrates the interaction between two threads:
Thread 10: The thread enters a loop where it processes a query in chunks. For operations like ORDER BY or GROUP BY or ALTER TABLE, it reads blocks of rows, processes them, and acknowledges the rows. After processing each block, it checks the thd_killed() flag to determine if it should continue or terminate.
Thread 12: This thread sends a KILL command, which sets the kill flag for Thread 10 using the function thd_set_kill_status().
Kill flag behavior
If the kill flag remains unset (thd_killed()=0), Thread 10 continues its processing. When the flag is set (thd_killed()=1), the query execution is aborted, the temporary table is discarded, and any active transactions are rolled back.
The function is_killed() checks whether the thread should terminate:
|
1 |
bool Sql_data_context::is_killed() const {<br> const auto kill = thd_killed(get_thd());<br> DBUG_LOG("debug", "is_killed:" << kill);<br> if (0 == kill) return false;<br> return ER_QUERY_INTERRUPTED != kill;<br>} |
If kill == 0, the thread continues running. If kill != 0the thread is interrupted, further execution is stopped.
By understanding the KILL command and how MySQL manages thread lifecycle, you can decide or understand why it takes more time than expected. As always, double-check your query and avoid killing threads indiscriminately in production environments, as this could disrupt critical operations. It was good to be safe than sorry.
Also, what should be next in the series? Comments are more than welcome, as always.
