"入门MySQL”系列文章已经完结,今后我的文章还是会以MySQL为主,主要记录下近期工作及学习遇到的场景或者自己的感悟想法,可能后续的文章不是那么连贯,但还是希望大家多多支持。言归正传,本篇文章主要介绍MySQL长事务相关内容,比如说我们开启的一个事务,一直没提交或回滚会怎样呢,出现事务等待情况应该如何处理,本篇文章将给你答案。
本篇文章并不聚焦于谈论事务隔离级别以及相关特性。而是介绍长事务相关危害以及监控处理方法。本文是基于MySQL5.7.23版本,不可重复读(RR)隔离级别所做实验。
首先我们先要知道什么是长事务,顾名思义就是运行时间比较长,长时间未提交的事务,也可以称之为大事务。这类事务往往会造成大量的阻塞和锁超时,容易造成主从延迟,要尽量避免使用长事务。
下面我将演示下如何开启事务及模拟长事务:
#假设我们有一张stu_tb表,结构及数据如下
mysql>显示创建表stu_tb \ G
* * * * * * * * * * * * * * * * * * * * * * * * * * * 1。行 ***************************
表:stu_tb
创建表:创建表“stu_tb”(
increment_id int (11) NOT NULL AUTO_INCREMENT评论的自增主键的,
stu_id int (11) NOT NULL评论“学”号,
stu_name varchar(20)默认空评论的学生姓名”,
“create_time”默认时间戳NOT NULL CURRENT_TIMESTAMP评论“创建时间”,
“update_time”默认时间戳NOT NULL CURRENT_TIMESTAMP>
mysql>现在选择t。*, to_seconds (()) -to_seconds (t.trx_started)从INFORMATION_SCHEMA idle_time。INNODB_TRX t \ G
* * * * * * * * * * * * * * * * * * * * * * * * * * * 1。行 ***************************
trx_id: 6168
trx_state:跑步
trx_started: 2019-09-16 11:08:27
trx_requested_lock_id:零
trx_wait_started:零
trx_weight: 3
trx_mysql_thread_id: 11
trx_query:零
trx_operation_state:零
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:可重复读
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error:零
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是空,这并不是说事务什么也没执行,一个事务可能包含多个SQL,如果SQL执行完毕就不再显示了。当前事务正在执行,innodb也不知道这个事务后续还有没有SQL,啥时候会提交。因此trx_query不能提供有意义的信息。
如果我们想看到这个事务执行过的SQL,看是否可以杀掉长事务,怎么办呢?我们可以联合其他系统表查询得到,具体查询SQL如下:
mysql>现在选择(),(UNIX_TIMESTAMP(现在())- UNIX_TIMESTAMP (a.trx_started)) diff_sec, b.id, b.user b.host b.db, d。从information_schema SQL_TEXT。innodb_trx内加入
→information_schema。PROCESSLIST b
→>
mysql>现在选择(),(UNIX_TIMESTAMP(现在())- UNIX_TIMESTAMP (a.trx_started)) diff_sec, b.id, b.user b.host b.db, d。从information_schema SQL_TEXT。innodb_trx内加入
→information_schema。PROCESSLIST b
→>
#假设一个事务执行select * from stu_tb stu_id=1006更新的地方
#另外一个事务执行更新stu_tb设置stu_name=酢痵tu_id=1006
mysql>select * from sys.innodb_lock_waits \ G
* * * * * * * * * * * * * * * * * * * * * * * * * * * 1。行 ***************************
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:记录
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:更新stu_tb设置stu_name=酢痵tu_id=1006
waiting_lock_id: 6178:47:4:7
waiting_lock_mode: X
blocking_trx_id: 6177
blocking_pid: 20
blocking_query:零
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:杀死查询20
sql_kill_blocking_connection:杀死20
