mysql大事务

定义：运行时间比较长，操作的数据比较多的事务。

大事务风险：

1. 锁定太多的数据，造成大量的阻塞和锁超时，回滚所需要的时间比较长。

2. 执行时间长，容易造成主从延迟。

如何发现大事务

方法1、Use INFORMATION_SCHEMA

information_schema.innodb_trx表的trx_rows_modified列显示事务处理了多少行

select trx_id, trx_state,trx_started, trx_rows_modified from information_schema.innodb_trx order by trx_rows_modified desc limit  20;
+-----------------+-----------+---------------------+-------------------+
| trx_id          | trx_state | trx_started         | trx_rows_modified |
+-----------------+-----------+---------------------+-------------------+
| 304113678379    | RUNNING   | 2023-12-08 20:34:13 |                 1 |
| 329392890983296 | RUNNING   | 2023-12-08 19:51:10 |                 0 |
| 329392891300672 | RUNNING   | 2023-12-08 17:55:01 |                 0 |
| 329392891205824 | RUNNING   | 2023-12-08 18:06:33 |                 0 |
| 329392890937696 | RUNNING   | 2023-12-08 17:55:59 |                 0 |
| 329392890975088 | RUNNING   | 2023-12-08 19:01:20 |                 0 |
| 329392890795424 | RUNNING   | 2023-12-08 17:19:12 |                 0 |
| 329392891110976 | RUNNING   | 2023-12-08 17:56:00 |                 0 |
| 329392890862000 | RUNNING   | 2023-12-08 20:30:07 |                 0 |
| 329392891015216 | RUNNING   | 2023-12-08 17:55:59 |                 0 |
| 329392890913072 | RUNNING   | 2023-12-08 18:32:50 |                 0 |
| 329392890851968 | RUNNING   | 2023-12-08 17:03:18 |                 0 |
| 329392891105504 | RUNNING   | 2023-12-08 17:55:59 |                 0 |
| 329392890794512 | RUNNING   | 2023-12-08 20:05:02 |                 0 |
| 329392890882064 | RUNNING   | 2023-12-08 17:55:59 |                 0 |
| 329392891203088 | RUNNING   | 2023-12-08 18:18:01 |                 0 |
| 329392890953200 | RUNNING   | 2023-12-08 16:20:11 |                 0 |
| 329392891089088 | RUNNING   | 2023-12-08 17:55:59 |                 0 |
| 329392891043488 | RUNNING   | 2023-12-08 17:55:58 |                 0 |
| 329392891252336 | RUNNING   | 2023-12-08 18:06:56 |                 0 |
+-----------------+-----------+---------------------+-------------------+
20 rows in set (0.00 sec)

具体事务语句查看

-- mysql 5.6,5.7
information_schema 简称I_S
innodb_trx
innodb_locks
innodb_lock_waits
SELECT  lw.requesting_trx_id AS request_XID, trx.trx_mysql_thread_id as request_mysql_PID
, trx.trx_query AS request_query,  lw.blocking_trx_id AS blocking_XID
, trx1.trx_mysql_thread_id as blocking_mysql_PID, trx1.trx_query AS blocking_query
, lo.lock_index AS lock_index 
FROM     information_schema.innodb_lock_waits lw 
INNER JOIN information_schema.innodb_locks lo ON lw.requesting_trx_id = lo.lock_trx_id 
INNER JOIN information_schema.innodb_locks lo1 ON lw.blocking_trx_id = lo1.lock_trx_id 
INNER JOIN information_schema.innodb_trx trx ON lo.lock_trx_id = trx.trx_id 
INNER JOIN information_schema.innodb_trx trx1 ON lo1.lock_trx_id = trx1.trx_id 
limit 10 ;

方法2、Use INNODB STATUS

在INNODB状态的事务部分中，在“ undo log entries”附近，也显示了按事务修改的行数，例如

show engine innodb status \G

方法3、Monitoring progress of ALTER commands in MySQL Server 5.7 using PERFORMANCE_SCHEMA

Read more in online documentation at https://dev.mysql.com/doc/refman/5.7/en/monitor-alter-table-performance-schema.html

mysqlbinlog /db/mysql/3306/log/binlog/mysql-bin.001994 -vv | grep -o "transaction_length=[0-9]*"|awk -F= '{print $2}'| sort -rn 
mysqlbinlog  -vv | grep -o "transaction_length=[0-9]*"  
transaction_length=212
transaction_length=223
transaction_length=216