在数据库日常运维中,锁问题常常成为性能瓶颈和系统卡顿的根源。本文系统总结了MySQL中常见的锁类型及其排查方法,涵盖全局读锁、表锁、元数据锁(MDL)及行锁,并提供标准化的诊断脚本,适用于MySQL 5.6、5.7与8.0多个版本。
全局读锁(Global Read Lock)
全局读锁通常由 FLUSH TABLES WITH READ LOCK
添加,常用于逻辑备份或主从切换。另一种风险情形则是权限设置不合理,具备 RELOAD
权限的账号可能会误操作导致加锁。
排查方法
MySQL 5.7 起支持通过 performance_schema.metadata_locks
表查看 Server 层级锁信息,包括全局读锁。
查询示例(未开启 performance_schema):
SELECT
CONCAT('KILL ', l.id, ';') AS kill_command,
e.THREAD_ID,
e.event_name,
e.CURRENT_SCHEMA,
e.SQL_TEXT,
ROUND(e.TIMER_WAIT / 1000000000000, 2) AS TIMER_WAIT_s,
l.host,
l.db,
l.state,
DATE_SUB(NOW(), INTERVAL (
SELECT VARIABLE_VALUE
FROM performance_schema.global_status
WHERE VARIABLE_NAME='UPTIME') - e.TIMER_START/1000000000000
SECOND) AS start_time
FROM performance_schema.events_statements_history e
JOIN information_schema.PROCESSLIST l
ON e.THREAD_ID = sys.ps_thread_id(l.id)
WHERE e.event_name = 'statement/sql/flush'
ORDER BY e.TIMER_START;
查询示例(开启 performance_schema):
SELECT *
FROM performance_schema.metadata_locks
WHERE owner_thread_id != sys.ps_thread_id(connection_id());
表锁(Table Lock)
表锁通常由显式语句如 LOCK TABLE t READ
加入,用于控制表级别的并发访问。
排查方法
SELECT *
FROM performance_schema.metadata_locks
WHERE owner_thread_id != sys.ps_thread_id(connection_id());
MDL锁(Metadata Lock)
MDL(元数据锁)在访问表对象时自动加锁,用于保证读写操作的元数据一致性。若遇到 DDL 阻塞等情况,往往与此类锁有关。
排查方法
未开启 sys 扩展(适用于 MySQL 5.7/8.0)
SELECT
p.THREAD_ID,
CONCAT('KILL ', l.id, ';') AS kill_command,
p.event_name,
ROUND(p.TIMER_WAIT / 1000000000000, 2) AS TIMER_WAIT_s,
p.CURRENT_SCHEMA,
p.SQL_TEXT,
l.host,
l.DB,
l.STATE,
l.INFO AS mdl_blocking_info,
DATE_SUB(NOW(), INTERVAL (
SELECT VARIABLE_VALUE
FROM performance_schema.global_status
WHERE VARIABLE_NAME='UPTIME') - p.TIMER_START/1000000000000
SECOND) AS start_time
FROM performance_schema.events_statements_history p
JOIN information_schema.PROCESSLIST l
ON p.THREAD_ID = sys.ps_thread_id(l.id)
WHERE l.state = 'Waiting for table metadata lock'
ORDER BY p.TIMER_START;
开启 sys 扩展时
SELECT * FROM sys.schema_table_lock_waits;
行锁(Row Lock)
行锁是InnoDB的核心特性之一,支持高并发访问。常见的行锁类型包括意向锁、Next-Key锁、间隙锁等,表现为以下几类:
IX
:意向排他锁(表级)X
:Next-Key锁(锁定记录本身及前间隙,排他)S
:Next-Key共享锁X,REC_NOT_GAP
:锁定记录本身(排他)S,REC_NOT_GAP
:锁定记录本身(共享)X,GAP
/S,GAP
:纯间隙锁X,GAP,INSERT_INTENTION
:插入意向锁
排查方法
MySQL 5.7 / 8.0(未启用 sys 扩展)
SELECT *
FROM (
SELECT DISTINCT
c.THREAD_ID,
x.sql_kill_blocking_connection AS kill_command,
x.blocking_lock_mode,
x.waiting_lock_mode,
c.event_name,
c.sql_text AS blocking_sql_text,
x.waiting_query AS blocked_sql_text,
c.CURRENT_SCHEMA,
c.OBJECT_NAME,
DATE_SUB(NOW(), INTERVAL (
SELECT VARIABLE_VALUE
FROM performance_schema.global_status
WHERE VARIABLE_NAME = 'UPTIME') - c.TIMER_START / 1000000000000
SECOND) AS blocking_session_sql_start_time,
x.wait_age_secs AS blocked_waiting_seconds,
x.locked_table,
x.locked_index,
x.locked_type
FROM performance_schema.events_statements_history c
JOIN (
SELECT
t.THREAD_ID,
ilw.sql_kill_blocking_connection,
ilw.waiting_lock_mode,
ilw.blocking_lock_mode,
ilw.wait_age_secs,
ilw.locked_table,
ilw.waiting_query,
ilw.locked_index,
ilw.locked_type
FROM sys.innodb_lock_waits ilw
JOIN performance_schema.threads t
ON t.PROCESSLIST_ID = ilw.blocking_pid
) x ON x.THREAD_ID = c.THREAD_ID
) xx
ORDER BY xx.blocking_session_sql_start_time;
MySQL 5.6(未启用 performance_schema)
SELECT
r.trx_wait_started AS wait_started,
TIMEDIFF(NOW(), r.trx_wait_started) AS wait_age,
TIMESTAMPDIFF(SECOND, r.trx_wait_started, NOW()) AS wait_age_secs,
rl.lock_table AS locked_table,
r1.lock_index AS locked_index,
r1.lock_type AS locked_type,
r.trx_id AS waiting_trx_id,
r.trx_started AS waiting_trx_started,
r.trx_mysql_thread_id AS waiting_pid,
sys.format_statement(r.trx_query) AS waiting_query,
b.trx_id AS blocking_trx_id,
b.trx_mysql_thread_id AS blocking_pid,
sys.format_statement(b.trx_query) AS blocking_query,
b.trx_started AS blocking_trx_started,
CONCAT('KILL QUERY ', b.trx_mysql_thread_id) AS sql_kill_blocking_query,
CONCAT('KILL ', b.trx_mysql_thread_id) AS sql_kill_blocking_connection
FROM information_schema.innodb_lock_waits w
JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id
JOIN information_schema.innodb_locks bl ON bl.lock_id = w.blocking_lock_id
JOIN information_schema.innodb_locks rl ON rl.lock_id = w.requested_lock_id
ORDER BY r.trx_wait_started;
performance_schema 和 sys
MySQL 的 performance_schema 和 sys 库是用于性能监控和问题诊断的重要工具。它们提供了对数据库内部运行状态的细粒度观察,帮助 DBA 或开发人员识别性能瓶颈、慢查询、锁等待等问题。
performance_schema 是 MySQL 内置的性能监控引擎,从 MySQL 5.5 开始引入。它通过收集服务器运行时的各种事件(如语句执行、锁等待、文件 IO 等)来提供详细的性能数据。
核心功能:
监控线程、连接、SQL 执行、锁等待等。
提供低开销的事件采集机制。
支持动态调整采集级别(instrumentation)。
sys 是一个基于 performance_schema 构建的系统数据库,包含一组视图、函数和存储过程,将复杂的性能数据以更易读的方式展示出来。
主要功能:
将 performance_schema 的原始数据转化为易于理解的视图;
提供常用的性能分析模板;
支持快速定位慢 SQL、热点表、锁等待等问题。
常用视图与用途: