在MySQL中,有些情况下仅仅查询一条语句,查询的过程也会非常慢,有时候还会出现不返回的情况,今天我们来分析可能造成这个现象的场景。
先复习一下MDL锁的概念,当我们对一个表进行增删改查的时候,MySQL会自动为这个表加上MDL读锁,当我们对表进行结构变更的时候,MySQL会自动为这个表加上MDL写锁,MDL锁是表级别的锁,MDL读锁直接不互斥,它的存在是为了防止一个线程进行读取表的时候,其他线程对该表进行变更操作,导致读取到的数据和真实表结构对不上。
当我们执行一条SQL的时候,如果半天没有返回执行结果,这个时候,建议你再另外一个会话里面执行show processlist命令,查看是否出现"waiting for table metadata lock"字样,出现这个字样,表示,有一个查询的线程正在表上持有MDL写锁,MDL写锁会导致select语句堵住。
解决方案:
查询sys.schema_table_lock_waits表,该表中可以查询到阻塞当前查询的线程id,我们用kill + pid的方法直接结束这个线程就可以恢复正常的查询。
当我们对某个表执行flush的时候,会执行诸如flush table t with read lock,正常情况下,这个语句的执行时间比较短,当在某种极端情况下,该语句被其他语句阻塞了,我们在另外的会话窗口就会看到"waiting for table flush"字样,例如:
会话1执行select sleep(1) from t;此时该条SQL每次取到表t的值时,都会执行1s,当表t的数据量比较多的时候,此时在会话2上执行flush table t with read lock就会堵住,会话2堵住的情况下,当我们要在会话3上执行select操作的时候,会话3就会接着堵住,show processlist的结果就会出现waiting for table flush字样。
解决方案:同场景一
当我们在会话1上执行:
begin;
update t set a=a+1 where id=1;
此时在会话2上执行:
select * from t where id=1 lock in share mode;
那么由于产生了id=1这一行的行锁争用,因此会话2上的SQL会被堵住。
解决方案:
可以通过sys.innodb_lock_waits表来查看当前堵住查询的线程pid,然后通过kill pid的方法来杀掉阻塞的线程。该命令会直接断开该线程的连接,会直接回滚线程里面的操作,也就释放了id=1上面的行锁。
当我们在会话1上执行:
begin;
此时在会话2上,执行:
update t set a=a+1 where id=1;
将这条语句执行10w次
再回到会话1上执行:
select * from t;
select * from t lock in share mode;
由于会话2的update操作会产生大量的undo log,此时会话1上先执行快照读,再执行当前读,那么执行快照读的时候,会从当前的真实值开始,查找undo log的历史版本链表,先前查找10w次,最终才会返回1这个数值。而执行当前读(lock in share mode)的时候,会直接返回100001这个值。因此会出现快照读比当前读还慢的情况(正常情况下,快照读的返回因为不牵扯锁争用,会快于当前读)。