教程网站搭建,ssh私钥 Wordpress,景点介绍网站开发设计,江门英文网站建设遇到故障#xff0c;我们往往想的是如何解决这个故障#xff0c;而不是从故障的根本去思考出现这个故障的原因#xff1f;这样的结果#xff0c;只能使我们得到了鱼#xff0c;失去了渔。今天#xff0c;我们就来分享一个由USE DB堵塞故障引发的思考案例。故障描述今天一…遇到故障我们往往想的是如何解决这个故障而不是从故障的根本去思考出现这个故障的原因这样的结果只能使我们得到了鱼失去了渔。今天我们就来分享一个由USE DB堵塞故障引发的思考案例。故障描述今天一个朋友遇到数据库遇到一个严重的故障故障环境如下MYSQL 5.6.16RR隔离级别GITD关闭表现如下use db不能进入数据库show table status不能查询到表信息schema.processlist来看有大量的 Waiting for table metadata lock情急之下他杀掉了一大堆线程后发现还是不能恢复最后杀掉了一个没有及时提交的事物才恢复正常。也仅仅留下了如下图的一个截图故障信息提取还是回到上图我们可以归纳一下语句类型如下1、CREATE TABLE A AS SELECT B其STATE为 sending data2、DROP TABLE A其STATE为 Waiting for table metadata lock3、SELECT * FROM A其STATE为 Waiting for table metadata lock4、 SHOW TABLE STATUS[like A]其STATE为 Waiting for table metadata lock信息分析要分析出这个案列其实不太容易因为他是MYSQL层MDL LOCK和RR模式innodb row lock的一个综合案列并且我们要对schema.processlist的STATE比较敏感才行。建议先阅读我的如下文章来学习MDL LOCK//www.jb51.net/article/131383.htm本节关于MDL LOCK的验证使用下面两种方式方式一笔者在MDL LOCK源码加锁函数处加日志输出如果要分析各种语句加MDL LOCK的类型还只能用这种方式因为MDL LOCK加锁往往一闪而过performance_schema.metadata_locks 没有办法观察到。方式二处于堵塞情况下使用5.7版本的performance_schema.metadata_locks观察。在P_S中打开mdl监测方法如下一、关于CREATE TABLE A AS SELECT B 对B表sending data的分析关于sending data这个状态其实可以代表很多含义从我现有的对的了解这是MYSQL上层对SELECT类型语句的这类语句在INNODB层和MYSQL层进行数据交互的时候一个统称所以出现它的可能包含:确实需要访问数据量特别大可能需要优化。由于INNODB 层的获取row lock需要等待比如我们常见的SELECT FOR UPDATE。同时我们还需要注意在RR模式下SELECT B这一部分加锁方式和INSERT...SELECT是一致的参考不再赘述从他反应的情况因为他在最后杀掉了一个长期的未提交的事物所以他因为是情况2。并且整个CREATE TABLE A AS SELECT B语句由于B表上某些数据库被上了锁而不能获取导致整个语句处于sending data状态下。二、关于SHOW TABLE STATUS[like A] Waiting for table metadata lock的分析这是本案例中最重要的一环SHOW TABLE STATUS[like A]居然被堵塞其STATE为Waiting for table metadata lock并且注意这里是table因为MDL LOCK类型分为很多。我在MDL介绍的那篇文章中提到了desc 一个表的时候会上MDL_SHARED_HIGH_PRIO(SH)其实在SHOW TABLE STATUS的时候也会对本表上MDL_SHARED_HIGH_PRIO(SH)。方式一方式二两种方式都能观察到MDL_SHARED_HIGH_PRIO(SH)的存在并且我模拟的是处于堵塞情况下的。但是MDL_SHARED_HIGH_PRIO(SH) 是一个优先级非常高的一个MDL LOCK类型表现如下兼容性阻塞队列优先级其被堵塞的条件除了被MDL_EXCLUSIVE(X)堵塞没有其他的可能。那么这就是一个非常重要的突破口。三、关于CREATE TABLE A AS SELECT B 对A表的加MDL LOCK的分析这一点也是我以前不知道的,也是本案列中花时间最多的地方,前文已经分析过要让SHOW TABLE STATUS[like A]这种只会上MDL_SHARED_HIGH_PRIO(SH) MDL LOCK的语句堵塞在MDL LOCK上只有一种可能那就是A表上了MDL_EXCLUSIVE(X)。那么我开始怀疑这个DDL语句在语句结束之前会对A表上MDL_EXCLUSIVE(X) 然后进行实际测试不出所料确实是这样的如下方式一方式二这里比较遗憾在performance_schema.metadata_locks中并没有显示出MDL_EXCLUSIVE(X)而显示为MDL_SHARED(S)是我们在我输出的日志中可以看到这里做了升级操作将MDL_SHARED(S) 升级为了MDL_EXCLUSIVE(X)。并且由前面的兼容性列表来看只有MDL_EXCLUSIVE(X)会堵塞MDL_SHARED_HIGH_PRIO(SH)。所以我们应该能够确认这里确实做了升级操作否则SHOW TABLE STATUS[like A] 是不会被堵塞的。四、关于SELECT * FROM A Waiting for table metadata lock的分析也许大家认为SELECT不会上锁但是那是在innodb 层次在MYSQL层会上MDL_SHARED_READ(SR) 如下方式一方式二可以看到确实有MDL_SHARED_READ(SR)的存在当前处于堵塞状态其兼容性如下显然MDL_SHARED_READ(SR) 和MDL_SHARED_HIGH_PRIO(SH)是不兼容的需要等待。五、关于DROP TABLE A Waiting for table metadata lock的分析这一点很好分析因为A表上了X锁而DROP TABLE A必然上MDL_EXCLUSIVE(X)锁它当然和MDL_EXCLUSIVE(X)不兼容。如下方式一方式二其中EXCLUSIVE就是我们说的MDL_EXCLUSIVE(X)它确实存在当前处于堵塞六、为何use db也会堵塞如果使用mysql客户端不使用-A选项(或者 no-auto-rehash)在USE DB的时候至少要做如下事情1、 对db下每个表上MDL (SH) lock如下(调用MDL_context::acquire_lock 这里给出堵塞时候的信息)方式一方式二可以看到USE DB确实也因为MDL_SHARED_HIGH_PRIO(SH) 发生了堵塞。2、对每个表加入到table cache并且打开表(调用open_table_from_share())那么这种情况就和SHOW TABLE STATUS[like A]被堵塞的情况一模一样了也是由于MDL 锁不兼容造成的。分析梳理有了前面的分析那么我们可以梳理这个故障发生的原因如下有一个在B表上长期未提交的DML语句会在innodb层对B表某些数据加innodb row lock。由步骤1引起了CREATE TABLE A AS SELECT B的堵塞因为RR模式下SELECT B必然对B表上满足的数据上锁因为步骤1已经加锁所以触发等待STATE为sending data。由步骤2引起了其他语句的堵塞因为CRATE TABLE A AS SELECT B在A表建立完成之前会上MDL_EXCLUSIVE(X),这把锁会堵塞其他全部的关于A表的语句包括DESC/SHOW TABLE STATUS/USE DB(非-A) 这种只上MDL_SHARED_HIGH_PRIO(SH)MDL LOCK 的语句。STATE统一为Waiting for table metadata lock。模拟测试测试环境5.7.14GITD关闭RR隔离级别使用脚本步骤如下session1session2session3session4------use test;---use test;begin; delete from b;------------use test;create table a asselect * from b;(由于b表innodb row lock堵塞)------------show table status like a;(由于a表MDL LOCK堵塞)------------use test(由于a表MDL LOCK堵塞)最后我们看到的等待状态如下这样我们就完美的模拟出线上的状态如果我们杀掉session1中的事物自然就全部解锁了让我们再来看一下performance_schema.metadata_locks中的输出我们可以看到如上的输出但是需要注意LOCK_TYPE: SHARED它不可能堵塞LOCK_TYPE: SHARED_HIGH_PRIO(可以参考附录或者我以前写的MDL LOCK分析的文章)如上文分析这里实际上是做了升级操作升级为了MDL_EXCLUSIVE(X)。总结RC模式下虽然CREATE TABLE A SELECT B中B表不会上任何INNODB ROW LOCK但是如果B表非常大那么A表也会处于MDL_EXCLUSIVE(X)保护下因此也会触发USE DB\SHOW TABLE STATUS等待的情况。如果打开GTID不能使用CREATE TABLE A SELECT B这样的语句。对于DML/DDL混用的系统一定要注意并发就像本例中如果注意到高并发下的情况可以想办法避免。这个案列再次说明了长期不提交的事物可能引发悲剧所以建议监控超过N秒没结束的事务。附录MDL LOCK TYPE兼容性矩阵等待队列优先级矩阵本文标题: Mysql解决USE DB堵塞详解本文地址: http://www.cppcns.com/shujuku/mysql/215579.html
