线上某数据库意外发现缺少索引,并且该表的数据量很少,因此很随意地尝试给该表添加索引。原本预期该表的记录很少,添加索引的耗时应该很短,结果却直接导致该表被锁,所有该表的增删改查操作全部阻塞,继而影响到了线上业务。
发现锁表后,执行show processlist发现大量线程阻塞,状态显示Waiting for table metadata lock。
MySQL5.7后 MySQL Online DDL的改进与应用
prepare阶段:尝试获取MDL排他锁,禁止其他线程读写;
ddl执行阶段:降级成MDL共享锁,允许其他线程读取;
commit阶段:升级成MDL排他锁,禁止其他线程读写;
finish阶段:释放MDL锁;
1、3、4如果没有锁冲突,执行时间非常短。第2步占用了DDL绝大部分时间,这期间这个表可以正常读写数据,是因此称为“online ”。
如果第3步升级为MDL写锁的时候,这个表的MDL锁有其他事务占着,那么这个事务会阻塞,等到可以拿到MDL写锁,而且如果不幸一直拿不到,最后锁超时了,就只好回滚这个DDL操作。
所以,DDL语句只有才开始和结束的时候,才会禁止读和写,在语句执行的时候是可以进行读的。
出现了死锁咋办?
找到ddl语句
show PROCESSLIST
终止
kill -PID
如何安全地给表添加字段、添加索引
最实用的:
按新结构创建新表 -> 将旧表数据迁移至新表 -> 重命名两个表(三步都通过编写sql语句完成,比手动操作快,第二步的数据迁移操作视情况而定
操作ddl之前,先用以下语句查一下有没有长事务:SELECT * FROM information_schema.INNODB_TRX;