- 有个表 t:
mysql> CREATE TABLE
t(
idint(11) NOT NULL,
cint(11) DEFAULT NULL, PRIMARY KEY (
id)) ENGINE=InnoDB;
第一类:查询长时间不返回
等 MDL 锁
-
使用 show processlist 命令查看 Waiting for table metadata lock 的示意图
-
出现这个状态表示的是,现在有一个线程正在表 t 上请求或持有 MDL 写锁,把 select 语句堵住了
-
简单的复现步骤
-
处理方式:找到谁持有 MDL 写锁,kill 掉
-
但是,由于在 show processlist 的结果里面,session A 的 Command 列是“Sleep”,导致查找起来很不方便。不过有了 performance_schema 和 sys 系统库以后,就方便多了。(MySQL 启动时需要设置 performance_schema=on,相比于设置为 off 会有 10% 左右的性能损失)
-
通过查询 sys.schema_table_lock_waits 这张表,我们就可以直接找出造成阻塞的 process id,把这个连接用 kill 命令断开即可。
等 flush
-
mysql> select * from information_schema.processlist where id=1;
-
Waiting for table flush 状态示意图
-
现在有一个线程正要对表 t 做 flush 操作。MySQL 里面对表做 flush 操作的用法,一般有以下两个:
-
flush tables t with read lock;
-
flush tables with read lock;
-
如果指定表 t 的话,代表的是只关闭表 t;如果没有指定具体的表名,则表示关闭 MySQL 里所有打开的表。
-
但是正常这两个语句执行起来都很快,除非它们也被别的线程堵住了。
-
所以,出现 Waiting for table flush 状态的可能情况是:有一个 flush tables 命令被别的语句堵住了,然后它又堵住了我们的 select 语句。
-
-
排查
-
图 7
-
这个例子的排查也很简单,你看到这个 show processlist 的结果,肯定就知道应该怎么做了。
-
等行锁
-
mysql> select * from t where id=1 lock in share mode;
-
由于访问 id=1 这个记录时要加读锁,如果这时候已经有一个事务在这行记录上持有一个写锁,我们的 select 语句就会被堵住。
-
行锁 show processlist
- session A 启动了事务,占有写锁,还不提交,是导致 session B 被堵住的原因。
-
MySQL 5.7 版本,可以通过 sys.innodb_lock_waits 表查到是谁占着行锁
mysql> select * from t sys.innodb_lock_waits where locked_table='
test.
t'\G
-
可以看到,这个信息很全,4 号线程是造成堵塞的罪魁祸首。而干掉这个罪魁祸首的方式,就是 KILL QUERY 4 或 KILL 4。不过,这里不应该显示“KILL QUERY 4”。这个命令表示停止 4 号线程当前正在执行的语句,而这个方法其实是没有用的。因为占有行锁的是 update 语句,这个语句已经是之前执行完成了的,现在执行 KILL QUERY,无法让这个事务去掉 id=1 上的行锁。实际上,KILL 4 才有效,也就是说直接断开这个连接。这里隐含的一个逻辑就是,连接被断开的时候,会自动回滚这个连接里面正在执行的线程,也就释放了 id=1 上的行锁。
第二类:查询慢
- 在连接后执行
set long_query_time=0
可以将慢查询日志的时间阈值设置为 0 - 一个例子
-
800 毫秒
mysql> select * from t where id=1
- id 上有索引
-
0.2 毫秒
mysql> select * from t where id=1 lock in share mode
-
提示
-
复现
-
带
lock in share mode
的 SQL 语句,是当前读,因此会直接读到 1000001 这个结果,所以速度很快;而select * from t where id=1
这个语句,是一致性读,因此需要从 1000001 开始,依次执行 undo log,执行了 100 万次以后,才将 1 这个结果返回。
-
思考题
-
begin;select * from t where c=5 for update;commit;
- c 上没索引
-
这个语句序列是怎么加锁的呢?
- RR 隔离级别下,为保证 binlog 记录顺序,非索引更新会锁住全表记录,且事务结束前不会对不符合条件记录有逐步释放的过程。
- 在 Read Committed 隔离级别下,会锁上聚簇索引中的所有记录;
- 在 Repeatable Read 隔离级别下,会锁上聚簇索引(主键索引)中的所有记录,并且会锁上聚簇索引内的所有 GAP(间隙锁);
-
加的锁又是什么时候释放呢?
- commit 的时候释放
- 在上面两个隔离级别的情况下,如果设置了 innodb_locks_unsafe_for_binlog 开启 semi-consistent read 的话,对于不满足查询条件的记录,MySQL 会提前放锁,不过加锁的过程是不可避免的。