ITPub博客

首页 > 数据库 > MySQL > 如何使用mysql 5.6 information schema定位事务锁信息

如何使用mysql 5.6 information schema定位事务锁信息

原创 MySQL 作者:wisdomone1 时间:2019-11-08 21:32:54 0 删除 编辑
引子
  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元)


联系方式


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9240380/viewspace-2663316/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
提供针对oracle初学者及进阶的数据库培训,欢迎大家咨询: 微信: wisdomone 微信公众号: lovedb 工作经历: 中国普天 北京科蓝 北京云和恩墨 北京神州新桥

注册时间:2008-04-04

  • 博文量
    2177
  • 访问量
    11924446