Shulou(Shulou.com)06/01 Report--

Foreword:

"getting started MySQL" series of articles have been completed, in the future, my article will still be based on MySQL, mainly recording the recent work and study encountered scenes or their own feelings and ideas, the follow-up articles may not be so coherent, but I still hope you will support me. To get back to the point, this article mainly introduces the relevant contents of MySQL long transactions, such as what happens when a transaction that we opened has not been committed or rolled back, and how to deal with transaction waiting. This article will give you the answer.

Note: this article does not focus on transaction isolation levels and related features. Instead, it introduces the hazards related to long-term transactions and the methods of monitoring and handling. This article is based on the MySQL5.7.23 version, the RR isolation level experiment.

1. What is a long transaction?

First of all, we need to know what is a long transaction. As the name implies, a transaction that runs for a long time and has not been committed for a long time can be called a big transaction. Such transactions often cause a lot of blocking and lock timeouts, which are easy to cause master-slave delay, so long transactions should be avoided as far as possible.

Next, I will demonstrate how to open a transaction and simulate a long transaction:

# suppose we have a stu_tb table The structure and data are as follows: mysql > show create table stu_tb\ gateway * 1. Row * * Table: stu_tbCreate Table: CREATE TABLE `stu_ tb` (`increment_ id` int (11) NOT NULL AUTO_INCREMENT COMMENT 'self-increasing primary key', `stu_ id`int (11) NOT NULL COMMENT 'student ID' `Name` varchar (20) DEFAULT NULL COMMENT 'student name', `create_ time`timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'creation time', `update_ time`timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'modification time', PRIMARY KEY (`increment_ id`), UNIQUE KEY `uk_stu_ id` (`stu_ id`) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COMMENT=' test student table '1row in set (0.1 sec) mysql > select * from stu_tb +-+ | increment_id | stu_id | stu_name | create_time | update_time | + -+ | 1 | 1001 | from1 | 2019-09-15 14:27:34 | 2019-09-15 14:27:34 | | 2 | 1002 | dfsfd | 2019-09-15 14:27:34 | 2019-09-15 14:27:34 | | 3 | 1003 | fdgfg | 2019-09-15 14:27:34 | 2019-09-15 14:27:34 | | 4 | 1004 | sdfsdf | 2019-09-15 14:27:34 | 2019-09-15 14:27:34 | | 5 | 1005 | dsfsdg | 2019-09-15 14:27:34 | 2019-09-15 14:27:34 | 6 | 1006 | fgd | 2019-09-15 14:27:34 | 2019-09-15 140 2734 | 7 | 1007 | Fgds | 2019-09-15 14:27:34 | 2019-09-15 14:27:34 | | 8 | 1008 | dgfsa | 2019-09-15 14:27:34 | 2019-09-15 14:27:34 | +- -+ 8 rows in set (0.00 sec) # explicitly open the transaction Begin or start transactionmysql > start transaction is available Query OK, 0 rows affected (0.00 sec) mysql > select * from stu_tb where stu_id = 1006 for update +-+ | increment_id | stu_id | stu_name | create_time | update_time | + -+ | 6 | 1006 | fgd | 2019-09-15 14:27:34 | 2019-09-15 14:27:34 | +- -+ 1 row in set (0.01 sec) # if we don't commit the last transaction in time Then the transaction becomes a long transaction and waits when other sessions want to manipulate the data.

two。 How to find long transactions

When faced with the problem of transaction waiting, the first thing we need to do is to find the transaction that is being executed. The information_schema.INNODB_TRX table contains the information about the current running transactions within the innodb. This table gives the start time of the transaction, and we can get the running time of the transaction with a little calculation.

Mysql > select t.* To_seconds (now ())-to_seconds (t.trx_started) idle_time from INFORMATION_SCHEMA.INNODB_TRX t\ Gmail * 1. Row * * trx_id: 6168 trx_state: RUNNING trx_started: 2019-09-16 11:08:27 trx _ requested_lock_id: NULL trx_wait_started: NULL trx_weight: 3 trx_mysql_thread_id: 11 trx_query: NULL trx_operation_state: NULL trx_tables_in_use: 0 trx_tables_locked: 1 trx_lock_structs: 3 trx_lock_memory_bytes: 1136 trx_rows_locked: 2 trx_rows_modified: 0 trx_concurrency_tickets: 0 trx_isolation_level: REPEATABLE READ trx_unique_checks: 1 trx _ foreign_key_checks: 1trx_last_foreign_key_error: NULL trx_adaptive_hash_latched: 0trx_ adaptive_hash_timeout: 0trx_ is_read_only: 0trx_autocommit_non_locking: 0 idle_time: 170

In the result, the idle_time is computationally generated and is also the duration of the transaction. But the trx_query of the transaction is NUL, which does not mean that the transaction does not execute anything. A transaction may contain multiple SQL, which is no longer displayed if the SQL execution is complete. The current transaction is executing, and innodb does not know whether there is any sql in the transaction and when it will be commit. Therefore, trx_query cannot provide meaningful information.

What if we want to see the SQL that this transaction executed and see if we can kill the long transaction? We can query it with other system tables. The specific query SQL is as follows:

Mysql > select now (), (UNIX_TIMESTAMP (now ())-UNIX_TIMESTAMP (a.trx_started)) diff_sec,b.id,b.user,b.host,b.db D.SQL_TEXT from information_schema.innodb_trx an inner join-> information_schema.PROCESSLIST b-> on a.TRX_MYSQL_THREAD_ID=b.id and b.command = 'Sleep'-> inner join performance_schema.threads c ON b.id = c.PROCESSLIST_ID-> inner join performance_schema.events_statements_current d ON d.THREAD_ID = c.THREAD_ID +- + | now () | diff_sec | id | user | host | db | SQL_TEXT | +- -+ | 2019-09-16 14:06:26 | 54 | 17 | root | localhost | testdb | select * from stu_tb where stu_id = 1006 for update | +-+-- -+

In the above results, diff_sec and the above idle_time have the same meaning, and both represent the number of seconds the transaction lasts. SQL_TEXT indicates the SQL that the transaction has just executed. However, the above statement can only find the SQL executed at the end of the transaction. We know that a transaction may contain multiple SQL, so we want to query which SQL has been executed by this uncommitted transaction, and whether it can be satisfied. The answer is that the combination of events_statements_history system tables can also meet the requirements. The following statement will query all SQL executed by the transaction:

Mysql > select now (), (UNIX_TIMESTAMP (now ())-UNIX_TIMESTAMP (a.trx_started)) diff_sec,b.id,b.user,b.host,b.db D.SQL_TEXT from information_schema.innodb_trx an inner join-> information_schema.PROCESSLIST b-> on a.TRX_MYSQL_THREAD_ID=b.id and b.command = 'Sleep'-> inner join performance_schema.threads c ON b.id = c.PROCESSLIST_ID-> inner join performance_schema.events_statements_current d ON d.THREAD_ID = c.THREAD_ID +- + | now () | diff_sec | id | user | host | db | SQL_TEXT | +- -+ | 2019-09-16 14:06:26 | 54 | 17 | root | localhost | testdb | select * from stu_tb where stu_id = 1006 for update | +-+-- -+

From the above results, we can see all the SQL that the transaction has executed from the beginning to the present. When we query all the relevant information about the transaction, we can determine whether the transaction can be killed or not, so as not to affect other transactions and cause waiting.

With a slight expansion here, long transactions can easily cause blocking or deadlock. Usually, we can first query the sys.innodb_lock_waits view to determine whether there is transaction blocking:

# suppose one transaction executes select * from stu_tb where stu_id = 1006 for update# and another transaction executes update stu_tb set stu_name = 'wang' where stu_id = 1006mysql > select * from sys.innodb_lock_waits\ gateway * 1. Row * * Wait_started: 2019-09-16 14:34:32 wait_age: 00:00:03 wait_age_secs: 3 locked_table: `testdb`.`stu _ tb` locked_index: uk_stu_id locked_type: RECORD waiting_trx_id: 6178 waiting_trx_started: 2019-09-16 14:34:32 waiting_trx_age: 00:00:03 waiting_trx_ Rows_locked: 1 waiting_trx_rows_modified: 0 waiting_pid: 19 waiting_query: update stu_tb set stu_name = 'wang' where stu_id = 1006 waiting_lock_id: 6178 blocking_trx_id 47 NULL blocking_lock_id 7 waiting_lock_mode: X blocking_trx_id: 6177 blocking_pid: 20 blocking_query: NULL blocking_lock_id: 6177 Mode: X blocking_trx_started: 2019-09-16 14:18:44 blocking_trx_age: 00:15:51 blocking_trx_rows_locked: 2 blocking_trx_rows_modified: 0sqlkilling blocking _ query: KILL QUERY 20sql_kill_blocking_connection: KILL 20

The above results show the blocked SQL and the type of lock, and more powerfully, the statement to kill the session is also given. However, the SQL blocking session execution was not found. If we want to find out more details, we can use the following statement:

Mysql > SELECT-> tmp.*,-> c.SQL_Text blocking_sql_text,-> p.HOST blocking_host-> FROM-> (- > SELECT-> r.trx_state wating_trx_state,-> r.trx_id waiting_trx_id,-> r.trx_mysql_thread_Id waiting_thread,-> r.trx_query waiting_query,-> b.trx_state blocking_trx_state -> b.trx_id blocking_trx_id,-> b.trx_mysql_thread_id blocking_thread,-> b.trx_query blocking_query-> FROM-> information_schema.innodb_lock_waits w-> INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id-> INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id->) tmp -> information_schema.PROCESSLIST p,-> PERFORMANCE_SCHEMA.events_statements_current c -> PERFORMANCE_SCHEMA.threads t-> WHERE-> tmp.blocking_thread = p.id-> AND t.thread_id = c.THREAD_ID-> AND t.PROCESSLIST_ID = p.id\ gateway * 1. Row * * wating_trx_state: LOCK WAIT Waiting_trx_id: 6180 waiting_thread: 19 waiting_query: update stu_tb set stu_name = 'wang' where stu_id = 1006blocking_trx_state: RUNNING blocking_trx_id: 6177 blocking_thread: 20 blocking_query: NULL blocking_sql_text: select * from stu_tb where stu_id = 1006 for update blocking_host: localhost

The above results are clearer, and we can clearly see the statements executed by the transactions on the blocking side and the blocked side, which helps us to troubleshoot and confirm whether the blocked session can be killed.

3. Chief Supervisor transaction

In real work, we need to monitor long transactions and define a threshold. For example, transactions with an execution time of more than 30 seconds are long transactions, which are required to be recorded and alerted to remind managers to deal with them. The following monitoring script is given. You can refer to it and change it according to your needs:

#! / bin/bash#-# FileName: long_trx.sh# Describe: monitor long transaction# Revision: 1.The Date: 2019-09-16 # Author: wang/usr/local/mysql/bin/mysql-N-uroot-pxxxxxx-e "select now () (UNIX_TIMESTAMP (now ())-UNIX_TIMESTAMP (a.trx_started)) diff_sec,b.id,b.user,b.host,b.db,d.SQL_TEXT from information_schema.innodb_trx an inner joininformation_schema.PROCESSLIST bon a.TRX_MYSQL_THREAD_ID=b.id and b.command = 'Sleep'inner join performance_schema.threads c ON b.id = c.PROCESSLIST_IDinner join performance_schema.events_statements_current d ON d.THREAD_ID = c.THREAD_ID | | while read A B C D E F G Hdo if ["$C"-gt 30] then echo $(date + "% Y-%m-%d% H:%M:%S") echo "processid [$D] $in db [$G] hold transaction time $C SQL:$H" fidone > > / tmp/longtransaction.txt

To put it simply, the-gt 30 here means 30 seconds. As long as it exceeds 30 seconds, it is considered to be a long transaction, and can be customized according to the actual needs. Add the script to the scheduled task and execute it.

Summary:

This paper mainly introduces the related contents of long-term transaction, how to find long-term transaction, how to deal with long-term transaction, how to monitor long-term transaction. There may be some friends who don't know much about affairs. I hope this article will help you. Due to the large number of query transaction related statements listed in this article, it is summarized as follows:

# query all running transactions and running time select t. Running seconds (now ())-to_seconds (t.trx_started) idle_time from INFORMATION_SCHEMA.INNODB_TRX t\ G# query transaction details and executed SQLselect now (), (UNIX_TIMESTAMP (now ())-UNIX_TIMESTAMP (a.trx_started)) diff_sec,b.id,b.user,b.host,b.db D.SQL_TEXT from information_schema.innodb_trx an inner join information_schema.PROCESSLIST bon a.TRX_MYSQL_THREAD_ID=b.id and b.command = 'Sleep'inner join performance_schema.threads c ON b.id = c.PROCESSLIST_IDinner join performance_schema.events_statements_current d ON d.THREAD_ID = c.THREAD_ID # query all historical SQL records executed by the transaction SELECT ps.id 'PROCESS ID', ps.USER, ps.HOST, esh.EVENT_ID, trx.trx_started, esh.event_name' EVENT NAME', esh.sql_text 'SQL' Ps.time FROM PERFORMANCE_SCHEMA.events_statements_history esh JOIN PERFORMANCE_SCHEMA.threads th ON esh.thread_id = th.thread_id JOIN information_schema.PROCESSLIST ps ON ps.id = th.processlist_id LEFT JOIN information_schema.innodb_trx trx ON trx.trx_mysql_thread_id = ps.id WHERE trx.trx_id IS NOT NULL AND ps.USER! = 'SYSTEM_USER' ORDER BY esh.EVENT_ID # simple query transaction lock select * from sys.innodb_lock_waits\ G # query transaction lock details SELECT tmp.*, c.SQL_Text blocking_sql_text, p.HOST blocking_host FROM (SELECT r.trx_state wating_trx_state, r.trx_id waiting_trx_id, r.trx_mysql_thread_Id waiting_thread, r.trx_query waiting_query, b.trx_state blocking_trx_state, b.trx_id blocking_trx_id B.trx_mysql_thread_id blocking_thread, b.trx_query blocking_query FROM information_schema.innodb_lock_waits w INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id) tmp, information_schema.PROCESSLIST p, PERFORMANCE_SCHEMA.events_statements_current c PERFORMANCE_SCHEMA.threads t WHERE tmp.blocking_thread = p.id AND t.thread_id = c.THREAD_ID AND t.PROCESSLIST_ID = p.id\ G

Summary

The above is the whole content of this article. I hope the content of this article has a certain reference and learning value for everyone's study or work. Thank you for your support.

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.