How does MySQL kill database idle transactions? 01/14 Update SLTechnology News&Howtos

How does MySQL kill database idle transactions?

2025-01-14 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

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

Editor to share with you how MySQL killed the database idle transactions, I hope you will gain something after reading this article, let's discuss it together!

We often encounter a situation, that is, the network is disconnected or the program Bug causes the COMMIT/ROLLBACK statement not to be sent to the database, and the thread is not released, but the lock waiting for online transactions is serious, and the number of connections soars, especially in the test library, which occasionally happens online, so we want to add a function to kill idle transactions for MySQL. The following editor to explain MySQ how to kill a database idle transactions?

How to kill database idle transactions one by one with MySQ

There are many uncertainties in the MySQL Server layer, but the safest implementation is in the storage engine layer. We almost always use InnoDB/XtraDB, so we modify it based on Percona, and the Oracle version of MySQL can also be modified accordingly.

Demand:

1. A transaction starts, and if the last statement within the transaction is executed for more than one time (innodb_idle_trx_timeout), the link should be closed.

two。 If the transaction is a read-only transaction, it is harmless and does not need to be closed because it is not locked.

Although this idea is pointed out by Percona that there may be "Even though SELECT queries do not place row locks by default (there are exceptions), they can still block undo log records from being purged." in Alexey Kopytov. But we do have a scenario where SELECT can never be kill unless later INSERT/UPDATE/DELETE occurs, so I modify it according to the characteristics of our business.

Yasufumi Kinoshita and Alexey Kopytov of Percona have suggested that pure SELECT transactions should not be kill, but the scheme controlled by one parameter has not been accepted by Alexey Kopytov. As a general processing, I have proposed to use two variables to control the idle timeout of read-only transactions and the idle timeout of locked transactions, respectively. I am still waiting for a reply from Percona, because this scheme is still being tested, so do not open it for modification, of course, if you are familiar with the MYSQL source code. I put forward this idea and you must know how to divide it into these two parameters to control.

According to these two requirements, we design the method. First of all, we think that this function must be the most convenient to put on InnoDB Master Thread. Master Thread schedules once a second, so it can check idle transactions by the way, and then close it. Because it is not safe to operate trx- > mysql_thd in transactions, generally speaking, it is best to replace it with Thread ID operation in the InnoDB layer. Besides ha_innodb.cc, you can't drink THD elsewhere in InnoDB. So the thread values needed in Master Thread need to be calculated in ha_innodb to pass the integer or Boolean return value to the master thread call.

First, we add a parameter: idle_trx_timeout, which indicates how long the transaction times out before the next statement occurs.

Add the following code to register the idle_trx_timeout variable under the "/ * plugin options * /" comment of storage/innodb_plugin/srv/srv0srv.c.

If (srv_idle_trx_timeout & & trx_sys) {

Trx_t* trx

Time_t now

Rescan_idle:

Now = time (NULL)

Mutex_enter & kernel_mutex)

Trx = UT_LIST_GET_FIRST (trx_sys- > mysql_trx_list); # get the first transaction from the current transaction list

While (trx) {# cycle through each transaction in turn to check

If (trx- > conc_state = = TRX_ACTIVE

& & trx- > mysql_thd

& & innobase_thd_is_idle (trx- > mysql_thd) {# if the transaction is still alive and its state is idle

Ib_int64_t start_time = innobase_thd_get_start_time (trx- > mysql_thd); # get the start time of the last statement of the thread

Ulong thd_id = innobase_thd_get_thread_id (trx- > mysql_thd); # get thread ID because it is not safe to manipulate THD directly in the storage engine

If (trx- > last_stmt_start! = start_time) {# if the last statement start time of the transaction is not equal to the last statement start time of the thread, the transaction is new

Trx- > idle_start = now; # start time of idle update transaction

Trx- > last_stmt_start = start_time; # the last statement start time of the update transaction

} else if (difftime (now, trx- > idle_start) # if the transaction is not new and part of it has been executed, determine how long the idle time is.

> srv_idle_trx_timeout) {# kill the link if the idle time exceeds the threshold

/ * kill the session * /

Mutex_exit & kernel_mutex)

Thd_kill (thd_id); # Kill links

Goto rescan_idle

}

Trx = UT_LIST_GET_NEXT (mysql_trx_list, trx); # check the next transaction

}

Mutex_exit & kernel_mutex)

}

The code goes down and adds the following to the innobase_system_variables structure: