前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >MySQL Cases-MySQL找出谁持有表锁之MDL锁

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

原创
作者头像
姚崇
修改2021-10-19 14:21:17
1.3K0
修改2021-10-19 14:21:17
举报
文章被收录于专栏:MySQL故障优化案例

全部关于锁文章

表锁之MDL锁

本文使用MySQL8.0.23测试

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

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

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

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

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

测试上述图片的场景

代码语言:javascript
复制
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查询阻塞情况

代码语言:javascript
复制
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;

代码语言:javascript
复制
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 这个语法。

代码语言:javascript
复制
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便查询不到相关锁信息了

代码语言:javascript
复制
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 删除。

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

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

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 全部关于锁文章
  • 表锁之MDL锁
    • 关闭mdl instruments重复图片加MDL锁操作
    • 结论:
    相关产品与服务
    云数据库 SQL Server
    腾讯云数据库 SQL Server (TencentDB for SQL Server)是业界最常用的商用数据库之一,对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权,可持续为用户提供最新的功能,避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
    领券
    问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档