InnoDB LOCK檢視變化

MySQL 8檢視已經與MySQL 5.7有了比較大的變化：

比如MySQL 5.7中的information_schema.innodb_lock_waits, 在8.0中已經被轉移到了sys這個schema中。

以下是示例：

mysql> select * from x$innodb_lock_waits\G

*************************** 1. row ***************************

                wait_started: 2021-12-01 09:33:26

                    wait_age: 00:00:14

               wait_age_secs: 14

                locked_table: `testdb`.`t1`

         locked_table_schema: testdb

           locked_table_name: t1

      locked_table_partition: NULL

   locked_table_subpartition: NULL

                locked_index: PRIMARY

                 locked_type: RECORD

              waiting_trx_id: 8522

         waiting_trx_started: 2021-12-01 09:29:48

             waiting_trx_age: 00:03:52

     waiting_trx_rows_locked: 3

   waiting_trx_rows_modified: 0

                 waiting_pid: 15

               waiting_query: update t1 set name = 'DDD' where id = 1

             waiting_lock_id: 140094083009696:6:4:5:140093981562912

           waiting_lock_mode: X,REC_NOT_GAP

             blocking_trx_id: 8519

                blocking_pid: 14

              blocking_query: NULL

            blocking_lock_id: 140094083008888:6:4:5:140093981556048

          blocking_lock_mode: X,REC_NOT_GAP

        blocking_trx_started: 2021-12-01 09:29:15

            blocking_trx_age: 00:04:25

    blocking_trx_rows_locked: 1

  blocking_trx_rows_modified: 1

     sql_kill_blocking_query: KILL QUERY 14

sql_kill_blocking_connection: KILL 14

1 row in set (0.00 sec)

同時

mysql> select database();

+------------+

| database() |

+------------+

| sys        |

+------------+

1 row in set (0.00 sec)

mysql> show tables like '%innodb%';

+---------------------------------+

| Tables_in_sys (%innodb%)        |

+---------------------------------+

| innodb_buffer_stats_by_schema   |

| innodb_buffer_stats_by_table    |

| innodb_lock_waits               |

| x$innodb_buffer_stats_by_schema |

| x$innodb_buffer_stats_by_table  |

| x$innodb_lock_waits             |

+---------------------------------+

6 rows in set (0.00 sec)