专栏首页MySQL故障优化案例MySQL Cases-MySQL找出谁持有表锁之MDL锁
原创

MySQL Cases-MySQL找出谁持有表锁之MDL锁

全部关于锁文章

表锁之MDL锁

本文使用MySQL8.0.23测试

另一类表级的锁是 MDL(metadata lock)。MDL 不需要显式使用,在访问一个表的时候会被自动加上。MDL 的作用是,保证读写的正确性。你可以想象一下,如果一个查询正在遍历一个表中的数据,而执行期间另一个线程对这个表结构做变更,删了一列,那么查询线程拿到的结果跟表结构对不上,肯定是不行的。

因此,在 MySQL 5.5 版本中引入了 MDL,当对一个表做增删改查操作的时候,加 MDL 读锁;当要对表做结构变更操作的时候,加 MDL 写锁。

  • 读锁之间不互斥,因此你可以有多个线程同时对一张表增删改查。
  • 读写锁之间、写锁之间是互斥的,用来保证变更表结构操作的安全性。因此,如果有两个线程要同时给一个表加字段,其中一个要等另一个执行完才能开始执行。

虽然 MDL 锁是系统默认会加的,但却是你不能忽略的一个机制。比如下面这个例子,我经常看到有人掉到这个坑里:给一个小表加个字段,导致整个库挂了。

你肯定知道,给一个表加字段,或者修改字段,或者加索引,需要扫描全表的数据。在对大表操作的时候,你肯定会特别小心,以免对线上服务造成影响。而实际上,即使是小表,操作不慎也会出问题。我们来看一下下面的操作序列,假设表 t 是一个小表。

测试上述图片的场景

drop table t;
create table t (id int primary key,name varchar(100),t timestamp(6));
insert into t values(10,'zhangtianba',now());
insert into t values(11,'xiaocong',now());
insert into t values(20,'zhangsan',now());
insert into t values(30,'lisi',now());

--session A
mysql> begin;
mysql> use test;
mysql> select * from t;
+----+-------------+----------------------------+
| id | name        | t                          |
+----+-------------+----------------------------+
| 10 | zhangtianba | 2021-08-30 12:11:27.000000 |
| 11 | xiaocong    | 2021-08-30 12:11:27.000000 |
| 20 | zhangsan    | 2021-08-30 12:11:27.000000 |
| 30 | lisi        | 2021-08-30 12:11:27.000000 |
+----+-------------+----------------------------+
4 rows in set (0.00 sec)

-- session B
mysql> use test;
mysql> begin;
mysql> select * from t limit 1;
+----+-------------+----------------------------+
| id | name        | t                          |
+----+-------------+----------------------------+
| 10 | zhangtianba | 2021-05-06 11:07:33.000000 |
+----+-------------+----------------------------+
1 row in set (0.00 sec)


-- session C
mysql> use test;
mysql> alter table t add f int; 
Session C is blocked

-- session D
mysql> use test;
mysql> select * from t limit 2;
Session D is blocked

然后通过SQL查询阻塞情况

SELECT

    ps.conn_id,
    concat('kill ',ps.conn_id,';') as kill_command,
    ps.user,
    ps.db,
    ps.command,
    ps.state,
    ps.time,
    DATE_SUB(NOW(), INTERVAL (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'UPTIME') -
                                                  p.TIMER_START / 1000 / 1000 / 1000 / 1000 second) AS 'session_sql_start_time',
    p.sql_text,
    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 sys.ps_thread_id(ps.conn_id) = lock_summary.OWNER_THREAD_ID
                    and lock_summary.owner_thread_id != sys.ps_thread_id(connection_id())
    inner join performance_schema.events_statements_history p
                   ON lock_summary.OWNER_THREAD_ID = p.THREAD_ID
order by lock_summary.OWNER_THREAD_ID,p.TIMER_START;


--再或者

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;

查询结果如下,可以根据pending确认为被阻塞,granted确认为阻塞者,结合time时间列确认先后顺序:

或者使用sys.schema_table_lock_waits;

mysql> select * from sys.schema_table_lock_waits;
+---------------+-------------+-------------------+-------------+-----------------+-------------------+-----------------------+-------------------------+--------------------+-----------------------------+-----------------------------+--------------------+--------------+------------------+--------------------+------------------------+-------------------------+------------------------------+
| object_schema | object_name | waiting_thread_id | waiting_pid | waiting_account | waiting_lock_type | waiting_lock_duration | waiting_query           | waiting_query_secs | waiting_query_rows_affected | waiting_query_rows_examined | blocking_thread_id | blocking_pid | blocking_account | blocking_lock_type | blocking_lock_duration | sql_kill_blocking_query | sql_kill_blocking_connection |
+---------------+-------------+-------------------+-------------+-----------------+-------------------+-----------------------+-------------------------+--------------------+-----------------------------+-----------------------------+--------------------+--------------+------------------+--------------------+------------------------+-------------------------+------------------------------+
| test          | t           |               171 |         128 | root@localhost  | EXCLUSIVE         | TRANSACTION           | alter table t add f int |                264 |                           0 |                           0 |                174 |          131 | root@localhost   | SHARED_READ        | TRANSACTION            | KILL QUERY 131          | KILL 131                     |
| test          | t           |               176 |         133 | root@localhost  | SHARED_READ       | TRANSACTION           | select * from t limit 2 |                181 |                           0 |                           0 |                174 |          131 | root@localhost   | SHARED_READ        | TRANSACTION            | KILL QUERY 131          | KILL 131                     |
| test          | t           |               171 |         128 | root@localhost  | EXCLUSIVE         | TRANSACTION           | alter table t add f int |                264 |                           0 |                           0 |                168 |          125 | root@localhost   | SHARED_READ        | TRANSACTION            | KILL QUERY 125          | KILL 125                     |
| test          | t           |               176 |         133 | root@localhost  | SHARED_READ       | TRANSACTION           | select * from t limit 2 |                181 |                           0 |                           0 |                168 |          125 | root@localhost   | SHARED_READ        | TRANSACTION            | KILL QUERY 125          | KILL 125                     |
| test          | t           |               171 |         128 | root@localhost  | EXCLUSIVE         | TRANSACTION           | alter table t add f int |                264 |                           0 |                           0 |                171 |          128 | root@localhost   | SHARED_UPGRADABLE  | TRANSACTION            | KILL QUERY 128          | KILL 128                     |
| test          | t           |               176 |         133 | root@localhost  | SHARED_READ       | TRANSACTION           | select * from t limit 2 |                181 |                           0 |                           0 |                171 |          128 | root@localhost   | SHARED_UPGRADABLE  | TRANSACTION            | KILL QUERY 128          | KILL 128                     |
+---------------+-------------+-------------------+-------------+-----------------+-------------------+-----------------------+-------------------------+--------------------+-----------------------------+-----------------------------+--------------------+--------------+------------------+--------------------+------------------------+-------------------------+------------------------------+
6 rows in set (0.20 sec)

我们可以看到 session A 先启动,这时候会对表 t 加一个 MDL 读锁。由于 session B 需要的也是 MDL 读锁,因此可以正常执行。之后 session C 会被 blocked,是因为 session A 的 MDL 读锁还没有释放,而 session C 需要 MDL 写锁,因此只能被阻塞。如果只有 session C 自己被阻塞还没什么关系,但是之后所有要在表 t 上新申请 MDL 读锁的请求也会被 session C 阻塞。前面我们说了,所有对表的增删改查操作都需要先申请 MDL 读锁,就都被锁住,等于这个表现在完全不可读写了。如果某个表上的查询语句频繁,而且客户端有重试机制,也就是说超时后会再起一个新 session 再请求的话,这个库的线程很快就会爆满。

你现在应该知道了,事务中的 MDL 锁,在语句执行开始时申请,但是语句结束后并不会马上释放,而会等到整个事务提交后再释放。

基于上面的分析,我们来讨论一个问题,如何安全地给小表加字段?

首先我们要解决长事务,事务不提交,就会一直占着 MDL 锁。在 MySQL 的 information_schema 库的 innodb_trx 表中,你可以查到当前执行中的事务。如果你要做 DDL 变更的表刚好有长事务在执行,要考虑先暂停 DDL,或者 kill 掉这个长事务。

但考虑一下这个场景。如果你要变更的表是一个热点表,虽然数据量不大,但是上面的请求很频繁,而你不得不加个字段,你该怎么做呢?

这时候 kill 可能未必管用,因为新的请求马上就来了。比较理想的机制是,在 alter table 语句里面设定等待时间,如果在这个指定的等待时间里面能够拿到 MDL 写锁最好,拿不到也不要阻塞后面的业务语句,先放弃。之后开发人员或者 DBA 再通过重试命令重复这个过程。

MariaDB 已经合并了 AliSQL 的这个功能,所以这两个开源分支目前都支持 DDL NOWAIT/WAIT n 这个语法。

ALTER TABLE tbl_name NOWAIT add column ...
ALTER TABLE tbl_name WAIT N add column ... 

以上为默认的performance_schema级别下的MDL定位情况。

在执行语句时,我们可能经常会遇到阻塞等待MDL锁的情况。例如:使用show processlist语句查看线程信息时可能会发现State字段值为"Waiting for table metadata lock"。那么,当遇到这种情况时,应该如何排查是谁持有了MDL锁没有释放呢?下面我们尝试进行MDL锁的等待场景模拟(MDL锁记录对应的instruments为wait/lock/metadata/sql/mdl,5.7中默认没有启用(MySQL8.0.23中默认开启了);对应的consumers为performance_schema.metadata_locks),在setup_consumers中只受全局配置项global_instrumentation控制,默认开启)。

通过sys.schema_table_lock_waits视图可以查看当前连接线程的MDL等待信息,显示哪些会话被MDL锁阻塞,是谁阻塞了这些会话,数据来源:ps下的threads、metadata_locks、events_statements_current表。该视图是MySQL5.7.9中新增的。下面使用schema_table_lock_waits视图查询的结果集。首先要启用

MySQL 5.7版本之前,我们不能从数据库层面很直观地查询谁持有MDL锁信息(如果使用GDB之类的工具来查看,则需要具有一定的C语言基础)。现在,可以通过查询performance_schema.metadata_locks表得知MDL锁信息。

关闭mdl instruments重复图片加MDL锁操作

关闭instrument后,发现使用sys.schema_table_lock_waits便查询不到相关锁信息了

CALL sys.ps_setup_disable_instrument('wait/lock/metadata/sql/mdl');

mysql> select * from sys.schema_table_lock_waits;
Empty set (0.01 sec)

结论:

可以使用上述两个脚本定位MDL锁信息,如果关闭了performance_schema,也是可以查询到MDL锁的。

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

作者:姚崇 Oracle OCM、MySQL OCP、Oceanbase OBCA、PingCAP PCTA认证,擅长基于Oracle、MySQL Performance Turning及多种关系型 NoSQL数据库

原创声明,本文系作者授权云+社区发表,未经许可,不得转载。

如有侵权,请联系 yunjia_community@tencent.com 删除。

关注作者,阅读全部精彩内容

我来说两句

0 条评论
登录 后参与评论

相关文章

  • MySQL Cases-MySQL找出谁持有表锁

    表级锁对应的instruments(wait/lock/table/sql/handler)默认已开启,对应的consumers为performance_sch...

    姚崇
  • MySQL Cases-MySQL找出谁持有行锁(RR)

    一个事务所做的修改,对其他事务是不可见的,好似是串行执行的。多个事务并行执行的,好似他是串行执行的,事务并发执行,但是效果和串行效果一致,一个事务所做的修改对其...

    姚崇
  • MySQL Cases-MySQL找出谁持有行锁(RC)

    承接上文RR级别下的锁粒度,这篇文章看下RC模式下有哪些特点,首先说下RC解决了什么问题。

    姚崇
  • MySQL Cases-MySQL找出谁持有全局读锁

    全局读锁通常是由flush table with read lock;这类语句添加的。在各种备份工具为了得到一致性备份,已经在具备主从复制架构的环境中做主备切换...

    姚崇
  • MySQL MDL锁

    session A 通过 lock table 命令持有表 t 的 MDL 写锁,而 session B 的查询需要获取 MDL 读锁。所以,session B...

    十毛
  • 应用示例荟萃 | performance_schema全方位介绍(上)

    经过前面6个篇幅的学习,相信大家对什么是performance_schema,已经初步形成了一个整体认识,但我想很多同行看完之前的文章之后可能还是一脸懵逼,今天...

    老叶茶馆
  • 应用示例荟萃 | performance_schema全方位介绍(上)

    经过前面6个篇幅的学习,相信大家对什么是performance_schema,已经初步形成了一个整体认识,但我想很多同行看完之前的文章之后可能还是一脸懵逼,今天...

    沃趣科技
  • 史上最详细MySQL全局锁和表锁

    原文:http://www.enmotech.com/web/detail/1/728/1.html (复制链接,打开浏览器即可查看)

    数据和云01
  • 史上最详细MySQL全局锁和表锁

    墨墨导读:根据加锁的范围,MySQL里面的锁大致可以分成全局锁,表级锁,行锁。本文主要讲述MySQL全局锁和表锁。

    数据和云
  • 深入理解MDL元数据锁

    当你在MySQL中执行一条SQL时,语句并没有在你预期的时间内执行完成,这时候我们通常会登陆到MySQL数据库上查看是不是出了什么问题,通常会使用的一个命令就是...

    MySQL技术
  • 女朋友问我:什么是 MySQL 的全局锁、表锁、行锁?

    小胖真的让人不省心。继上次小胖误删数据之后,这次这货直接给我把整个表锁住了。页面无响应,用户疯狂投诉,我特么脸都绿了。。。

    一个优秀的废人
  • 会话和锁信息查询视图 | 全方位认识 sys 系统库

    在上一篇《等待事件统计视图 | 全方位认识 sys 系统库》中,我们介绍了sys 系统库中的等待事件统计视图,本期的内容先给大家介绍会话信息和锁等待信息查询视图...

    沃趣科技
  • MySQL核心知识学习之路(4)

    作为一个后端工程师,想必没有人没用过数据库,跟我一起复习一下MySQL吧,本文是我学习《MySQL实战45讲》的总结笔记的第四篇,总结了MySQL的锁相关知识。

    Edison Zhou
  • 应用示例荟萃 | performance_schema全方位介绍(中)

    表级锁对应的instruments(wait/lock/table/sql/handler)默认启用,对应的consumers表为performance_sch...

    老叶茶馆
  • Mysql DDL出现长时间等待MDL问题分析

    给表新增字段时,发现锁表了,查看进程,提示Waiting for table metadata lock,等待锁释放;然而蛋疼的是几分钟过去了,依然没有任何的进...

    一灰灰blog
  • MySQL基础篇5 mysql的全局锁和表锁

    当出现并发访问的时候,数据库需要合理地控制资源的访问规则。而锁就是用来实现这些访问规则的重要数据结构;

    历久尝新
  • MySQL Online DDL 原理和踩坑

    导读:MySQL 的 DDL(Data Definition Language) 包括增减字段、增减索引等操作。在 MySQL 5.6 之前,MySQL 的 D...

    良月柒
  • MySQL Online DDL 原理和踩坑

    导读:MySQL 的 DDL(Data Definition Language) 包括增减字段、增减索引等操作。在 MySQL 5.6 之前,MySQL 的 D...

    码农架构
  • 【MySQL经典案例分析】 Waiting for table metadata lock

    2018年某个周末,接到连续数据库的告警,看到too many connection的报错信息,基本上可以把问题定位在...

    迪B哥

扫码关注云+社区

领取腾讯云代金券