《博主主页》CSDN主页奈斯DBIF Club社区主页奈斯、微信公众号奈斯DB《擅长领域》️数据库阿里云AnalyticDB (云原生分布式数据仓库)、Oracle、MySQL、SQLserver、NoSQL(Redis)️运维平台与工具Prometheus监控、DataX离线异构同步工具如果觉得文章对你有所帮助欢迎点赞收藏加关注在MySQL数据库运维中对长事务和大事务的实时监控可不是“可选项”而是“必选项”。一个不受控制的长事务或大事务就足以瞬间拉高数据库的IO、内存和CPU水位引发连锁性的性能危机。因此对于数据库运维而言实时监控长事务与大事务至关重要。它们往往是系统资源IO、内存、CPU异常波动的直接诱因。无论是使用CREATE TABLE ... AS SELECT备份2000万行数据还是执行大规模批处理作业仅知道此事务“正在运行”以及执行了“多长时间”是远远不够的。对于第三方监控平台以及云RDS数据库都是可以看到SQL的执行状态的但具体的执行进度并没有体现那么怎么查看具体写入了多少行数据呢MySQL中自带的information_schema.innodb_trx视图就很好的解决了这个问题通过这个视图就可以实时看到插入了多少行数据当然除了实时看到插入了多少行数据之外还可以看到数据的回滚情况等等因此此视图全面揭示了事务的 开始时间⚙️ 运行状态正在执行、等待锁、回滚中、提交中 持有的锁信息…及其他关键元数据下面将剖析information_schema.innodb_trx视图掌握数据库事务执行的详细信息。目录长事务 or 大事务一、大事务二、长事务三、对比表格四、如何避免和优化相关视图案例模拟一个insert超大事务查看事务具体执行了多少行数据的插入以及还剩多少在介绍information_schema.innodb_trx视图之前先了解一下什么是大事务和长事务。长事务 or 大事务“大事务”和“长事务”经常被一起提及因为它们都可能对数据库性能和数据一致性造成负面影响但它们的侧重点和具体问题完全不同。一、大事务核心特征涉及的数据量大操作的数据行多、产生的日志多。好比一次性把整个仓库的货物全部清点、打包、并记录在案。主要关注点资源消耗日志文件暴涨事务在提交前所有的修改都会记录在日志中如MySQL的binlog PostgreSQL的WAL。大事务会产生海量的日志可能迅速占满磁盘空间。内存压力大数据库需要缓存事务中修改的数据大量数据操作会消耗巨大的内存如InnoDB Buffer Pool。锁竞争激烈它可能会锁定大量的数据行或表阻塞其他会话的读写操作甚至可能导致死锁。主从复制延迟在主从架构中一个大事务的Binlog或WAL事件需要在从库上一次性执行这会占用从库很长时间导致主从之间的数据出现严重延迟。回滚代价高如果大事务执行失败需要回滚其回滚过程会像执行它一样慢甚至更慢期间系统几乎不可用。典型场景一次性清理或删除大量历史数据例如DELETE FROM logs WHERE created_at 2020-01-01。一次性更新全表例如UPDATE users SET status inactive WHERE ...。大数据量的批量导入。二、长事务核心特征持续的时间长从开始到提交/回滚的时间间隔长。好比在仓库里拿起一件货物研究了半天然后又去喝杯咖啡再回来继续研究期间一直占着这件货物不让别人动。主要关注点锁持有时间长这是长事务最致命的问题。事务在执行过程中获得的锁会一直持有直到事务结束。这会导致其他需要访问相同数据的会话被长时间阻塞引发“锁等待”超时极大影响系统的并发性能和响应速度。MVCC快照过期对于使用多版本并发控制MVCC的数据库如PostgreSQL, MySQL InnoDB长事务会导致数据库无法清理旧的、不再需要的数据版本因为它可能还需要访问这些旧版本来保证自身事务的一致性。这会导致表膨胀性能下降。连接池占用长事务会长时间占用一个数据库连接如果连接池有限可能导致新的请求无法获取连接。典型场景在应用程序中开启了一个事务然后等待用户输入这是绝对禁止的。在一个事务中执行一系列复杂的、耗时的业务计算。不小心忘掉了提交或回滚事务例如代码中没有正确处理事务边界。三、对比表格特征大事务长事务核心问题数据量时间主要影响资源消耗日志、内存、主从延迟锁竞争、阻塞、MVCC垃圾回收好比一次性搬走一座山长期占着一条路不让别人走典型场景批量删除、大数据导入事务中等待用户输入、忘记提交优化策略分而治之拆分成小批次提交快进快出减少事务内耗时操作尽快提交四、如何避免和优化对于大事务分批处理将大批量操作拆分成多个小批次每处理一批就提交一次。例如用循环每次处理1000条数据。选择合适的时机在业务低峰期执行大批量操作。使用特定工具对于数据导入使用LOAD DATA INFILE或COPY命令通常比在事务中执行大量INSERT更高效。对于长事务避免交互绝对禁止在事务中等待用户输入或进行外部API调用。业务逻辑应在事务之外处理。设置超时在数据库或ORM框架中设置事务超时时间自动终止长时间运行的事务。优化查询确保事务内的所有SQL语句都经过优化使用了正确的索引以减少执行时间。及时提交/回滚在代码中使用try-catch-finally等结构确保事务被正确关闭。相关视图SQL select * from information_schema.innodb_trx;—INNODB_TRX表提供了关于INNODB内当前执行的每个事务的信息包括事务是否正在等待锁、事务何时启动以及事务正在执行的SQL语句如果有的话TRX_IDInnoDB内部的唯一事务ID号。这些ID不是为只读和非锁定的传输创建的。TRX_WEIGHT事务的权重反映但不一定是事务更改的行数和锁定的行数。为了解决死锁InnoDB选择权重最小的事务作为要回滚的“牺牲品”。已更改非事务表的事务被认为比其他事务重而不管已筛选和锁定的行数是多少。TRX_STATE事务执行状态。允许的值为RUNNING、LOCK WAIT、ROLLING BACK和COMMITTING。RUNNING运行中当事务正在执行其包含的 SQL 语句时它处于 RUNNING 状态。在这个阶段事务可能会读取、写入或修改数据库中的数据。LOCK WAIT等待锁当事务尝试访问一个被另一个事务锁定的资源时它会进入 LOCK WAIT 状态。这通常发生在两个或多个事务尝试修改同一行数据或访问被另一个事务锁定的资源时。事务会等待直到锁被释放然后才能继续执行。ROLLING BACK回滚中如果事务在执行过程中遇到错误或者它被明确地要求回滚例如通过 SQL 语句 ROLLBACK那么它会进入 ROLLING BACK 状态。在这个阶段事务会撤销所有已执行的更改使数据库恢复到事务开始之前的状态。COMMITTING提交中当事务成功完成其所有操作并且没有遇到任何错误时它会进入 COMMITTING 状态。在这个阶段事务所做的所有更改都会被永久地写入数据库。一旦提交完成事务就完成了它对数据库的更改对其他事务也是可见的。TRX_STARTED事务开始时间。TRX_REQUESTED_LOCK_ID如果TRX_STATE为LOCK WAIT则事务当前正在等待的锁的ID否则为NULL。TRX_WAIT_STARTED事务开始等待锁的时间如果TRX_STATE为LOCK WAIT否则为NULL。TRX_MYSQL_THREAD_IDMySQL线程ID。获得线程的详细信息需要将该列与INFORMATION_SCHEMA.PROCESSLIST表的ID列连接起来。trx_mysql_thread_id对应的内容就是show processlist中会话的线程TRX_QUERY事务正在执行的SQL语句。TRX_OPERATION_STATE交易的当前操作如有否则为NULL。TRX_TABLES_IN_USE处理此事务的当前SQL语句时使用的InnoDB表的数量。TRX_TABLES_LOCKED当前SQL语句具有行锁的InnoDB表的数量TRX_LOCK_STRUCTS事务保留的锁数。TRX_LOCK_MEMORY_BYTES内存中此事务的锁结构所占用的总大小。TRX_ROWS_LOCKED此事务锁定的大约行数。该值可能包括删除标记的行这些行是物理显示的但对事务不可见。TRX_ROWS_MODIFIED此事务中修改和插入的行数。TRX_STATE字段为RUNNING状态TRX_ROWS_MODIFIED显示的是插入了多少行数据当源表的全部数据插入到目标表后此事务才算结束。TRX_STATE字段为ROLLING BACK状态TRX_ROWS_MODIFIED显示的是还剩余多少行数据需要回滚当数值为0时此事务的全部数据才算回滚完成。TRX_CONCURRENCY_TICKETS一个值指示当前事务在交换出去之前可以做多少工作由innodb_concurrency_ticktssystem变量指定。TRX_ISOLATION_LEVEL当前事务的隔离级别。TRX_UNIQUE_CHECKS当前事务的唯一检查是打开还是关闭。例如它们可能会在数据加载过程中关闭。TRX_FOREIGN_KEY_CHECKS当前事务的外键检查是打开还是关闭。例如它们可能在大容量数据加载期间关闭。TRX_LAST_FOREIGN_KEY_ERROR最后一个外键错误的详细错误消息如果有的话否则为NULL。TRX_ADAPTIVE_HASH_LATCHED自适应哈希索引是否被当前事务锁定。当自适应哈希索引搜索系统被分割时单个事务不会锁定整个自适应哈希索引。自适应哈希索引分区由innodb_Adaptive_hash_index_parts控制默认情况下设置为8。TRX_ADAPTIVE_HASH_TIMEOUT是立即放弃自适应哈希索引的搜索锁存还是在MySQL的调用中保留它。当不存在自适应哈希索引争用时该值保持为零语句保留锁存直到它们完成。在争用期间它倒计时到零语句在每次行查找后立即释放锁存器。当自适应哈希索引搜索系统被划分由innodb_adaptive_hash_index_parts控制时该值保持为0。TRX_IS_READ_ONLY值为1表示事务是只读的。TRX_AUTOCOMMIT_NON_LOCKING值1表示事务是一个SELECT语句它不使用FOR UPDATE或LOCK INSHARED MODE子句并且在启用自动提交的情况下执行因此事务只包含这一条语句。当thiscolumn和TRX_IS_READ_ONLY都为1时InnoDB会优化事务以减少与更改表数据的事务相关的开销。TRX_SCHEDULE_WEIGHT由内容感知事务调度CATS算法分配给等待锁定的事务的事务调度权重。该值相对于其他事务的值。数值越高中量级就越大。仅为处于LOCK WAIT状态的事务计算一个值如TRX_state列所报告的。为不等待锁定的事务报告NULL值。TRX_SCHEDULE_WEIGHT值与TRX_WEIGHT不同后者是由不同的算法为不同的目的计算的。案例模拟一个insert超大事务查看事务具体执行了多少行数据的插入以及还剩多少1insert一条超大事务liudbywcs.liu_mysqloltp_member表有一千万行的数据mysqlinsertintotest_memberselect*fromliudbywcs.liu_mysqloltp_member;数据量巨大liu_mysqloltp_member表有一千万行数据这个INSERT INTO...SELECT语句会一次性将这1000万行数据全部插入到test_member表中。这是典型的数据量大的特征。会产生大量日志在执行过程中数据库需要为这1000万次插入生成大量的重做日志Redo Log和二进制日志Binlog占用大量磁盘I/O可能撑满日志文件空间在主从复制中造成严重的延迟回滚代价高如果这个操作在执行过程中失败或需要回滚回滚过程会非常缓慢。2查看事务的信息包括事务是否正在等待锁、事务何时启动以及事务正在执行的SQL语句mysqlselect*frominformation_schema.innodb_trx\G;*************************** 1. row ***************************trx_id: 3208058###InnoDB内部的唯一事务ID号trx_state: RUNNING###当事务正在执行其包含的 SQL 语句时它处于 RUNNING 状态。在这个阶段事务可能会读取、写入或修改数据库中的数据。trx_started: 2024-06-14 02:16:19###事务开始时间。trx_requested_lock_id: NULL###如果TRX_STATE为LOCK WAIT则事务当前正在等待的锁的ID否则为NULL。trx_wait_started: NULL###事务开始等待锁的时间如果TRX_STATE为LOCK WAIT否则为NULL。trx_weight: 2427132trx_mysql_thread_id: 133###MySQL线程ID。trx_mysql_thread_id对应的内容就是show processlist中会话的线程trx_query: insert into test_member select * from liudbywcs.liu_mysqloltp_member###事务正在执行的SQL语句。trx_operation_state: fetching rows###交易的当前操作如有否则为NULL。trx_tables_in_use: 2###处理此事务的当前SQL语句时使用的InnoDB表的数量。trx_tables_locked: 1###当前SQL语句具有行锁的InnoDB表的数量trx_lock_structs: 1###事务保留的锁数。trx_lock_memory_bytes: 1136###内存中此事务的锁结构所占用的总大小。trx_rows_locked: 0###此事务锁定的大约行数。该值可能包括删除标记的行这些行是物理显示的但对事务不可见。trx_rows_modified: 2427131###此事务中修改和插入的行数。因为此事务是在插入TRX_STATE字段为RUNNING状态所以这里显示的是插入了多少行数据当liudbywcs.liu_mysqloltp_member表的全部数据插入到test_member表后此事务才算结束也就是数值显示为10000000。trx_concurrency_tickets: 0trx_isolation_level: READ COMMITTED###当前事务的隔离级别。trx_unique_checks: 1trx_foreign_key_checks: 1trx_last_foreign_key_error: NULLtrx_adaptive_hash_latched: 0trx_adaptive_hash_timeout: 0trx_is_read_only: 0trx_autocommit_non_locking: 0trx_schedule_weight: NULLmysqlselect*frominformation_schema.innodb_trx\G;trx_rows_modified: 3013129###此事务中修改和插入的行数。因为此事务是在插入TRX_STATE字段为RUNNING状态所以这里显示的是插入了多少行数据当liudbywcs.liu_mysqloltp_member表的全部数据插入到test_member表后此事务才算结束也就是数值显示为10000000。这里就是事务的执行进度3业务反馈大事务影响了数据库的性能导致CPU、io、内存监控指标飙升所以需要先kill掉或者停止。通过监控观察insert into … select 操作直接导致了IO飙升如果是业务高峰期会严重影响其他业务的查询和写入数据小提示在大量insert时kill会话涉及的数据全部回滚到insert之前。update、delete也是kill会话涉及的数据全部回滚因为MySQL支持undo的数据回滚(rollback)终止事务方式一KILL [CONNECTION | QUERY] processlist_id方式二CTRLC查看进程列表mysqlshowprocesslist;### 向线程发送了一个KILL语句一般情况下很短的时间内结束。但有时候大事务回滚或线程因资源协调上缓慢这个状态可能执行几十分钟或几个小时的情况。要是出现几个小时保持这个状态可以考虑直接重新启动回滚速度会更快但是在启动数据库时可能出现异常的情况所以kill大事务需要谨慎。通过视图查看事务的信息mysql select * from information_schema.innodb_trx\G;trx_state: ROLLING BACK###如果事务在执行过程中遇到错误或者它被明确地要求回滚例如通过 SQL 语句 ROLLBACK那么它会进入 ROLLING BACK 状态。在这个阶段事务会撤销所有已执行的更改使数据库恢复到事务开始之前的状态。trx_operation_state: ROLLBACK###交易的当前操作如有否则为NULL。trx_rows_modified: 2258272###此事务中修改和插入的行数。因为此事务是在回滚TRX_STATE字段为ROLLING BACK状态所以这里显示的是还剩余多少行数据需要回滚当数值为0时此事务的全部数据才算回滚完成。mysql select * from information_schema.innodb_trx\G;### 再次执行。因为是大事务所以回滚数据是需要时间的。通过trx_rows_modified字段确定还剩余多少行数据需要回滚mysql select * from information_schema.innodb_trx\G;### 再次执行。大事务花了30分钟将全部数据回滚完成information_schema.innodb_trx看似只是一个简单的系统视图但通过它不仅能精准定位拖垮性能的长事务与大事务还能实时追踪其执行进度、分析锁等待情况。掌握这把内置的利器️下次面对数据库性能波动时就能快速锁定元凶从被动等待变为主动掌控。
