作者：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 /* default performance_schema level*/
concat('kill ',l.id,';') as kill_command,
e.THREAD_ID,
e.event_name,
e.CURRENT_SCHEMA,
e.SQL_TEXT,
round(e.TIMER_WAIT / 1000 / 1000 / 1000 /1000,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/1000/1000/1000/1000 second) AS 'start_time' 
from performance_schema.events_statements_history e inner join information_schema.PROCESSLIST l 
on e.THREAD_ID = sys.ps_thread_id(l.id) 
and 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）

use performance_schema;
select /* 默认 performance_schema级别*/
p.THREAD_ID,
concat('kill ',l.id,';') as kill_command,
p.event_name,
p.TIMER_START,
round(p.TIMER_WAIT / 1000 / 1000 / 1000 /1000,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/1000/1000/1000/1000 second) AS 'start_time' 
from performance_schema.events_statements_history p
         inner join information_schema.PROCESSLIST l
         on p.THREAD_ID = sys.ps_thread_id(l.id) 
         and 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 扩展）

-- 默认开启performance_schema的情况，5.7和8.0都能用。
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 / 1000 / 1000 / 1000 / 1000 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
                  inner 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
                      inner 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,
TIMEDIFF(NOW(), r.trx_started) as waiting_trx_age,
r.trx_rows_locked as waiting_trx_rows_locked,
r.trx_rows_modified as waiting_trx_rows_modified,
r.trx_mysql_thread_id as waiting_pid,
sys.format_statement(r.trx_query) As waiting_query,
r1.lock_id As waiting_lock_id,
r1.lock_mode as waiting_lock_mode,
b.trx_id as blocking_trx_id,
b.trx_mysql_thread_id as blocking_pid,
sys.format_statement(b.trx_query) as blocking_query,
bl.lock_id as blocking_lock_id,
bl.lock_mode As blocking_lock_mode,
b.trx_started As blocking_trx_started,
TIMEDIFF(NOW(),b.trx_started) as blocking_trx_age,
b.trx_rows_locked as blocking_trx_rows_locked, 
b.trx_rows_modified as blocking_trx_rows_modified,
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 
 inner join information_schema.innodb_trx b on b.trx_id = w.blocking_trx_id
 inner join information_schema.innodb_trx r on r.trx_id = w.requesting_trx_id
 inner join information_schema.innodb_locks bl on bl.lock_id = w.blocking_lock_id
 inner join information_schema.innodb_locks rl on rl.lock_id = w.requested_lock_id
order by r.trx_wait_started;

通过上述脚本与方法，可以对MySQL不同层级的锁进行精准排查与定位。建议在生产环境中提前配置好 performance_schema 和 sys 库，以提升锁问题的可观测性和处理效率，保障数据库系统的稳定运行。