记一次mysql执行DDL导致锁表

祈雨的笔记

安全多方计算MPC spark原理解析 kueue执行源码分析 spark on k8s执行源码分析 spark-operator源码解析系统压测遇到的缓存击穿问题我的世界PC与安卓联机蚂蚁金服流量投放平台的AIG改造 G1大对象致Old区占用率高日志打印导致接口响应率下跌分析 Groovy加载类导致OOM分析 ERROR日志打印导致CPU满载记OceanBase死锁超时应用发版期间服务响应超时 Ark Serverless初探系统优化复盘一二三 The user specified as a definer does not exist Kong网关初探 API网关选型调研 CPU火焰图常用工具配置中心选型调研 root操作Nginx导致用户组错误基于Proxifier使用代理 FastJSON字段智能匹配踩坑 Nacos初探记一次Nginx服务器CPU满荷载故障基于券系统分库分表的思考 limit不参与SQL成本计算致索引失效 Linux常用性能监控命令 golang低版本http2偶现400 hostname in certificate didn't match 常见对称加密原理以及应用 tcp_tw_recycle引起的TCP握手失败 mysql磁盘占用查看 mysql对text字段update致磁盘增长 elasticsearch报错index read-only TIME_WAIT与Http的Keep-Alive 记一次TIME_WAIT导致连接数报警记一次生产事故OOM问题排查 redis分布式锁RedissonLock的实现细节 webservice复杂加密签名(2)java调用 webservice复杂加密签名(1)SoapUI mysql延时关联利用中间人拦截实现APP内H5窜改 MySQL表字符集不同导致关联查询索引失效通过SSH隧道远程办公数据落盘方案 BeanDefinitionRegistryPostProcessor扩展 mysql空间索引 HTTPS攻击 spring循环依赖过程解析 elasticsearch性能优化 mysql IS NULL 使用索引 mysql字符集utf8mb4失效踩坑常用加密算法 xml与javaBean转换初探InnoDB MVCC源码实现 mysql索引原理 redis之list源码分析 redis之key过期源码分析 redis之string源码分析 redis之hash源码分析线程池之ThreadPoolExecutor mysql数据页结构 Using temporary与Using filesort mysql回表致索引失效 springboot(28)HTTP连接池定时任务之ScheduledThreadPoolExecutor elasticsearch常用script聚合 elasticsearch实现like查询 elasticsearch实现乐观锁 elasticsearch准实时原理 springboot(27)自定义缓存读写机制CachingConfigurerSupport optimizer tracing arthas常用命令 HTTP和HTTPS详解 redis集群选举机制 kafka消息重试一点压力测试的经验 kafka架构概念 explain分析sql语句字段的解释 JVM问题分析处理手册 logstash过滤器(2)date logstash过滤器(3)dissect logstash编码器(1)json logstash编码器(2)multiline logstash表达式 logstash输入(1)通用选项 logstash输入(3)file logstash过滤器(1)通用选项 logstash输入(2)stdin logstash安装记一次前端vConsole导致JSON序列化错误排查解决多个异步操作嵌套问题 fastjson反序列化失败autoType is not support RTMP串流服务 POI自动调整列宽错误 Nginx+Lua实现动态黑名单使用curl命令模拟POST和GET请求 ResponseEntity下载文件

记一次mysql执行DDL导致锁表

祈雨的笔记 · 2020-06-06 · via 祈雨的笔记

背景

线上某数据库意外发现缺少索引，并且该表的数据量很少，只有几万条记录而已，因此很随意地尝试给该表添加索引。原本预期该表的记录很少，添加索引的耗时应该很短，结果却直接导致该表被锁，所有该表的增删改查操作全部阻塞，继而影响到了线上业务。

发现锁表后，执行show processlist发现大量线程阻塞，状态显示Waiting for table metadata lock。通过命令终止了DDL线程，该表恢复正常。mysql从5.6版本起支持Online DDL，理论上执行DDL语句不会阻塞诸如INSERT、UPDATE、DELETE这类DML操作。

事后排查发现，该表有个持续了3天未提交的事务，正是该事务导致DDL语句执行时锁表。

复现

新建表

CREATE TABLE `lock_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `content` varchar(32) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

新建一个会话1，开启事务执行以下命令后不要提交事务

1 2	begin; select * from lock_table limit 1;

新建另一个会话2，执行DDL命令，发现DDL语句执行被阻塞

1	ALTER TABLE `lock_table` ADD INDEX content_index(content);

此时表被锁定，再新建一个会话3，执行查询语句发现该操作同样被阻塞住

1	select id from lock_table limit 1;

-	会话1	会话2	会话3
步骤1	`begin;`
步骤2	`select * from lock_table limit 1;`
步骤3	-	`ALTER TABLE lock_table ADD INDEX content_index(content);`
步骤4	-	-	`select * from lock_table limit 1;`

原理

DDL上锁流程

MySQL Online DDL的改进与应用

prepare阶段：尝试获取MDL排他锁，禁止其他线程读写；
ddl执行阶段：降级成MDL共享锁，允许其他线程读取；
commit阶段：升级成MDL排他锁，禁止其他线程读写；
finish阶段：释放MDL锁；

DDL导致锁表的原因

To ensure transaction serializability, the server must not permit one session to perform a data definition language (DDL) statement on a table that is used in an uncompleted explicitly or implicitly started transaction in another session. The server achieves this by acquiring metadata locks on tables used within a transaction and deferring release of those locks until the transaction ends. A metadata lock on a table prevents changes to the table’s structure. This locking approach has the implication that a table that is being used by a transaction within one session cannot be used in DDL statements by other sessions until the transaction ends.

mysql官方文档metadata-locking一节中指出，为了确保事务可序列化，mysql不允许一个会话对在另一会话中未完成的显式或隐式启动的事务中使用的表执行DDL语句。服务器通过获取事务中使用的表上的元数据锁并将这些锁的释放推迟到事务结束之前来实现。表上的元数据锁可防止更改表的结构。这种锁定方法的含义是，一个会话中事务正在使用的表在事务结束之前不能被其他会话在DDL语句中使用。

mysql对申请MDL锁的操作会形成一个队列，队列中写锁获取优先级高于读锁。一旦出现写锁等待，不但当前操作会被阻塞，同时还会阻塞后续该表的所有操作。由上可知当事务一旦申请到MDL锁后，直到事务执行完才会将锁释放，当长事物或未提交的事务未提交完成时，执行DDL语句会等待MDL排他锁而阻塞，继而阻塞该表的后续其他操作。

观察MDL锁

MySQL5.7中的performance_schea库下新增了一张表metadata_locks，可以很方便地查看MDL锁的状态。默认情况下mysql未启动该表，可以通过以下两种方式打开该功能：

临时启用(mysql实例重启后，恢复默认值)

1	UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES' WHERE NAME = 'wait/lock/metadata/sql/mdl';

永久启用

修改配置文件，添加以下参数

1 2	[mysqld] performance-schema-instrument='wait/lock/metadata/sql/mdl=ON'

为了能方便查看到事务以及DDL执行过程中MDL锁的状态，需要延长DDL执行时长，否则会由于DDL执行过快而难以观察MDL锁的状态。可以通过如下语句向测试表中添加200万条测试记录。

delimiter  //
CREATE PROCEDURE init_lock_table() 
BEGIN 
	DECLARE index_id INT;
	SET index_id = 0;
	START TRANSACTION; 
	WHILE index_id < 2000000 DO
		insert into lock_table(content) values (CONCAT('content',index_id));
		SET index_id = index_id + 1;
	END WHILE;
	COMMIT; 
END //
delimiter ;
call init_lock_table();

事务获取共享锁

开启会话1，启用事务执行以下语句，获取MDL共享锁：

1 2	begin; select id from lock_table limit 1;

查看MDL锁状态，可见348线程（会话1）获取到测试表的共享锁。

mysql> select * from performance_schema.metadata_locks;
+
| OBJECT_TYPE | OBJECT_SCHEMA      | OBJECT_NAME    | OBJECT_INSTANCE_BEGIN | LOCK_TYPE   | LOCK_DURATION | LOCK_STATUS | SOURCE | OWNER_THREAD_ID | OWNER_EVENT_ID |
+
| TABLE       | test               | lock_table     |       140633203352496 | SHARED_READ | TRANSACTION   | GRANTED     |        |             348 |              6 |
| TABLE       | performance_schema | metadata_locks |       140633346571936 | SHARED_READ | TRANSACTION   | GRANTED     |        |             340 |             24 |
+

DDL语句获取排它锁

开启会话2，执行DDL语句，尝试获取MDL排他锁：

1	ALTER TABLE `lock_table` ADD INDEX content_index(content);

查看MDL锁状态，可见349线程（会话2）获取到测试表的共享锁，并且在等待测试表的排它锁。由于DML锁已经被会话1的事务占有，会话2只能等待会话1事务结束后释放DML锁，因而该锁状态为PENDING。

mysql> select * from performance_schema.metadata_locks;
+
| OBJECT_TYPE | OBJECT_SCHEMA      | OBJECT_NAME    | OBJECT_INSTANCE_BEGIN | LOCK_TYPE           | LOCK_DURATION | LOCK_STATUS | SOURCE | OWNER_THREAD_ID | OWNER_EVENT_ID |
+
| TABLE       | test               | lock_table     |       140633203352496 | SHARED_READ         | TRANSACTION   | GRANTED     |        |             348 |              6 |
| GLOBAL      | NULL               | NULL           |       140633201296448 | INTENTION_EXCLUSIVE | STATEMENT     | GRANTED     |        |             349 |              5 |
| SCHEMA      | test               | NULL           |       140633201296544 | INTENTION_EXCLUSIVE | TRANSACTION   | GRANTED     |        |             349 |              5 |
| TABLE       | test               | lock_table     |       140633201296352 | SHARED_UPGRADABLE   | TRANSACTION   | GRANTED     |        |             349 |              5 |
| TABLE       | test               | lock_table     |       140633202663984 | EXCLUSIVE           | TRANSACTION   | PENDING     |        |             349 |              5 |
| TABLE       | performance_schema | metadata_locks |       140633346571936 | SHARED_READ         | TRANSACTION   | GRANTED     |        |             340 |             25 |
+

DDL持有锁降级成共享锁

会话1执行语句commit;提交事务，令会话1释放MDL共享锁。此时可见会话2获取MDL排它锁成功，然后DDL的排他锁降级为共享锁：

mysql> select * from performance_schema.metadata_locks;
+
| OBJECT_TYPE | OBJECT_SCHEMA      | OBJECT_NAME    | OBJECT_INSTANCE_BEGIN | LOCK_TYPE           | LOCK_DURATION | LOCK_STATUS | SOURCE | OWNER_THREAD_ID | OWNER_EVENT_ID |
+
| GLOBAL      | NULL               | NULL           |       140633201296448 | INTENTION_EXCLUSIVE | STATEMENT     | GRANTED     |        |             349 |              5 |
| SCHEMA      | test               | NULL           |       140633201296544 | INTENTION_EXCLUSIVE | TRANSACTION   | GRANTED     |        |             349 |              5 |
| TABLE       | test               | lock_table     |       140633201296352 | SHARED_UPGRADABLE   | TRANSACTION   | GRANTED     |        |             349 |              5 |
| TABLE       | performance_schema | metadata_locks |       140633346571936 | SHARED_READ         | TRANSACTION   | GRANTED     |        |             340 |             26 |
+

当DDL持有的MDL锁降级为共享锁后，不再阻塞该表的其他操作，此时会话1再开启事务获取MDL共享锁不会阻塞，锁状态如下，从而实现Online DDL。

mysql> select * from performance_schema.metadata_locks;
+
| OBJECT_TYPE | OBJECT_SCHEMA      | OBJECT_NAME    | OBJECT_INSTANCE_BEGIN | LOCK_TYPE           | LOCK_DURATION | LOCK_STATUS | SOURCE | OWNER_THREAD_ID | OWNER_EVENT_ID |
+
| GLOBAL      | NULL               | NULL           |       140633201296448 | INTENTION_EXCLUSIVE | STATEMENT     | GRANTED     |        |             349 |              5 |
| SCHEMA      | test               | NULL           |       140633201296544 | INTENTION_EXCLUSIVE | TRANSACTION   | GRANTED     |        |             349 |              5 |
| TABLE       | test               | lock_table     |       140633201296352 | SHARED_UPGRADABLE   | TRANSACTION   | GRANTED     |        |             349 |              5 |
| TABLE       | test               | lock_table     |       140633203352496 | SHARED_READ         | TRANSACTION   | GRANTED     |        |             348 |              9 |
| TABLE       | performance_schema | metadata_locks |       140633346571936 | SHARED_READ         | TRANSACTION   | GRANTED     |        |             340 |             27 |
+

DDL持有锁升级为排他锁

当DDL执行完成时，会再次尝试获取MDL排他锁，如果此时348线程（会话1）的事务未完成而持有MDL共享锁时，349线程（会话2）的DDL操作等待MDL锁继而再一次0阻塞该表的其他操作。

mysql> select * from performance_schema.metadata_locks;
+
| OBJECT_TYPE | OBJECT_SCHEMA      | OBJECT_NAME    | OBJECT_INSTANCE_BEGIN | LOCK_TYPE           | LOCK_DURATION | LOCK_STATUS | SOURCE | OWNER_THREAD_ID | OWNER_EVENT_ID |
+
| GLOBAL      | NULL               | NULL           |       140633201296448 | INTENTION_EXCLUSIVE | STATEMENT     | GRANTED     |        |             349 |              5 |
| SCHEMA      | test               | NULL           |       140633201296544 | INTENTION_EXCLUSIVE | TRANSACTION   | GRANTED     |        |             349 |              5 |
| TABLE       | test               | lock_table     |       140633201296352 | SHARED_UPGRADABLE   | TRANSACTION   | GRANTED     |        |             349 |              5 |
| TABLE       | test               | lock_table     |       140633203352496 | SHARED_READ         | TRANSACTION   | GRANTED     |        |             348 |              9 |
| TABLE       | test               | lock_table     |       140633202744688 | EXCLUSIVE           | TRANSACTION   | PENDING     |        |             349 |              5 |
| TABLE       | performance_schema | metadata_locks |       140633346571936 | SHARED_READ         | TRANSACTION   | GRANTED     |        |             340 |             28 |
+

解决方案

解除正在锁表的状态有两种方法：

方法一

终止DDL语句，选择业务低峰期或其他时间段执行。

查询是否锁表

1	show OPEN TABLES where in_use > 0;

查询进程
（如果账号有SUPER权限，则可以看到所有线程，否则，只能看到当前用户的线程）

1	show processlist;

或者

1	select * from information_schema.processlist where COMMAND != 'Sleep';

杀死进程

1	kill 进程ID;

示例：

查询到DDL会话的进程ID为286，然后通过kill命令杀死该进程终止会话，解决阻塞问题。

kill 286;

方法二

杀死阻塞DDL语句的会话，但并不建议这么操作，因为无法判断该会话执行了什么操作，武断地终止该会话会造成无法预估的业务异常。

查看当前的事务

1	SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

查看当前锁定的事务

1	SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

查看当前等锁的事务

1	SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

杀死进程

1	kill 进程ID;

示例：

查询到阻塞DDL操作的长事务会话的进程ID为285，然后通过kill命令杀死该进程终止会话，解决阻塞问题。

kill 285;

方法三

执行DDL语句前，先通过lock_wait_timeout设置好锁超时时间，避免长时间的DML锁等待。当DDL语句等待MDL锁超时时，自动终止当前会话，避免长时间等待锁继而阻塞其他线程。

示例：

1
2
3


SET lock_wait_timeout=5;
ALTER TABLE `lock_table` ADD INDEX content_index2(content);

当等待DML锁超时后，DDL终止并且抛出错误：1205 - Lock wait timeout exceeded; try restarting transaction。

总结

谨慎使用长事务，业务代码中的耗时操作尽量不要在事务中执行；
执行DDL语句前先查看当前活跃事务，防止有未提交事务或者长事务存在；
DDL语句导致锁表后，先杀掉DDL语句进程，终止锁库减小影响范围，再然后排查问题；

此内容由惯性聚合(RSS阅读器)自动聚合整理，仅供阅读参考。原文来自 — 版权归原作者所有。

推荐订阅源

祈雨的笔记

背景

复现

原理

DDL上锁流程

DDL导致锁表的原因

观察MDL锁

解决方案

方法一

方法二

方法三

总结