行莫
行莫
发布于 2025-07-04 / 18 阅读
1
0

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_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、热点表、锁等待等问题。

常用视图与用途:

视图名称

用途

processlist

类似 SHOW PROCESSLIST,但更详细

statements_with_runtimes_in_95th_percentile

显示执行时间处于 95 分位的 SQL

io_global_by_file_by_bytes

按文件显示 I/O 使用情况

schema_index_statistics

显示索引使用情况

user_summary_by_statement_type

按用户分类的 SQL 类型统计


评论