前言:
本篇文章主要介绍MySQL长事务相关内容,比如说我们开启的一个事务,一直没提交或回滚会怎样呢,出现事务等待情况应该如何处理,本篇文章将给你答案。
注意:本篇文章并不聚焦于谈论事务隔离级别以及相关特性。而是介绍长事务相关危害以及监控处理方法。本文是基于MySQL5.7.23版本,不可重复读(RR)隔离级别所做实验。(语句为\G可以使查询结构显示更易读,但只可以在mysql命令行使用。)
1.什么是长事务
首先我们先要知道什么是长事务,顾名思义就是运行时间比较长,长时间未提交的事务,也可以称之为大事务。这类事务往往会造成大量的阻塞和锁超时,容易造成主从延迟,要尽量避免使用长事务。
下面我将演示下如何开启事务及模拟长事务:
#假设我们有一张stu_tb表,结构及数据如下 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 '自增主键', `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> 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 ->> mysql> 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> #假设一个事务执行 select * from stu_tb where stu_id = 1006 for update #另外一个事务执行 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
上述结果显示出被阻塞的SQL以及锁的类型,更强大的是杀掉会话的语句也给出来了。但是并没有找到阻塞会话执行的SQL,如果我们想找出更详细的信息,可以使用下面语句:
深入了解mysql长事务
扫一扫手机访问
