当前位置：首页 > 数据库 > 正文

如何实时检查MySQL锁表情况

查询数据库系统表或视图（如MySQL的 information_schema.innodb_trx，Oracle的 v$locked_object），或执行特定命令（如SQL Server的 sp_lock/ sys.dm_tran_locks），观察是否存在长时间运行的事务或阻塞的锁信息。

数据库锁表是影响系统性能和用户体验的常见问题，当关键表或记录被长时间锁定，可能导致应用响应缓慢甚至完全卡死，无论是数据库管理员（DBA）还是开发人员，掌握如何快速诊断锁表情况至关重要，以下是针对不同主流数据库的查看方法：

为什么要关注锁表？

在深入方法之前,理解锁表的危害有助于判断其严重性：

  1. 性能急剧下降： 被阻塞的查询会堆积，消耗系统资源（CPU、内存、连接数），导致整体响应变慢。
  2. 应用功能异常： 用户操作（如下单、修改信息）可能因等待锁而超时失败，直接影响业务。
  3. 潜在死锁风险： 不当的锁竞争容易引发死锁，需要数据库自动或手动干预才能解除。
  4. 资源耗尽： 大量阻塞的连接可能耗尽数据库的连接池或线程资源，导致新连接无法建立。

如何查看数据库是否锁表？（按数据库类型）

MySQL / MariaDB

  • 使用 SHOW PROCESSLIST / SHOW FULL PROCESSLIST

    • 这是最基础的方法,在MySQL客户端执行： 
      SHOW FULL PROCESSLIST;
    • 关键看 State 列和 Info 列：
      • State 显示为 Waiting for table metadata lock, Waiting for table level lock (对于MyISAM表), 或者 Waiting for ... lock (如 Waiting for row lock)，则表示该线程在等待锁。
      • 观察 Info 列，看该线程正在执行或等待执行什么SQL语句。
      • 特别留意 Command 列是 Sleep 但 Time 值却很高的连接，它们可能是被阻塞的源头（持有锁未释放）或受害者（等待锁）。

  • 查询 information_schema / performance_schema (更推荐)

    • 查看当前锁信息 (MySQL 5.6+ / MariaDB 10.0+): 
      SELECT * FROM information_schema.INNODB_LOCKS;
SELECT * FROM information_schema.INNODB_LOCK_WAITS;
      • INNODB_LOCKS： 显示当前InnoDB存储引擎产生的锁请求（包括已获取的和正在等待的）。
      • INNODB_LOCK_WAITS： 直接显示哪些事务在等待锁，以及哪个事务持有该锁，这是诊断阻塞链的关键视图。blocking_trx_id 是持有锁的事务ID，requesting_trx_id 是等待锁的事务ID。
    • 查看当前锁和等待关系 (MySQL 5.7+ / MariaDB 10.3+): 
      SELECT * FROM sys.innodb_lock_waits; -- 需要先启用sys schema
      • sys.innodb_lock_waits 视图对 INNODB_LOCK_WAITS 做了更友好的封装，通常直接显示阻塞的SQL语句和被阻塞的SQL语句，非常直观。
    • 查看元数据锁 (MySQL 5.5+ / MariaDB)： 
      SELECT * FROM performance_schema.metadata_locks
WHERE OWNER_THREAD_ID IS NOT NULL AND LOCK_STATUS = 'PENDING';
-- 或者结合 threads 表查看更详细信息
      • 元数据锁（MDL）通常与DDL操作（如 ALTER TABLE）或长时间运行的查询相关。

  • 使用 mysqladmin 命令

    mysqladmin -u[username] -p[password] processlist
    • 输出类似于 SHOW FULL PROCESSLIST，可在命令行快速查看。

Microsoft SQL Server

如何实时检查MySQL锁表情况 第1张

  • 使用系统动态管理视图 (DMVs)

    • 核心查询 (查看阻塞链): 
      SELECT
    t1.resource_type AS [锁类型],
    t1.resource_database_id AS [数据库ID],
    DB_NAME(t1.resource_database_id) AS [数据库名],
    t1.resource_associated_entity_id AS [关联实体ID],
    OBJECT_NAME(p.object_id) AS [被锁对象名],
    t1.request_mode AS [请求的锁模式],
    t1.request_session_id AS [请求会话ID (等待者)],
    t2.blocking_session_id AS [阻塞会话ID (持有者)],
    s1.login_name AS [等待者登录名],
    s1.host_name AS [等待者主机],
    s1.program_name AS [等待者程序],
    s2.login_name AS [阻塞者登录名],
    s2.host_name AS [阻塞者主机],
    s2.program_name AS [阻塞者程序],
    wt.wait_type AS [等待类型],
    wt.wait_duration_ms AS [等待时长(ms)],
    st1.text AS [等待者执行的SQL],
    st2.text AS [阻塞者执行的SQL]
FROM sys.dm_tran_locks AS t1
INNER JOIN sys.dm_os_waiting_tasks AS wt ON t1.lock_owner_address = wt.resource_address
INNER JOIN sys.dm_exec_sessions AS s1 ON t1.request_session_id = s1.session_id
INNER JOIN sys.dm_exec_connections AS c1 ON s1.session_id = c1.most_recent_session_id
OUTER APPLY sys.dm_exec_sql_text(c1.most_recent_sql_handle) AS st1
LEFT JOIN sys.dm_exec_sessions AS s2 ON wt.blocking_session_id = s2.session_id
LEFT JOIN sys.dm_exec_connections AS c2 ON s2.session_id = c2.most_recent_session_id
OUTER APPLY sys.dm_exec_sql_text(c2.most_recent_sql_handle) AS st2
LEFT JOIN sys.partitions p ON p.hobt_id = t1.resource_associated_entity_id -- 关联对象名 (表/索引)
WHERE t1.resource_database_id = DB_ID(); -- 限制在当前数据库

      这个查询是诊断SQL Server锁阻塞的利器，清晰地展示了谁在等待、谁在阻塞、等待什么锁、等了多久、双方执行的SQL是什么。

    • 查看所有当前锁： 
      SELECT * FROM sys.dm_tran_locks;

  • 使用系统存储过程 sp_who / sp_who2

    EXEC sp_who2;
    • 关键看 BlkBy 列： 如果某个会话的 BlkBy 列有值（非 ），则表示该会话被该值对应的会话ID阻塞。Status 列显示 suspended 通常也表示在等待资源（如锁）。
    • sp_who2 比 sp_who 提供更多信息（如执行的命令、主机名等）。

  • SQL Server Management Studio (SSMS) 活动监视器

    • 图形化工具,在SSMS中，右键点击服务器实例 -> “活动监视器”。
    • 查看“进程”页签：关注“阻塞者”列（显示阻塞该进程的会话ID）和“等待类型”列（如 LCK_M_XXX 表示等待某种锁）。
    • 查看“资源等待”页签：关注 LCK_M_XXX 相关的等待类型和累积等待时间。

  • SQL Server Profiler / Extended Events

    用于捕获锁获取/释放、死锁等事件的详细信息，适合深度分析和事后追踪，配置相对复杂。

    如何实时检查MySQL锁表情况 第2张

Oracle Database

  • 查询动态性能视图 v$lock 和 v$session

    • 核心查询 (查看锁和会话信息): 
      SELECT
    s.sid, s.serial#,
    s.username, s.osuser, s.machine, s.program, s.status,
    l.type AS lock_type,
    DECODE(l.type,
        'TM', 'DML/表锁',
        'TX', '事务/行锁',
        'UL', '用户定义',
        l.type) AS lock_type_desc,
    l.id1, l.id2,
    l.lmode, l.request, l.block,
    o.object_name AS locked_object
FROM v$lock l
JOIN v$session s ON l.sid = s.sid
LEFT JOIN dba_objects o ON l.id1 = o.object_id AND l.type = 'TM' -- 关联表锁对象名
WHERE l.type IN ('TM', 'TX') -- 关注最常见的DML/事务锁
ORDER BY l.sid, l.type;
    • 关键列解释：
      • l.block： 最重要！ 1 表示该会话持有的锁阻塞了其他会话；0 表示不阻塞。
      • l.lmode： 锁模式（持有模式），如 3 (Row-X/SX), 6 (Exclusive/X)。
      • l.request： 锁请求模式（等待模式），如 6 表示该会话正在请求排他锁(X)。
      • l.type： TM 是表级锁，TX 是事务级锁（通常指行锁）。
      • s.status： ACTIVE 通常表示正在执行，INACTIVE 可能表示空闲但有未提交事务（可能持有锁！）。
      • locked_object： 显示被锁定的表名（对于 TM 锁）。
    • 查找阻塞链： 如果一个会话的 block=1，查找 v$session 中 final_blocking_session 指向它的会话（Oracle 12c+），或者通过 v$lock 中的 id1, id2, type 匹配请求(request>0)和持有(lmode>0)相同资源的会话。

  • 查询 v$locked_object

    SELECT * FROM v$locked_object;
    • 更简洁地列出当前被锁定的对象（表）和持有锁的事务信息（XIDUSN, XIDSLOT, XIDSQN），结合 v$transaction 可以找到相关会话。

  • 使用 Enterprise Manager (OEM)

    Oracle提供的图形化管理工具,在“性能”或“阻塞会话”相关页面可以直观地查看锁和阻塞情况。

PostgreSQL

如何实时检查MySQL锁表情况 第3张

  • 查询 pg_locks 系统目录

    • 核心查询： 
      SELECT
    pl.pid AS process_id,
    pl.mode AS lock_mode,
    pl.granted AS is_granted,
    pl.locktype AS lock_type,
    CASE pl.locktype
        WHEN 'relation' THEN pg_class.relname
        WHEN 'transactionid' THEN pl.transactionid::text
        WHEN 'virtualxid' THEN pl.virtualxid
        -- ... 其他locktype可以类似处理
        ELSE NULL::text
    END AS locked_object,
    a.usename AS username,
    a.application_name,
    a.client_addr,
    a.query AS waiting_query,
    a.state AS session_state,
    a.query_start
FROM pg_locks pl
LEFT JOIN pg_database pd ON pl.database = pd.oid
LEFT JOIN pg_class ON pl.relation = pg_class.oid
LEFT JOIN pg_stat_activity a ON pl.pid = a.pid
WHERE pd.datname = current_database() -- 当前数据库
ORDER BY pl.pid;
    • 关键列解释：
      • granted： true 表示该进程已获得锁；false 表示该进程正在等待这个锁。
      • mode： 锁模式（如 AccessShareLock, RowExclusiveLock, ExclusiveLock），强度依次增加。
      • locktype： 锁类型（如 relation 表锁, tuple 行锁, transactionid 事务锁）。
      • locked_object： 根据 locktype 关联显示被锁的对象（表名、事务ID等）。
      • waiting_query： 显示该进程当前执行的查询（如果是等待者，这就是被阻塞的查询）。
      • 查找阻塞：如果一个进程在等待锁(granted=false)，查找持有相同对象、冲突锁模式(mode)且 granted=true 的其他进程。

  • 查询 pg_stat_activity 结合等待事件

    SELECT * FROM pg_stat_activity WHERE wait_event_type = 'Lock';

    直接列出当前所有因为等待锁而被阻塞的会话及其查询。

发现锁表后怎么办？

  1. 分析原因：
    • 查看阻塞者和被阻塞者正在执行的SQL语句,是慢查询？大事务？缺少索引？DDL操作？
    • 检查锁的类型和模式,是合理的业务锁还是设计缺陷？
  2. 评估影响： 阻塞链有多长？影响了多少用户和业务？
  3. 尝试解决：
    • 优化SQL/索引： 这是根本解决之道，减少锁的持有时间和范围。
    • 终止源头事务 (谨慎！)： 如果确认持有锁的事务是异常或可以终止的（如长时间未提交的事务、死循环），可以使用数据库提供的命令终止该会话/进程（如MySQL的 KILL [process_id], SQL Server的 KILL [spid], Oracle的 ALTER SYSTEM KILL SESSION 'sid,serial#', PostgreSQL的 SELECT pg_terminate_backend(pid)）。
    • 调整事务： 避免在事务中执行耗时操作或大量修改，尽快提交事务。
    • 调整隔离级别 (谨慎！)： 降低隔离级别（如从 READ COMMITTED 到 READ UNCOMMITTED）可以减少锁竞争，但可能引入脏读等问题。
    • 设计优化： 考虑乐观锁、队列、拆分大事务等架构层面的优化。
  4. 监控与预防： 建立数据库监控，对锁等待时间、阻塞会话数设置告警，及时发现和处理问题。

诊断数据库锁表是数据库管理和性能调优的基本功,掌握你所使用的数据库提供的特定工具和视图（如MySQL的 sys.innodb_lock_waits / INNODB_LOCK_WAITS, SQL Server的 sys.dm_tran_locks 和 sys.dm_os_waiting_tasks, Oracle的 v$lock 和 v$session, PostgreSQL的 pg_locks 和 pg_stat_activity）是关键，通过分析锁的类型、持有者和等待者信息，结合具体的SQL语句，可以快速定位问题根源，终止会话是最后手段，优化SQL、索引和事务设计才是长久之计，保持对数据库锁状态的监控，有助于在问题影响扩大前及时干预。

引用说明：

  • 本文中提到的数据库系统视图、命令和概念主要参考了各数据库官方文档：
    • MySQL: https://dev.mysql.com/doc/
    • Microsoft SQL Server: https://docs.microsoft.com/en-us/sql/sql-server/
    • Oracle Database: https://docs.oracle.com/en/database/
    • PostgreSQL: https://www.postgresql.org/docs/
  • 最佳实践部分结合了常见的数据库性能调优经验。
0

相关文章