MySQL45讲之查询慢或者阻塞flowers

database

本文介绍锁表和执行慢的例子,以及发生锁表时的排查方法。

前言

本文介绍锁表和执行慢的例子,以及发生锁表时的排查方法。

锁表

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_waitsinnodb_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 后,在另一个事务对全表任何一行的更新操作都需要等待,在全表任何位置的插入操作也需要等待。

参考

  • [1] 为什么我只查一行的语句,也执行这么慢

以上是 MySQL45讲之查询慢或者阻塞flowers 的全部内容, 来源链接: utcz.com/z/535955.html

回到顶部