作者：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   面试宝典知识星球，解锁更多面试技巧！