廊坊电子网站建设,网站怎么制作软件,一尊网 又一个wordpress站点,网站的建设ppt文章目录写在前面一、短事务场景下#xff0c;执行DDL语句场景分析1、短事务场景下#xff0c;执行表字段添加操作2、短事务场景下#xff0c;执行表字段修改操作3、短事务场景下#xff0c;执行表字段删除操作#xff08;1#xff09;往里添加一条数据试试4、短事务场景…
文章目录写在前面一、短事务场景下执行DDL语句场景分析1、短事务场景下执行表字段添加操作2、短事务场景下执行表字段修改操作3、短事务场景下执行表字段删除操作1往里添加一条数据试试4、短事务场景下添加索引操作5、总结二、完美解决方案三、原因分析写在后面写在前面
DDL语句就是对数据库对象数据库、表、列、索引等进行创建、删除、修改等。
之前分享过一篇mysql创建索引导致死锁数据库崩溃mysql的表级锁之【元数据锁meta data lockMDL)】全解
通过上一篇文章我们了解到MySQL有一种表锁叫做元数据锁meta data lockMDL)元数据锁执行DDL时会检查元数据锁并尝试获取。
之前一直以为只要保证MySQL数据库当前没有长事务就可以安枕无忧地执行DDL语句我们天真的认为短事务场景中DDL语句总是会在上一个事务结束后获取到元数据锁并不会有死锁的危险。
但是最近发现事情并没有想象中的那么简单就算是没有长事务MySQL8.0在创建索引的时候仍然有可能会导致死锁的发生 我们一起来分析一下。
注本文操作都是基于mysql 8.0.21InnoDB引擎可重复读事务隔离级别下来完成的。
一、短事务场景下执行DDL语句场景分析
先创建一个表
CREATE TABLE lock_test (id int NOT NULL,name varchar(20) DEFAULT NULL,age int DEFAULT NULL,PRIMARY KEY (id)
) ENGINEInnoDB;1、短事务场景下执行表字段添加操作 我们发现当事务A尚未提交时事务B、事务C都会处于等待状态。
当事务A一经发起commit命令事务B紧跟着会进行执行并执行成功事务C在事务B之后也会执行成功。
整个过程只有正常的事务等待并不会发生死锁。
2、短事务场景下执行表字段修改操作 我们发现修改字段的场景下和添加字段场景下的结果是一样的。
3、短事务场景下执行表字段删除操作 当该表无数据时我们会发现当事务A执行提交之后事务B和事务C进入死锁此时该表的任何SQL语句都无法执行
此时导致数据库表死锁数据库SQL堆积越来越多导致数据库崩溃
1往里添加一条数据试试
INSERT INTO ourea.lock_test(id, name, age, column_name) VALUES (1, 1, 1, 1);有数据的情况和没有数据的情况是不一样的
有数据时当事务A提交之后事务C会执行成功当事务C提交之后事务B才是最终执行成功。
4、短事务场景下添加索引操作
还是保持数据库中有数据。
INSERT INTO ourea.lock_test(id, name, age, column_name) VALUES (1, 1, 1, 1);我们发现当事务A执行提交之后事务B和事务C进入死锁此时该表的任何SQL语句都无法执行
此时导致数据库表死锁数据库SQL堆积越来越多导致数据库崩溃
5、总结
用事实说话。
通过实例我们可以看出即使没有长事务执行DDL语句仍会导致表死锁。 尤其是对索引的操作非常危险
二、完美解决方案
在执行DDL语句之前通过以下SQL首先要确保没有长事务
SELECT * FROM information_schema.INNODB_TRX;然后再执行DDL语句之前先将整表锁住然后执行DDL语句 使用如下操作完美解决死锁问题
-- 锁整表加上写锁
lock table lock_test write;
-- 添加索引
CREATE INDEX index_tb ON lock_test(column_name);
-- 解锁
unlock table;三、原因分析
当对非主键字段更改索引时其实并不是一个原子操作会先更新非主键字段索引然后再更新主键索引。
当我们把主键删掉之后 这种情况和当表中有数据执行表字段删除操作的场景一模一样。
原因是MySQL5.6引入的OnLine-DDL一个DDL语句其实包含着两个等待操作
prepare阶段尝试获取MDL排他锁禁止其他线程读写ddl执行阶段降级成MDL共享锁允许其他线程读取commit阶段升级成MDL排他锁禁止其他线程读写finish阶段释放MDL锁 1、3、4如果没有锁冲突执行时间非常短。第2步占用了DDL绝大部分时间这期间这个表可以正常读写数据是因此称为“online ”。 如果第3步升级为MDL写锁的时候这个表的MDL锁有其他事务占着那么这个事务会阻塞等到可以拿到MDL写锁而且如果不幸一直拿不到最后锁超时了就只好回滚这个DDL操作。 所以DDL语句只有才开始和结束的时候才会禁止读和写在语句执行的时候是可以进行读的。
写在后面
如果本文对你有帮助请点赞收藏关注一下吧 ~
