13518219792
这篇文章将为大家详细讲解有关怎么使用MySQL 5.6 information schema定位事务锁信息，小编觉得挺实用的，因此分享给大家做个参考，希望大家阅读完这篇文章后可以有所收获。
成都创新互联凭借专业的设计团队扎实的技术支持、优质高效的服务意识和丰厚的资源优势，提供专业的网站策划、成都网站设计、网站制作、网站优化、软件开发、网站改版等服务，在成都10多年的网站建设设计经验，为成都1000多家中小型企业策划设计了网站。
引子 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定位事务锁信息”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，使各位可以学到更多知识，如果觉得文章不错，请把它分享出去让更多的人看到。
