全部关于锁文章
- 全局读锁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 是一个小表。
测试上述图片的场景
代码语言：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数据库。
