文档章节

查看Mysql正在执行的事务、锁、等待

吴伟祥
 吴伟祥
发布于 02/22 11:30
字数 1676
阅读 25
收藏 2

一、关于锁的三张表(MEMORY引擎)

## 当前运行的所有事务
mysql> select * from information_schema.innodb_trx\G;
*************************** 1. row ***************************
                    trx_id: 613963
                 trx_state: LOCK WAIT                  #事务状态
               trx_started: 2019-02-22 10:48:48
     trx_requested_lock_id: 613963:460:3:4
          trx_wait_started: 2019-02-22 11:08:41
                trx_weight: 2
       trx_mysql_thread_id: 140
                 trx_query: update tx1 set c1='heyfffff',c2='heyffffff' where id =3
       trx_operation_state: starting index read
         trx_tables_in_use: 1
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 7                          #事务锁住的行数
         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: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
*************************** 2. row ***************************
                    trx_id: 613962
                 trx_state: RUNNING
               trx_started: 2019-02-22 10:46:29
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 3
       trx_mysql_thread_id: 138
                 trx_query: select * from information_schema.innodb_trx
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 1
         trx_rows_modified: 1
   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: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
2 rows in set (0.00 sec)

## 当前出现的锁
mysql> select * from information_schema.innodb_locks\G;
*************************** 1. row ***************************
    lock_id: 613963:460:3:4
lock_trx_id: 613963
  lock_mode: X
  lock_type: RECORD
 lock_table: `test`.`tx1`
 lock_index: PRIMARY
 lock_space: 460
  lock_page: 3
   lock_rec: 4
  lock_data: 3
*************************** 2. row ***************************
    lock_id: 613962:460:3:4
lock_trx_id: 613962
  lock_mode: X
  lock_type: RECORD
 lock_table: `test`.`tx1`
 lock_index: PRIMARY
 lock_space: 460
  lock_page: 3
   lock_rec: 4
  lock_data: 3
2 rows in set, 1 warning (0.00 sec)

## 锁等待的对应关系 
mysql> select * from information_schema.innodb_lock_waits\G;
*************************** 1. row ***************************
requesting_trx_id: 613963             
requested_lock_id: 613963:460:3:4         #请求锁的锁ID
  blocking_trx_id: 613962                 #当前拥有锁的事务ID
 blocking_lock_id: 613962:460:3:4
1 row in set, 1 warning (0.00 sec)

二、查看锁的情况

mysql> show status like 'innodb_row_lock_%';
+-------------------------------+--------+
| Variable_name                 | Value  |
+-------------------------------+--------+
| Innodb_row_lock_current_waits | 1      |
| Innodb_row_lock_time          | 479764 |
| Innodb_row_lock_time_avg      | 39980  |
| Innodb_row_lock_time_max      | 51021  |
| Innodb_row_lock_waits         | 12     |
+-------------------------------+--------+
5 rows in set (0.00 sec)

解释如下:
Innodb_row_lock_current_waits : 当前等待锁的数量
Innodb_row_lock_time : 系统启动到现在,锁定的总时间长度
Innodb_row_lock_time_avg : 每次平均锁定的时间
Innodb_row_lock_time_max : 最长一次锁定时间
Innodb_row_lock_waits : 系统启动到现在总共锁定的次数  



# 查询是否锁表
mysql> show OPEN TABLES where In_use > 0;
+----------+-------+--------+-------------+
| Database | Table | In_use | Name_locked |
+----------+-------+--------+-------------+
| test     | tx1   |      1 |           0 |
+----------+-------+--------+-------------+
1 row in set (0.00 sec)

三、杀死进程

# 1.查询进程
mysql> show processlist\G                   # \G 结构旋转90度变成纵向
*************************** 1. row ***************************
     Id: 138
   User: root
   Host: localhost:55106
     db: test
Command: Query
   Time: 0
  State: starting
   Info: show processlist
*************************** 2. row ***************************
     Id: 140
   User: root
   Host: localhost:56158
     db: test
Command: Sleep   # 正在等待客户端向它发送执行语句
   Time: 145
  State:
   Info: NULL
2 rows in set (0.00 sec)

# 2.杀死对应进程ID 
mysql> kill 140;
Query OK, 0 rows affected (0.00 sec)

mysql> show processlist\G
*************************** 1. row ***************************
     Id: 138
   User: root
   Host: localhost:55106
     db: test
Command: Query             #该线程正在执行一个语句            Sleep:线程正在等待客户端向其发送新的语句。
   Time: 0
  State: starting
   Info: show processlist
1 row in set (0.00 sec)

四、SQL分析

explain使用方法

EXPLAIN命令,用于显示SQL语句的查询执行计划。EXPLAIN为用于SELECT语句中的每个表返回一行信息。

    EXPLAIN EXTENDED命令:显示SQL语句的详细的查询执行计划;之后可以通过"SHOW WARNINGS"命令查看详细信息。
    SHOW WARNINGS命令:可以查看MySQL优化器优化后的SQL语句。

    EXPLAIN PARTITIONS命令:显示SQL语句的带有分区表信息的查询执行计划。
    EXPLAIN命令的输出格式:
      TRADITIONAL:传统类型,按行隔离,每行标识一个自操作。
      JSON:JSON格式。

使用方法为在SQL语句前加explain

得到结果如下:

mysql> explain select id,c1 from t1 where c1=4398825;
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 4992210 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

各列功能如下:

  • id: 按照sql语法解析后分层后的编号,可能重复

  • select_type:

    • SIMPLE,简单的select查询,不使用union及子查询

    • PRIMARY,最外层的select查询

    • UNION,UNION 中的第二个或随后的 select 查询,不依赖于外部查询的结果集

    • DEPENDENT UNION,UNION 中的第二个或随后的 select 查询,依赖于外部查询的结果集

    • SUBQUERY,子查询中的第一个 select 查询,不依赖于外部查询的结果集

    • DEPENDENT SUBQUERY,子查询中的第一个 select 查询,依赖于外部查询的结果集

    • DERIVED,用于 from子句里有子查询的情况。 MySQL会递归执行这些子查询, 把结果放在临时表里。

    • UNCACHEABLE SUBQUERY,结果集不能被缓存的子查询,必须重新为外层查询的每一行进行评估。

    • UNCACHEABLE UNION,UNION 中的第二个或随后的 select 查询,属于不可缓存的子查询

  • table:涉及的表,如果SQL中表有赋别名,这里出现的是别名

  • type:

    • system,从系统表读一行。这是const联接类型的一个特例。

    • const,表最多有一个匹配行,它将在查询开始时被读取。因为仅有一行,在这行的列值可被优化器剩余部分认为是常数。const表很快,因为它们只读取一次!

    • eq_ref,查询条件为等于

    • ref,条件查询不等于

    • ref_or_null,同ref(条件查询),包含NULL值的行。

    • index_merge,索引联合查询

    • unique_subquery,利用唯一索引进行子查询

    • index_subquery,用非唯一索引进行子查询

    • range,索引范围扫描

    • index,索引全扫描

    • ALL,全表扫描。

  • possible_keys:可能使用的索引

  • key:sql中使用的索引

  • key_len:索引长度

  • ref:使用哪个列或常数与key一起从表中选择行。

  • rows:显示MYSQL执行查询的行数,简单且重要,数值越大越不好,说明没有用好索引

  • Extra:该列包含MySQL解决查询的详细信息。

    • Distinct,去重,返回第一个满足条件的值

    • Not exists 使用not exists查询

    • Range checked for each record,有索引,但索引选择率很低

    • Using filesort,有序查询

    • Using index,索引全扫描

    • Using index condition,索引查询

    • Using temporary,临表表检索

    • Using where,where条件查询

    • Using sort_union,有序合并查询

    • Using union,合并查询

    • Using intersect,索引交叉合并

    • Impossible WHERE noticed after reading const tables,读取const tables,查询结果为空

    • No tables used,没有使用表

    • Using join buffer (Block Nested Loop),使用join buffer(BNL算法)

    • Using MRR(Multi-Range Read ) 使用辅助索引进行多范围读

五、数据库的一些默认设置

# 查看数据库默认存储引擎
mysql> show engines; 
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

# 查看当前数据库默认隔离级别
mysql> select @@global.tx_isolation; 
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ       |
+-----------------------+
1 row in set, 1 warning (0.00 sec)

# 默认自动提交事务
mysql> show global variables like 'autocommit';  
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> select @@global.autocommit;
+---------------------+
| @@global.autocommit |
+---------------------+
|                   1 |
+---------------------+
1 row in set (0.00 sec)

© 著作权归作者所有

共有 人打赏支持
吴伟祥

吴伟祥

粉丝 22
博文 459
码字总数 281384
作品 0
泉州
后端工程师
私信 提问
解决MySQL中死进程(一个联合查询耗时32s)

解决MySQL中死进程(一个联合查询耗时32s) 很奇怪的现象,一个联合查询(in)直接执行sql脚本的时候,Navicat Premium直接假死; 检查是否有死进程: SELECT * FROM INFORMATION_SCHEMA.INN...

xiaocao13140
2018/06/15
0
0
记录一次 Mysql 死锁排查过程

背景 以前接触到的数据库死锁,都是批量更新时加锁顺序不一致而导致的死锁,但是上周却遇到了一个很难理解的死锁。借着这个机会又重新学习了一下mysql的死锁知识以及常见的死锁场景。在多方调...

wooyoo
2017/02/19
0
0
MySQL 5.5 InnoDB 锁状态解析

目录 目录 一 引子 二 表结构说明 三 INNODB 锁等待模拟 3.1 创建测试表,录入测试数据 3.2 模拟锁等待 3.3 再次模拟锁等待 3.4 查询锁等待 3.4.1 直接查看 innodblockwaits 表 3.4.2 innodb...

java_龙
2018/11/30
0
0
记录一次Mysql死锁排查过程

背景 以前接触到的数据库死锁,都是批量更新时加锁顺序不一致而导致的死锁,但是上周却遇到了一个很难理解的死锁。借着这个机会又重新学习了一下mysql的死锁知识以及常见的死锁场景。在多方调...

wooyoo
2017/02/19
0
0
深入理解 MySQL ——锁、事务与并发控制

深入理解 MySQL ——锁、事务与并发控制 目录 MySQL 服务器逻辑架构 MySQL 锁 事务 隔离级别 并发控制 与 MVCC MySQL 死锁问题 1、MySQL 服务器逻辑架构 (图片来源MySQL官网) 每个连接都会...

优惠券活动
03/14
0
0

没有更多内容

加载失败,请刷新页面

加载更多

filebeat multiline配置(转)

使用filebeat5.0.1版本,用filebeat作为日志收集工具时: java日志格式需要多行匹配,在filebeat配置文件中添加: ### Multiline options # Mutiline can be used for log messages spanning...

xiaomin0322
25分钟前
1
0
ConstraintLayout的基本使用

<?xml version="1.0" encoding="utf-8"?><android.support.constraint.ConstraintLayout xmlns:android="http://schemas.android.com/apk/res/android" xmlns:tools="ht......

SuShine
29分钟前
1
0
ActiveMQ多个消费者消费不均匀问题

如果客户端处理很慢的话,Broker会在之前发送消息的反馈之前,继续发送新的消息到客户端。如果客户端依旧很慢的话,没有得到确认反馈的消息会持续增长。在这种情况下,Broker有可能会停止发送...

编程SHA
30分钟前
1
0
【机器学习PAI实战】—— 玩转人工智能之综述

模型训练与在线预测服务、推荐算法四部曲、机器学习PAI实战、更多精彩,尽在开发者分会场 【机器学习PAI实战】—— 玩转人工智能之商品价格预测 【机器学习PAI实战】—— 玩转人工智能之你最...

阿里云云栖社区
33分钟前
1
0
根据国务院2019年劳动节假期安排五一放假四天 免费节假日api第一时间调整

根据国务院发布http://www.gov.cn/zhengce/content/2019-03/22/content_5375877.htm 以下为原文 国务院办公厅关于调整 2019年劳动节假期安排的通知 国办发明电〔2019〕3号 各省、自治区、直辖...

xiaogg
38分钟前
3
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部