这次咱们来聊聊 MySQL 的锁。很多同学一听到锁就头大其实只要按作用范围把它们分好类理清各自的应用场景一切就会豁然开朗。为了让大家看得不那么枯燥这篇博客我们直接采用 QA 的形式直击痛点。不多 BB 了发车在 MySQL 里根据加锁的范围锁大致可以分为三类全局锁、表级锁和行级锁。一、 全局锁Q1全局锁是怎么用的要给整个数据库加上全局锁只需要执行这条命令SQLflush tables with read lock执行之后整个数据库就进入了只读状态。这时候如果有其他线程想执行以下操作都会被无情阻塞对数据的增删改比如insert、delete、update。对表结构的更改比如alter table、drop table。要释放全局锁执行这条命令即可当然如果你当前会话断开了全局锁也会自动释放SQLunlock tablesQ2全局锁的应用场景是什么全局锁主要用于全库逻辑备份。这样能保证在备份期间不会因为数据的更新导致备份出来的文件跟预期不一致。举个很现实的例子假设你在备份期间不加锁。用户买了一件商品业务逻辑是1. 扣减用户余额2. 扣减商品库存。 如果备份顺序刚好是先备份了用户表 - 用户发起购买 - 再备份商品表。 结果就是备份文件里用户的钱没扣但商品的库存却少了。等以后用这个备份文件恢复数据时用户相当于白嫖了一件商品这老板不得气死加上全局锁整个库不让改就不会出现这种时间差导致的不一致了。Q3加全局锁会带来什么大坑有替代方案吗缺点很明显加上全局锁意味着整个库在此期间只能读、不能写业务直接大面积停滞。如果你的库有几百 GB备份要几个小时这段时间业务直接瘫痪。有什么优雅的替代方案吗有的前提是你的存储引擎比如 InnoDB支持可重复读Repeatable Read的事务隔离级别。 在使用官方备份工具mysqldump时加上-single-transaction参数。它会在备份前开启一个事务利用MVCC多版本并发控制生成一个 Read View。整个备份期间都在读这个旧版本的视图而其他业务线程依然可以正常对数据进行更新操作互不干扰。注意对于 MyISAM 这种不支持事务的古老引擎想备份保证一致性就只能乖乖用全局锁了。二、 表级锁MySQL 里面的表级锁主要分为四种表锁、元数据锁MDL、意向锁、AUTO-INC 锁。1. 表锁Q1表锁怎么加比如我们想对学生表t_student加锁SQL-- 表级别的共享锁读锁 lock tables t_student read; -- 表级别的独占锁写锁 lock tables t_student write;释放的话同样也是unlock tables或者等会话断开。需要特别注意的是表锁不仅限制别的线程还会限制你自己如果你的线程对表加了“共享读锁”那么你自己接下来想写这张表也会被报错阻止。 在 InnoDB 引擎下千万别傻乎乎地去用表锁它的颗粒度太大了严重影响并发。InnoDB 的杀手锏是下面要讲的行锁。2. 元数据锁MDLQ2什么是 MDL我需要手动加吗不需要显示调用MySQL 会自动帮你加。当你对表执行 CRUD增删改查操作时会自动加MDL 读锁。当你对表结构进行变更比如加个字段、删个索引时会自动加MDL 写锁。它的作用是保证读写隔离当有人在查表的时候防止另一个愣头青突然把表结构给改了比如删了个列。Q3MDL 锁什么时候释放长事务为什么会导致数据库崩溃MDL 锁要等到事务提交后才会释放。这就引出了生产环境一个极其经典的血案长事务阻塞导致线程池爆满。场景是这样的线程 A 开启了事务执行了select拿到 MDL 读锁但一直没提交长事务。线程 B 正常select没问题因为读读不冲突。线程 C 想修改表结构alter table需要申请 MDL 写锁。但因为 A 的读锁还没释放C 被阻塞进入等待队列。恐怖的事情来了因为写锁的优先级高于读锁一旦 C 在排队等写锁后续所有想要查询这张表申请读锁的普通select请求全部会被 C 挡在门外全部阻塞如果这是一个高频查询的表几秒钟内就会堆积成千上万个阻塞线程数据库瞬间被打挂。避坑指南在执行 DDL表结构变更之前一定要先查一下有没有长事务在跑如果有可以考虑先把它kill掉再改表结构。3. 意向锁Intention LockQ4为什么要有意向锁当你在 InnoDB 里对某行记录加锁之前引擎会先在“表级别”加一个对应的“意向锁”。准备加行级共享锁 - 先加表级意向共享锁 (IS)。准备加行级独占锁 - 先加表级意向独占锁 (IX)。它的核心目的只有一个为了快速判断表里有没有行锁。假设没有意向锁现在有人想对这张表加“独占表锁”他必须一行一行去遍历第一行有锁吗第二行有锁吗…… 效率极低。 有了意向锁之后他只要看一眼表上有没有“意向锁”。如果有说明表里肯定有某行被锁了直接等待即可省去了遍历所有记录的麻烦。(注意向锁之间完全不冲突它只和表锁冲突不和行锁冲突。)4. AUTO-INC 锁Q5自增主键底层的 AUTO-INC 锁是怎么玩的这是一种特殊的表级锁用于保证AUTO_INCREMENT字段递增的连续性。 传统的 AUTO-INC 锁是在执行插入语句时加上插入语句执行完立马释放不用等事务提交。但如果是批量插入这个表锁依然会严重影响其他事务的插入并发度。后来 InnoDB 引入了更优的轻量级锁申请完自增 ID 后立马释放锁根本不等语句执行完。 这由参数innodb_autoinc_lock_mode控制 0用传统的 AUTO-INC 锁。 1普通插入用轻量级锁批量插入用 AUTO-INC 锁。 2全部用轻量级锁性能最高。注意坑点如果你用了mode 2并且 binlog 的格式是statement记录原始 SQL在主从复制时可能会出现主库和从库生成的自增 ID 顺序不一致的问题。最佳实践是mode 2搭配binlog_format row既能火力全开保证高并发又能保证主从数据绝对一致。三、 行级锁行级锁是 InnoDB 的独门绝技MyISAM 不支持。普通的select是快照读不加锁。如果你想显式加行锁可以这样称为锁定读SQL-- 加共享锁 (S锁) select ... lock in share mode; -- 加独占锁 (X锁) select ... for update;(必须在开启事务的前提下使用)行级锁主要有三类1. Record Lock记录锁仅仅锁住一条记录。 S 锁和 S 锁兼容X 锁和谁都互斥写写互斥、读写互斥。 比如select * from t where id 1 for update就给 id1 的这行加上了 X 型记录锁。2. Gap Lock间隙锁只存在于可重复读Repeatable Read隔离级别用来解决幻读问题。 它不锁具体的记录而是锁住两个记录之间的“间隙”。 比如给 (3, 5) 加上了间隙锁这时候别人想insert一条 id 4 的数据直接被阻塞。(注间隙锁之间是完全兼容的无论是 X 还是 S 型大家都可以给同一个间隙加锁因为它们的目的都是一样的——防止别人插数据进来。)3. Next-Key Lock临键锁这是 InnoDB 默认的行锁基本单位。它就是Record Lock Gap Lock 的终极合体。 它既锁住范围又锁住记录本身。比如范围是(3, 5]的 Next-Key Lock它不仅不让别人往 3 到 5 之间插数据连 id5 这条记录本身也给你锁死别人改不了。它既保护了现有数据又防住了幻读。4. 插入意向锁Insert Intention Lock名字里有“意向锁”但它不是表锁而是一种特殊的间隙锁属于行级。 当一个事务想要插入新数据发现这个位置已经被别人加了间隙锁或 Next-Key Lock它就会阻塞等待并生成一个“插入意向锁”。 它锁住的其实是一个点代表“我想在这里插数据但我现在只能排队”。不同事务在同一个间隙内插入不同的位置生成的插入意向锁彼此不冲突。