MySQL 锁机制详解｜表锁、行锁、意向锁，死锁成因与解决方案

前言MySQL 锁是并发场景下数据库控制资源竞争的核心机制。很多开发写 SQL 的时候经常遇到事务卡住、更新超时、死锁报错但是不知道根源是锁。 很多人混淆 MyISAM 和 InnoDB 锁差异分不清行锁、表锁、意向锁尤其对临键锁Next-Key Lock和死锁理解模糊。核心前提MyISAM只支持表级锁不支持事务不支持行锁读锁共享写锁互斥。InnoDB支持事务支持行级锁 表级意向锁是我们生产环境主流引擎。 InnoDB 锁是基于索引实现不走索引行锁会升级为表锁坑点。一、锁粒度表锁 vs 行锁1.1 MyISAM 表锁表级锁对整张表加锁。分为共享读锁S 锁、排他写锁X 锁读锁S多个会话可以同时加读锁读的时候阻塞写写锁X写锁独占写阻塞读、写阻塞写特点开销小加锁快无死锁 锁粒度大并发差。只要一条记录写操作整张表被锁住。适用只读报表、静态数据生产业务几乎不用 MyISAM。1.2 InnoDB 行锁行级锁锁粒度是单行记录只锁定访问的那一行。 同样分为共享锁 S读锁多个事务可以同时加 S 锁S 和 S 兼容S 与 X 互斥select * from user where id 1 lock in share mode; -- 手动加共享锁排他锁 X写锁事务修改数据自动加 X 锁X 锁和任何锁都互斥select * from user where id 1 for update; -- 手动加排他锁 update user set namexxx where id1; -- update/delete自动加X锁优点粒度小并发高只锁目标行缺点加锁开销大会产生死锁重点坑行锁是基于索引实现如果 where 条件没有命中索引InnoDB 无法定位行会退化成表锁1.3 InnoDB 意向锁Intention Lock【高频】意向锁属于表级锁意向锁之间互相兼容意向锁和真实 S/X 锁互斥。 分为意向共享锁 IS事务打算给表里面某行加 S 锁先给表加 IS 锁意向排他锁 IX事务打算给表里面某行加 X 锁先给表加 IX 锁意向锁作用提升表锁判断效率如果没有意向锁当事务想要加表锁时需要遍历表中所有行判断是否有行锁 有了意向锁只需要判断表上有没有意向锁快速判断能不能加表锁。意向锁兼容矩阵S表共享X表排他ISIXS兼容互斥兼容互斥X互斥互斥互斥互斥IS兼容互斥兼容兼容IX互斥互斥兼容兼容意向锁不需要手动操作InnoDB 自动维护。二、InnoDB 行锁细分记录锁、间隙锁、临键锁Next-Key LockInnoDB 默认隔离级别RR可重复读为了解决幻读问题使用临键锁。RC读已提交隔离级别没有间隙锁只有记录锁但会出现幻读。2.1 记录锁 Record Lock锁定索引中具体的某一行记录。主键 / 唯一索引等值查询命中存在数据时加记录锁。where id 10id 为主键且存在这条记录锁定 id10 这一行。2.2 间隙锁 Gap Lock锁定索引记录之间的间隙不包含记录本身防止其他事务在间隙插入数据解决幻读。等值查询但是这条记录不存在会加间隙锁。 例id 主键表中有 1,5,10查询 where id7不存在则锁住 (5,10) 这个间隙其他事务无法插入 id7。2.3 临键锁 Next-Key LockRR 默认行锁算法临键锁 记录锁 间隙锁左开右闭区间。是 RR 隔离级别默认加锁算法。 索引1,5,10 Next-Key 区间(-∞,1], (1,5], (5,10], (10,∞) 查询 id5临键锁区间 (1,5]锁住 5 这条记录 1~5 之间的间隙。等值查询命中唯一索引存在数据临键锁退化成记录锁 等值查询记录不存在临键锁退化成间隙锁。幻读同一个事务内相同查询前后读到行数不一样别的事务插入新数据。RR 依靠临键锁解决幻读。三、死锁3.1 死锁四大必要条件缺一不可互斥条件资源排他一个资源同一时间只能被一个事务持有锁互斥特性请求与保持条件事务已经持有锁同时请求获取新锁不释放已有锁不可剥夺条件已经获取的锁不能被其他事务强行抢占只能自己释放循环等待条件多个事务形成锁等待循环链事务 A 等 B 的锁B 等 CC 等 A四个条件同时满足死锁产生破坏任意一个条件就可以解除死锁。3.2 死锁示例事务 1begin; update user set namea where id1; -- 获取id1行X锁 update user set nameb where id2; -- 请求id2锁事务 2begin; update user set nameb where id2; -- 获取id2行X锁 update user set namea where id1; -- 请求id1锁事务 1 持有 id1等 id2事务 2 持有 id2等 id1 → 循环等待死锁。InnoDB 会自动检测死锁回滚代价较小的事务抛出Deadlock found when trying to get lock。3.3 如何定位死锁开启死锁日志查看show engine innodb status;找到LATEST DETECTED DEADLOCK查看死锁事务、锁、SQL。3.4 死锁规避方案生产实践统一资源访问顺序所有事务更新多行都按固定顺序更新先 id1 再 id2破坏循环等待条件。最有效事务尽量简短尽早提交减少锁持有时间避免大事务不要在事务中做耗时操作尽量使用主键 / 唯一索引更新缩小锁范围不走索引会锁整张表避免一次性锁多条记录业务层增加重试机制捕获死锁异常重试。四、锁等待超时死锁是循环等待InnoDB 自动检测回滚 锁等待超时单纯拿不到锁没有循环等待。参数innodb_lock_wait_timeout默认 50 秒。超过时间直接报错。五、RC 和 RR 隔离级别锁差异总结隔离级别锁机制幻读RC 读已提交只有记录锁无间隙锁存在幻读RR 可重复读默认 Next-Key 临键锁包含记录 间隙锁解决幻读RC 并发性能略高但是存在幻读MySQL 默认 RR。六、生产环境锁优化最佳实践更新语句一定要走索引避免行锁升级表锁update 条件字段必须建索引事务尽量小而短不要长事务长事务会长时间占用锁增加锁冲突select 普通查询不加锁快照读for update/lock in share mode 是当前读会加锁尽量减少事务内锁的行数统一更新顺序规避死锁监控锁等待、死锁次数、长事务告警。快照读 vs 当前读 普通 select 是快照读读取 undo 日志版本不加行锁 update、delete、for update、lock in share mode 是当前读读取最新数据加行锁。七、面试高频问答Q1InnoDB 行锁是锁行还是锁索引A锁索引行锁是加在索引上如果 SQL 没有索引无法定位行退化成表锁。Q2意向锁作用意向锁之间互斥吗A意向锁是表级锁标记表内存在行锁意向锁之间互相兼容意向锁和表 S/X 锁互斥。Q3临键锁是什么解决什么问题ANext-Key 临键锁 记录锁 间隙锁RR 隔离级别默认锁算法用来解决幻读。Q4死锁四个必要条件怎么避免死锁A互斥、请求保持、不可剥夺、循环等待破坏任意一个就不会死锁。最常用统一资源访问顺序破坏循环等待。Q5RC 和 RR 在锁上最大区别ARC 没有间隙锁只有记录锁会出现幻读RR 有间隙锁 临键锁防止幻读。Q6select * from table where id1 会加行锁吗A普通 select 快照读不加锁select ... for update /lock in share mode 当前读才加锁。结语MySQL 锁机制是并发数据库核心难点非常喜欢深挖。重点记住MyISAM 只有表锁InnoDB 行锁基于索引RR 下临键锁防幻读意向锁是表锁用于快速判断锁冲突死锁四大条件统一访问顺序是解决死锁最有效的手段。写业务 SQL 的时候一定要思考这条 SQL 会加什么锁锁范围多大防止并发场景锁等待、死锁故障。