mysql大事務

參考:   DBA爛筆頭

什麼是大事務

• 定義：執行時間比較長，操作的資料比較多的事務。

• 大事務風險：

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

方法4、 解析binlog

可參考部落格   如何獲取MySQL中的查詢和事務大小 的方法

擴充套件：mysql 8  binlog有記錄 transaction_length，低版本不存在特性。

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