泰兴市城乡住房建设局网站,微信小程序怎么制作商城,梧州高端网站建设服务,中小企业建网站哪个好背景
最近遇到一个问题#xff0c;需要在user用户表千万级别数据中添加两个字段#xff0c;发现老是加不上去#xff0c;一直卡死。表数据量不仅大#xff0c;而且是一个热点表#xff0c;访问频率特别高#xff0c;而且该表的访问是在一个大事务中。加字段的时候一直在…背景
最近遇到一个问题需要在user用户表千万级别数据中添加两个字段发现老是加不上去一直卡死。表数据量不仅大而且是一个热点表访问频率特别高而且该表的访问是在一个大事务中。加字段的时候一直在等待获取MDL写锁。这个等待也影响了后续表访问对MDL读锁的获取导致后面的查询也都被堵塞了。更惨的是客户端有重试机制查询堵塞超过超时时间会再起一个session进行请求导致数据库的线程池很快就爆满了直接挂掉。
MDL锁
MDL锁介绍
MDL锁属于表级别的元数据锁。表级别锁分为数据锁和元数据锁通常我们说的加锁一般指的是加的数据锁。跟数据锁一样元数据锁也分读锁和写锁。
MDL不需要显示使用在进行表操作时会自动加上。当对表数据进行增删改查 insert、delete、select、update等时会自动加上MDL读锁当要对表进行加减字段的结构修改时会自动加上MDL写锁。
读锁不互斥意味着可以多个线程同时对一张表进行增删改查CRUD的操作。写锁独占进行结构修改前要先等待其他所有的MDL锁释放了才能获取到MDL写锁。获取到写锁后在写锁释放前其他线程无法获取到MDL读锁和写锁。也就是说修改一个表的结构过程中会阻塞其他线程对表的操作
MDL锁是MySQL自动隐式加锁无需我们手动操作。在我们执行DML语句的时候MySQL自动添加MDL读锁。在我们执行DDL语句的时候MySQL自动添加MDL写锁。读锁与读锁之间不互斥读锁与写锁、写锁与写锁之间互斥。注意MDL锁是表锁会对整张表加锁
DMLData Manipulation Language数据操纵语言适用范围对表数据进行操作比如 insert、delete、select、update等。DDLData Definition Language数据定义语言适用范围对表结构进行操作比如create、drop、alter、rename、truncate等。
MDL锁的必要性
MDL锁 的存在其实是为了保证 数据的一致性 。想象一下假如没有 MDL锁 一个查询在遍历表数据的过程中另外一个线程执行了ALTER TABLE t DELETE COLUMN col_1把col_1这一列删掉了那查询结果就乱了结果中是否应该有这一列数据所以为了保证并发操作下数据的一致性。如果一个事务正在执行中另一个在这时修改了表结构不但可能导致当前事务出现不可重复读的问题还有可能连事务都无法提交
事故复现
介绍完MDL锁我们再来复现下事故。我们通过下面的操作序列来模拟线上情况。
Session 1Session 2Session 3begin;select * from user limit 10;alter table user add ‘age’ int not null default ‘0’ comment ‘年龄’; (阻塞)select * from user limit 10;
时刻1事务1对表user进行查询注意此时事务1并未提交所以获取的MDL读锁也不会释放。时刻2另外一个线程想要添加字段, 由于 事务1正持着MDL读锁所以事务2会陷入阻塞等待事务1释放读锁后获取MDL写锁。
申请 MDL 锁的操作会形成一个队列队列中写锁获取优先级高于读锁。 所以事务2不仅阻塞了加字段的操作也会阻塞后续对该表的所有操作。比如后面的事务3查询由于获取不到MDL读锁都被阻塞了。
这时如果客户端有重试机制查询超时后会重新进行请求容易把数据库的连接池给挤爆了。
Mysql服务宕机的原因
为什么会出现这种情况呢 原因是在执行查询语句的时候MySQL自动加了MDL锁metadata lock即元数据锁。 不行的话我们可以再执行一下show processlist命令查看有哪些正在执行的进程 可以清楚的看到Session2和Session3的语句正在等待MDL锁Waiting for table metadata lock。
解决方案 方案一 了解了原因事情就比较好处理了数据库奔溃原因是由于加字段等待时间太长导致影响后续请求但mysql又无法在 alter table 语句里面设定等待时间。 所以当时做法是继续尝试加字段语句语句卡住30秒就手动cancel掉。避免对后续请求的影响。重试了几次发现一直没能加上。最后是通过查看接口调用监控在请求频率较低的时间点给加上了。 方案二 从MySQL5.6版本开始增加了Online DDL作用就是在执行DDL的时候允许并发执行DML。简单翻译就是修改表结构的时候也能同时支持并发执行增删查改操作。从MySQL8.0版本开始又优化了Online DDL支持快速添加列可以实现给大表秒级加字段。具体用法就是在DDL语句后面增加两个参数ALGORITHM和LOCK。比如下面这样 ALTER TABLE user ADD age int NOT NULL DEFAULT 0 COMMENT 年龄, ALGORITHMInplace, LOCKNONE;。 ALGORITHM可以指定使用哪种算法执行DDL可选项有 Copy拷贝方式MySQL5.6 之前 DDL 的执行方式过程就是先创建新表修改新表结构把旧表数据复制到新表删除旧表重命名新表。执行过程非常耗时产生大量的磁盘IO和占用CPU还有使Buffer poll失效而且需要锁住旧表性能较差现在基本很少使用。Inplace原地修改MySQL5.6开始引入的优点是不会在Server层发生表数据拷贝过程中允许并发执行DML操作。过程就是先添加MDL写锁执行初始化操作然后降级为MDL读锁执行DDL操作比较耗时允许并发执行DML操作升级为MDL写锁完成DDL操作。Instant快速修改MySQL8.0开始引入的可以实现快速给大表添加字段。 性能依次是Instant Inplace Copy。 LOCK可以指定执行过程中是否加锁可选项有 NONE不加锁允许DML操作。SHARED加读锁允许读操作禁止DML操作。DEFAULT默认锁模式在满足DDL操作前提下默认锁模式会允许尽可能多的读操作和DML操作。EXCLUSIVE加写锁禁止读操作和DML操作。
其他
这里我们重点关注INNODB_TRX, INNODB_LOCKS, 以及INNODB_LOCK_WAITS三张表表如其名这三张表记录了正在运行的事务包括事务占用or释放锁的信息状态信息
select * FROM information_schema.INNODB_TRX;
select * FROM information_schema.INNODB_LOCKS;
select * FROM information_schema.INNODB_LOCK_WAITS;desc information_schema.INNODB_TRX;
desc information_schema.INNODB_LOCKS;
desc information_schema.INNODB_LOCK_WAITSshow engine innodb status;
show processlist;
