两个用户同时修改同一条订单数据其中一个请求直接报错Lock wait timeout exceeded线上突然大量请求超时排查后发现是一条UPDATE语句没走索引把整张表都锁了执行ALTER TABLE加索引的时候卡住所有DML请求全部堆积数据库直接雪崩这些问题的核心都和MySQL的锁机制有关。今天我们就把MySQL的锁从原理到实战一次性讲透帮你避开线上90%的锁相关的坑。一、MySQL锁到底在解决什么问题想象你是一个图书馆管理员书架上有1000本书两个人同时借同一本书如果没有规则系统无法判断到底借给谁 → 这就是并发写冲突MySQL用排他锁X锁解决谁先拿到锁谁先操作另一个人等着读者正在看书的时候管理员想撕掉其中一页重新印 → 这就是读写冲突MySQL用共享锁S锁解决看书的人可以加共享锁多个人可以同时看但管理员要改内容必须等所有人都看完你正在整理科幻区第3排到第7排的书有人想往第5排塞一本新书进去你还没整理完他塞进去你就找不到了 → 这就是幻读问题MySQL用间隙锁Gap Lock解决锁定这段区间不允许别人在中间插入新的东西二、MySQL锁的全景分类MySQL的锁可以按粒度分为表级锁、行级锁、意向锁RR隔离级别下还有专门解决幻读的间隙锁和临键锁另外还有容易被忽略的MDL元数据锁。1. 表级锁锁住整张表表共享读锁LOCK TABLE xxx READ多个事务可以同时加读锁有读锁时其他事务不能写表排他写锁LOCK TABLE xxx WRITE只有一个事务持有其他事务读写都阻塞适用场景MyISAM引擎默认用表锁InnoDB一般不会手动用表锁粒度太大并发太差2. 行级锁InnoDB的核心能力行锁是InnoDB特有的能力只锁被操作的数据行并发度高。但很多人不知道的是InnoDB的行锁是加在索引上的而不是数据行上这个特性决定了行锁的生效条件只有SQL走了索引行锁才会生效如果没走索引行锁会退化成表锁这是线上事故的高发点。行锁按功能分为两类共享锁S锁读锁多个事务可以同时加S锁有S锁时其他事务不能加X锁修改数据。手动加锁方式SELECT ... LOCK IN SHARE MODE排他锁X锁写锁当前事务独占其他事务不能加S锁也不能加X锁读写都阻塞。UPDATE、DELETE、INSERT会自动加X锁手动加锁方式SELECT ... FOR UPDATE3. 意向锁InnoDB的“导航系统”意向锁是表级锁InnoDB自动加不需要手动干预分两种意向共享锁IS事务准备给表中某些行加S锁先给表加IS意向排他锁IX事务准备给表中某些行加X锁先给表加IX意向锁的作用是什么假设没有意向锁当你想给整张表加写锁的时候系统需要遍历表中每一行检查有没有行锁在千万级记录的表里这个开销是不可接受的。有了意向锁之后只需要检查表上有没有冲突的意向锁就行比如表上有IX锁就说明有事务正在给行加X锁不能加表写锁大幅提高了锁检查的效率。4. RR隔离级别特有解决幻读的锁间隙锁Gap Lock锁住索引记录之间的间隙不锁记录本身防止其他事务在间隙中插入新数据。只存在于RR隔离级别RC隔离级别没有间隙锁临键锁Next-Key Lock记录锁间隙锁的组合左开右闭区间锁住当前记录的同时锁住前面的间隙是RR隔离级别下InnoDB的默认加锁行为。当查询命中唯一索引做等值匹配时临键锁会退化成普通记录锁减小锁定范围5. MDL元数据锁容易被忽略的坑MySQL5.6之后引入访问表时自动获取MDL读锁执行增删改查DML时自动加多个事务可以共享MDL写锁执行ALTER TABLE等DDL时自动加排他锁经典坑点一个长事务持有MDL读锁一直没提交此时执行ALTER TABLE会被阻塞后续所有对该表的DML请求全部堆积直接导致数据库雪崩。三、行锁的加锁规则详解假设表users有主键索引id现有数据id 5, 10, 15在RR隔离级别下执行SELECT * FROM users WHERE id 10 FOR UPDATE命中唯一索引等值查询临键锁退化为记录锁只锁id10这一行执行SELECT * FROM users WHERE id 5 AND id 15 FOR UPDATE范围查询加临键锁锁定区间(5, 15]也就是id10和id15的记录以及5到10、10到15的间隙其他事务不能在这个区间插入新数据执行SELECT * FROM users WHERE id 12 FOR UPDATE记录不存在临键锁退化为间隙锁锁定区间(10, 15)其他事务不能在这个区间插入id12的数据再看非唯一索引的情况假设表orders有普通索引user_id现有数据user_id 1, 3, 5执行SELECT * FROM orders WHERE user_id 3 FOR UPDATE非唯一索引等值查询会对命中的二级索引记录加临键锁直到扫描到第一个不符合条件的记录退化为间隙锁同时会对命中的记录的主键索引加记录锁四、锁的兼容性速记表格锁类型S锁读X锁写IS锁IX锁S锁读✅ 兼容❌ 冲突✅ 兼容❌ 冲突X锁写❌ 冲突❌ 冲突❌ 冲突❌ 冲突IS锁✅ 兼容❌ 冲突✅ 兼容✅ 兼容IX锁❌ 冲突❌ 冲突✅ 兼容✅ 兼容一句话总结读读不冲突读写冲突写写冲突意向锁之间互相兼容只和表级读写锁互斥。五、实际开发中的避坑指南UPDATE/DELETE一定要走索引执行前先用EXPLAIN看执行计划确保type不是ALL全表扫描否则行锁会退化成表锁并发直接崩掉控制事务粒度不要在事务里做远程调用、大文件读写等耗时操作尽量缩短事务持有锁的时间避免长事务持有MDL锁阻塞DDL合理选择隔离级别如果你的业务对幻读不敏感比如大部分互联网业务可以把隔离级别降到RC减少间隙锁带来的锁范围提高并发性能死锁排查如果出现死锁用SHOW ENGINE INNODB STATUS查看LATEST DETECTED DEADLOCK部分或者查询performance_schema.data_locks表查看当前锁的持有和等待情况