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

原创

姚崇

修改于 2021-10-19 14:21:17

1.3K0

修改于 2021-10-19 14:21:17

文章被收录于专栏：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锁

本文使用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数据库。

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

云数据库 SQL Server

云数据库 MySQL

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

登录后参与评论

0 条评论

热度