MySQL45讲之查询慢或者阻塞
前言
本文介绍锁表和执行慢的例子，以及发生锁表时的排查方法。
锁表
1. 等MDL锁
比如像 select * from t where id=1; 长时间不返回，可能是因为等 MDL 锁而阻塞。
排查方法：
使用 show processlist; 查看线程状态
由上图可知，其他线程正在表上请求或者持有 MDL 写锁，所以阻塞了 select 获取 MDL 读锁。
如果想模拟获取 MDL 写锁，可以通过
lock table t write;实现。
解决办法：
只需要找到阻塞 select 获取 MDL 读锁的线程，kill 掉就可以了。因为 MDL 是表级锁，所以查询 sys 表的 schema_table_lock_waits 字段，就可以得到阻塞的 process id。
2. 等待flush
MySQL里面对表做flush操作的用法，一般有以下两个：
# 关闭表 t
flush tables t with read lock;
# 关闭所有打开的表
flush tables with read lock;
等待 flush 表示一个线程正要对表做 flush 操作，但是被其他线程阻塞，而 flush 操作又会阻塞后来的 select 查询操作。
排查方法：
很简单，通过 show processlist; 就可以发现。
3. 等行锁
如上图，session A 对 id=1 的行上了行锁，且事务一直没有提交，导致阻塞 session B 对该行执行当前读。
排查方法：
同样通过 show processlist; 方法排查。
其中，id=8 行就是被阻塞的查询，线程被阻塞状态为 statistics。
解决办法：
同样是找到阻塞的线程 id，然后 kill 掉。行锁是 innodb 引擎提供的，通过查询 sys 表的 innodb_lock_waits 行可以得到 process id。
执行慢
session A 中第二条查询语句比第一条查询快很多。因为第二条加锁查询是当前读，直接获取当前行数据；第一条查询是快照读，因为 session B 在之前对 id=1 的行更新了 100 万次，所以需要根据 undo log 日志对当前行执行 100 万次回滚操作。
总结
对于阻塞问题的排查，使用 show processlist;。对于阻塞问题的解决，通过在 sys 表中查询阻塞线程的 process id，比如 schema_table_lock_waits、innodb_lock_waits。
提问
存在索引的字段进行当前读，我们知道会在索引树上对符合的行上锁，那对于 select * from t where c = 5 for update; （字段 c 不存在索引）语句如何上锁，什么时候释放锁？
回答：
（1）提交读 rc 隔离级别下
因为字段 c 不存在索引，所以进入存储引擎后，会在主键索引树上对全表加排他锁。在此优化器做了一些优化，返回 MySQL server 后，会判断出不符合条件的行，即 c != 5 的行，释放这些行上的排他锁。最后，在事务提交的时候释放锁。
所以，在一个事务执行完这条 SQL 后，在另一个事务可以对 c != 5 的行进行更新和删除操作，并且可以在任何位置执行插入操作（因为 rc 模式下，没有使用间隙锁）。
（2）可重复读 rr 隔离级别下
因为字段 c 不存在索引，所以进入存储引擎后，会在主键索引树上对全表加排他和间隙锁。最后，在事务提交的时候释放锁。
所以，在一个事务执行完这条 SQL 后，在另一个事务对全表任何一行的更新操作都需要等待，在全表任何位置的插入操作也需要等待。
