MySQL锁(二)表锁:为什么给小表加字段会导致整个库挂掉?

概述

表级锁是MySQL中锁定粒度最大的一种锁,表示对当前操作的整张表加锁,它实现简单,资源消耗较少,被大部分MySQL引擎支持。最常使用的MYISAM与INNODB都支持表级锁定。表级锁定分为表共享读锁(共享锁)与表独占写锁(排他锁)。

特点:开销小,加锁快;不会出现死锁;锁定粒度大,发出锁冲突的概率最高,并发度最低。

MySQL 中表级锁有两种:一种是表锁,另一种是元数据锁(meta data lock, MDL)。

表锁

表锁的语法是 lock tables...read/write。与 FTWRL 类似,可以使用 unlock tables 主动释放锁,或者在客户端断开链接时自动释放。

在还没有出现更细粒度的锁之前,表锁是最常用于处理并发的。而对 InnoDB 这种支持行锁的引擎,一般不使用 lock tables 命令来控制并发。

MDL

另一类表级的锁是MDL。MDL不需要显式使用,在访问一个表的时候会被自动加上。

在MySQL 5.5版本中引入MDL,当对一个表做 CURD 时,加 MDL 读锁;当要对表做 DDL 时,加 MDL 写锁;

  • 读锁之间不互斥。
  • 读写锁之间、写锁之间互斥。

有时候给一个小表加个字段,可能会导致整个库挂掉。假设表 t 是一个小表。

MySQL锁(二)表锁:为什么给小表加字段会导致整个库挂掉?

图1 给表 t 加字段

会话A加了 MDL 读锁;会话B也加了 MDL 读锁,可以正常执行;会话C被阻塞,等待会议A释放 MDL 读锁。

如果只是会话C被阻塞倒没关系,但之后所有在表 t 上新申请MDL 读锁都会被会议C阻塞。也就是这个表现在完全不能读写了。

如何安全地给小表加字段?

很简单,在 ALTER TABLE 加等待时间。如果超过等待时间后,操作回滚。

MariaDB 已经合并了 AliSQL 的这个功能,所以这两个开源分支目前都支持 DDL NOWAIT/WAIT n 这个语法。

ALTER TABLE tbl_name NOWAIT add column ...
ALTER TABLE tbl_name WAIT N add column ... 

参考资料

发表评论

相关文章