MySQL中的长事务示范详解
发布时间：2022-03-16 05:59:41 所属栏目：MySql教程 来源：互联网
导读：『入门MySQL』系列文章已经完结，今后我的文章还是会以MySQL为主，主要记录下近期工作及学习遇到的场景或者自己的感悟想法，可能后续的文章不是那么连贯，但还是希望大家多多支持。言归正传，本篇文章主要介绍MySQL长事务相关内容，比如说我们开启的一个事务
|
『入门MySQL』系列文章已经完结，今后我的文章还是会以MySQL为主，主要记录下近期工作及学习遇到的场景或者自己的感悟想法，可能后续的文章不是那么连贯，但还是希望大家多多支持。言归正传，本篇文章主要介绍MySQL长事务相关内容，比如说我们开启的一个事务，一直没提交或回滚会怎样呢，出现事务等待情况应该如何处理，本篇文章将给你答案。 注意：本篇文章并不聚焦于谈论事务隔离级别以及相关特性。而是介绍长事务相关危害以及监控处理方法。本文是基于MySQL5.7.23版本，不可重复读(RR)隔离级别所做实验。 1.什么是长事务 首先我们先要知道什么是长事务，顾名思义就是运行时间比较长，长时间未提交的事务，也可以称之为大事务。这类事务往往会造成大量的阻塞和锁超时，容易造成主从延迟，要尽量避免使用长事务。 下面我将演示下如何开启事务及模拟长事务： #假设我们有一张stu_tb表，结构及数据如下 mysql> show create table stu_tbG *************************** 1. row *************************** Table: stu_tb Create Table: CREATE TABLE `stu_tb` ( `increment_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键', `stu_id` int(11) NOT NULL COMMENT '学号', `stu_name` varchar(20) DEFAULT NULL COMMENT '学生姓名', `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间', PRIMARY KEY (`increment_id`), UNIQUE KEY `uk_stu_id` (`stu_id`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COMMENT='测试学生表' 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) #显式开启事务，可用begin或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) #如果我们不及时提交上个事务，那么这个事务就变成了长事务，当其他会话要操作这条数据时，就会一直等待。 2.如何找到长事务 遇到事务等待问题时，我们首先要做的是找到正在执行的事务。 information_schema.INNODB_TRX 表中包含了当前innodb内部正在运行的事务信息，这个表中给出了事务的开始时间，我们可以稍加运算即可得到事务的运行时间。 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 在结果中idle_time是计算产生的，也是事务的持续时间。但事务的trx_query是NUL，这并不是说事务什么也没执行，一个事务可能包含多个SQL，如果SQL执行完毕就不再显示了。当前事务正在执行，innodb也不知道这个事务后续还有没有sql,啥时候会commit。 因此trx_query不能提供有意义的信息。 如果我们想看到这个事务执行过的SQL，看是否可以杀掉长事务，怎么办呢？我们可以联合其他系统表查询得到，具体查询SQL如下： 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 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; +---------------------+----------+----+------+-----------+--------+-----------------------------------------------------+ | 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 | +---------------------+----------+----+------+-----------+--------+-----------------------------------------------------+ 从上述结果中我们可以看到该事务从一开始到现在执行过的所有SQL，当我们把该事务相关信息都查询清楚后，我们就可以判定该事务是否可以杀掉，以免影响其他事务造成等待现象。 （编辑：财气旺网 - 财气网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐