处理步骤-数据库管理平台DMP-深信服技术支持

处理步骤

更新时间：2023-09-04

MySQL 中锁分别在 Server 层（如元数据锁）以及存储引擎层实现（默认为 InnoDB 引擎，如记录锁，Gap 锁等）。Server 层的锁无法在 InnoDB 存储引擎层体现，所以会导致某些锁相关的视图无法捕捉当前发生的锁情况。

一键诊断

在[监控中心/数据库监控/一键诊断]界面，点击<发起诊断>。

点击详情将会跳转至“锁分析”功能界面。也可以直接在锁分析界面点击刷新按钮，进行锁分析。

锁分析

点击“+”号，展开锁阻塞信息，这里的“事务线程ID”即 connection_id，也就是我们在 show processlist 命令中看到的ID，可用于 KILL 命令。本次示例中，第一条语句的“锁状态”为 GRANTED 表示已获取到锁，下面一条数据为 WAITING 表示正等待获取锁，也就是说第二条语句在等待第一条语句中锁的释放。按需点击<结束会话>，即可 KILL 掉正在执行的会话。

注意：若是 KILL 了长时间执行的某一些变更语句，如 delete、update 或 DDL 等语句，会导致数据回滚，回滚期间也是会产生锁阻塞，必须要等到所有数据回滚完成，数据库实例才会恢复正常。

使用 sys 库

使用 sys 库中的 innodb_lock_waits、schema_table_lock_waits 查看当前锁阻塞状态。innodb_lock_waits 视图与 DAS 功能中一键诊断及锁分析功能类似，按实际情况使用即可。

innodb_lock_waits：在MySQL 8.0 中使用了 performance 库中的 data_locks 和 data_lock_waits 视图来返回所有等待 InnoDB 记录锁的情况。在 MySQL 5.7 中使用了 information 库中的 INNODB_LOCKS、INNODB_LOCK_WAITS 等视图。

schema_table_lock_waits：使用 metadata_locks 表来返回与方案对象相关的锁等待信息（不常用）。

使用 innodb_lock_waits

mysql> select * from sys.innodb_lock_waits\G

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

                wait_started: 2023-01-17 15:40: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: GEN_CLUST_INDEX

                 locked_type: RECORD

              waiting_trx_id: 17981

         waiting_trx_started: 2023-01-17 15:27:08

             waiting_trx_age: 00:13:28

     waiting_trx_rows_locked: 3

   waiting_trx_rows_modified: 0

                 waiting_pid: 421656

               waiting_query: select * from t for update

             waiting_lock_id: 140295813921144:10:4:2:140295710802000

           waiting_lock_mode: X

             blocking_trx_id: 17980

                blocking_pid: 414031

              blocking_query: NULL

            blocking_lock_id: 140295813919528:10:4:2:140295710788992

          blocking_lock_mode: X

        blocking_trx_started: 2023-01-17 15:27:03

            blocking_trx_age: 00:13:33

    blocking_trx_rows_locked: 2

  blocking_trx_rows_modified: 0

     sql_kill_blocking_query: KILL QUERY 414031

sql_kill_blocking_connection: KILL 414031

1 row in set (0.01 sec)

使用 schema_table_lock_waits

mysql> select * from sys.schema_table_lock_waits\G

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

               object_schema: test

                 object_name: t

           waiting_thread_id: 421720

                 waiting_pid: 421656

             waiting_account: sangforroot@localhost

           waiting_lock_type: SHARED_NO_READ_WRITE

       waiting_lock_duration: TRANSACTION

               waiting_query: OPTIMIZE TABLE t

          waiting_query_secs: 27

 waiting_query_rows_affected: 0

 waiting_query_rows_examined: 0

          blocking_thread_id: 414095

                blocking_pid: 414031

            blocking_account: sangforroot@localhost

          blocking_lock_type: SHARED_READ

      blocking_lock_duration: TRANSACTION

     sql_kill_blocking_query: KILL QUERY 414031

sql_kill_blocking_connection: KILL 414031

1 row in set (0.00 sec)

“sql_kill_blocking_query: KILL QUERY 414031”表示 KILL 查询、“sql_kill_blocking_connection: KILL 414031”表示 KILL session。

详情见官方文档：

https://dev.mysql.com/doc/refman/5.7/en/sys-innodb-lock-waits.html

https://dev.mysql.com/doc/refman/5.7/en/sys-schema-table-lock-waits.html

使用 performance 库

可以通过如下四个表获得这些锁信息：

data_locks：该表包含 InnoDB 级别的表和锁记录的详细信息。它显示了当前持有的或者挂起的所有锁。该表从 MySQL 8.0 及以上版本才开始引入。
data_lock_waits：与 data_locks 表类似，它显示了与 InnoDB 相关的锁，但只显示处于等待状态的锁，以及有关哪些线程正在阻塞请求的信息。该表从 MySQL 8.0 及以上版本才开始引入。

metadata_locks：该表包含有关用户级别锁、元数据锁等信息。要记录这些信息，那么需要启用 wait/lock/metadata/sql/mdl 等 instrument（MySQL 8.0 中默认启用）。
table_handles：该表保存有关当前有效表锁的信息。要记录这些信息，那么需要启用 wait/lock/table/sql/handler 等 instrument （MySQL 8.0 中默认启用）。该表的使用频率比其他表要低。

这其中 data_locks 与 data_lock_waits 查询效果与 sys 库中的 innodb_lock_waits 相似。经常用到的表为 metadata_locks 以及 table_handles。

若 sys 库中的 innodb_lock_waits 未查询到结果，可能存在表级锁，此时可以使用 table_handles 查询

首先要开启 “wait/lock/table/sql/handler”instrument

UPDATE performance_schema.setup_instruments

SET ENABLED = 'YES', TIMED = 'YES'

WHERE NAME = 'wait/lock/table/sql/handler';

查询表锁

mysql> select * from performance_schema.table_handles\G

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

          OBJECT_TYPE: TABLE

        OBJECT_SCHEMA: test

          OBJECT_NAME: t

OBJECT_INSTANCE_BEGIN: 140291282625392

      OWNER_THREAD_ID: 414095

       OWNER_EVENT_ID: 37

        INTERNAL_LOCK: NULL

        EXTERNAL_LOCK: WRITE EXTERNAL

1 rows in set (0.00 sec)

“OWNER_THREAD_ID: 414095”为 MySQL 内部线程 ID，若想获取对应的 PID 需要在 threads 表中做转换

select * from performance_schema.threads where thread_id = 414095

PROCESSLIST_ID 对应 PID （show processlist 中的 ID），可用于 KILL 命令。

官方文档参考：

https://dev.mysql.com/doc/refman/5.7/en/performance-schema-table-handles-table.html

截止到目前，如上方法都无法查出任何相关锁信息，但是我们的 SQL 语句执行还存在阻塞状况，那可能就是元数据锁引起的，需要查看 metadata_locks。

首先，需要开启 MDL 相关的 instrument

UPDATE performance_schema.setup_instruments

SET ENABLED = 'YES', TIMED = 'YES'

WHERE NAME = 'wait/lock/metadata/sql/mdl';

关闭 MDL 相关的 instrument

UPDATE performance_schema.setup_instruments

SET ENABLED = 'NO', TIMED = 'NO'

WHERE NAME = 'wait/lock/metadata/sql/mdl';

查看开启情况

mysql> select * from performance_schema.setup_instruments where name 

like 'wait/lock/metadata/sql/mdl'\G

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

         NAME: wait/lock/metadata/sql/mdl

      ENABLED: YES

        TIMED: YES

   PROPERTIES:

   VOLATILITY: 0

DOCUMENTATION: NULL

1 row in set (0.00 sec)

查看元数据锁相关信息

mysql> select * from performance_schema.metadata_locks where

owner_thread_id  <> sys.ps_thread_id(connection_id())\G

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

          OBJECT_TYPE: GLOBAL

        OBJECT_SCHEMA: NULL

          OBJECT_NAME: NULL

          COLUMN_NAME: NULL

OBJECT_INSTANCE_BEGIN: 140291283590240

            LOCK_TYPE: INTENTION_EXCLUSIVE

        LOCK_DURATION: STATEMENT

          LOCK_STATUS: GRANTED

               SOURCE: sql_base.cc:5458

      OWNER_THREAD_ID: 414095

       OWNER_EVENT_ID: 37

*************************** 2. row ***************************

          OBJECT_TYPE: SCHEMA

        OBJECT_SCHEMA: test

          OBJECT_NAME: NULL

          COLUMN_NAME: NULL

OBJECT_INSTANCE_BEGIN: 140291283567856

            LOCK_TYPE: INTENTION_EXCLUSIVE

        LOCK_DURATION: TRANSACTION

          LOCK_STATUS: GRANTED

               SOURCE: sql_base.cc:5445

      OWNER_THREAD_ID: 414095

       OWNER_EVENT_ID: 37

*************************** 3. row ***************************

          OBJECT_TYPE: TABLE

        OBJECT_SCHEMA: test

          OBJECT_NAME: t

          COLUMN_NAME: NULL

OBJECT_INSTANCE_BEGIN: 140291281596928

            LOCK_TYPE: SHARED_NO_READ_WRITE

        LOCK_DURATION: TRANSACTION

          LOCK_STATUS: GRANTED

               SOURCE: sql_parse.cc:5947

      OWNER_THREAD_ID: 414095

       OWNER_EVENT_ID: 37

*************************** 4. row ***************************

          OBJECT_TYPE: TABLESPACE

        OBJECT_SCHEMA: NULL

          OBJECT_NAME: test/t

          COLUMN_NAME: NULL

OBJECT_INSTANCE_BEGIN: 140291282002912

            LOCK_TYPE: INTENTION_EXCLUSIVE

        LOCK_DURATION: TRANSACTION

          LOCK_STATUS: GRANTED

               SOURCE: lock.cc:806

      OWNER_THREAD_ID: 414095

       OWNER_EVENT_ID: 37

*************************** 5. row ***************************

          OBJECT_TYPE: GLOBAL

        OBJECT_SCHEMA: NULL

          OBJECT_NAME: NULL

          COLUMN_NAME: NULL

OBJECT_INSTANCE_BEGIN: 140291418983872

            LOCK_TYPE: INTENTION_EXCLUSIVE

        LOCK_DURATION: STATEMENT

          LOCK_STATUS: GRANTED

               SOURCE: sql_base.cc:3040

      OWNER_THREAD_ID: 421720

       OWNER_EVENT_ID: 29

*************************** 6. row ***************************

          OBJECT_TYPE: TABLE

        OBJECT_SCHEMA: test

          OBJECT_NAME: t

          COLUMN_NAME: NULL

OBJECT_INSTANCE_BEGIN: 140291419803520

            LOCK_TYPE: SHARED_WRITE

        LOCK_DURATION: TRANSACTION

          LOCK_STATUS: PENDING

               SOURCE: sql_parse.cc:5947

      OWNER_THREAD_ID: 421720

       OWNER_EVENT_ID: 29

6 rows in set (0.01 sec)

本例中 OWNER_THREAD_ID 为MySQL 内部线程 ID ，若想知道 PID （show processlist 中的 ID），需要查询 threads 表

mysql> select * from performance_schema.threads where thread_id in

(414095,421720) \G # 传入OWNER_THREAD_ID

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

          THREAD_ID: 414095

               NAME: thread/sql/one_connection

               TYPE: FOREGROUND

     PROCESSLIST_ID: 414031

   PROCESSLIST_USER: sangforroot

   PROCESSLIST_HOST: localhost

     PROCESSLIST_DB: test

PROCESSLIST_COMMAND: Sleep

   PROCESSLIST_TIME: 526

  PROCESSLIST_STATE: NULL

   PROCESSLIST_INFO: NULL

   PARENT_THREAD_ID: NULL

               ROLE: NULL

       INSTRUMENTED: YES

            HISTORY: YES

    CONNECTION_TYPE: Socket

       THREAD_OS_ID: 37365

     RESOURCE_GROUP: USR_default

*************************** 2. row ***************************

          THREAD_ID: 421720

               NAME: thread/sql/one_connection

               TYPE: FOREGROUND

     PROCESSLIST_ID: 421656

   PROCESSLIST_USER: sangforroot

   PROCESSLIST_HOST: localhost

     PROCESSLIST_DB: test

PROCESSLIST_COMMAND: Query

   PROCESSLIST_TIME: 1460

  PROCESSLIST_STATE: Waiting for table metadata lock

   PROCESSLIST_INFO: select * from t for update

   PARENT_THREAD_ID: NULL

               ROLE: NULL

       INSTRUMENTED: YES

            HISTORY: YES

    CONNECTION_TYPE: Socket

       THREAD_OS_ID: 399924

     RESOURCE_GROUP: USR_default

2 rows in set (0.01 sec)

第二行记录可以看看到 PROCESSLIST_STATE: Waiting for table metadata lock ，表示等待获取元数据锁，初步判断是由 OWNER_THREAD_ID: 414095 阻塞。若想看具体执行的内容可查看events_statements_current、events_statements_history、events_statements_history_long（默认未开启）。

深信服自助服务平台

数据库管理平台DMP

处理步骤