MySQL Cases-MySQL找出谁持有表锁

原创

姚崇

修改于 2021-10-25 14:21:32

7380

修改于 2021-10-25 14:21:32

文章被收录于专栏：MySQL故障优化案例MySQL故障优化案例

全部关于锁文章

全局读锁https://cloud.tencent.com/developer/article/1869375
表锁 https://cloud.tencent.com/developer/article/1869546
MDLhttps://cloud.tencent.com/developer/article/1869667
MySQL找出谁持有行锁（RR级别）https://cloud.tencent.com/developer/article/1869793
MySQL找出谁持有行锁（RC级别）https://cloud.tencent.com/developer/article/1869900

表锁(表锁也是MDL锁的一种)

表级锁对应的instruments(wait/lock/table/sql/handler)默认已开启，对应的consumers为performance_schema.table_handlers ，在setup_consumers中只受全局配置项global_instrumentation控制，默认已开启。所以默认情况下只要设置系统配置参数performance_schema=ON即可。下面通过一个示例来演示如何找出谁持有表级锁。

会话一：

lock table t read;

会话二：

update t set pad='xxx' where id = 1; # 被阻塞

查询:

找出持有表锁的脚本

SELECT
    ps.conn_id,
    concat('kill ',ps.conn_id,';') as kill_command,
    ps.user,
    ps.db,
    ps.command,
    ps.state,
    ps.time,
    ps.last_statement,
    lock_summary.lock_summary
FROM
    sys.processlist ps INNER JOIN (
        SELECT
            owner_thread_id,
            GROUP_CONCAT(
                DISTINCT CONCAT(
                    mdl.LOCK_STATUS,
                    ' ',
                    mdl.lock_type,
                    ' on ',
                    IF(
                        mdl.object_type = 'USER LEVEL LOCK',
                        CONCAT(mdl.object_name, ' (user lock)'),
                        CONCAT(mdl.OBJECT_SCHEMA, '.', mdl.OBJECT_NAME)
                    )
                )
                ORDER BY
                    mdl.object_type ASC,
                    mdl.LOCK_STATUS ASC,
                    mdl.lock_type ASC SEPARATOR '\n'
            ) as lock_summary
        FROM
            performance_schema.metadata_locks mdl
        GROUP BY
            owner_thread_id
    ) lock_summary ON (ps.thd_id = lock_summary.owner_thread_id) and lock_summary.owner_thread_id != sys.ps_thread_id(connection_id())
order by ps.time desc;

查询结果如下，默认级别下可以看到按照时间顺序130会话的操作信息，如果想kill掉130，直接执行kill 130;即可

+---------+--------------+----------------+------+---------+---------------------------------+------+-------------------+------------------------------------+
| conn_id | kill_command | user           | db   | command | state                           | time | last_statement    | lock_summary                       |
+---------+--------------+----------------+------+---------+---------------------------------+------+-------------------+------------------------------------+
|    1047 | kill 1047;   | root@localhost | test | Sleep   | NULL                            |  115 | lock table t read | GRANTED SHARED_READ_ONLY on test.t |
|    1048 | kill 1048;   | root@localhost | test | Query   | Waiting for table metadata lock |   38 | NULL              | PENDING SHARED_WRITE on test.t     |
+---------+--------------+----------------+------+---------+---------------------------------+------+-------------------+------------------------------------+
2 rows in set (0.24 sec)

更多文章欢迎关注本人公众号，搜dbachongzi或扫二维码