MySQL锁情况查看命令

本文介绍如何在MySQL数据库中分析锁的情况及处理思路。

MySQL版本

mysql> select version();
+------------+
| version()  |
+------------+
| 5.7.38-log |
+------------+
1 row in set (0.01 sec)

模拟锁产生

A会话加锁

mysql> show create table t\G;
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `name` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> select * from t;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | s    |
|  3 | c    |
|  4 | d    |
|  5 | e    |
+----+------+
5 rows in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t where id<5 for update;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | s    |
|  3 | c    |
|  4 | d    |
+----+------+
4 rows in set (0.00 sec)

B会话插入数据,造成锁等待现象

mysql> insert into t values(0,'null');

这里介绍MySQL查看锁的3个数据字典表,分别是位于information_schema数据库下的innodb_trx、innodb_lock_waits、innodb_locks三张表,查看步骤如下:

先看innodb_trx表

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> select * from innodb_trx\G;
*************************** 1. row ***************************
                    trx_id: 8553
                 trx_state: LOCK WAIT
               trx_started: 2022-12-14 16:52:29
     trx_requested_lock_id: 8553:45:3:2
          trx_wait_started: 2022-12-14 16:52:29
                trx_weight: 2
       trx_mysql_thread_id: 22
                 trx_query: insert into t values(0,'null')
       trx_operation_state: inserting
         trx_tables_in_use: 1
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 1
         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: 8552
                 trx_state: RUNNING
               trx_started: 2022-12-14 16:51:39
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 2
       trx_mysql_thread_id: 20
                 trx_query: NULL
       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: 5
         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 rows in set (0.00 sec)

ERROR: 
No query specified

mysql> show full processlist;
+----+--------+-----------+--------------------+---------+------+----------+--------------------------------+
| Id | User   | Host      | db                 | Command | Time | State    | Info                           |
+----+--------+-----------+--------------------+---------+------+----------+--------------------------------+
| 20 | root   | localhost | ray                | Sleep   |  132 |          | NULL                           |
| 22 | raybak | localhost | ray                | Query   |   82 | update   | insert into t values(0,'null') |
| 24 | root   | localhost | information_schema | Query   |    0 | starting | show full processlist          |
+----+--------+-----------+--------------------+---------+------+----------+--------------------------------+
3 rows in set (0.00 sec)

trx_id:唯一事务id号,本次测试中是8552和8553
trx_state:当前事务的状态,本次测试中8553是LOCK WAIT 锁等待状态
trx_wait_started:事务开始等待时间,本次测试中为2022-12-14 16:52:29
trx_mysql_thread_id:线程id,与show full processlist中的id对应,本次测试中为22
trx_query:事务运行的SQL语句,本次测试为insert into t values(0,‘null’)
trx_operation_state:事务运行的状态,本次测试为inserting

再看innodb_lock_waits表

mysql> select * from innodb_lock_waits\G;
*************************** 1. row ***************************
requesting_trx_id: 8553
requested_lock_id: 8553:45:3:2
  blocking_trx_id: 8552
 blocking_lock_id: 8552:45:3:2
1 row in set, 1 warning (0.00 sec)

requesting_trx_id:请求锁的事务id,本次测试为8553
blocking_trx_id:持有锁的事务id,也就是造成锁等待的事务id,本次测试为8552

再看innodb_locks表

mysql> select * from innodb_locks\G;
*************************** 1. row ***************************
    lock_id: 8553:45:3:2
lock_trx_id: 8553
  lock_mode: X,GAP
  lock_type: RECORD
 lock_table: `ray`.`t`
 lock_index: PRIMARY
 lock_space: 45
  lock_page: 3
   lock_rec: 2
  lock_data: 1
*************************** 2. row ***************************
    lock_id: 8552:45:3:2
lock_trx_id: 8552
  lock_mode: X
  lock_type: RECORD
 lock_table: `ray`.`t`
 lock_index: PRIMARY
 lock_space: 45
  lock_page: 3
   lock_rec: 2
  lock_data: 1
2 rows in set, 1 warning (0.00 sec)

ERROR: 
No query specified

综合三张表查询和show prcess fulllist得知,会话id 20(事务id 8552),锁住了ray.t表,锁模式是行级锁,会话id 22(事务id 8553)的insert操作需要等待会话20释放锁后才能执行,因此出现了会话id 22(事务id 8553)hang住现象。

解决方法,杀会话

mysql> kill 20;
Query OK, 0 rows affected (0.00 sec)

当然,杀会话也可以通过pt-kill工具更方便,在后续文章会对pt-kill工具做详细介绍

到此这篇关于MySQL锁情况查看命令的文章就介绍到这了,更多相关MySQL锁情况查看内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

(0)

相关推荐

  • MySQL 查看事务和锁情况的常用语句分享

    一些查看数据库中事务和锁情况的常用语句 查看事务等待状况: SELECT r.trx_id waiting_trx_id, r.trx_mysql_thread_id waiting_thread, r.trx_query waiting_query, b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread, b.trx_query blocking_query FROM information_schema.innodb_

  • 详解MySQL中的死锁情况以及对死锁的处理方法

    当多个事务同时持有和请求同一资源上的锁而产生循环依赖的时候就产生了死锁.死锁发生在事务试图以不同的顺序锁定资源.以StockPrice表上的两个事务为例: 事务1 START TRANSACTION; UPDATE StockPrice SET close = 45.50 WHERE stock_id = 4 and date = '2002-05-01'; UPDATE StockPrice SET close = 19.80 WHERE stock_id = 3 and date = '20

  • MySQL连接及基本信息查看命令汇总

    学习PHP就不得不提MySQL,虽然有phpMyadmin这样的工具可以图形化操作数据库,但我还是想借学习PHP的机会使用下命令行方式操作数据库.以下就是我的学习小结,包括命令行连接数据库,查看当前用户,查看当前使用的存储引擎,查看MySQL版本等基本命令. 连接数据库 命令格式:mysql -h hostname -u username -p -h指定要连接的MySQL数据库所在的主机,如果是本机,可以省略. -u指定登录的用户名,如果不指定,默认是操作系统的登录用户名. -p指定密码.可以在

  • Yii+MYSQL锁表防止并发情况下重复数据的方法

    本文实例讲述了Yii+MYSQL锁表防止并发情况下重复数据的方法.分享给大家供大家参考,具体如下: lock table 读锁定 如果一个线程获得在一个表上的read锁,那么该线程和所有其他线程只能从表中读数据,不能进行任何写操作. lock tables user read;//读锁定表 unlock tables;//解锁 lock tables user read local;//本地读锁定表,其他线程的insert未被阻塞,update操作被阻塞 lock table 写锁定 如果一个线

  • MySQL锁机制与用法分析

    本文实例讲述了MySQL锁机制与用法.分享给大家供大家参考,具体如下: MySQL的锁机制比较简单,其最显著的特点是不同的存储引擎支持不同的锁机制.比如,MyISAM和MEMORY存储引擎采用的是表级锁:BDB存储引擎采用的是页面锁,但也支持表级锁:InnoDB存储引擎既支持行级锁,也支持表级锁,但默认情况下采用行级锁. MySQL这3种锁的特性可大致归纳如下: (1)表级锁:开销小,加锁快:不会出现死锁:锁定粒度大,发生锁冲突的概率最高,并发度最低. (2)行级锁:开销大,加锁慢:会出现死锁:

  • MySQL锁(表锁,行锁,共享锁,排它锁,间隙锁)使用详解

    锁,在现实生活中是为我们想要隐藏于外界所使用的一种工具.在计算机中,是协调多个进程或县城并发访问某一资源的一种机制.在数据库当中,除了传统的计算资源(CPU.RAM.I/O等等)的争用之外,数据也是一种供许多用户共享访问的资源.如何保证数据并发访问的一致性.有效性,是所有数据库必须解决的一个问题,锁的冲突也是影响数据库并发访问性能的一个重要因素.从这一角度来说,锁对于数据库而言就显得尤为重要. MySQL锁 相对于其他的数据库而言,MySQL的锁机制比较简单,最显著的特点就是不同的存储引擎支持不

  • 一文教你学会定位线上MySQL锁超时问题

    前言: 昨晚我正在床上睡得着着的,突然来了一条短信. 什么?线上的订单无法取消! 我赶紧登录线上系统,查看业务日志. 发现有MySQL锁超时的错误日志. 不用想,肯定有另一个事务正在修改这条订单,持有这条订单的锁. 导致当前事务获取不到锁,一直等待,直到超过锁超时时间,然后报错. 既然问题已经清楚了,接下来就轮到怎么排查一下到底是哪个事务正在持有这条订单的锁. 好在MySQL提供了丰富的工具,帮助我们排查锁竞争问题. 现场复现一个这个问题: 创建一张用户表,造点数据: CREATE TABLE

  • 一篇文章带你了解清楚Mysql 锁

    一丶为什么数据库需要锁 数据库锁设计的初衷是处理并发问题.作为多用户共享 的资源,当出现并发访问的时候,数据库需要合理地控制资源的访问规则.而锁就是用来实 现这些访问规则的重要数据结构. 根据加锁的范围,MySQL 里面的锁大致可以分成全局锁.表级锁和行锁三类 二丶全局锁&全库逻辑备份 全局锁就是对整个数据库实例加锁.全局锁的典型使用场景是,做全库逻辑备份,全库逻辑备份有以下几种方式: 1.Flush tables with read lock (FTWRL) Flush tables with

  • 详解MySQL中EXPLAIN解释命令及用法讲解

    1,情景描述:同事教我在mysql中用explain,于是查看了一番返回内容的含义 2,现就有用处的内容做如下记录: 1,explain显示了mysql如何使用索引来处理select语句以及连接表.可以帮助选择更好的索引和写出更优化的查询语句. 使用方法,在select语句前加上explain就可以了: explain select count(DISTINCT uc_userid) as user_login from user_char_daily_gameapp_11 where uc_d

  • MySQL锁阻塞的深入分析

    日常维护中,经常会碰到线程被阻塞,导致数据库响应非常慢,下面就看看如何获取是哪个线程导致了阻塞的. 1. 环境说明 RHEL 6.4 x86_64 + MySQL 5.6.19 事务隔离级别:RR 2. 测试过程 3. 查看锁阻塞线程信息 这里用几中方法进行分析: 3.1  使用show processlist查看 MySQL [(none)]> show processlist; +----+------+-----------+------+---------+------+--------

  • MySQL 锁的相关知识总结

    MySQL中的锁 锁是为了解决并发环境下资源竞争的手段,其中乐观并发控制,悲观并发控制和多版本并发控制是数据库并发控制主要采用的技术手段(具体可见我之前的文章),而MySQL中的锁就是其中的悲观并发控制. MySQL中的锁有很多种类,我们可以按照下面方式来进行分类. 按读写 从数据库的读写的角度来分,数据库的锁可以分为分为以下几种: 独占锁:又称排它锁.X锁.写锁.X锁不能和其他锁兼容,只要有事务对数据上加了任何锁,其他事务就不能对这些数据再放置X了,同时某个事务放置了X锁之后,其他事务就不能再

随机推荐