阻塞，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；
