为了便于描述,我还是构造一个表,基于这个表来说明今天的问题。这个表有两个字段id和c, 并且我在里面插入了10万行记录。
CREATE TABLE `t` (
`id` int(11) NOT NULL,
`c` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
delimiter ;;
create procedure idata()
begin
declare i int;
set i=1;
while(i<=100000)do
insert into t values(i,i)
set i=i+1;
end while;
end;;
delimiter ;
call idata();
1.第一类:查询长时间不返回
select * from t where id=1;
查询长时间不返回
一般碰到这种情况的话,大概率是表t被锁住了。接下来分析原因的时候,一般都是首先执行一
下show processlist命令,看看当前语句处于什么状态。
然后我们再针对每种状态,去分析它们产生的原因、如何复现,以及如何处理。
(1)等MDL锁
出现这个状态表示的是,现在有一个线程正在表t上请求或者持有MDL写锁,把select语句
堵住了。这类问题的处理方式,就是找到谁持有MDL写锁,然后把它kill掉。
但是,由于在show processlist的结果里面,session A的Command列是“Sleep”,导致查找起来 很不方便。不过有了performance_schema和sys系统库以后,就方便多了。(MySQL启动时需 要设置performance_schema=on)
通过查询sys.schema_table_lock_waits这张表,我们就可以直接找出造成阻塞的process id,把 这个连接用kill 命令断开即可。
(2)等flush
接下来,我给你举另外一种查询被堵住的情况。
我在表t上,执行下面的SQL语句:
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;
这两个flush语句,如果指定表t的话,代表的是只关闭表t;如果没有指定具体的表名,则表示关 闭MySQL里所有打开的表。
但是正常这两个语句执行起来都很快,除非它们也被别的线程堵住了。
所以,出现Waiting for table flush状态的可能情况是:有一个flush tables命令被别的语句堵住了,然后它又堵住了我们的select语句。
(3)等行锁
select * from t where id=1 lock in share mode;