mysql 查看阻塞_MySQL8.0怎么找出阻塞源头

阻塞，MySQL中非常让人头疼的问题，因为锁资源之间的兼容关系，当一个事务需要等待另一事务所持有的锁的时候，便产生了阻塞，当数据库中存在大量阻塞的时候，导致CPU使用率飙升，整个系统的响应速度变慢。不过MySQL提供了参数innodb_lock_wait_timeout来限制锁等待时间，该参数默认值为50s，我们可以设置innodb_lock_wait_timeout为5s或者3s，这样锁等待时间不会太长。但这是治标不治本的方法，根本解决办法还是要找到产生阻塞的源头，从业务逻辑上避免产生阻塞。

在mysql5.7中，可以通过以下SQL来找出产生阻塞的语句：

select

b.trx_mysql_thread_id , – 被阻塞的会话ID

b.trx_query , – 被阻塞的SQL

c.trx_mysql_thread_id , – 造成阻塞的会话ID

c.trx_query,d.lock_mode , – 造成阻塞的SQL

lock_type, – 锁类型

lock_table , – 阻塞的表

lock_index – 阻塞的索引

from

performance_schema.innodb_lock_waits a

inner join performance_schema.innodb_trx b on a.requesting_trx_id=b.trx_id

inner join performance_schema.innodb_trx c on a.blocking_trx_id =c.trx_id

inner join performance_schema.innodb_locks d on a.blocking_lock_id=d.lock_id;

但是在mysql8.0中innodb_lock_waits，innodb_trx 这两张表被删除了，取而代之的是在performance_schema库中增加data_lock_waits，data_locks这两张表。先通过官方文档来解读一下这两张表。

data_locks：

该表显示了所有请求中和已经持有的锁。

列名

含义

ENGINE

存储引擎

ENGINE_LOCK_ID

锁的ID

ENGINE_TRANSACTION_ID

存储引擎内部ID

THREAD_ID

trx_id

EVENT_ID

会话ID

OBJECT_SCHEMA

数据库名称

OBJECT_NAME

表名称

PARTITION_NAME

分区名称

SUBPARTITION_NAME

子分区名称

INDEX_NAME

索引名称

OBJECT_INSTANCE_BEGIN

锁的内存中的地址

LOCK_TYPE

锁的类型

LOCK_MODE

如何请求锁定

LOCK_STATUS

请求状态

LOCK_DATA

锁定数据量

了解详细信息请参见官方文档：https://dev.mysql.com/doc/refman/8.0/en/performance-schema-data-locks-table.html

innodb_lock_waits：

该表显示了存在锁等待即阻塞的信息

列名

含义

ENGINE

存储引擎

REQUESTING_ENGINE_LOCK_ID

被阻塞锁的ID

REQUESTING_ENGINE_TRANSACTION_ID

被阻塞的trx_id

REQUESTING_THREAD_ID

被阻塞会话的线程ID

REQUESTING_EVENT_ID

被阻塞事件

REQUESTING_OBJECT_INSTANCE_BEGIN

内存地址

BLOCKING_ENGINE_LOCK_ID

造成阻塞锁ID

BLOCKING_ENGINE_TRANSACTION_ID

被阻塞trx_id

BLOCKING_THREAD_ID

阻塞会话的线程ID

BLOCKING_EVENT_ID

造成阻塞事件

BLOCKING_OBJECT_INSTANCE_BEGIN

内存地址

了解详细信息请参见官方文档：https://dev.mysql.com/doc/refman/8.0/en/performance-schema-data-lock-waits-table.html

现在通过锁等待信息表(innodb_lock_waits)和锁详细信息表(data_locks)以及详细事务信息的表(INNODB_TRX)查询出系统中存在阻塞的SQL和锁定的资源：

SELECT

b.trx_query,

c.trx_query,

c.OBJECT_SCHEMA,

OBJECT_NAME,

INDEX_NAME

FROM

performance_schema.data_lock_waits a

LEFT JOIN

information_schema.INNODB_TRX b ON a.REQUESTING_ENGINE_TRANSACTION_ID = b.trx_id

LEFT JOIN

information_schema.INNODB_TRX c ON a.BLOCKING_ENGINE_TRANSACTION_ID = c.trx_id

LEFT JOIN

performance_schema.data_locks c ON a.REQUESTING_ENGINE_LOCK_ID = c.ENGINE_LOCK_ID

例如：

首先将MySQL的innodb_lock_wait_timeout参数设置为600方便观察：

set global innodb_lock_wait_timeout=6000;

在session1 执行SQL:

begin：

update t_mysql_instances set f_port=3308 where f_id =1;

在session2 执行SQL:

begin：

select *from t_mysql_instances where f_id=1 for update ;

此时可以看到的现象为：

session1 ：

session2：

可以看到session执行完成，但是未提交，session2 处于执行中状态，因为session1现在仍然持有锁，所以session2被阻塞。查看阻塞：

可以看到被阻塞的SQL，但是此时造成阻塞的SQL已经执行完成但是未提交，所以在information_schema.INNODB_TRX中无法查看，可以通过performance_schema.events_statements_current查询：

select *from performance_schema.events_statements_current where event_id=23；