MySQL中一些锁的PID查询

MDL锁

select blocking_pid from sys.schema_table_lock_waits;

查询MDL锁的阻塞pid，结果类似如下：

+--------------+
| blocking_pid |
+--------------+
|            8 |
+--------------+
1 row in set (0.03 sec)

行锁

SELECT t.* FROM sys.innodb_lock_waits t WHERE locked_table = '`TEST`.`t`'\G;

可以查看占用行锁的阻塞PID，结果类似如下：

*************************** 1. row ***************************
                wait_started: 2022-04-20 06:10:32
                    wait_age: 00:00:04
               wait_age_secs: 4
                locked_table: `TEST`.`t`
         locked_table_schema: TEST
           locked_table_name: t
      locked_table_partition: NULL
   locked_table_subpartition: NULL
                locked_index: PRIMARY
                 locked_type: RECORD
              waiting_trx_id: 421119626546584
         waiting_trx_started: 2022-04-20 06:10:32
             waiting_trx_age: 00:00:04
     waiting_trx_rows_locked: 1
   waiting_trx_rows_modified: 0
                 waiting_pid: 18
               waiting_query: select * from t where id = 1 lock in share mode
             waiting_lock_id: 139644649835928:395:7:2:139644553473008
           waiting_lock_mode: S,REC_NOT_GAP
             blocking_trx_id: 723727
                blocking_pid: 19
              blocking_query: NULL
            blocking_lock_id: 139644649836784:395:7:2:139644553479232
          blocking_lock_mode: X,REC_NOT_GAP
        blocking_trx_started: 2022-04-20 06:05:38
            blocking_trx_age: 00:04:58
    blocking_trx_rows_locked: 1
  blocking_trx_rows_modified: 1
     sql_kill_blocking_query: KILL QUERY 19
sql_kill_blocking_connection: KILL 19
1 row in set (0.00 sec)

参考：

• 19 | 为什么我只查一行的语句，也执行这么慢？