Shulou(Shulou.com)05/31 Report--

This article mainly introduces "the implementation principle of kill session in MySQL". In daily operation, I believe that many people have doubts about the implementation principle of kill session in MySQL. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts about "the implementation principle of kill session in MySQL". Next, please follow the editor to study!

First, the simple process of combing and listing

First, simply comb through the life cycle of the execution of the statement:

For example, let's take the following implementation plan as an example:

Mysql > desc select * from T1 where name='gaopeng' +-+ | Id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +-- + -+ | 1 | SIMPLE | T1 | NULL | ALL | NULL | 14 | 10.00 | Using where | +- -+ 1 row in set 1 warning (1.67 sec)

A client starts a process through the mysql client and uniquely confirms a mysqld server process through socket IP:PORT.

The server process mysqld prepares a thread for network communication with the mysql client.

The mysql client sends commands to the mysqld server through the mysql net protocol.

The mysqld server thread unpacks and gets the commands sent by the mysql client.

The mysqld server thread passes authority authentication, syntax and semantic parsing, and then generates an execution plan through physical logic optimization.

Loop:

The mysqld server thread executes the statement through this execution plan. First, the innodb layer scans the first piece of data and returns it to the mysql layer for filtering, that is, whether the conditional name='gaopeng'; is met.

If it does, return it to the mysql client, and if it does not, continue loop.

Until the end of the loop, the entire data is returned.

A mysql client process and a mysqld server thread are involved, and they communicate through socket. If we want to kill a session, we obviously generally open a new mysql client process to connect to the mysqld server. Obviously, at this time, we need to open a server thread to dock with it in response to your kill command, then the figure is as follows:

Image.png

As shown in the figure, what we need to study is how thread 2 acts on thread 1. In fact, sharing memory between threads is very simple, which is determined by the characteristics of threads. In MySQL, we share such a variable THD::killed, which can be accessed not only by thread 1 but also by thread 2. In fact, this is achieved by relying on a THD::killed check at some point in the code. Let's first roughly describe the process of the kill session in this situation.

Thread 2 sets the THD::killed

Thread 1 checks whether its thread is set to KILL_CONNECTION after each line is scanned at the innodb layer.

If set to KILL_CONNECTION, then do the termination process accordingly

2. Different situations of kill

The flow of a kill for a select statement has been described above, but this is not always the case, so I briefly summarize the possible scenarios:

A command is being executed, as in the case of select on the (non-Innodb line lock waiting case).

Commands are being executed, such as DML wait (Innodb line lock wait case), which needs to be awakened by the InnoDB layer, and the code continues.

The command is being executed, and the MySQL layer waits for a command such as the sleep command, which requires the MySQL layer to wake up, and the code continues.

The idle state is waiting for the order to arrive.

Note that the above situation is where the thread to be killed is in, and the only way for the thread to initiate the command is to call the kill_one_thread function. I will describe it in detail below. As for the contents of the reference appendix for the wake-up operation, I will default to all of you here.

The thread that initiates the kill command

Here are the stack frames:

# 0 THD::awake (this=0x7ffe7800e870, state_to_set=THD::KILL_CONNECTION) at / root/mysqlc/percona-server-locks-detail-5.7.22/sql/sql_class.cc:2206#1 0x00000000015d5430 in kill_one_thread (thd=0x7ffe7c000b70, id=18, only_kill_query=false) at / root/mysqlc/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:6859#2 0x00000000015d5548 in sql_kill (thd=0x7ffe7c000b70, id=18 Only_kill_query=false) at / root/mysqlc/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:6887

Kill_one_thread

This is a main function, which gets the THD structure of the session based on the my_thread_id to be killed, which is followed by our kill, and then calls the THD::awake function as follows:

Tmp= Global_THD_manager::get_instance ()-> find_thd (& find_thd_with_id); / / get the THD structure of the session to be killed: tmp- > awake (only_kill_query? THD::KILL_QUERY: THD::KILL_CONNECTION); / / call the THD::awake command. We have THD::KILL_CONNECTION here.

THD::awake

This is the main function, which marks the THD::killed of the session to be killed as THD::KILL_CONNECTION, and then closes the socket connection, where the client process receives an error similar to the following:

ERROR 2013 (HY000): Lost connection to MySQL server during query

The wait to enter the innodb connection will then be terminated, and then the wake-up operation will be done. We will talk about why the wake-up operation is done later as follows:

Killed= state_to_set;\\ here sets the THD::killed status to KILL_CONNECTIONvio_cancel (active_vio, SHUT_RDWR);\\ closes the socket connection. After closing the socket connection, the client connection closes / * Interrupt target waiting inside a storage engine. * / if (state_to_set! = THD::NOT_KILLED) ha_kill_connection (this);\\ lock_trx_handle_waitmysql_mutex_lock (current_mutex); mysql_cond_broadcast (current_cond);\\ Wake up operation mysql_mutex_unlock (current_mutex); fourth, the thread to be killed is executing the command, as in the case of select above (non-Innodb line lock waiting case).

This is done by checking the return value in the appropriate place in the code, such as the following stack frame:

# 0 convert_error_code_to_mysql (error=DB_INTERRUPTED, flags=33, thd=0x7ffe74012f30) at / root/mysqlc/percona-server-locks-detail-5.7.22/storage/innobase/handler/ha_innodb.cc:2064#1 0x00000000019d651e in ha_innobase::general_fetch (this=0x7ffe7493c960, buf=0x7ffe7493cea0 "\ 377", direction=1, match_mode=0) at / root/mysqlc/percona-server-locks-detail-5.7.22/storage/innobase/handler/ha_innodb.cc:9907#2 0x00000000019d658b in ha_innobase::index_next (this=0x7ffe7493c960 Buf=0x7ffe7493cea0 "\ 377") at / root/mysqlc/percona-server-locks-detail-5.7.22/storage/innobase/handler/ha_innodb.cc:9929

We can find this part of the code in the function ha_innobase::general_fetch as follows:

Default: error = convert_error_code_to_mysql (ret, masked prebuild-> table- > flags, m_user_thd)

Here, if ret is equal to DB_INTERRUPTED, it will enter the thread exit logic, which we will see later.

DB_INTERRUPTED represents the terminated state of being killed, which is set by the following code (the so-called "burying point"):

If (trx_is_interrupted (prebuilt- > trx)) {ret = DB_INTERRUPTED

Trx_is_interrupted is very simple, and the code is as follows:

Return (trx & & trx- > mysql_thd & & thd_killed (trx- > mysql_thd)) and thd_killed are as follows: extern "C" int thd_killed (const MYSQL_THD thd) {if (thd = = NULL) return current_thd! = NULL? Current_thd- > killed: 0; return thd- > killed; / / returned THD::killed}

We can see that thd- > killed is exactly what we set the THD::killed to THD::KILL_CONNECTION by the kill thread earlier, and eventually this error will be returned layer by layer, causing the end of the handle_connection loop to enter the termination process.

Fifth, the thread to be killed is executing commands, such as DML waiting (Innodb line lock waiting), which needs to be awakened by the InnoDB layer, and the code will continue.

In this case, similar to the above, you also need to check whether the THD::killed state of the thread is THD::KILL_CONNECTION, but we know that if you are waiting for the pthread_cond_wait function, then there must be other threads to wake it up before the code will continue, otherwise it will never run to the judgment logic. Let's first take a look at the waiting stack frame.

# 0 0x00007ffff7bca68c in pthread_cond_wait@@GLIBC_2.3.2 () from / lib64/libpthread.so.0#1 0x0000000001ab1d35 in os_event::wait (this=0x7ffe74011f18) at / root/mysqlc/percona-server-locks-detail-5.7.22/storage/innobase/include/os0event.h:156#2 0x0000000001ab167d in os_event::wait_low (this=0x7ffe74011f18 Reset_sig_count=2) at / root/mysqlc/percona-server-locks-detail-5.7.22/storage/innobase/os/os0event.cc:131#3 0x0000000001ab1aa6 in os_event_wait_low (event=0x7ffe74011f18 Reset_sig_count=0) at / root/mysqlc/percona-server-locks-detail-5.7.22/storage/innobase/os/os0event.cc:328#4 0x0000000001a7305f in lock_wait_suspend_thread (thr=0x7ffe74005190) at / root/mysqlc/percona-server-locks-detail-5.7.22/storage/innobase/lock/lock0wait.cc:387#5 0x0000000001b391fc in row_mysql_handle_errors (new_err=0x7fffec091c4c, trx=0x7fffd78045f0, thr=0x7ffe74005190) Savept=0x0) at / root/mysqlc/percona-server-locks-detail-5.7.22/storage/innobase/row/row0mysql.cc:1312#6 0x0000000001b7c2ea in row_search_mvcc (buf=0x7ffe74010160 "\ 377", mode=PAGE_CUR_G, prebuilt=0x7ffe74004a20, match_mode=0, direction=0) at / root/mysqlc/percona-server-locks-detail-5.7.22/storage/innobase/row/row0sel.cc:6318#7 0x00000000019d5443 in ha_innobase::index_read (this=0x7ffe7400e280, buf=0x7ffe74010160 "\ 377", key_ptr=0x0, key_len=0 Find_flag=HA_READ_AFTER_KEY) at / root/mysqlc/percona-server-locks-detail-5.7.22/storage/innobase/handler/ha_innodb.cc:9536

This situation requires a thread to wake it up, but waking up here is not the same thing between the Innodb layer and the MySQL layer described above (described later). Who is going to wake it up? we can set the breakpoint at:

Event::broadcast

Event::signal

You can find out who did it on the. It turns out that there is a thread inside the Innodb that specializes in doing this. This thread is as follows:

# 0 os_event::broadcast (this=0x7ffe74011f18) at / root/mysqlc/percona-server-locks-detail-5.7.22/storage/innobase/include/os0event.h:166#1 0x0000000001ab1be8 in os_event::set (this=0x7ffe74011f18) at / root/mysqlc/percona-server-locks-detail-5.7.22/storage/innobase/include/os0event.h:61#2 0x0000000001ab1a3a in os_event_set (event=0x7ffe74011f18) at / root/mysqlc/percona-server-locks-detail-5.7.22/storage/ Innobase/os/os0event.cc:277#3 0x0000000001a73460 in lock_wait_release_thread_if_suspended (thr=0x7ffe70013360) at / root/mysqlc/percona-server-locks-detail-5.7.22/storage/innobase/lock/lock0wait.cc:491#4 0x0000000001a6a80d in lock_cancel_waiting_and_release (lock=0x30b1938) at / root/mysqlc/percona-server-locks-detail-5.7.22/storage/innobase/lock/lock0lock.cc:6896#5 0x0000000001a736a6 in lock_wait_check_and_cancel (slot=0x7fff0060a2a0) at / root/mysqlc/percona-server-locks-detail-5.7.22/storage/innobase/lock/lock0wait.cc:539#6 0x0000000001a7383d in lock_wait_timeout_thread (arg=0x0) at / root/mysqlc/percona-server-locks-detail-5.7.22/storage/innobase/lock/lock0wait.cc:599#7 0x00007ffff7bc6aa1 in start_thread () from / lib64/libpthread.so.0#8 0x00007ffff6719bcd in clone () from / lib64/libc.so.6

If we examine the lock_wait_check_and_cancel code a little bit, we will see something like this:

If (trx_is_interrupted (trx) | | (slot- > wait_timeout)

< 100000000 && (wait_time >

(double) slot- > wait_timeout | | wait_time

< 0))) { /* Timeout exceeded or a wrap-around in system time counter: cancel the lock request queued by the transaction and release possible other transactions waiting behind; it is possible that the lock has already been granted: in that case do nothing */ lock_mutex_enter(); trx_mutex_enter(trx); if (trx->

Lock.wait_lock! = NULL & &! trx_is_high_priority (trx) {ut_a (trx- > lock.que_state = = TRX_QUE_LOCK_WAIT); lock_cancel_waiting_and_release (trx- > lock.wait_lock);} lock_mutex_exit (); trx_mutex_exit (trx);}

We can see that trx_is_interrupted makes a judgment on THD::KILL_CONNECTION in key places, and of course this thread also wakes up the Innodb row lock timeout. This thread can be seen as follows:

| | 35 | 4036 | innodb/srv_lock_timeout_thread | NULL | BACKGROUND | NULL | NULL |

If the statement that is being executed, the one that needs to be rolled back will do the following later:

If (thd- > is_error () | | (thd- > variables.option_bits & OPTION_MASTER_SQL_ERROR)) trans_rollback_stmt (thd); VI. Summary of Innodb kill logic trigger phase

Generally speaking, Innodb determines whether a thread has been killed by what teacher Dingqi called "burying point". What its "embedding point" does is check whether the THD::killed state of the thread is THD::KILL_CONNECTION. This embedding point has a detection cycle, and it is impossible to check every line of code once, so I probably summarized the check location of the buried point:

When each row of records is returned to the MySQL layer

If you encounter an Innodb row lock in the pthread_cond_wait state, you need the srv_lock_timeout_ thread to wake it up and do the broadcast operation first.

You can actually search in full code when the location of ret = DB_INTERRUPTED; is the "burying point" of the Innodb layer.

The idle state of the thread to be killed is waiting for the arrival of the command

This situation is relatively simple. In the idle state, the thread to be killed will always block the socket read, because initiating the kill thread closes the socket channel, and the thread to be killed can easily perceive it. Here is the intercept in net_read_raw_loop

/ * On failure, propagate the error code. * / if (count) {/ * Socket should be closed. * / net- > error= 2; / * Interrupted by a timeout? * / if (! eof & & vio_was_timeout (net- > vio)) net- > last_errno= ER_NET_READ_INTERRUPTED; else net- > last_errno= ER_NET_READ_ERROR;#ifdef MYSQL_SERVER my_error (net- > last_errno, MYF (0)); / / trigger # endif} here

This ends the handle_connection loop and enters the termination process. In this case, clean_up will do a rollback operation in release_resources

8. The thread to be killed is executing the command, and the MySQL layer waits for sleep commands such as MySQL layer to wake up, and the code continues.

Remember that when our initiator thread called THD::awake, it finally did a wake-up operation? Like Innodb layer row lock waiting, if you don't wake up, then the code can't move forward and can't reach the buried point set in the Innodb layer. I'll use sleep as an example to describe it below. First, let's take a look at the logic of sleep. In fact, there is the following code in the Item_func_sleep::val_int function:

Timed_cond.set_timeout ((ulonglong) (timeout * 1000000000.0)); / / here the value of sleep is added to the structure of timed_cond mysql_cond_init (key_item_func_sleep_cond, & cond); / / pthread_cond_init initializes cond mysql_mutex_lock (& LOCK_item_func_sleep) / / lock pthread_mutex_lock pair LOCK_item_func_sleep mutex THD::enter_cond thd- > ENTER_COND (& cond, & LOCK_item_func_sleep, & stage_user_sleep, NULL) / / # define ENTER_COND (C, M, S, O) enter_cond (C, M, S, O, _ func__, _ _ FILE__, _ LINE__) / / this step cond will be passed to other threads in THD to get the cond, and you can wake it up. When KILL is triggered, you need to wake it up DEBUG_SYNC (current_thd, "func_sleep_before_sleep") through this condition variable; error= 0 Thd_wait_begin (thd, THD_WAIT_SLEEP); while (! thd- > killed) {error= timed_cond.wait (& cond, & LOCK_item_func_sleep); / / here it looks like waitable and sleep function implementation calls the underlying pthread_cond_timedwait function implementation, and can be awakened by the condition variable if (error= = ETIMEDOUT | | error= = ETIME) break; error= 0;}

Let's prove here that the following is the stack frame of the sleep thread:

[Switching to Thread 0x7fffec064700 (LWP 4738)] # 0 THD::enter_cond (this=0x7ffe70000950, cond=0x7fffec061510, mutex=0x2e4d6a0, stage=0x2d8b630, old_stage=0x0, src_function=0x1f2598c "val_int", src_file=0x1f232e8 "/ root/mysqlc/percona-server-locks-detail-5.7.22/sql/item_func.cc" Src_line=6057) at / root/mysqlc/percona-server-locks-detail-5.7.22/sql/sql_class.h:3395#1 0x00000000010265d8 in Item_func_sleep::val_int (this=0x7ffe70006210) at / root/mysqlc/percona-server-locks-detail-5.7.22/sql/item_func.cc:6057#2 0x0000000000fafea5 in Item::send (this=0x7ffe70006210, protocol=0x7ffe70001c68) Buffer=0x7fffec0619b0) at / root/mysqlc/percona-server-locks-detail-5.7.22/sql/item.cc:7564#3 0x000000000156b10c in THD::send_result_set_row (this=0x7ffe70000950, row_items=0x7ffe700055d8) at / root/mysqlc/percona-server-locks-detail-5.7.22/sql/sql_class.cc:5026#4 0x0000000001565708 in Query_result_send::send_data (this=0x7ffe700063a8, items=...) At / root/mysqlc/percona-server-locks-detail-5.7.22/sql/sql_class.cc:2932

Notice the address of the structure cond=0x7fffec061510 here, which will eventually be passed to the THD so that other threads can get it later. Let's take a look at the address of the condition variable that THD::awake wakes up as follows:

[Switching to Thread 0x7fffec0f7700 (LWP 4051)] Breakpoint 2, THD::awake (this=0x7ffe70000950, state_to_set=THD::KILL_CONNECTION) at / root/mysqlc/percona-server-locks-detail-5.7.22/sql/sql_class.cc:2206. (gdb) n2288 mysql_cond_broadcast (current_cond); (gdb) p current_cond$6 = (mysql_cond_t * volatile) 0x7fffec061510

We can see that it is also 0x7fffec061510, and they are the same condition variable, which proves that it is THD::awake that finally awakens our sleep. The code continues, and when it continues, it reaches the "burying point", and eventually the handle_connection loop terminates to reach the termination process.

9. The thread to be killed terminates

Finally, the exit condition is reached in the loop of handle_connection, and the connection termination logic is as follows:

{while (thd_connection_alive (thd)) / {if (do_command (thd)) break;} end_connection (thd);} close_connection (thd, 0, false, false); thd- > get_stmt_da ()-> reset_diagnostics_area (); thd- > release_resources ();. Thd_manager- > remove_thd (thd); / / take it off the THD list here, and then the thread in the KILLED state is gone. Connection_handler_manager::dec_connection_count (extra_port_connection);.... Delete thd; if (abort_loop) / / Server is shutting down so end the pthread. Break; channel_info= Per_thread_connection_handler::block_until_new_connection (); if (channel_info= = NULL) break; pthread_reused= true

Here we find that we will go through several functions end_connection/get_stmt_da ()-> reset_diagnostics_area () / release_resources and then come to thd_manager- > remove_thd (thd), and eventually this link will be reused. In fact, we won't see the state in show processlist disappear until release_resources is done. You can modify the code and add the sleep (10) function before and after the release_resources function to verify it, as follows:

Sleep (10); thd- > release_resources (); sleep (10)

The test results are as follows:

Mysql > show processlist; kill 31 * 33 * * +-- + | Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined | +- -+ | 7 | root | localhost | NULL | Query | 0 | starting | show processlist | 0 | 0 | 31 | root | localhost | NULL | Sleep | 35 | NULL | 1 | 0 | 33 | root | localhost | Sleep | 32 | NULL | 1 | 0 | | 35 | root | localhost | NULL | Sleep | 29 | NULL | 1 | 0 | + +-+-+ mysql > show processlist +-+ | Id | User | Host | db | | Command | Time | State | Info | Rows_sent | Rows_examined | + -+ | 7 | root | localhost | NULL | Query | 0 | starting | show processlist | 0 | 0 | 31 | root | localhost | NULL | Killed | 44 | cleaning up | NULL | 1 | 0 | 33 | root | localhost | NULL | Killed | 41 | cleaning up | NULL | 1 | 0 | | 35 | root | localhost | NULL | Killed | 38 | cleaning up | NULL | 1 | 0 | +-+- -+ 4 rows in set (0.02 sec) mysql > show processlist +-- + | Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined | +- -+ | 7 | root | localhost | NULL | Query | 0 | starting | show processlist | 0 | 0 | +-- + -+

You can see that it takes about 10 seconds for the Killed state to disappear, while the Killed state does not appear for 20 seconds, so you can be sure that the Killed thread will disappear in the show processlist after this step is completed.

X. summary

A kill action is a thread that acts on another thread, and the bridge between them is the shared variable THD:killed.

For the Innodb layer, if there is a line lock waiting, kill wakes it up through the thread srv_lock_timeout_thread, and then continues the code logic.

Waiting for the MySQL layer also needs to be woken up, which is done by the kill command thread, and then continues the code logic

The thread in the killed state in show processlist is removed after the whole work is completed, such as rollback, etc.

The response to the kill status is through some pre-set checkpoint, which will always be in the Killed state if the checkpoint is not reached

Even if the checkpoint is reached, if there are other Mutex lock problems in the code logic that cannot be exited, the Killed state persists as follows (BUG?):

MySQL:kill and show global status command hang to live in a train of https://www.jianshu.com/p/70614ae01046

At this point, the study of "the implementation principle of kill session in MySQL" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.