通常有这样的疑问: 为什么阻塞查询为NULL并处于睡眠状态，以及如何找到关于阻塞事务的更多信息？
当阻塞查询为NULL时，意味着阻塞的事务当前没有执行任何查询。然而，它仍然持有锁。您需要查看阻塞事务的历史记录，以确定它在空闲之前在做什么。
例如，考虑以下两个交易:
Connection 1:
(Fri Sep 8 08:58:19 2023)[root@GreatSQL][andy]>START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
(Fri Sep 8 08:58:36 2023)[root@GreatSQL][andy]>insert into andy values(1);
Query OK, 1 row affected (0.00 sec)
(Fri Sep 8 08:58:36 2023)[root@GreatSQL][andy]>insert into andy values(2);
Query OK, 1 row affected (0.00 sec)
(Fri Sep 8 08:58:36 2023)[root@GreatSQL][andy]>insert into andy values(3);
Query OK, 1 row affected (0.00 sec)
(Fri Sep 8 08:58:36 2023)[root@GreatSQL][andy]>insert into andy values(4);
Query OK, 1 row affected (0.00 sec)
(Fri Sep 8 08:58:36 2023)[root@GreatSQL][andy]>insert into andy values(5);
Query OK, 1 row affected (0.00 sec)
(Fri Sep 8 08:58:36 2023)[root@GreatSQL][andy]>insert into andy values(6);
Query OK, 1 row affected (0.00 sec)
(Fri Sep 8 08:58:36 2023)[root@GreatSQL][andy]>insert into andy values(7);
Query OK, 1 row affected (0.00 sec)
(Fri Sep 8 08:58:36 2023)[root@GreatSQL][andy]>insert into andy values(8);
Query OK, 1 row affected (0.00 sec)
(Fri Sep 8 08:58:36 2023)[root@GreatSQL][andy]>insert into andy values(9);
Query OK, 1 row affected (0.00 sec)
(Fri Sep 8 08:58:36 2023)[root@GreatSQL][andy]>insert into andy values(10);
Query OK, 1 row affected (0.00 sec)
(Fri Sep 8 08:58:36 2023)[root@GreatSQL][andy]>insert into andy values(11);
Query OK, 1 row affected (0.00 sec)
(Fri Sep 8 08:58:36 2023)[root@GreatSQL][andy]>insert into andy values(12);
Query OK, 1 row affected (0.00 sec)
Connection 2 (blocks for innodb_lock_wait_timeout seconds):
(Fri Sep 8 08:58:44 2023)[root@GreatSQL][andy]>insert into andy values(12);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
Connection 3 sys.innodb_lock_waits的以下输出显示阻塞查询为NULL:
所以这里的阻塞查询为空。但是事务在变得闲置之前做了什么？
如果启用events_statements_history使用者(在MySQL 5.7和更高版本中默认启用)，events_statements_history表将包括为该连接执行的最后10条语句(默认情况下):
SELECT THREAD_ID FROM performance_schema.threads WHERE PROCESSLIST_ID =7113444;
SELECT THREAD_ID, SQL_TEXT FROM performance_schema.events_statements_history WHERE THREAD_ID =7113456 ORDER BY EVENT_ID;
如果启用了事务监控(仅在MySQL 5.7和更高版本中可用)，则有可能获得有关事务的更多信息，并自动将历史记录的查询限制在当前事务。默认情况下，不启用事务监控。要启用它，请使用:
mysql> UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME = 'events_transactions_current';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME = 'transaction';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
现在可以看到阻塞事务的历史，并且可以确定为什么会发生锁定问题
