作者：Digital Observer（施嘉伟） Oracle ACE Pro: Database PostgreSQL ACE Partner 11年数据库行业经验，现主要从事数据库服务工作 拥有Oracle OCM、DB2 10.1 Fundamentals、MySQL 8.0 OCP、WebLogic 12c OCA、KCP、PCTP、PCSD、 PGCM、OCI、PolarDB技术专家、达梦师资认证、数据安全咨询高级等认证 ITPUB认证专家、PolarDB开源社区技术顾问、HaloDB技术顾问、TiDB社区技术布道师、青学会MOP技术社区专家顾问、国内某高校企业实践指导教师 公众号：Digital Observer；CSDN：施嘉伟；ITPUB：sjw1933；墨天轮：Digital Observer；PGFans：施嘉伟。
MySQL锁定位实践指南
在数据库日常运维中，锁问题常常成为性能瓶颈和系统卡顿的根源。本文系统总结了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_timeFROM performance_schema.events_statements_history eJOIN 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_timeFROM performance_schema.events_statements_history pJOIN 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
) xxORDER 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_connectionFROM information_schema.innodb_lock_waits wJOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_idJOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_idJOIN information_schema.innodb_locks bl ON bl.lock_id = w.blocking_lock_idJOIN information_schema.innodb_locks rl ON rl.lock_id = w.requested_lock_idORDER BY r.trx_wait_started;
通过上述脚本与方法，可以对MySQL不同层级的锁进行精准排查与定位。建议在生产环境中提前配置好
performance_schema 和
sys 库，以提升锁问题的可观测性和处理效率，保障数据库系统的稳定运行。
面试技巧
面试时经常会被问到运维过什么版本的数据库，如果运维过
Oracle 8i、
9i这些老古董，通常会给面试官一个经验丰富、老前辈的印象，一个
8i OCP的认证绝对比
19C OCP更有说服力，
…… 说了这么多废话，其实是想打一个广告，欢迎加入
DBA
面试宝典知识星球，解锁更多面试技巧！
