锁这个东西理论学的时候觉得全是概念一上线上就懵。尤其是MySQL面试十次有八次会问到表锁、行锁和幻读但真到排查线上问题时很多人连当前读和快照读都分不清更搞不懂为什么InnoDB在可重复读RR隔离级别下可以避免幻读而别的数据库在RR下却可能出问题。这篇文章我会把InnoDB存储引擎下的锁机制、锁的颗粒度、行锁为什么会退化成表锁以及RR隔离级别是怎么通过间隙锁和MVCC把幻读按住的全部拆开讲清楚文末附上可以直接收藏的速查表格。适合正在准备MySQL面试的同学也适合被线上锁等待、死锁、慢事务折磨过的后端开发、DBA和运维朋友。我尽量不写教科书只讲实际能用、面试能答、排查能上手的部分。1. 为什么锁和隔离级别是MySQL实战的试金石1.1 面试必问、线上必踩锁到底锁的是什么先说一个大家容易忽略的前提MySQL的锁机制是由存储引擎实现的。MyISAM只有表锁InnoDB才有行锁也正是因为InnoDB支持行锁它才能支撑高并发下的读写场景。面试里问MySQL的表锁和行锁默认就是在问InnoDB相关的行为。锁的本质是解决并发事务之间的资源竞争问题。两个人同时改一条数据如果不加控制后写的人就会覆盖先写的人这叫丢失更新。锁就是让同时变成排队保证数据的一致性。但锁不是免费的午餐它带来一致性的同时也带来了阻塞。锁的颗粒度越大并发能力越差锁的颗粒度越小并发能力越强但管理和检测的成本也会更高。InnoDB选择行锁正是为了兼顾并发度而在某些特定场景比如DDL、全表扫描下又不得不使用表锁作为兜底。1.2 锁、事务、隔离级别三者是什么关系这三个概念经常被分开背但实际上是联动的事务定义了一组操作要么全成功要么全失败的边界。隔离级别定义了事务之间互相干扰的程度SQL标准里分了四种读未提交RU、读已提交RC、可重复读RR、串行化。锁和MVCC是数据库实现隔离级别的底层工具。MySQL默认的隔离级别是RR可重复读这是InnoDB的默认配置。很多人在别的数据库上把RC当成默认但MySQL就是RR而且它还能在RR下避免幻读这在SQL标准里是不保证的。所以这道题的正确打开方式是先讲清表锁、行锁、间隙锁各自锁什么再讲RR隔离级别下InnoDB如何用MVCC解决快照读的幻读、用间隙锁解决当前读的幻读最后落到实操排查。2. 从表锁到行锁MySQL锁家族的完整拆解2.1 表级锁LOCK TABLES、MDL、AUTO-INC锁表锁是最直观的锁它锁住整张表分两种模式表级共享锁S锁读锁多个事务可以同时持有互不阻塞但不能写。表级排他锁X锁写锁只有持有者能读写其他事务读写都被阻塞。在MyISAM里LOCK TABLES t READ/WRITE是常用操作。但在InnoDB里一般不建议手动用表锁因为InnoDB有行锁手动加表锁反而会降低并发度。真正容易被忽略的是另外两类表级锁MDL元数据锁MySQL 5.5引入它锁的是表结构。任何对表的DML操作都会加MDL读锁任何DDL操作ALTER、DROP会加MDL写锁两边互斥。这就是线上经典的一个小ALTER把整个表的读写都堵死的元凶。我见过不止一次白天对一个大表执行ALTERDDL没跑完所有业务SQL全部卡在Waiting for table metadata lock。AUTO-INC锁专门保护自增字段在INSERT时加插入完成后释放在MySQL 5.7里默认情况下innodb_autoinc_lock_mode1插入前先拿一个轻量级锁。这个锁比较特殊它不锁数据行但它确实保护了自增值的分配逻辑。2.2 InnoDB行级锁记录锁、间隙锁、临键锁InnoDB的行锁建立在索引上这是整篇文章最核心的一句话。锁定的不是数据行本身而是索引记录。如果查询条件没有走索引InnoDB就只能扫描聚簇索引主键索引的所有记录逐个加锁效果就跟锁全表一样。行锁内部其实分三种实现记录锁Record Lock只锁索引记录本身。比如SELECT * FROM user WHERE id1 FOR UPDATE如果id是主键这条SQL就只锁住id1的这一行。间隙锁Gap Lock锁住索引记录之间的空隙防止其他事务在这个区间插入新记录。比如表里有id为1、5、10三行间隙锁可能锁住(1,5)、(5,10)、(10,∞)这些区间。间隙锁只存在于RR隔离级别这也是InnoDB在RR下防幻读的关键武器。临键锁Next-Key Lock它是记录锁间隙锁的组合。锁住的不是一个点而是一个左开右闭的区间比如(1,5]。InnoDB在RR下默认使用临键锁它既锁住了已有记录也锁住了记录之间的空隙。行锁类型对比锁类型锁定范围作用是否防幻读记录锁 Record Lock单条索引记录锁住已有行不能间隙锁 Gap Lock索引记录之间的区间阻止区间内插入能临键锁 Next-Key Lock左开右闭区间含记录和间隙锁定记录及空隙能2.3 锁的颗粒度对比为什么InnoDB默认选行锁锁的颗粒度这个点面试官很爱问。直观理解就是锁的范围有多大表锁锁一整个区间行锁锁一条间隙锁锁一段空隙。用大白话类比表锁像是在自习室门口贴了张纸整间教室都归我用其他人都不能进。行锁像是只占了自己那个座位别人还能进教室坐在别的座位上。间隙锁像是我虽然没占那个座位但那个座位和座位之间的过道我也不让别人走为了不给新人留位置。表锁的优势是开销小、实现简单劣势是并发度极低行锁的优势是并发度高劣势是加锁、检测、释放成本高可能产生死锁。InnoDB选行锁本质上是为了在线交易场景下牺牲一点锁管理成本换取高并发能力。2.4 本来该行锁怎么变成表锁了这是热词里大家搜得很多的场景也是线上最常见的翻车现场。明明是一条UPDATE按理说行锁就够了结果它在information_schema里显示锁了全表其他事务全部卡住。原因主要有这几类查询条件没走索引。这是最大的原因。前面说了InnoDB的行锁是锁索引记录。如果UPDATE user SET age30 WHERE name张三这个name列上没有索引MySQL只能全表扫描把所有聚簇索引记录都加锁看起来就是表锁。索引失效。隐式类型转换、对索引列使用函数、字符集不一致都会让索引失效导致全表扫描。比如WHERE mobile13812345678而mobile是varchar类型MySQL会把int转成varchar再比较但某些写法下优化器会放弃索引。事务长时间不提交。即使走索引如果事务A锁了几行后迟迟不提交事务B要修改同一行就会开始等待。从业务表现上看B的所有操作都卡住了很容易被误解为表被锁了。所以排查锁问题的时候第一件事不是看锁而是先看SQL的执行计划看type是不是ALLkey是不是NULL。如果是先解决索引问题锁的问题往往自动消失。3. RR隔离级别下InnoDB如何避免幻读3.1 幻读的定义与复现场景先搞清楚幻读和不可重复读的区别这个很多人背概念的时候容易混。不可重复读事务A读了一条记录事务B把这条记录UPDATE了事务A再读一次发现值变了。它强调的是同一行数据内容发生变化。幻读事务A执行了一次范围查询事务B往这个范围内INSERT了一条新记录事务A再执行同样的范围查询发现多了一行。它强调的是记录数的变化。SQL标准里RR隔离级别本应解决不可重复读但不能解决幻读。可在InnoDB里RR不仅能防不可重复读还能防幻读比标准要求做得更狠。网上很多文章说MySQL的RR通过MVCC解决了幻读这话只说对了一半。它实际上是两条路配合对于普通查询快照读靠MVCC解决幻读。对于加锁查询当前读靠间隙锁和临键锁解决幻读。3.2 快照读和当前读两个世界的读这是理解整个问题的钥匙必须先分清。普通SELECT是快照读不加锁通过MVCC机制读一个事务开始时的快照。RR下这个快照在事务第一次执行SELECT时生成整个事务期间都用这一份快照所以看不到其他事务后插入的数据。这就是为什么RR不会幻读到新插入的记录。加锁的读和写操作是当前读包括SELECT ... FOR UPDATESELECT ... FOR SHARE就是LOCK IN SHARE MODEUPDATEDELETEINSERT当前读必须读最新已提交版本并对其扫描范围内的记录加锁。如果不小心事务B插入的新记录就可能穿过当前读的防线造成幻读。所以InnoDB对当前读单独动用了间隙锁。3.3 间隙锁与临键锁把空隙也锁住假设user表里有id为主键数据有4、8、12三条。事务A执行SELECT * FROM user WHERE id BETWEEN 5 AND 10 FOR UPDATE;正常情况下这条SQL扫描到的范围是(4,12]这个区间InnoDB会在这个区间加临键锁(4,8] 的记录锁间隙锁(8,12] 的记录锁间隙锁同时间隙部分(4,8)、(8,12)也被锁住了。此时事务B尝试执行INSERT INTO user (id, name) VALUES (9, 新人);这条插入会被阻塞因为9落在(8,12)这个被锁住的间隙里。这就让事务A在当前读的过程中看不到、插不进新记录幻读被物理层面按住了。这里有个细节值得注意间隙锁的目的是防插入所以它和间隙锁之间是兼容的。两个事务可以同时持有同一个间隙的间隙锁但如果都想去插入就会互相等待甚至死锁。3.4 一个完整的RR防幻读案例我用一个具体案例把两者串起来。事务ABEGIN; SELECT * FROM user WHERE name 老张 FOR UPDATE;假设name上没有索引这条SQL会全表扫描并给所有扫描到的聚簇索引记录加临键锁等于把整张表范围内的间隙都锁住。此时事务B想往表里插任何一条记录都会被阻塞。如果name上有非唯一索引情况稍微复杂一点InnoDB会锁住所有匹配的记录及其间隙还会锁住索引中记录之间的空隙阻止其他事务往这个匹配范围内插入新记录。所以回到问题本身为什么InnoDB在RR下能避免幻读答案可以直接背下来快照读时用一致性视图保证整个事务看到的是同一份数据新插入的行不可见。当前读时用临键锁锁定扫描范围内已有记录和相邻间隙让其他事务无从插入。两条路合在一起RR下的幻读被彻底挡住。这也解释了一个很多人困惑的反问既然RR能避免幻读为什么还要串行化SERIALIZABLE隔离级别 因为串行化把所有普通SELECT都变成了当前读彻底用锁串行化所有读操作并发度最低但一致性最强。RR则通过MVCC保留了普通的非阻塞读。4. 线上锁问题的定位与排查实录4.1 锁表/锁等待的常见表现线上的锁问题体感上就几种SQL突然变慢一条原本毫秒级的UPDATE跑了几十秒还没结束。大量线程堆积在同一个表或同一行数据上应用连接池被占满。应用日志里出现Lock wait timeout exceeded; try restarting transaction。偶尔出现死锁报错Deadlock found when trying to get lock; try restarting transaction。我看到锁等待问题的第一反应是三级排查先看有没有事务没提交再看锁的持有情况最后看死锁日志。4.2 用系统表定位锁等待第一步找长时间运行的事务SELECT * FROM information_schema.innodb_trx\G;重点看trx_started、trx_state、trx_query这几列找到那个迟迟不提交的事务ID。第二步查看锁和锁等待关系。MySQL 5.7及以下查innodb_locks和innodb_lock_waitsSELECT * FROM information_schema.innodb_locks; SELECT * FROM information_schema.innodb_lock_waits;MySQL 8.0之后这两个表和performance_schema合并了改查SELECT * FROM performance_schema.data_locks; SELECT * FROM performance_schema.data_lock_waits;之前我在一个线上故障里就是用这条SQL快速定位到了谁持有锁、谁在等锁SELECT r.trx_id AS waiting_trx_id, r.trx_mysql_thread_id AS waiting_thread, b.trx_id AS blocking_trx_id, b.trx_mysql_thread_id AS blocking_thread FROM performance_schema.data_lock_waits w JOIN information_schema.innodb_trx r ON w.REQUESTING_ENGINE_TRANSACTION_ID r.trx_id JOIN information_schema.innodb_trx b ON w.BLOCKING_ENGINE_TRANSACTION_ID b.trx_id;查出阻塞线程后如果确认是死循环、长事务或者误操作可以KILL掉阻塞线程KILL thread_id;第三步看锁等待超时参数。InnoDB默认innodb_lock_wait_timeout是50秒如果业务对延迟敏感可以调小一些比如5秒让失败的查询快速失败而不是长时间阻塞连接。4.3 死锁怎么查、怎么避免死锁是行锁场景下无法完全根除的问题但可以快速定位和降低概率。查死锁最直接的是SHOW ENGINE INNODB STATUS\G;重点看LATEST DETECTED DEADLOCK段里面有事务A、事务B分别持有什么锁、等待什么锁以及最后牺牲的是哪个事务。日常开发中我可以给出的几条硬经验所有事务以固定顺序访问表和记录比如都先操作id1再操作id2避免交叉加锁。尽量让事务短小精悍减少锁持有时间降低死锁概率。保持索引到位让UPDATE和DELETE走索引避免行锁退化成表锁后形成更大范围的锁竞争。关注隐式类型转换这个坑特别容易踩。比如表里字段是varchar查询条件里写成intMySQL可能放弃索引行锁变全表。之前看到热词里有mysql中int5其实就是这类隐式转换问题的延伸。大规模批量更新时分批提交每次只更新几百行不要一个事务锁几千几万行。死锁发生并不是bug数据库会自动回滚其中一个事务但应用层面要做好重试。网上大量生产环境死锁案例最后查下来很大比例都和索引失效、事务过长有关。4.4 常见锁问题速查表现象可能原因排查命令/手段解决方向DDL后业务全卡MDL写锁等待查innodb_trx看是否有长事务未提交先处理长事务再执行DDL用pt-online-schema-changeUPDATE/查询突然变慢行锁退化成表锁索引失效或全表扫描EXPLAIN看执行计划查data_locks建立合适索引修复隐式类型转换Lock wait timeout报错其他事务持有行锁或间隙锁未释放查innodb_trx、data_lock_waitsKILL阻塞事务或者拆分事务死锁日志出现多事务交叉加锁SHOW ENGINE INNODB STATUS统一加锁顺序缩小事务范围自增字段跳号AUTO-INC锁行为导致查看innodb_autoinc_lock_mode一般无需处理了解其机制5. 收藏向锁机制与幻读排查速查表附表格5.1 锁类型与隔离级别速查表下面这张表是我面试前必背的也推荐你直接复制走。对比项MyISAMInnoDB锁粒度表级锁行锁、间隙锁、表锁默认隔离级别不支持事务RR可重复读是否支持事务否是行锁是否依赖索引无行锁是锁索引记录防幻读能力无RR下可防适用场景读多写少、非事务金融/电商等事务型隔离级别脏读不可重复读幻读InnoDB是否彻底防幻读读未提交 RU可能可能可能否读已提交 RC避免可能可能否可重复读 RR避免避免避免InnoDB特殊实现是串行化 SERIALIZABLE避免避免避免是但并发极低InnoDB在RR下能防幻读是它比SQL标准超纲的地方靠的是MVCC加间隙锁/临键锁这套组合拳。5.2 行锁类型与加锁范围速查表锁类型锁定的范围典型场景其他事务能做什么记录锁 Record Lock单行索引记录WHERE id1 FOR UPDATE其他行可操作间隙锁 Gap Lock记录之间的区间范围查询未命中记录不能插入该区间临键锁 Next-Key Lock左开右闭的索引区间范围查询命中记录不能插入目标区间也不能改目标记录插入意向锁 Insert Intention Lock间隙锁的一个特殊变体多个事务往同一间隙插入不同位置间隙锁可以共存但受间隙锁阻塞5.3 线上排查SQL速查表目标命令查看所有正在运行的事务SELECT * FROM information_schema.innodb_trx;查看当前持有/等待的锁MySQL 8.0SELECT * FROM performance_schema.data_locks;查看锁等待关系MySQL 8.0SELECT * FROM performance_schema.data_lock_waits;查看当前持有/等待的锁MySQL 5.7SELECT * FROM information_schema.innodb_locks;查看锁等待关系MySQL 5.7SELECT * FROM information_schema.innodb_lock_waits;查看死锁日志SHOW ENGINE INNODB STATUS\G;杀掉阻塞事务KILL thread_id;查看锁等待超时时间SHOW VARIABLES LIKE innodb_lock_wait_timeout;查看当前隔离级别SHOW VARIABLES LIKE transaction_isolation;5.4 面试重点一句话总结如果面试官问InnoDB在RR下如何避免幻读可以这样回答InnoDB把读分成了快照读和当前读。普通SELECT走快照读靠MVCC的一致性视图保证整个事务期间看到的是同一份数据快照而UPDATE、DELETE、SELECT FOR UPDATE这类当前读会通过临键锁同时锁住扫描范围内的索引记录以及记录之间的间隙让其他事务无法插入新记录。两条路径合在一起InnoDB就在RR隔离级别下彻底避免了幻读。这段回答包含了三个关键词MVCC、快照读、临键锁面试官继续往下追问也基本就是沿着这三条线深挖。结语一点排查经验其实锁的问题并不玄真正难的是第一反应。我踩过很多次坑之后现在线上遇到锁问题固定的套路就是先SHOW PROCESSLIST看有没有积压再查innodb_trx找长事务然后看data_lock_waits找阻塞关系最后用SHOW ENGINE INNODB STATUS看死锁。90%的情况都能在五分钟内定位。最后再分享一个小技巧建表时尽量保证所有用于查询、更新的列都有合适的索引尤其是UPDATE和DELETE的条件列。这不是为了查询性能而是为了让行锁留在行这个颗粒度上而不是退化成表锁。很多数据库被锁死的故障背后的SQL拿EXPLAIN一看typeALLkeyNULL当场破案。希望这篇文章能帮你少踩几个和我当年一样的坑。