更新时间: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(默认未开启)。