怎么使用mysql 5.6 information schema定位事务锁信息
短信预约 -IT技能 免费直播动态提醒
这篇文章将为大家详细讲解有关怎么使用mysql 5.6 information schema定位事务锁信息，小编觉得挺实用的，因此分享给大家做个参考，希望大家阅读完这篇文章后可以有所收获。
引子
mysql数据库在运行期间，随着业务体量增加及并发会话陡升，可能随时会出现各种性能问题。其中比较常见的一种
现象，某一天公司业务人员或客户反馈说某个业务模板突然卡住了，或者开发同学说某个SQL语句不能继续运行了。
mysql 5.6引入的information_schema数据库，可以完美解决上述的问题。它提供一系列的数据视图或表，便于
诊断及分析数据库的各种各样的性能问题，对于运维同学真是大大福利。本文主要介绍information_schema与锁
相关的几个表，快速定位是哪些会话或事务导致事务操作不能持续。
概念
information-schema是一个内置的数据库，通过一系列的表，比如：锁方面的表，字符集相关的表，插件相关的
表，进程相关的表，视图相关的表，不一而足。运维人员可以通过不同的表的信息，有助于分析各种各样的性能问题
。 当然，可以结合另一个数据库performance_schema数据库，更方便诊断数据库的各种各样的性能问题甚至故障
情形。
mysql information-schema官方手册，请查阅如下链接
https://dev.mysql.com/doc/refman/5.6/en/information-schema.html
mysql锁相关官方手册，请查阅如下链接
https://dev.mysql.com/doc/refman/5.6/en/innodb-locking-transaction-model.html
information-schema实践
1，数据库版本
[root@standbygtid ~]# mysql -V
mysql Ver 14.14 Distrib 5.6.25, for Linux (x86_64) using EditLine wrapper
2，登陆mysql
[root@standbygtid ~]# mysql -uroot -psystem
3，显示事务及锁相关的表
（注：有ORACLE从业经验的同学，类似于oracle 动态性能视图v$session及locked_objects）
mysql> use information_schema;
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>
mysql> show tables like 'INNODB%';
+----------------------------------------+
| Tables_in_information_schema (INNODB%) |
+----------------------------------------+
| INNODB_LOCKS |
| INNODB_TRX |
略
| INNODB_LOCK_WAITS |
+----------------------------------------+
28 rows in set (0.00 sec)
4，上述几个表的含义
---锁表
（注：锁是什么，就是你需要某种资源，但此时由人家占着，你需要等待，这就是一种锁，锁的目标就是维护数据一致性）
mysql> desc innodb_locks;
+-------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------------+------+-----+---------+-------+
| lock_id | varchar(81) | NO | | | | 锁编号
| lock_trx_id | varchar(18) | NO | | | | 锁所属事务
| lock_mode | varchar(32) | NO | | | | 锁模式
| lock_type | varchar(32) | NO | | | | 锁类型
| lock_table | varchar(1024) | NO | | | | 锁对应表
| lock_index | varchar(1024) | YES | | NULL | | 锁对应索引
| lock_space | bigint(21) unsigned | YES | | NULL | | 锁空间
| lock_page | bigint(21) unsigned | YES | | NULL | | 锁对应的页面
| lock_rec | bigint(21) unsigned | YES | | NULL | | 锁对应的表记录
| lock_data | varchar(8192) | YES | | NULL | |
+-------------+---------------------+------+-----+---------+-------+
10 rows in set (0.00 sec)
---事务表
mysql> desc innodb_trx;
+----------------------------+---------------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------------------+---------------------+------+-----+---------------------+-------+
| trx_id | varchar(18) | NO | | | | 事务编号
| trx_state | varchar(13) | NO | | | | 事务状态
| trx_started | datetime | NO | | 0000-00-00 00:00:00 | | 事务开始时间
| trx_requested_lock_id | varchar(81) | YES | | NULL | | 事务请求锁编号
| trx_wait_started | datetime | YES | | NULL | | 事务等待开始时间
| trx_weight | bigint(21) unsigned | NO | | 0 | | 事务权重
| trx_mysql_thread_id | bigint(21) unsigned | NO | | 0 | | 事务对应的线程
| trx_query | varchar(1024) | YES | | NULL | | 事务所属的SQL语句
| trx_operation_state | varchar(64) | YES | | NULL | |
| trx_tables_in_use | bigint(21) unsigned | NO | | 0 | |
| trx_tables_locked | bigint(21) unsigned | NO | | 0 | |
| trx_lock_structs | bigint(21) unsigned | NO | | 0 | |
| trx_lock_memory_bytes | bigint(21) unsigned | NO | | 0 | |
| trx_rows_locked | bigint(21) unsigned | NO | | 0 | |
| trx_rows_modified | bigint(21) unsigned | NO | | 0 | |
| trx_concurrency_tickets | bigint(21) unsigned | NO | | 0 | |
| trx_isolation_level | varchar(16) | NO | | | |
| trx_unique_checks | int(1) | NO | | 0 | |
| trx_foreign_key_checks | int(1) | NO | | 0 | |
| trx_last_foreign_key_error | varchar(256) | YES | | NULL | |
| trx_adaptive_hash_latched | int(1) | NO | | 0 | |
| trx_adaptive_hash_timeout | bigint(21) unsigned | NO | | 0 | |
| trx_is_read_only | int(1) | NO | | 0 | |
| trx_autocommit_non_locking | int(1) | NO | | 0 | |
+----------------------------+---------------------+------+-----+---------------------+-------+
24 rows in set (0.01 sec)
---锁等待表
mysql> desc innodb_lock_waits;
+-------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+-------------+------+-----+---------+-------+
| requesting_trx_id | varchar(18) | NO | | | | 请求锁事务编号
| requested_lock_id | varchar(81) | NO | | | | 请求锁编号
| blocking_trx_id | varchar(18) | NO | | | | 持锁事务编号
| blocking_lock_id | varchar(81) | NO | | | | 持锁 锁编号
+-------------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
5，为了模拟事务，关闭自动提交
（注：生产系统一定要关闭，防止不小心在生产系统产生误操作无法撤回）
mysql> show variables like '%autocommit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
1 row in set (0.00 sec)
mysql> set autocommit=off;
Query OK, 0 rows affected (0.01 sec)
mysql> show variables like '%autocommit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | OFF |
+---------------+-------+
1 row in set (0.00 sec)
6，产生一个事务
--新开一个登陆会话，不提交
（注：如果一提交，则事务马上消失）
mysql> update zxydb.t_go set a=3;
Query OK, 16778789 rows affected (1 min 0.91 sec)
Rows matched: 25168933 Changed: 16778789 Warnings: 0
--在另一会话查看线程信息
mysql> show processlist;
+----+------+-----------+--------------------+---------+------+----------+---------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+--------------------+---------+------+----------+---------------------------+
| 28 | root | localhost | information_schema | Query | 19 | updating | update zxydb.t_go set a=3 |
| 29 | root | localhost | NULL | Query | 0 | init | show processlist |
+----+------+-----------+--------------------+---------+------+----------+---------------------------+
2 rows in set (0.00 sec)
--查看事务表
mysql> select * from information_schema.innodb_trx\G;
*************************** 1. row ***************************
trx_id: 3996
trx_state: RUNNING 事务运行状态
trx_started: 2019-11-06 05:46:18 事务开始的时间
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 25224373
trx_mysql_thread_id: 28 事务所属的线程，对应上述的show processlist之id列
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 0
trx_lock_structs: 55440
trx_lock_memory_bytes: 8042024
trx_rows_locked: 25224372
trx_rows_modified: 25168933 事务影响的表记录数
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
1 row in set (0.03 sec)
ERROR:
No query specified
--由上可见如果没有竞争资源时，不会产生锁
（注：产生锁的前提条件是必须在2个会话以上，当然不包括mysql自身产生的bug）
mysql> select * from information_schema.innodb_locks\G;
Empty set (0.04 sec)
ERROR:
No query specified
--没有竞资源，当然也不会产生锁等待
mysql> select * from information_schema.innodb_lock_waits\G;
Empty set (0.03 sec)
ERROR:
No query specified
7，再开启一个新事务会话
（注：更新上述同一个表的记录，即会产生锁等待，因为需要更新同一个表的记录资源）
mysql> set autocommit=off;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into zxydb.t_go select 3,3;
----可见产生了锁信息
mysql> select * from information_schema.innodb_locks\G;
*************************** 1. row ***************************
lock_id: 3997:6:55726:1
lock_trx_id: 3997
lock_mode: X 锁模式，x表示排它锁，s表示共享锁
lock_type: RECORD
lock_table: `zxydb`.`t_go` 锁定表
lock_index: GEN_CLUST_INDEX GEN_CLUST_INDEX表示表级锁
lock_space: 6
lock_page: 55726
lock_rec: 1
lock_data: supremum pseudo-record
*************************** 2. row ***************************
lock_id: 3996:6:55726:1
lock_trx_id: 3996
lock_mode: X
lock_type: RECORD
lock_table: `zxydb`.`t_go`
lock_index: GEN_CLUST_INDEX
lock_space: 6
lock_page: 55726
lock_rec: 1
lock_data: supremum pseudo-record
2 rows in set (0.03 sec)
ERROR:
No query specified
---同时也产生锁等待信息
mysql> select * from information_schema.innodb_lock_waits\G;
*************************** 1. row ***************************
requesting_trx_id: 3997 请求锁的事务id
requested_lock_id: 3997:6:55726:1
blocking_trx_id: 3996 持锁的事务id
blocking_lock_id: 3996:6:55726:1
1 row in set (0.03 sec)
ERROR:
No query specified
8，为了方便监控锁等待的信息，可以编写下述SQL语句
---获取持锁会话及等待锁会话更详细的信息
select trx.trx_mysql_thread_id,
trx.trx_id,
trx.trx_state,
trx.trx_started,
trx.trx_query,
locks.lock_type,
locks.lock_table,
lock_waits.requesting_trx_id,
lock_waits.blocking_trx_id
from information_schema.innodb_trx trx inner join information_schema.innodb_locks locks
on trx.trx_id=locks.lock_trx_id
inner join information_schema.innodb_lock_waits lock_waits
on trx.trx_id=lock_waits.requesting_trx_id
inner join information_schema.innodb_lock_waits lock_waits
on trx.trx_id=lock_waits.blocking_trx_id;
可知，3997事务是等待锁，而3996是持锁，所以如果你想让3997可以继续工作，有几种方法：
1，继续等待3996事务完成
2，完成3996事务
3，杀死3996事务（操作语句为:kill 28,28为事务所属的线程）
+---------------------+--------+-----------+---------------------+-----------------------------------+-----------+----------------+-------------------+-----------------+
| trx_mysql_thread_id | trx_id | trx_state | trx_started | trx_query | lock_type | lock_table | requesting_trx_id | blocking_trx_id |
+---------------------+--------+-----------+---------------------+-----------------------------------+-----------+----------------+-------------------+-----------------+
| 30 | 3997 | LOCK WAIT | 2019-11-06 05:51:04 | insert into zxydb.t_go select 3,3 | RECORD | `zxydb`.`t_go` | 3997 | 3996 |
| 28 | 3996 | RUNNING | 2019-11-06 05:46:18 | NULL | RECORD | `zxydb`.`t_go` | NULL | NULL |
+---------------------+--------+-----------+---------------------+-----------------------------------+-----------+----------------+-------------------+-----------------+
2 rows in set (0.04 sec)培训课件
（收费20元）
关于“怎么使用mysql 5.6 information schema定位事务锁信息”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，使各位可以学到更多知识，如果觉得文章不错，请把它分享出去让更多的人看到。
免责声明：
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
![[mysql]mysql8修改root密码](https://static.528045.com/imgs/16.jpg?imageMogr2/format/webp/blur/1x0/quality/35)
