In-depth understanding of MySQL long transactions

Preface:

This article mainly introduces the content related to MySQL long transactions. For example, what will happen if we start a transaction but it has not been committed or rolled back? How should we deal with the transaction waiting situation? This article will give you the answer.

Note: This article does not focus on discussing transaction isolation levels and related features. Instead, it introduces the hazards associated with long transactions and how to monitor and handle them. This article is based on experiments conducted on MySQL version 5.7.23 and the non-repeatable read (RR) isolation level. (The \G syntax can be used to make the query structure more readable, but it can only be used from the mysql command line.)

1. What is a long transaction?

First of all, we need to know what a long transaction is. As the name suggests, it is a transaction that runs for a long time and is not committed for a long time. It can also be called a large transaction. This type of transaction often causes a lot of blocking and lock timeouts, which can easily cause master-slave delays. Try to avoid using long transactions.

Below I will demonstrate how to start a transaction and simulate a long transaction:

#Suppose we have a stu_tb table with the following structure and data mysql> show create table stu_tb\G
*************************** 1. row ***************************
    Table: stu_tb
Create Table: CREATE TABLE `stu_tb` (
 `increment_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Auto-increment primary key',
 `stu_id` int(11) NOT NULL COMMENT 'Student ID',
 `stu_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'
1 row in set (0.01 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 14:27:34 |
| 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)

#Open a transaction explicitly, use begin or start transaction
mysql> start transaction;
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 do not commit the previous transaction in time, then this transaction becomes a long transaction. When other sessions want to operate on this data, they will keep waiting.

2. How to find long transactions

When we encounter a transaction waiting problem, the first thing we need to do is to find the transaction being executed. The information_schema.INNODB_TRX table contains the transaction information currently running inside innodb. This table gives the start time of the transaction. 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 \G
*************************** 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: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
     trx_is_read_only: 0
trx_autocommit_non_locking: 0
         idle_time: 170

In the result, idle_time is calculated and is the duration of the transaction. However, the trx_query of the transaction is NUL. This does not mean that the transaction has not executed anything. A transaction may contain multiple SQL statements. If the SQL statement is executed, it will no longer be displayed. The current transaction is being executed, and InnoDB does not know whether there will be any subsequent SQL statements for this transaction, or when it will be committed. Therefore trx_query cannot provide meaningful information.

What if we want to see the SQL executed by this transaction to see if we can kill the long transaction? We can query it in conjunction 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 has the same meaning as idle_time above, which is the number of seconds that this transaction lasts. SQL_TEXT indicates the SQL just executed by the transaction. However, the above statement can only find the SQL executed last in the transaction. We know that a transaction may contain multiple SQLs. Then we want to query which SQLs have been executed by this uncommitted transaction. Can it be satisfied? The answer is that it can also be satisfied by combining the events_statements_history system table. The following statement will query all SQL executed by the transaction:

mysql> 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;
+------------+------+-----------+----------+---------------------+------------------------------+-----------------------------------------------------+------+
| PROCESS ID | USER | HOST | EVENT_ID | trx_started | EVENT NAME | SQL | time |
+------------+------+-----------+----------+---------------------+------------------------------+-----------------------------------------------------+------+
| 20 | root | localhost | 1 | 2019-09-16 14:18:44 | statement/sql/select | select @@version_comment limit 1 | 60 |
| 20 | root | localhost | 2 | 2019-09-16 14:18:44 | statement/sql/begin | start transaction | 60 |
| 20 | root | localhost | 3 | 2019-09-16 14:18:44 | statement/sql/select | SELECT DATABASE() | 60 |
| 20 | root | localhost | 4 | 2019-09-16 14:18:44 | statement/com/Init DB | NULL | 60 |
| 20 | root | localhost | 5 | 2019-09-16 14:18:44 | statement/sql/show_databases | show databases | 60 |
| 20 | root | localhost | 6 | 2019-09-16 14:18:44 | statement/sql/show_tables | show tables | 60 |
| 20 | root | localhost | 7 | 2019-09-16 14:18:44 | statement/com/Field List | NULL | 60 |
| 20 | root | localhost | 8 | 2019-09-16 14:18:44 | statement/com/Field List | NULL | 60 |
| 20 | root | localhost | 9 | 2019-09-16 14:18:44 | statement/sql/select | select * from stu_tb | 60 |
| 20 | root | localhost | 10 | 2019-09-16 14:18:44 | statement/sql/select | select * from stu_tb where stu_id = 1006 for update | 60 |
+------------+------+-----------+----------+---------------------+------------------------------+-----------------------------------------------------+------+

From the above results, we can see all the SQL statements executed by the transaction from the beginning to now. After we have queried all the relevant information of the transaction, we can determine whether the transaction can be killed to avoid affecting other transactions and causing waiting.

Let me expand on this a little bit. Long transactions are very likely to cause blocking or deadlock. Usually, we can first query the sys.innodb_lock_waits view to determine whether there is any transaction blocking:

#Assume a transaction executes select * from stu_tb where stu_id = 1006 for update
#Another transaction executes update stu_tb set stu_name = 'wang' where stu_id = 1006

mysql> select * from sys.innodb_lock_waits\G
*************************** 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:47:4:7
      waiting_lock_mode: X
       blocking_trx_id: 6177
        blocking_pid: 20
       blocking_query: NULL
      blocking_lock_id: 6177:47:4:7
     blocking_lock_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: 0
   sql_kill_blocking_query: KILL QUERY 20
sql_kill_blocking_connection: KILL 20

The above results show the blocked SQL and the type of lock. What’s more powerful is that the statement to kill the session is also given. However, no SQL blocking the session execution was found. If we want to find out more detailed information, we can use the following statement:

mysql> SELECT
  -> tmp.*,
  -> c.SQL_Text blocking_sql_text,
  -> p.HOST blocking_host
  -> FROM
  -> (
  -> SELECT
  -> r.trx_state watching_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
*************************** 1. row ***************************
 watching_trx_state: LOCK WAIT
  waiting_trx_id: 6180
  waiting_thread: 19
   waiting_query: update stu_tb set stu_name = 'wang' where stu_id = 1006
blocking_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. We can clearly see the statements executed by the blocking and blocked transactions, which helps us troubleshoot and confirm whether the blocked session can be killed.

3. Monitor long transactions

In real work, we need to monitor long transactions and define a threshold, such as 30s. Transactions that take more than 30 seconds to execute are long transactions and require recording and alerting to remind management personnel to handle them. The monitoring script is given below. You can refer to it and modify it according to your needs:

#!/bin/bash
# -------------------------------------------------------------------------------
# FileName: long_trx.sh
# Describe: monitor long transaction
# Revision: 1.0
# 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 a 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;" | while read ABCDEFGH
do
 if [ "$C" -gt 30 ]
   then
   echo $(date +"%Y-%m-%d %H:%M:%S")
   echo "processid[$D] $E@$F in db[$G] hold transaction time $C SQL:$H"
 fi
done >> /tmp/longtransaction.txt

To explain briefly, -gt 30 here means 30 seconds. If it exceeds 30 seconds, it is considered a long transaction. You can customize it according to actual needs. Add the script to the scheduled task and it will be executed.

Summarize:

This article mainly introduces the content related to long transactions, how to find long transactions, how to handle long transactions, and how to monitor long transactions. Maybe some of you don’t know much about transactions. I hope this article will be helpful to you. Since this article lists many query transaction related statements, they are summarized as follows:

# Query all running transactions and their running time select t.*,to_seconds(now())-to_seconds(t.trx_started) idle_time from INFORMATION_SCHEMA.INNODB_TRX t \G

# Query transaction details and executed SQL
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 a 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;

# 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 detailsSELECT
 tmp.*,
 c.SQL_Text blocking_sql_text,
 p.HOST blocking_host
FROM
 (
 SELECT
  r.trx_state watching_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

The above is the detailed content of in-depth understanding of MySQL long transactions. For more information about MySQL long transactions, please pay attention to other related articles on 123WORDPRESS.COM!

You may also be interested in:

Briefly describe the four transaction isolation levels of MySql
Using SpringBoot annotations to handle transaction rollback
Detailed explanation of MySQL transactions and MySQL logs
Detailed explanation of SpringAOP transaction configuration syntax and implementation process
Spring exception capture and rollback transaction solution
How to write transaction code elegantly in golang
Java annotation @Transactional transaction class call does not take effect and solution
MySQL transaction concepts and usage in-depth explanation
How to query the latest transaction ID in MySQL

<<: JavaScript to implement the aircraft war game

>>: Implementing file content deduplication and intersection and difference in Linux