怎么使用mysql 5.6 information schema定位事务锁信息

这篇文章将为大家详细讲解有关怎么使用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.htmlinformation-schema实践1，数据库版本[root@standbygtid ~]# mysql -Vmysql  Ver 14.14 Distrib 5.6.25, for Linux (x86_64) using  EditLine wrapper2，登陆mysql[root@standbygtid ~]# mysql -uroot -psystem3，显示事务及锁相关的表（注：有ORACLE从业经验的同学，类似于oracle 动态性能视图v$session及locked_objects）mysql> use information_schema;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> 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: 1trx_last_foreign_key_error: NULL trx_adaptive_hash_latched: 0 trx_adaptive_hash_timeout: 10000          trx_is_read_only: 0trx_autocommit_non_locking: 01 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 specified7，再开启一个新事务会话（注：更新上述同一个表的记录，即会产生锁等待，因为需要更新同一个表的记录资源）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:1lock_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-record2 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               请求锁的事务idrequested_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 specified8，为了方便监控锁等待的信息，可以编写下述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定位事务锁信息"这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，使各位可以学到更多知识，如果觉得文章不错，请把它分享出去让更多的人看到。