MySQL 中阻塞语句的定位和分析

数据库中阻塞语句的查询和分析

数据库中阻塞语句的查询和分析

前言

MySQL 阻塞是指在并发访问 MySQL 数据库时，某个事务占用了资源并且长时间不释放，导致其他事务无法执行或执行缓慢的情况。

MySQL 阻塞可能会导致数据库性能下降，甚至出现死锁等问题，需要马上进行处理。

在 MySQL中，线程阻塞可能是由于以下原因导致：

1、锁冲突：如果两个或者多个线程同时请求同一个资源(栗如：同一行或者同一个表)，其中一个将被阻塞，直到其他线程释放锁；

2、长事务：如果一个事务占用锁的时间过长，可能会导致其它事务长时间等待甚至是超时；

3、死锁：如果两个线程或者更多的线程相互等待对方的资源，将会发生死锁(Deadlock)，进而导致语句的执行阻塞。

如何排查和定位阻塞语句呢，下面来分析下？

MySQL

面对阻塞的语句如何查看呢？

首先我们来模拟2个阻塞的场景，然后使用命令来排查定位。

准备数据

CREATE TABLE `t_user` (
  `id` int(11) NOT NULL,
  `name` varchar(16) NOT NULL,
  `age` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

insert into t_user values(1,  "小明",12);
insert into t_user values(2, "小红",20);
insert into t_user values(3, "小白",19);
insert into t_user values(4, "张三",24);
insert into t_user values(5, "李四",25);
insert into t_user values(6, "王五",26);

模拟长事务的场景

事务 1

SET autocommit = 0;
begin;
UPDATE t_user SET age=12  WHERE id=1;
select SLEEP(12000);
commit;

事务 2

SET autocommit = 0;
begin;
UPDATE t_user SET age=13  WHERE id=1;
commit;

两个事务，第一个事务更新语句对 id=1 这一行加了行锁，同时这个事务 sleep 了 120 秒。事务2同样更新 id=1 这一行数据，也会加一把行锁，因为事务 1 的 sleep，导致事务 1 的行锁没有释放，事务 2 就处于阻塞中了。

下面来看下如何排查

1、使用 show processlist 查询正在运行的进程

show processlist 就是查看当前 mysql正在执行的进程,主要有两个作用：

1、查看慢查询的sql是哪个；

2、查看出现锁的sql是哪个。

root 用户或者有 PROCESS 权限的用户能执行这个查询。

show processlist 显示的信息都是来自 MySQL 系统库 information_schema 中的 processlist 表。也可以直接查询这个。

Select * from information_schema.processlist；

$ show processlist;

+----+------+------------------+--------------------+---------+------+------------+--------------------------------------+
| Id | User | Host             | db                 | Command | Time | State      | Info                                 |
+----+------+------------------+--------------------+---------+------+------------+--------------------------------------+
|  7 | root | 172.21.0.1:56974 | test               | Query   |    3 | updating   | UPDATE t_user SET age=13  WHERE id=1 |
|  8 | root | 172.21.0.1:56976 | information_schema | Query   |    0 | starting   | show processlist                     |
|  9 | root | 172.21.0.1:56978 | test               | Query   | 2120 | User sleep | select SLEEP(12000)                  |
+----+------+------------------+--------------------+---------+------+------------+--------------------------------------+

来看下 show processlist 中几个参数的含义：

Id：线程的标识，如果该线程幼有问题，可以直接通过 kill <Id>，杀死该线程；
User：启动这个线程的用户；
Host: 客户端 IP 和端口号。结合 ss -n | grep :<port> 命令，可以定位到是哪个进程发送的请求；
db：当前执行的命令是在哪个数据库；
Command：显示正在执行的命令，常见的有休眠（sleep），查询（query），连接（connect）等，更多的可参见官方文档Thread Command Values;
Time：表示这个状态持续的时间，单位是秒；
State：表示当前执行 sql 语句的状态,例如 executing 表示开始执行语句，Rolling back 表示线程正在回滚事务。更多的可参见官方文档 General Thread States；
Info：显示的是正在执行的 sql 语句，不过这个只能显示前100个字符，要看全部的执行 sql，可使用 show full processlist 。

下面列举几个常用的查询分析栗子

按客户端 IP 分组，看哪个客户端的链接数最多

select
  client_ip, count(client_ip) as client_num
from
	(select 
	substring_index(host, ':', 1) as client_ip
  from 
	information_schema.processlist) as connect_info
group by client_ip order by client_num desc;

+------------+------------+
| client_ip  | client_num |
+------------+------------+
| 172.21.0.1 |          1 |
+------------+------------+

查询正在执行的 sql,根据时间倒叙查询，查询执行时间较长的 sql

select
   * 
from 
   information_schema.processlist 
where 
   Command != 'Sleep' 
	 order by Time desc;

2、使用 INNODB_TRX 查询当前运行的事务

INNODB_TRX 表提供了当前在 InnoDB 内部执行的所有事务信息，包含事务是否在等待锁，事务何时开始以及事务正在执行的 SQL 语句(如果有的话，sql语句阻塞就可以显示)。

select * from information_schema.innodb_trx where trx_state="LOCK WAIT"\G;

*************************** 1. row ***************************
                    trx_id: 5800
                 trx_state: LOCK WAIT
               trx_started: 2023-07-14 01:34:06
     trx_requested_lock_id: 5800:630:3:8
          trx_wait_started: 2023-07-14 01:34:06
                trx_weight: 2
       trx_mysql_thread_id: 16
                 trx_query: UPDATE t_user SET age=13  WHERE id=1
       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: 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

看几个主要的参数，更详细的信息可参见The INFORMATION_SCHEMA INNODB_TRX Table

trx_id：InnoDB 内部的唯一事务 ID，不会为只读且非锁定事务创建 ID；
trx_state：事务的执行状态。值为 RUNNING(运行), LOCK WAIT(等待锁), ROLLING BACK(正在回滚), 和 COMMITTING(正在提交)；
trx_query：事务正在执行的 sql;
trx_isolation_level：事务的隔离级别；
trx_autocommit_non_locking：无锁自动提交标识。值1表示该事务是一个 SELECT 语句，不使用 FOR UPDATE 或 LOCK IN SHARED MODE 子句，并且在启用自动提交的情况下执行，因此该事务只包含这一条语句。当这一列和 TRX_IS_READ_ONLY 都为 1 时，InnoDB会优化事务，以减少与更改表数据的事务相关的开销。

3、使用 INNODB_LOCKS 来查询当前出现的锁

SELECT * FROM information_schema.INNODB_LOCKS; 

+--------------+-------------+-----------+-----------+-----------------+------------+------------+-----------+----------+-----------+
| lock_id      | lock_trx_id | lock_mode | lock_type | lock_table      | lock_index | lock_space | lock_page | lock_rec | lock_data |
+--------------+-------------+-----------+-----------+-----------------+------------+------------+-----------+----------+-----------+
| 5812:630:3:8 | 5812        | X         | RECORD    | `test`.`t_user` | PRIMARY    |        630 |         3 |        8 | 1         |
| 5811:630:3:8 | 5811        | X         | RECORD    | `test`.`t_user` | PRIMARY    |        630 |         3 |        8 | 1         |
+--------------+-------------+-----------+-----------+-----------------+------------+------------+-----------+----------+-----------+

来看下每个字段的含义，更详细的可参加The data_locks Table

lock_id：锁 id;
lock_trx_id：拥有锁的事务id, 可以和 INNODB_TRX 表 JOIN 得到事务的详细信息；
lock_mode：锁的模式，如下锁的类型，行级锁包括：S、X、IS、IX，分别代表：共享锁、排它锁、意向共享锁、意向排它锁。表级锁包括：S_GAP、X_GAP、IS_GAP、IX_GAP 和 AUTO_INC，分别代表共享间隙锁、排它间隙锁、意向共享间隙锁、意向排它间隙锁和自动递增锁；
lock_type：锁的类型，RECORD 代表行级锁，TABLE 代表表级锁；
lock_table：被锁定的或者包含锁记录的表名称；
lock_index：当 LOCK_TYPE=’RECORD’ 时，表示索引的名称；否则为 NULL；
lock_space：当 LOCK_TYPE=’RECORD’ 时，表示锁定行的表空间 ID；否则为 NULL。
lock_page：当 LOCK_TYPE=’RECORD’ 时，表示锁定行的页号；否则为 NULL。
lock_rec：当 LOCK_TYPE=’RECORD’ 时，表示一堆页面中锁定行的数量，亦即被锁定的记录号；否则为 NULL。
lock_data：当 LOCK_TYPE=’RECORD’ 时，表示锁定行的主键；否则为NULL。

4、使用 INNODB_LOCK_WAITS 来查询当前锁等待的关系

SELECT * FROM information_schema.INNODB_LOCK_WAITS;

+-------------------+-------------------+-----------------+------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+-------------------+-----------------+------------------+
| 5812              | 5812:630:3:8      | 5811            | 5811:630:3:8     |
+-------------------+-------------------+-----------------+------------------+

来看下每个字段的含义，跟详细的参数可参见The data_lock_waits Table

requesting_trx_id：请求事务的 ID ;
requested_lock_id：事务所等待的锁定的 ID。可以和 INNODB_LOCKS 表 JOIN;
blocking_trx_id：阻塞事务的 ID;
blocking_lock_id：某一事务的锁的 ID，该事务阻塞了另一事务的运行。可以和 INNODB_LOCKS 表 JOIN。

总结

如果发现数据库响应变慢，排查阻塞语句，通过 show processlist 命令或者 performance_schema 表来查看当前正在执行的 SQL 语句，就能简单的分析出执行较长的 sql 语句，以及正在等待的锁和事务信息，找到阻塞的原因；

不过需要看更加详细的信息，就需要借助于下面的信息来分析定位。

1、使用 INNODB_TRX 查询当前运行的事务；

2、使用 INNODB_LOCKS 来查询当前出现的锁；

3、使用 INNODB_LOCK_WAITS 来查询当前锁等待的关系；

如果某个事务已经卡住了，可以使用 MySQL的 kill 命令来强制结束该事务，以释放资源。

当前要彻底结局问题还是要分析原因，优化查询语句或者业务中对 sql 的使用。

参考

【mysql阻塞怎么处理】https://juejin.cn/s/mysql%E9%98%BB%E5%A1%9E%E6%80%8E%E4%B9%88%E5%A4%84%E7%90%86
【mysql线程阻塞】https://juejin.cn/s/mysql%E7%BA%BF%E7%A8%8B%E9%98%BB%E5%A1%9E
【mysql: show processlist 详解】https://zhuanlan.zhihu.com/p/30743094
【mysql 使用 innodb_trx innodb_lock_waits innodb_locks 来分析锁】https://www.cnblogs.com/digdeep/p/15259091.html
【Mysql数据库系统表之详细了解INNODB_TRX、INNODB_LOCKs、INNODB_LOCK_waits、PROCESSLIST表】https://blog.csdn.net/yu121380/article/details/110132741

MySQL 中阻塞语句的定位和分析

MySQL 中分析和定位阻塞的语句

数据库中阻塞语句的查询和分析

前言

MySQL

1、使用 show processlist 查询正在运行的进程

2、使用 INNODB_TRX 查询当前运行的事务

3、使用 INNODB_LOCKS 来查询当前出现的锁

4、使用 INNODB_LOCK_WAITS 来查询当前锁等待的关系

总结

参考

CATALOG

FEATURED TAGS

FRIENDS