mysql 查看事务 show_关于查询MySQL事务阻塞信息，你还可以这样玩？

前言众所周知 innodb 是支持事务型的存储引擎，在日常运维中大部分运维人员都会遇到关于 DDL 锁阻塞的情况，对于解决这类问题，有常规快速的解决方式。

那今天主要分享一个，如何去查找有关阻止事务的更多信息。测试过程

测试环境：mysql> USE test

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

新建测试表：mysql> CREATE TABLE tx_albert (

->     id     INT PRIMARY KEY,

->     name   VARCHAR(20),

->     age    INT,

->     sex    CHAR(2),

->     city   VARCHAR(20),

->     job    VARCHAR(10)

-> );

Query OK, 0 rows affected (0.24 sec)

插入数据：mysql> INSERT INTO tx_albert (id,name,age,sex,city,job) VALUES (1,'albert',18,'M','GuiYang','DBA');

Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO tx_albert (id,name,age,sex,city,job) VALUES (2,'john',24,'F','GuiYang','DEV');

Query OK, 1 row affected (0.00 sec)

会话1：mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE tx_albert SET name = 'Taeyeon' WHERE id = 2;

Query OK, 1 row affected (0.13 sec)

Rows matched: 1  Changed: 1  Warnings: 0

会话2：mysql> UPDATE tx_albert SET name = 'Jessica' WHERE id = 2;

会话等待....不同方法解读对于一般常规的做法，可以直接利用 show processlist

如果发生了 DDL 锁阻塞，特别是表上有事务未提交的会话，利用show processlist，不便于具体定位。

当然，可以看 Command，找出 locked 的 id，然后 kill，但是这样太过于暴力，也容易会 kill 错。

备注：command列:显示当前连接的执行的命令，一般就是休眠(sleep)，查询(query)，连接(connect)。当然，还有另一种方式，借助于 information_schemamysql> select * from information_schema.innodb_locks \G

*************************** 1. row ***************************

lock_id: 7460:26:3:5

lock_trx_id: 7460

lock_mode: X

lock_type: RECORD

lock_table: `test`.`tx_albert`

lock_index: PRIMARY

lock_space: 26

lock_page: 3

lock_rec: 5

lock_data: 2

*************************** 2. row ***************************

lock_id: 7459:26:3:5

lock_trx_id: 7459

lock_mode: X

lock_type: RECORD

lock_table: `test`.`tx_albert`

lock_index: PRIMARY

lock_space: 26

lock_page: 3

lock_rec: 5

lock_data: 2

2 rows in set (0.00 sec)mysql> SELECT * FROM information_schema.innodb_lock_waits \G

*************************** 1. row ***************************

requesting_trx_id: 7460

requested_lock_id: 7460:26:3:5

blocking_trx_id: 7459

blocking_lock_id: 7459:26:3:5

1 row in set (0.00 sec)

mysql> select * from information_schema.innodb_trx \G;

*************************** 1. row ***************************

trx_id: 7466

trx_state: LOCK WAIT

trx_started: 2018-08-30 05:18:42

trx_requested_lock_id: 7466:26:3:5

trx_wait_started: 2018-08-30 05:18:42

trx_weight: 2

trx_mysql_thread_id: 14

trx_query: UPDATE tx_albert SET name = 'Jessica' WHERE id = 2

trx_operation_state: starting index read

trx_tables_in_use: 1

trx_tables_locked: 1

trx_lock_structs: 2

trx_lock_memory_bytes: 360

trx_rows_locked: 1

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: 10000

trx_is_read_only: 0

trx_autocommit_non_locking: 0

*************************** 2. row ***************************

trx_id: 7459

trx_state: RUNNING

trx_started: 2018-08-30 04:45:08

trx_requested_lock_id: NULL

trx_wait_started: NULL

trx_weight: 4

trx_mysql_thread_id: 13

trx_query: NULL

trx_operation_state: NULL

trx_tables_in_use: 0

trx_tables_locked: 0

trx_lock_structs: 2

trx_lock_memory_bytes: 360

trx_rows_locked: 1

trx_rows_modified: 2

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: 10000

trx_is_read_only: 0

trx_autocommit_non_locking: 0

2 rows in set (0.10 sec)

从这里可以看出 trx_mysql_thread_id 14 被阻塞，以及当前正在执行的语句，而与此相联系的 trx_mysql_thread_id  13 正在运行。

通过 information_schema 关于 innodb lock 的表，可以将 kill 对象的缩减了一部分，减少了错 kill 的范围。

备注：1.对于MySQL的Innodb的默认事务隔离级别是重复读(repeatable read)

2.trx_mysql_thread_id，对应着线程id如果想要更详细的信息，可以借助于 performance_schema

Performance Schema 主要用于监视MySQL服务器，且运行时消耗很少的性能，并进行等待事件统计。它可以把等待事件统计表按照不同的分组列(不同纬度)对等待事件相关的数据进行聚合(聚合统计数据列包括：事件发生次数，总等待时间，最小、最大、平均等待时间)启用等待事件的采集功能

默认情况下等待事件的采集功能有一部分默认是禁用的，需要的时候可以通过setup_instruments和setup_objects表动态开启：

例如：

如果没有开启setup_consumers，那么会出现为空

设置开启也比较简单，设置为NO就可以了mysql> UPDATE setup_consumers SET ENABLED = 'YES';

Query OK, 8 rows affected (0.00 sec)

Rows matched: 12  Changed: 8  Warnings: 0

mysql> select * from setup_consumers;

setup_consumers

那 setup_consumers 是什么？

先来了解一下performance_schema 关于 setup 的表mysql> SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'performance_schema' AND TABLE_NAME LIKE 'setup%';

setup表的作用

这里共有5种关于 setup 的表，下面大概简述一下其作用。

1)setup_actors，配置用户纬度的监控，默认监控所有用户。

2)setup_consumers，配置events的消费者类型，即收集的events写入到哪些统计表中。

如果没有开启，则需要进行单独启用

3)setup_instruments：配置具体的instrument，主要包含4大类：idle、stage/xxx、statement/xxx、wait/xxx：

4)setup_objects：配置监控对象，默认对 mysql，performance_schema和information_schema中的表都不监控，而其它DB的所有表都监控。

5)setup_timers：配置每种类型指令的统计时间单位。MICROSECOND表示统计单位是微妙，CYCLE表示统计单位是时钟周期，时间度量与CPU的主频有关，NANOSECOND表示统计单位是纳秒。但无论采用哪种度量单位，最终统计表中统计的时间都会装换到皮秒。(1秒＝1000000000000皮秒)

查看更多的innodb锁信息

回到刚才的话题，如何去查看更多的 innodb 锁信息

查看线程号：

这里，我们可以利用 performance_schema 数据库的 events_statements_history表，这个表是一个等待事件统计表，类似于 Oracle 的 v$system_event 视图。

它记录着该线程所执行过的语句，以及一些等待信息，实际上， performance_schema的events_statements_history 语句事件记录表中针对每一个语句的执行状态都记录了较为详细的信息，甚至能够记录和包含执行错误的 SQL 语句信息。

可以看出线程33，分别执行了两次 update 语句，平均等待了51秒，而被锁定的时间为1分20秒。

TIMER_WAIT：事件已用时间，它的算法为等待的开始时间减去等待的结束时间，这段时间也即是持续时间。单位是皮秒(万亿分之一秒)

LOCK_TIME：等待表锁的时间。 此值以微秒计算，但标准化为皮秒，以便于与其他性能模式计时器进行比较。

SQL_TEXT：顾名思义就是执行SQL语句的文本。 如果是与SQL语句执行无关的命令，那么该值为NULL。 备注：可用于语句显示的最大空间为1024个字节。

同样也可借助于 events_waits_history 表，来查看历史的执行信息，不仅如此还可以辅助一些压测软件例如 TCPCopy，查询历史执行情况的汇总信息。

当然，根据自己的需求，可以开启更多的记录表进行观察，这样更有助于较为详细的诊断问题。

原创：陈龙。

投稿：有投稿意向技术人请在公众号对话框留言。