MySQL Cases-MySQL找出谁持有行锁（RR）

原创

姚崇

修改于 2021-09-18 11:35:12

1.4K0

修改于 2021-09-18 11:35:12

文章被收录于专栏：MySQL故障优化案例MySQL故障优化案例

全部关于锁文章

全局读锁https://cloud.tencent.com/developer/article/1869375
表锁 https://cloud.tencent.com/developer/article/1869546
MDLhttps://cloud.tencent.com/developer/article/1869667
MySQL找出谁持有行锁（RR级别）https://cloud.tencent.com/developer/article/1869793
MySQL找出谁持有行锁（RC级别）https://cloud.tencent.com/developer/article/1869900

提到行锁，不得不提事务，不得不提事物隔离级别

事务隔离级别：

一个事务所做的修改，对其他事务是不可见的，好似是串行执行的。多个事务并行执行的，好似他是串行执行的，事务并发执行，但是效果和串行效果一致，一个事务所做的修改对其他事务是不可见的，好似是串行执行的。

不符合隔离性就会存在三个问题：脏读、幻读、不可重复读。或者换句话说隔离级别就是解决脏读、幻读、不可重复读.。MySQL下加锁都是对索引进行加锁。

ANSI（美国国家标准学会 AMERICAN NATIONAL STANDARDS INSTITUTE: ANSI）推出的标准只有serializable符合隔离性要求，如下：

innodb下实现如下图

innodb下RU(read-uncommitted) 什么都没解决，RR、SR 都解决了，这就是为什么MySQL在默认事务隔离下达到隔离性要求，但是锁比较重，然而RR比serializable要好，下面我们看下RC和RR级别下，锁的粒度，我们以MySQL8.0为参考。

RR级别行锁

where列无索引无主键

sess 1:
drop table t;
create table t(id int, id2 int,t timestamp(6));
insert into t values(1,1,now());
insert into t values(2,5,now());
insert into t values(5,12,now());
insert into t values(10,13,now());
begin;
update t set t = now() where id2 = 9;

mysql> select * from performance_schema.data_locks;
+--------+----------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-----------------+-----------------------+-----------+-----------+-------------+------------------------+
| ENGINE | ENGINE_LOCK_ID                         | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME      | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA              |
+--------+----------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-----------------+-----------------------+-----------+-----------+-------------+------------------------+
| INNODB | 140023602396568:1090:140023517226320   |                174531 |       192 |       26 | test          | t           | NULL           | NULL              | NULL            |       140023517226320 | TABLE     | IX        | GRANTED     | NULL                   |
| INNODB | 140023602396568:29:4:1:140023517223216 |                174531 |       192 |       26 | test          | t           | NULL           | NULL              | GEN_CLUST_INDEX |       140023517223216 | RECORD    | X         | GRANTED     | supremum pseudo-record |
| INNODB | 140023602396568:29:4:2:140023517223216 |                174531 |       192 |       26 | test          | t           | NULL           | NULL              | GEN_CLUST_INDEX |       140023517223216 | RECORD    | X         | GRANTED     | 0x000000000301         |
| INNODB | 140023602396568:29:4:3:140023517223216 |                174531 |       192 |       26 | test          | t           | NULL           | NULL              | GEN_CLUST_INDEX |       140023517223216 | RECORD    | X         | GRANTED     | 0x000000000302         |
| INNODB | 140023602396568:29:4:4:140023517223216 |                174531 |       192 |       26 | test          | t           | NULL           | NULL              | GEN_CLUST_INDEX |       140023517223216 | RECORD    | X         | GRANTED     | 0x000000000303         |
| INNODB | 140023602396568:29:4:5:140023517223216 |                174531 |       192 |       26 | test          | t           | NULL           | NULL              | GEN_CLUST_INDEX |       140023517223216 | RECORD    | X         | GRANTED     | 0x000000000304         |
+--------+----------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-----------------+-----------------------+-----------+-----------+-------------+------------------------+
6 rows in set (0.18 sec)

会话2执行

sess2:下面的情况插入8 10 22 都是一个下面的结果
begin;
insert into t values(8,8,now());
insert into t values(10,10,now());
insert into t values(22,22,now());
都被阻塞

会话3执行

--sys.innodb_lock_waits MySQL5.7也可以使用

mysql> select wait_started,wait_age_secs,locked_table,locked_index,locked_type,waiting_lock_mode,blocking_lock_mode from sys.innodb_lock_waits;
+---------------------+---------------+--------------+-----------------+-------------+--------------------+--------------------+
| wait_started        | wait_age_secs | locked_table | locked_index    | locked_type | waiting_lock_mode  | blocking_lock_mode |
+---------------------+---------------+--------------+-----------------+-------------+--------------------+--------------------+
| 2021-08-30 13:40:59 |            45 | `test`.`t`   | GEN_CLUST_INDEX | RECORD      | X,INSERT_INTENTION | X                  |
+---------------------+---------------+--------------+-----------------+-------------+--------------------+--------------------+
1 row in set (0.22 sec)

mysql> select * from performance_schema.data_locks;
+--------+----------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-----------------+-----------------------+-----------+-----------+-------------+------------------------+
| ENGINE | ENGINE_LOCK_ID                         | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME      | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA              |
+--------+----------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-----------------+-----------------------+-----------+-----------+-------------+------------------------+
| INNODB | 140023602397424:1090:140023517232480   |                174536 |       193 |       16 | test          | t           | NULL           | NULL              | NULL            |       140023517232480 | TABLE     | IX        | GRANTED     | NULL                   |
| INNODB | 140023602396568:1090:140023517226320   |                174531 |       192 |       26 | test          | t           | NULL           | NULL              | NULL            |       140023517226320 | TABLE     | IX        | GRANTED     | NULL                   |
| INNODB | 140023602396568:29:4:1:140023517223216 |                174531 |       192 |       26 | test          | t           | NULL           | NULL              | GEN_CLUST_INDEX |       140023517223216 | RECORD    | X         | GRANTED     | supremum pseudo-record |
| INNODB | 140023602396568:29:4:2:140023517223216 |                174531 |       192 |       26 | test          | t           | NULL           | NULL              | GEN_CLUST_INDEX |       140023517223216 | RECORD    | X         | GRANTED     | 0x000000000301         |
| INNODB | 140023602396568:29:4:3:140023517223216 |                174531 |       192 |       26 | test          | t           | NULL           | NULL              | GEN_CLUST_INDEX |       140023517223216 | RECORD    | X         | GRANTED     | 0x000000000302         |
| INNODB | 140023602396568:29:4:4:140023517223216 |                174531 |       192 |       26 | test          | t           | NULL           | NULL              | GEN_CLUST_INDEX |       140023517223216 | RECORD    | X         | GRANTED     | 0x000000000303         |
| INNODB | 140023602396568:29:4:5:140023517223216 |                174531 |       192 |       26 | test          | t           | NULL           | NULL              | GEN_CLUST_INDEX |       140023517223216 | RECORD    | X         | GRANTED     | 0x000000000304         |
+--------+----------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-----------------+-----------------------+-----------+-----------+-------------+------------------------+
7 rows in set (0.00 sec)

会话3使用关联脚本查询

-- 默认开启performance_schema的情况，5.7和8.0都能用。
select /*default performance_schema level 5.7 and 8.0 both can*/ *
from (
         select distinct c.THREAD_ID,
                         x.sql_kill_blocking_connection                                             as kill_command,
                         x.blocking_lock_mode,
                         x.waiting_lock_mode,
                         c.event_name,
                         c.sql_text                                                                 as blocking_sql_text,
                         x.waiting_query                                                            as blocked_sql_text,
                         c.CURRENT_SCHEMA,
                         c.OBJECT_NAME,
                         DATE_SUB(NOW(), INTERVAL (SELECT VARIABLE_VALUE
                                                   FROM performance_schema.global_status
                                                   WHERE VARIABLE_NAME = 'UPTIME') -
                                                  c.TIMER_START / 1000 / 1000 / 1000 / 1000 second) AS 'blocking_session_sql_start_time',
                         x.wait_age_secs                                                            as blocked_waiting_seconds,
                         x.locked_table,
                         x.locked_index,
                         x.locked_type
         from performance_schema.events_statements_history c
                  inner join (
             select t.THREAD_ID,
                    ilw.sql_kill_blocking_connection,
                    ilw.waiting_lock_mode,
                    ilw.blocking_lock_mode,
                    ilw.wait_age_secs,
                    ilw.locked_table,
                    ilw.waiting_query,
                    ilw.locked_index,
                    ilw.locked_type
             from sys.innodb_lock_waits ilw
                      inner join performance_schema.threads t on t.PROCESSLIST_ID = ilw.blocking_pid) x
                             on x.THREAD_ID = c.THREAD_ID) xx
order by xx.blocking_session_sql_start_time;
+-----------+--------------+--------------------+--------------------+----------------------------+------------------------------------------------+-----------------------------------+----------------+-------------+---------------------------------+-------------------------+--------------+-----------------+-------------+
| THREAD_ID | kill_command | blocking_lock_mode | waiting_lock_mode  | event_name                 | blocking_sql_text                              | blocked_sql_text                  | CURRENT_SCHEMA | OBJECT_NAME | blocking_session_sql_start_time | blocked_waiting_seconds | locked_table | locked_index    | locked_type |
+-----------+--------------+--------------------+--------------------+----------------------------+------------------------------------------------+-----------------------------------+----------------+-------------+---------------------------------+-------------------------+--------------+-----------------+-------------+
|       192 | KILL 149     | X                  | X,INSERT_INTENTION | statement/com/Field List   | NULL                                           | insert into t values(10,10,now()) | test           | NULL        | 2021-08-30 13:39:06.485359      |                       1 | `test`.`t`   | GEN_CLUST_INDEX | RECORD      |
|       192 | KILL 149     | X                  | X,INSERT_INTENTION | statement/sql/drop_table   | drop table t                                   | insert into t values(10,10,now()) | test           | NULL        | 2021-08-30 13:39:07.335545      |                       1 | `test`.`t`   | GEN_CLUST_INDEX | RECORD      |
|       192 | KILL 149     | X                  | X,INSERT_INTENTION | statement/sql/create_table | create table t(id int, id2 int,t timestamp(6)) | insert into t values(10,10,now()) | test           | NULL        | 2021-08-30 13:39:07.527888      |                       1 | `test`.`t`   | GEN_CLUST_INDEX | RECORD      |
|       192 | KILL 149     | X                  | X,INSERT_INTENTION | statement/sql/insert       | insert into t values(1,1,now())                | insert into t values(10,10,now()) | test           | NULL        | 2021-08-30 13:39:07.674543      |                       1 | `test`.`t`   | GEN_CLUST_INDEX | RECORD      |
|       192 | KILL 149     | X                  | X,INSERT_INTENTION | statement/sql/insert       | insert into t values(2,5,now())                | insert into t values(10,10,now()) | test           | NULL        | 2021-08-30 13:39:07.692373      |                       1 | `test`.`t`   | GEN_CLUST_INDEX | RECORD      |
|       192 | KILL 149     | X                  | X,INSERT_INTENTION | statement/sql/insert       | insert into t values(5,12,now())               | insert into t values(10,10,now()) | test           | NULL        | 2021-08-30 13:39:07.704618      |                       1 | `test`.`t`   | GEN_CLUST_INDEX | RECORD      |
|       192 | KILL 149     | X                  | X,INSERT_INTENTION | statement/sql/insert       | insert into t values(10,13,now())              | insert into t values(10,10,now()) | test           | NULL        | 2021-08-30 13:39:07.714441      |                       1 | `test`.`t`   | GEN_CLUST_INDEX | RECORD      |
|       192 | KILL 149     | X                  | X,INSERT_INTENTION | statement/sql/begin        | begin                                          | insert into t values(10,10,now()) | test           | NULL        | 2021-08-30 13:39:07.746770      |                       1 | `test`.`t`   | GEN_CLUST_INDEX | RECORD      |
|       192 | KILL 149     | X                  | X,INSERT_INTENTION | statement/sql/update       | update t set t = now() where id2 = 9           | insert into t values(10,10,now()) | test           | NULL        | 2021-08-30 13:39:07.747276      |                       1 | `test`.`t`   | GEN_CLUST_INDEX | RECORD      |
|       192 | KILL 149     | X                  | X,INSERT_INTENTION | statement/sql/select       | select * from performance_schema.data_locks    | insert into t values(10,10,now()) | test           | NULL        | 2021-08-30 13:39:09.834606      |                       1 | `test`.`t`   | GEN_CLUST_INDEX | RECORD      |
+-----------+--------------+--------------------+--------------------+----------------------------+------------------------------------------------+-----------------------------------+----------------+-------------+---------------------------------+-------------------------+--------------+-----------------+-------------+
10 rows in set (0.00 sec)

IX：代表意向排他锁表锁。
X：代表Next-Key Lock锁定记录本身和记录之前的间隙(X)。
S：代表Next-Key Lock锁定记录本身和记录之前的间隙(S)。
X,REC_NOT_GAP：代表只锁定记录本身(X)。
S,REC_NOT_GAP：代表只锁定记录本身(S)。
X,GAP：代表间隙锁，不锁定记录本身(X)。
X,GAP：代表间隙锁，不锁定记录本身(S)。
X,GAP,INSERT_INTENTION：代表插入意向锁。

where列为普通索引列

sess 1:
drop table t;
create table t(id int, id2 int,t timestamp(6) , key(id2));
insert into t values(1,1,now());
insert into t values(2,5,now());
insert into t values(5,12,now());
insert into t values(10,13,now());
begin;
update t set t = now() where id2 = 9;
select * from performance_schema.data_locks;

mysql> select * from performance_schema.data_locks;
+--------+----------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+--------------------+
| ENGINE | ENGINE_LOCK_ID                         | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA          |
+--------+----------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+--------------------+
| INNODB | 140023602396568:1091:140023517226320   |                174562 |       192 |       43 | test          | t           | NULL           | NULL              | NULL       |       140023517226320 | TABLE     | IX        | GRANTED     | NULL               |
| INNODB | 140023602396568:30:5:4:140023517223216 |                174562 |       192 |       43 | test          | t           | NULL           | NULL              | id2        |       140023517223216 | RECORD    | X,GAP     | GRANTED     | 12, 0x00000000030B |
+--------+----------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+--------------------+
2 rows in set (0.00 sec)

会话2

sess2:
begin;
insert into t values(8,8,now());
insert into t values(10,10,now());
会被阻塞
insert into t values(22,22,now());
不会被阻塞

会话3查询到的阻塞信息如下

-- 默认开启performance_schema的情况，5.7和8.0都能用。
select /*default performance_schema level 5.7 and 8.0 both can*/ *
from (
         select distinct c.THREAD_ID,
                         x.sql_kill_blocking_connection                                             as kill_command,
                         x.blocking_lock_mode,
                         x.waiting_lock_mode,
                         c.event_name,
                         c.sql_text                                                                 as blocking_sql_text,
                         x.waiting_query                                                            as blocked_sql_text,
                         c.CURRENT_SCHEMA,
                         c.OBJECT_NAME,
                         DATE_SUB(NOW(), INTERVAL (SELECT VARIABLE_VALUE
                                                   FROM performance_schema.global_status
                                                   WHERE VARIABLE_NAME = 'UPTIME') -
                                                  c.TIMER_START / 1000 / 1000 / 1000 / 1000 second) AS 'blocking_session_sql_start_time',
                         x.wait_age_secs                                                            as blocked_waiting_seconds,
                         x.locked_table,
                         x.locked_index,
                         x.locked_type
         from performance_schema.events_statements_history c
                  inner join (
             select t.THREAD_ID,
                    ilw.sql_kill_blocking_connection,
                    ilw.waiting_lock_mode,
                    ilw.blocking_lock_mode,
                    ilw.wait_age_secs,
                    ilw.locked_table,
                    ilw.waiting_query,
                    ilw.locked_index,
                    ilw.locked_type
             from sys.innodb_lock_waits ilw
                      inner join performance_schema.threads t on t.PROCESSLIST_ID = ilw.blocking_pid) x
                             on x.THREAD_ID = c.THREAD_ID) xx
order by xx.blocking_session_sql_start_time;
                             
+-----------+--------------+--------------------+------------------------+----------------------------+-----------------------------------------------------------+---------------------------------+----------------+-------------+---------------------------------+-------------------------+--------------+--------------+-------------+
| THREAD_ID | kill_command | blocking_lock_mode | waiting_lock_mode      | event_name                 | blocking_sql_text                                         | blocked_sql_text                | CURRENT_SCHEMA | OBJECT_NAME | blocking_session_sql_start_time | blocked_waiting_seconds | locked_table | locked_index | locked_type |
+-----------+--------------+--------------------+------------------------+----------------------------+-----------------------------------------------------------+---------------------------------+----------------+-------------+---------------------------------+-------------------------+--------------+--------------+-------------+
|       192 | KILL 149     | X,GAP              | X,GAP,INSERT_INTENTION | statement/sql/rollback     | rollback                                                  | insert into t values(8,8,now()) | test           | NULL        | 2021-08-30 13:49:41.780473      |                      47 | `test`.`t`   | id2          | RECORD      |
|       192 | KILL 149     | X,GAP              | X,GAP,INSERT_INTENTION | statement/sql/drop_table   | drop table t                                              | insert into t values(8,8,now()) | test           | NULL        | 2021-08-30 13:49:42.974121      |                      47 | `test`.`t`   | id2          | RECORD      |
|       192 | KILL 149     | X,GAP              | X,GAP,INSERT_INTENTION | statement/sql/create_table | create table t(id int, id2 int,t timestamp(6) , key(id2)) | insert into t values(8,8,now()) | test           | NULL        | 2021-08-30 13:49:58.766664      |                      47 | `test`.`t`   | id2          | RECORD      |
|       192 | KILL 149     | X,GAP              | X,GAP,INSERT_INTENTION | statement/sql/insert       | insert into t values(1,1,now())                           | insert into t values(8,8,now()) | test           | NULL        | 2021-08-30 13:49:58.919023      |                      47 | `test`.`t`   | id2          | RECORD      |
|       192 | KILL 149     | X,GAP              | X,GAP,INSERT_INTENTION | statement/sql/insert       | insert into t values(2,5,now())                           | insert into t values(8,8,now()) | test           | NULL        | 2021-08-30 13:49:58.954370      |                      47 | `test`.`t`   | id2          | RECORD      |
|       192 | KILL 149     | X,GAP              | X,GAP,INSERT_INTENTION | statement/sql/insert       | insert into t values(5,12,now())                          | insert into t values(8,8,now()) | test           | NULL        | 2021-08-30 13:49:58.970091      |                      47 | `test`.`t`   | id2          | RECORD      |
|       192 | KILL 149     | X,GAP              | X,GAP,INSERT_INTENTION | statement/sql/insert       | insert into t values(10,13,now())                         | insert into t values(8,8,now()) | test           | NULL        | 2021-08-30 13:49:58.982245      |                      47 | `test`.`t`   | id2          | RECORD      |
|       192 | KILL 149     | X,GAP              | X,GAP,INSERT_INTENTION | statement/sql/begin        | begin                                                     | insert into t values(8,8,now()) | test           | NULL        | 2021-08-30 13:49:58.997404      |                      47 | `test`.`t`   | id2          | RECORD      |
|       192 | KILL 149     | X,GAP              | X,GAP,INSERT_INTENTION | statement/sql/update       | update t set t = now() where id2 = 9                      | insert into t values(8,8,now()) | test           | NULL        | 2021-08-30 13:49:58.997906      |                      47 | `test`.`t`   | id2          | RECORD      |
|       192 | KILL 149     | X,GAP              | X,GAP,INSERT_INTENTION | statement/sql/select       | select * from performance_schema.data_locks               | insert into t values(8,8,now()) | test           | NULL        | 2021-08-30 13:49:59.001964      |                      47 | `test`.`t`   | id2          | RECORD      |
+-----------+--------------+--------------------+------------------------+----------------------------+-----------------------------------------------------------+---------------------------------+----------------+-------------+---------------------------------+-------------------------+--------------+--------------+-------------+
10 rows in set (0.04 sec)

IX：代表意向排他锁表锁。
X：代表Next-Key Lock锁定记录本身和记录之前的间隙(X)。
S：代表Next-Key Lock锁定记录本身和记录之前的间隙(S)。
X,REC_NOT_GAP：代表只锁定记录本身(X)。
S,REC_NOT_GAP：代表只锁定记录本身(S)。
X,GAP：代表间隙锁，不锁定记录本身(X)。
X,GAP：代表间隙锁，不锁定记录本身(S)。
X,GAP,INSERT_INTENTION：代表插入意向锁。

where列为主键或者唯一索引

sess 1:
drop table t;
create table t(id int, id2 int,t timestamp(6) , primary key(id));
insert into t values(1,1,now());
insert into t values(2,5,now());
insert into t values(5,12,now());
insert into t values(10,13,now());
begin;
update t set t = now() where id = 9;
select * from performance_schema.data_locks;
+--------+----------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+-----------+
| ENGINE | ENGINE_LOCK_ID                         | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+----------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+-----------+
| INNODB | 140023602396568:1092:140023517226320   |                174607 |       192 |       76 | test          | t           | NULL           | NULL              | NULL       |       140023517226320 | TABLE     | IX        | GRANTED     | NULL      |
| INNODB | 140023602396568:31:4:5:140023517223216 |                174607 |       192 |       76 | test          | t           | NULL           | NULL              | PRIMARY    |       140023517223216 | RECORD    | X,GAP     | GRANTED     | 10        |
+--------+----------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+-----------+
2 rows in set (0.00 sec)

会话2

sess2:
begin;
insert into t values(8,8,now());
会被阻塞
mysql> insert into t values(11,11,now());
Query OK, 1 row affected (0.00 sec)
11不会被阻塞

阻塞时候，会话3信息

-- 默认开启performance_schema的情况，5.7和8.0都能用。
select /*default performance_schema level 5.7 and 8.0 both can*/ *
from (
         select distinct c.THREAD_ID,
                         x.sql_kill_blocking_connection                                             as kill_command,
                         x.blocking_lock_mode,
                         x.waiting_lock_mode,
                         c.event_name,
                         c.sql_text                                                                 as blocking_sql_text,
                         x.waiting_query                                                            as blocked_sql_text,
                         c.CURRENT_SCHEMA,
                         c.OBJECT_NAME,
                         DATE_SUB(NOW(), INTERVAL (SELECT VARIABLE_VALUE
                                                   FROM performance_schema.global_status
                                                   WHERE VARIABLE_NAME = 'UPTIME') -
                                                  c.TIMER_START / 1000 / 1000 / 1000 / 1000 second) AS 'blocking_session_sql_start_time',
                         x.wait_age_secs                                                            as blocked_waiting_seconds,
                         x.locked_table,
                         x.locked_index,
                         x.locked_type
         from performance_schema.events_statements_history c
                  inner join (
             select t.THREAD_ID,
                    ilw.sql_kill_blocking_connection,
                    ilw.waiting_lock_mode,
                    ilw.blocking_lock_mode,
                    ilw.wait_age_secs,
                    ilw.locked_table,
                    ilw.waiting_query,
                    ilw.locked_index,
                    ilw.locked_type
             from sys.innodb_lock_waits ilw
                      inner join performance_schema.threads t on t.PROCESSLIST_ID = ilw.blocking_pid) x
                             on x.THREAD_ID = c.THREAD_ID) xx
order by xx.blocking_session_sql_start_time;

+-----------+--------------+--------------------+------------------------+----------------------------+------------------------------------------------------------------+---------------------------------+----------------+-------------+---------------------------------+-------------------------+--------------+--------------+-------------+
| THREAD_ID | kill_command | blocking_lock_mode | waiting_lock_mode      | event_name                 | blocking_sql_text                                                | blocked_sql_text                | CURRENT_SCHEMA | OBJECT_NAME | blocking_session_sql_start_time | blocked_waiting_seconds | locked_table | locked_index | locked_type |
+-----------+--------------+--------------------+------------------------+----------------------------+------------------------------------------------------------------+---------------------------------+----------------+-------------+---------------------------------+-------------------------+--------------+--------------+-------------+
|       192 | KILL 149     | X,GAP              | X,GAP,INSERT_INTENTION | statement/sql/drop_table   | drop table t                                                     | insert into t values(8,8,now()) | test           | NULL        | 2021-08-30 14:00:20.578418      |                       5 | `test`.`t`   | PRIMARY      | RECORD      |
|       192 | KILL 149     | X,GAP              | X,GAP,INSERT_INTENTION | statement/sql/create_table | create table t(id int, id2 int,t timestamp(6) , primary key(id)) | insert into t values(8,8,now()) | test           | NULL        | 2021-08-30 14:00:20.704013      |                       5 | `test`.`t`   | PRIMARY      | RECORD      |
|       192 | KILL 149     | X,GAP              | X,GAP,INSERT_INTENTION | statement/sql/insert       | insert into t values(1,1,now())                                  | insert into t values(8,8,now()) | test           | NULL        | 2021-08-30 14:00:20.891387      |                       5 | `test`.`t`   | PRIMARY      | RECORD      |
|       192 | KILL 149     | X,GAP              | X,GAP,INSERT_INTENTION | statement/sql/insert       | insert into t values(2,5,now())                                  | insert into t values(8,8,now()) | test           | NULL        | 2021-08-30 14:00:20.922129      |                       5 | `test`.`t`   | PRIMARY      | RECORD      |
|       192 | KILL 149     | X,GAP              | X,GAP,INSERT_INTENTION | statement/sql/insert       | insert into t values(5,12,now())                                 | insert into t values(8,8,now()) | test           | NULL        | 2021-08-30 14:00:20.933297      |                       5 | `test`.`t`   | PRIMARY      | RECORD      |
|       192 | KILL 149     | X,GAP              | X,GAP,INSERT_INTENTION | statement/sql/insert       | insert into t values(10,13,now())                                | insert into t values(8,8,now()) | test           | NULL        | 2021-08-30 14:00:20.944569      |                       5 | `test`.`t`   | PRIMARY      | RECORD      |
|       192 | KILL 149     | X,GAP              | X,GAP,INSERT_INTENTION | statement/sql/begin        | begin                                                            | insert into t values(8,8,now()) | test           | NULL        | 2021-08-30 14:00:20.980070      |                       5 | `test`.`t`   | PRIMARY      | RECORD      |
|       192 | KILL 149     | X,GAP              | X,GAP,INSERT_INTENTION | statement/sql/update       | update t set t = now() where id = 9                              | insert into t values(8,8,now()) | test           | NULL        | 2021-08-30 14:00:20.980521      |                       5 | `test`.`t`   | PRIMARY      | RECORD      |
|       192 | KILL 149     | X,GAP              | X,GAP,INSERT_INTENTION | statement/sql/select       | select * from performance_schema.data_locks                      | insert into t values(8,8,now()) | test           | NULL        | 2021-08-30 14:00:21.346885      |                       5 | `test`.`t`   | PRIMARY      | RECORD      |
|       192 | KILL 149     | X,GAP              | X,GAP,INSERT_INTENTION | statement/sql/select       | select * from t                                                  | insert into t values(8,8,now()) | test           | NULL        | 2021-08-30 14:02:26.835415      |                       5 | `test`.`t`   | PRIMARY      | RECORD      |
+-----------+--------------+--------------------+------------------------+----------------------------+------------------------------------------------------------------+---------------------------------+----------------+-------------+---------------------------------+-------------------------+--------------+--------------+-------------+
10 rows in set (0.03 sec)