Detailed explanation of long transaction examples in MySQL

Detailed explanation of long transaction examples in MySQL

Preface:

The "Getting Started with MySQL" series of articles has ended. In the future, my articles will still focus on MySQL, mainly recording the scenarios encountered in recent work and study or my own feelings and ideas. The subsequent articles may not be so coherent, but I still hope for your support. Let's get back to the topic. This article mainly introduces the content related to MySQL long transactions. For example, what happens 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.

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 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 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

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM.

You may also be interested in:
  • Detailed explanation of MySQL transactions and MySQL logs
  • Detailed explanation of the implementation principle of MySQL transactions and Spring isolation levels
  • MySQL transaction concepts and usage in-depth explanation
  • Detailed explanation of mysql transaction management operations
  • Example of viewing and modifying MySQL transaction isolation level
  • MySQL transaction, isolation level and lock usage example analysis
  • Detailed example of how to implement transaction commit and rollback in mysql
  • MYSQL transaction tutorial Yii2.0 merchant withdrawal function
  • How to find out uncommitted transaction information in MySQL
  • Detailed explanation of the implementation principle of ACID transaction in Mysql
  • MySQL cross-database transaction XA operation example
  • In-depth understanding of MySQL long transactions

<<:  Centos8 bridge static IP configuration method in VMware virtual machine

>>:  Vue uses GraphVis to develop an infinitely expanded relationship graph

Recommend

The difference between storing full-width characters and half-width characters in MySQL

Unfortunately, the MYSQL_DATA_TRUNCATED error occ...

How to implement JavaScript output of Fibonacci sequence

Table of contents topic analyze Basic solution Ba...

mysql 5.7.19 latest binary installation

First download the zip archive version from the o...

Detailed explanation of how to build an Ftp server on Ubuntu (success guaranteed)

1. This afternoon, due to the requirements of the...

Detailed explanation of the alternative implementation code of CSS vertical centering (unconventional)

Preface As we all know, "How to vertically c...

Specific use of Linux gcc command

01. Command Overview The gcc command uses the C/C...

Why web page encoding uses utf-8 instead of gbk or gb2312?

If you have a choice, you should use UTF-8 In fac...

Bootstrap FileInput implements image upload function

This article example shares the specific code of ...

How to upgrade MySQL 5.6 to 5.7 under Windows

Written in front There are two ways to upgrade My...

MySQL master-slave replication delay causes and solutions

Table of contents A brief overview of the replica...

How to build lnmp environment in docker

Create a project directory mkdir php Create the f...

Analysis of the Principle and Method of Implementing Linux Disk Partition

remember: IDE disk: the first disk is hda, the se...

How to use iostat to view Linux hard disk IO performance

TOP Observation: The percentage of CPU time occup...

Summary of MySQL Architecture Knowledge Points

1. Databases and database instances In the study ...

WeChat applet implements countdown for sending SMS verification code

This article shares the specific code for the WeC...