Mysql查询当前数据库有哪些事务、都锁定哪些资源

最新推荐文章于 2025-08-28 23:24:46 发布

原创最新推荐文章于 2025-08-28 23:24:46 发布 · 1.1w 阅读

7 ·

CC 4.0 BY-SA版权

文章标签：

#Mysql #锁 #阻塞 #事务

mysql 专栏收录该内容

25 篇文章

订阅专栏

本文深入解析InnoDB存储引擎中的事务与锁机制，通过SQL查询展示事务状态、资源等待、使用的表与锁信息，以及锁等待情况，帮助读者理解数据库内部运行机制。

SELECT
    trx_id AS  `事务ID`,
    trx_state AS `事务状态`,
    trx_requested_lock_id  AS  `事务需要等待的资源`,
    trx_wait_started    AS  `事务开始等待时间`,
    trx_tables_in_use AS `事务使用表`,
    trx_tables_locked AS `事务拥有锁`,
    trx_rows_locked  AS `事务锁定行`,
    trx_rows_modified  AS `事务更改行`
FROM
    information_schema.innodb_trx ;

SELECT
    lock_id         AS  `锁ID`,
    lock_trx_id    AS  `拥有锁的事务ID`,
    lock_mode      AS  `锁模式    `,
    lock_type      AS  `锁类型`,
    lock_table         AS  `被锁的表`,
    lock_index         AS  `被锁的索引`,
    lock_space         AS  `被锁的表空间号`,
    lock_page      AS  `被锁的页号`,
    lock_rec       AS  `被锁的记录号`,
    lock_data      AS  `被锁的数据`
FROM
    information_schema.innodb_locks;

SELECT
  requesting_trx_id   AS  `请求锁的事务ID`,
  requested_lock_id   AS  `请求锁的锁ID`,
  blocking_trx_id     AS  `当前拥有锁的事务ID`,
  blocking_lock_id    AS  `当前拥有锁的锁ID`
FROM
  innodb_lock_waits;