数据库管理平台DMP

深信服数据库管理平台(DMP)提供MySQL、Oracle的RDS功能,支持全生命周期管理、自动MySQL主从切换、故障自愈、事务一致性备份、监控告警等,能够实现业务快速上线、资源快速分发、数据安全保护,提供SLA保障,使数据库运维复杂度大幅下降,帮助客户减少被动性救火工作,专注业务创新。
点击可切换产品版本
知道了
不再提醒
DMP 2.3.0
{{sendMatomoQuery("数据库管理平台DMP","处理步骤")}}

处理步骤

更新时间:2023-09-04

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

一键诊断

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

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

锁分析

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

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

使用 sys

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

  1. innodb_lock_waits:在MySQL 8.0 中使用了 performance 库中的 data_locks data_lock_waits 视图来返回所有等待 InnoDB 记录锁的情况。在 MySQL 5.7 中使用了 information 库中的 INNODB_LOCKSINNODB_LOCK_WAITS 等视图。
  1. 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

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

  1. data_locks该表包含 InnoDB 级别的表和锁记录的详细信息。它显示了当前持有的或者挂起的所有锁。该表从 MySQL 8.0 及以上版本才开始引入。
  2. data_lock_waitsdata_locks 表类似,它显示了与 InnoDB 相关的锁,但只显示处于等待状态的锁,以及有关哪些线程正在阻塞请求的信息。该表从 MySQL 8.0 及以上版本才开始引入。
  1. metadata_locks该表包含有关用户级别锁、元数据锁等信息。要记录这些信息,那么需要启用 wait/lock/metadata/sql/mdl instrumentMySQL 8.0 中默认启用)。
  2. 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/handlerinstrument

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_currentevents_statements_historyevents_statements_history_long(默认未开启)。