数据库备份时大查询导致从库堵塞一例

数据库备份时大查询导致从库长时间堵塞

阻塞的原因分析
	1、数据库有t表慢查询（执行时间1000S）
		占用t表元数据锁
	2、数据库备份执行flush tables with read lock;
		flush table堵塞（等待慢查询结束进行close table）
		close table进入等待队列，后续对t表的增删改查全部进入等待队列
		
	3、从库SQL线程执行t表binlog
		加入t表等待队列（Slave_SQL_Running_State: Waiting for dependent transaction to commit //并行复制
						 Slave_SQL_Running_State: Waiting for global read lock //单线程复制）
	4、其他session增删改查t表
		加入t表等待队列（Waiting for table flush）
长时间原因分析：
	1、lock_wait_timeout设置过大，导致flush tables with read lock操作长时间未超时
	2、innodb_lock_wait_timeout设置过大，导致增删改查均长时间未超时

首先理解以下知识点：
lock_wait_timeout与innodb_lock_wait_timeout

lock_wait_timeout （元数据锁）
	适用于表，视图，存储过程和存储函数的DML和DDL操作，以及LOCK TABLE和具有READ LOCK的FLUSH TABLE
	DDL和DML、LOCK TABLE 、FLUSH TBALES WITH READ LOCK、FLUSH TABLE T WITH READ LOCK
innodb_lock_wait_timeout（innodb行锁）
	适用于InnoDB行锁,MySQL表锁不会在InnoDB内部发生，并且此超时不适用于等待表锁
		UPDATE t set name='liu' WHERE id =1;
二者锁超时报错均为：ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction	

元数据锁

锁的获取
	DML语句通常按语句中提到表的顺序获取锁
	DDL语句，LOCK TABLES和其他类似语句试图通过按表名称顺序获取表上的锁
锁的释放
	服务器不允许一个会话对在另一会话中未完成的显式或隐式启动的事务中使用的表执行DDL（如下）语句，执行堵塞（Waiting for table metadata lock）
		DROP TABLE t;
		ALTER TABLE t ...;
		LOCK TABLE t ... WRITE;
	在自动提交模式下，每个语句实际上是一个完整的事务，因此为该语句获取的元数据锁仅保留到该语句的末尾
		select、update、delete等
	未提交事物涉及到的表（包括语法有效，执行报错涉及到的表）类似对表进行（LOCK TABLE t ... READ）

FLUSH TABLE WITH READ LOCK详解：

FTWRL主要包括3个步骤：
	1.上全局读锁(lock_global_read_lock)
	2.清理表缓存(close_cached_tables)
	3.上全局COMMIT锁(make_global_read_lock_block_commit)
上全局读锁会导致所有更新操作都会被堵塞；
关闭表过程中，如果有大查询导致关闭表等待，那么所有访问这个表的查询和更新都需要等待；
上全局COMMIT锁时，会堵塞活跃事务提交。

清理表缓存   
	每个表在内存中都有一个table_cache，不同表的cache对象通过hash链表维护。
	访问cache对象通过LOCK_open互斥量保护，每个会话打开的表时，引用计数share->ref_count++，
	关闭表时，都会去对引用计数share->ref_count--。
	若发现是share对象的最后一个引用(share->ref_count==0)，并且share有old_version,则将table_def_cache从hash链表中摘除
close table流程如下：
	1.关闭所有未使用的表对象
	2.更新全局字典的版本号
	3.对于在使用的表对象，逐一检查，若表还在使用中，进入等待队列
	4.将等待对象关联到table_cache对象中
	5.继续遍历使用的表对象
	6.直到所有表都不再使用，则关闭成功。
	7.lock_wait_timeout秒未成功命令执行失败
会话操作表流程
	1.打开表操作，若发现还有old_version,则进行等待队列（lock_wait_timeout秒未成功命令执行失败）
	2.share->ref_count++
	3.操作完毕，检查share->ref_count--是否为0
	4.若为0,并且检查发现有新版本号，则认为cache对象需要重载
	5.将cache对象摘除，调用MDL_wait::set_status唤醒所有等待的线程。

元数据锁导致的准备切换问题：

在生产环境中，为了容灾一般mysql服务都由主备库组成，当主库出现问题时，可以切换到备库运行，保证服务的高可用。在这个过程中有一点很重要，避免双写。
因为导致切换的场景有很多，可能是因为主库压力过大hang住了，也有可能是主库触发mysql bug重启了等。
当我们将备库写开启时，如果老主库活着，一定要先将其设置为read_only状态。“set global read_only=1”这个命令实际上也和FTWRL类似，也需要上两把MDL，只是不需要清理表缓存而已。
如果老主库上还有大的更新事务，将导致set global read_only hang住，设置失败。因此切换程序在设计时，要考虑这一点。
关键函数：fix_read_only
	1.lock_global_read_lock(),避免新的更新事务，阻止更新操作
	2.make_global_read_lock_block_commit，避免活跃的事务提交