搞数据库的谁敢说自己从来没被锁坑过我印象最深的一次线上一个只有几万行的小表半夜定时任务一跑整个订单写入全部卡死show processlist一看全是Waiting for table metadata lock。还有一次更离谱两个更新语句互相等锁死锁日志刷了一屏最后只能 kill 掉一个事务才缓过来。所以这次想把 MySQL 的锁机制一次性讲透。标题里提到的读锁、写锁、表锁、行锁、悲观锁、乐观锁、间隙锁其实是锁在不同维度上的分类按粒度分有表锁和行锁按模式分有读锁和写锁按策略分有悲观锁和乐观锁而间隙锁是 InnoDB 行锁里专门用来处理幻读的一种特殊锁。这些概念单独看都不难但放到实际 SQL 执行计划里组合起来就很容易翻车。这篇文章适合刚接触 MySQL 锁的初学者也适合写过一些业务 SQL 但被锁等待、死锁搞到头大的开发同学。我会结合自己的实战经历把这几种锁的原理、触发条件、排查方法和避坑技巧全部梳理一遍。1. 先搞清楚 MySQL 锁的分类体系1.1 为什么数据库需要锁很多人在理解锁之前先要理解并发。数据库是共享资源多个连接可以同时读写同一批数据。如果没有锁两个事务同时改同一行后写的覆盖先写的数据就丢了两个事务同时读同一行没问题但边读边写就会读到中间状态。锁的本质就是串行化对临界资源的访问。MySQL 的锁机制要解决两个问题一是保证数据一致性二是尽量提高并发度。这两个目标天然是冲突的锁的粒度越细并发度越高但管理成本也越大锁的粒度越粗实现越简单并发度越低。不同的存储引擎、不同的隔离级别下MySQL 对锁的处理策略也不一样。MySQL 的锁可以分为几个维度来看按粒度分全局锁、表级锁、行级锁按模式分读锁共享锁S 锁、写锁排他锁X 锁按策略分悲观锁、乐观锁按实现机制分记录锁Record Lock、间隙锁Gap Lock、临键锁Next-Key Lock、插入意向锁Insert Intention Lock其中全局锁主要用在备份场景日常开发碰得少表级锁在 MyISAM 里是主角但 InnoDB 下更多是作为行锁的补充存在行级锁才是 InnoDB 引擎下并发控制的核心。1.2 锁的兼容性矩阵读锁和写锁的兼容关系是理解一切锁冲突的基础。一句话概括读锁和读锁兼容读锁和写锁不兼容写锁和写锁不兼容。我用一个表格把这个矩阵列出来大家存一下当前事务持锁其他事务请求读锁其他事务请求写锁读锁S兼容可继续读不兼容阻塞等待写锁X不兼容阻塞等待不兼容阻塞等待这里有个容易误解的地方很多人以为读锁会让其他连接都读不了其实恰恰相反读锁的目的是允许大家一起读但不允许别人写。写锁则是最严格的持有写锁时其他事务既不能读也不能写。注意这个读在 MySQL 的锁语境下特指当前读锁定读也就是SELECT ... FOR UPDATE、SELECT ... LOCK IN SHARE MODE以及 INSERT、UPDATE、DELETE。普通的SELECT走的是快照读在 MVCC 机制下是不加锁的这也是 MySQL 能在高并发下保持较高读取性能的关键。1.3 InnoDB 和 MyISAM 在锁上的根本差异如果你还在用 MyISAM 表或者面试中被问到 MyISAM 和 InnoDB 锁的区别核心差异就两条对比项MyISAMInnoDB锁粒度表级锁行级锁 表级锁支持事务不支持支持ACID并发写入差写操作串行好行级并发写崩溃恢复差好Redo LogMyISAM 在执行 SELECT 时会给表加读锁在执行 INSERT/UPDATE/DELETE 时会给表加写锁。这意味着只要有一个写操作在跑其他任何操作都得等着。这也是为什么 MyISAM 在读写混合场景下性能会急剧下降。InnoDB 不是不用表锁而是默认走行锁只在特殊情况下升级到表锁。比如LOCK TABLES命令显式锁表或者 UPDATE/DELETE 的 WHERE 条件没有命中索引导致全表扫描这时 InnoDB 会对扫描到的所有记录加锁效果上等同于锁表。这个点非常关键后面展开讲。2. 表锁什么时候会锁整张表2.1 隐式表锁MDL 锁和自增锁InnoDB 下最常见的表级锁其实是MDL 锁MetaData Lock元数据锁。MDL 锁是 MySQL 5.5 版本引入的作用是保护表结构定义防止一个事务在查询时另一个事务修改表结构导致数据错乱。MDL 锁分两种MDL 读锁执行 SELECT、INSERT、UPDATE、DELETE 时自动获取多个事务可以同时持有MDL 写锁执行 ALTER TABLE 等 DDL 操作时自动获取是排他的这里有一个非常经典的线上事故场景一个长事务迟迟不提交持有某张表的 MDL 读锁这时候 DBA 对这张表执行 ALTER TABLE 加字段需要 MDL 写锁被阻塞接着所有要访问这张表的新的 SELECT 语句都在 MDL 读锁队列上排队整个表的所有读写全部卡死。排查 MDL 锁的思路是先看performance_schema.metadata_locks表找到持有 MDL 锁的事务然后 kill 掉这个长事务。但根本解法是避免长事务应用层设置合理的事务超时或者对大事务做拆分。另外一个隐式表锁是自增锁AUTO-INC Lock。在 MySQL 5.7 及之前插入数据到带自增主键的表时InnoDB 会申请一个表级锁来保证自增值的唯一性插入完成后立即释放。MySQL 8.0 开始默认使用轻量级的互斥量来维护自增计数器性能好很多但在批量插入时仍然需要特殊处理。2.2 显式表锁LOCK TABLES 的适用场景开发者也可以手动加表锁语法是-- 加读锁 LOCK TABLES user READ; -- 加写锁 LOCK TABLES user WRITE; -- 释放锁 UNLOCK TABLES;我的观点很明确业务代码里尽量不要用 LOCK TABLES。原因是它会把并发度直接降为 0而且如果事务里先 LOCK TABLES 再执行操作最后 UNLOCK TABLES 时事务并不会自动提交容易造成锁泄漏。除非你是做数据修复、批量清理这种离线任务需要保证某张表在操作期间不被其他连接修改才值得显式加表锁。日常 OLTP 场景下InnoDB 的行锁已经足够了。2.3 表锁在 MyISAM 下的表现MyISAM 的表锁实现其实很有意思它的设计是写优先的如果读锁和写锁同时在等待写锁会插队到读锁前面。这样做的目的是防止写操作饿死。但代价是读操作的延迟可能变得很高——一个慢写能把所有读都堵住。MyISAM 下还有一个坑SELECT ... LOCK IN SHARE MODE虽然能加读锁但因为 MyISAM 不支持事务这个读锁的释放时机很难控制容易出现锁没释放的错觉。用 MyISAM 做读写分离的读库可以但读写混合的业务库还是趁早迁到 InnoDB 更稳妥。3. 行锁InnoDB 并发控制的核心3.1 行锁的三种实现记录锁、间隙锁、临键锁进入 InnoDB 的行锁世界官网文档把行锁分为三类我直接用 SQL 例子说明。记录锁Record Lock锁住索引记录本身。比如-- 假设 user 表 id 为主键这条语句会锁住 id5 这一行 SELECT * FROM user WHERE id 5 FOR UPDATE;如果 id5 这一行不存在记录锁不会生效。但注意如果id不是主键而是二级索引InnoDB 除了给二级索引上的记录加锁还会给对应的主键索引记录加锁这个细节经常被忽略。间隙锁Gap Lock锁住索引记录之间的间隙防止其他事务在这个间隙插入新记录。间隙锁锁的是范围不是具体的行。比如-- 假设 user 表 id 有 1、3、5 三行数据 SELECT * FROM user WHERE id BETWEEN 2 AND 4 FOR UPDATE;这会在 (1,3) 和 (3,5) 这两个开区间内加间隙锁其他事务如果插入 id2 或 id4 的记录会被阻塞但插入 id6 不会阻塞。临键锁Next-Key Lock可以理解为记录锁 间隙锁的组合锁住的是左开右闭的区间。比如 id 为 1、3、5 时临键锁覆盖的范围是 (-∞,1]、(1,3]、(3,5]、(5,∞)。InnoDB 在可重复读RR隔离级别下默认用临键锁做范围查询的加锁。这里有个极其容易踩的坑如果 WHERE 条件里的列没有索引InnoDB 会对整个表的所有记录加临键锁等同于把整张表锁住。后面单独讲这个。3.2 当前读与快照读为什么 SELECT 不加锁还不出错InnoDB 的并发读比其他数据库强核心秘密在于多版本并发控制MVCC。MVCC 让普通的 SELECT 走快照读不需要加锁而是通过 undo log 回溯到一个历史版本。这样读操作永远不会阻塞写操作写操作也不会阻塞读操作。但这是有前提的快照读只能看到某个时间点之前已提交的数据。在可重复读隔离级别下事务第一次执行 SELECT 时生成快照之后整个事务都用这个快照在读已提交RC级别下每次 SELECT 都会生成新的快照。而SELECT ... FOR UPDATE和SELECT ... LOCK IN SHARE MODE走的是当前读直接读取最新版本的数据并加锁。当前读和快照读的结果在某些场景下会不一致这就是半一致性读等概念的来源不过日常开发重点记住普通 SELECT 不加锁FOR UPDATE 加锁就够了。3.3 行锁到底锁的是行还是索引这个问题是我面试候选人时必问的InnoDB 的行锁锁的到底是什么答案是锁的是索引记录不是行数据。InnoDB 的表实际上是按主键索引聚簇索引组织的数据就挂在主键索引的叶子节点上。对一行数据加锁本质是对主键索引上对应的那个索引项加锁。如果 WHERE 条件用到二级索引InnoDB 会先锁二级索引记录再回表锁主键索引记录。如果二级索引是唯一索引等值查询只需要锁一条如果不是唯一索引范围可能被放大导致间隙锁的范围比预期更大。这个设计带来一个重要推论如果 SQL 没有走任何索引InnoDB 只能扫描主键索引的全部分支等于给整张表的每条索引记录都加锁。所以优化 SQL 的第一步永远是看执行计划里 type 是不是 ALL凡是全表扫描的写操作就要警惕锁表风险。4. 悲观锁与乐观锁两种并发控制策略4.1 悲观锁在 MySQL 里的落地方式悲观锁的思想是我总觉得有人会改数据所以在操作之前先把数据锁住别人想碰都碰不了。在 MySQL 里落地悲观锁最常见的方式就是-- 事务 A BEGIN; SELECT stock FROM product WHERE id 100 FOR UPDATE; -- 在应用层判断 stock 是否充足 -- 执行 UPDATE product SET stock stock - 1 WHERE id 100; COMMIT;事务 A 执行FOR UPDATE后事务 B 同样执行FOR UPDATE查询同一行时会被阻塞直到 A 提交或回滚。这样就保证了库存扣减不会被两个并发请求同时执行。悲观锁的优点是实现简单、逻辑直观绝对不会有超卖缺点是并发度低大量请求同时抢同一行时后面全部排队接口响应时间飙升。在实际项目中用悲观锁有几个必须注意的细节一定要在事务里执行否则 SELECT 加锁后没有事务边界锁不会释放事务要短平快拿到锁后不要做远程调用、不要做复杂计算尽快提交设置合理的锁等待超时比如innodb_lock_wait_timeout 5秒避免一个连接无限期等待连接池要规划好如果并发请求远大于连接池大小锁等待会把连接池全部耗尽4.2 乐观锁的实现版本号机制乐观锁的思想是我觉得冲突概率很低先随便改提交的时候再检查有没有人和我抢。MySQL 里没有内置的乐观锁语法通常靠版本号字段或时间戳字段来实现-- 1. 先查出当前版本号 SELECT stock, version FROM product WHERE id 100; -- 2. 更新时带上版本号条件 UPDATE product SET stock stock - 1, version version 1 WHERE id 100 AND version 1;如果执行 UPDATE 后影响行数为 0说明期间有其他人修改过这条数据版本号对不上了应用层需要重试或提示用户。还有一种实现方式是条件更新只更新符合业务条件的行UPDATE product SET stock stock - 1 WHERE id 100 AND stock 0;这种方式没有显式的版本号字段但同样能达到类似效果因为 MySQL 默认只更新匹配的行行数不匹配就意味着数据已经被改动。4.3 悲观锁和乐观锁到底怎么选这是很多开发纠结的问题。我根据实际场景给出选择建议场景推荐策略原因下单扣库存库存量少并发高乐观锁条件更新冲突概率可控避免排队阻塞金额转账余额充足并发不高悲观锁保证强一致防止并发覆盖热点行更新比如秒杀同一件商品乐观锁 重试悲观锁会让所有请求排队QPS 上不去多表联动更新逻辑复杂悲观锁事务期间锁住数据降低业务复杂度一个很典型的误区是很多团队把所有更新操作都改成乐观锁以为这样就高枕无忧了。实际上乐观锁对并发冲突严重的场景并不友好每次冲突都要重试重试越多数据库压力越大。秒杀场景下大家抢同一行用乐观锁可能出现大量 update 失败和重试反而不如对库存做分段或异步化。从我个人经验来说简单业务、数据一致性要求极高用悲观锁复杂业务、冲突概率低或可重试用乐观锁。没有银弹只有权衡。5. 间隙锁与幻读RR 隔离级别的守护神5.1 幻读到底是怎么产生的幻读指的是同一个事务内连续执行两次相同的查询第二次查出了第一次没有的行。例子BEGIN; SELECT * FROM user WHERE age BETWEEN 20 AND 30; -- 返回 5 行 -- 此时另一个事务插入了 age25 的新记录并提交 SELECT * FROM user WHERE age BETWEEN 20 AND 30; -- 返回 6 行 COMMIT;如果用普通 SELECT快照读第一个事务执行后生成了快照第二次查询还是读快照不会出现幻读。但如果第二次查询是加锁的当前读比如SELECT ... FOR UPDATE就能看到新插入的记录这就是幻读。问题是即使第一次是当前读加锁的也是 5 条已存在的记录如果另一个事务插入一条 age25 的新记录它的行为和锁没有冲突所以记录锁拦不住它。这就需要一个机制来锁住插入新记录这件事间隙锁应运而生。5.2 间隙锁的加锁规则间隙锁的加锁规则比较复杂我结合经验总结几点只在可重复读RR隔离级别下生效读已提交RC级别下 InnoDB 只保留记录锁这也是很多团队选择 RC 级别的原因之一间隙锁锁的是索引记录之间的空隙包括某个具体记录之前、之后、记录之间的范围间隙锁之间互相兼容两个事务可以持有一致的间隙锁但间隙锁和插入意向锁不兼容插入操作会被阻塞使用唯一索引做等值查询且记录存在时不会产生间隙锁只加记录锁使用普通二级索引做等值查询时即使记录存在也会在其前后产生间隙锁因为它不是唯一的使用范围查询时匹配到的所有范围都会加临键锁范围之外的间隙也可能被锁住关于最后一点有个经典的坑SELECT * FROM user WHERE id 10 FOR UPDATE在 RR 级别下不仅锁住 id 大于 10 的所有现有记录还会锁住从 id10 到正无穷的所有间隙。也就是说其他事务连插入一条 id100 的新记录都会被阻塞因为新插入的记录属于 (10, ∞) 这个间隙范围内的。5.3 怎么排查和解决间隙锁引起的阻塞间隙锁引发的最大问题就是莫名其妙的插入阻塞明明锁的是 10 到 20 之间的记录为什么我插入 id100 也被卡住了因为间隙锁覆盖的范围比你想的大。排查思路-- 查看当前有哪些锁等待 SELECT * FROM performance_schema.data_lock_waits; -- 查看持有锁的事务 SELECT * FROM performance_schema.data_locks; -- 查看当前运行的事务 SELECT * FROM information_schema.innodb_trx;如果确认是间隙锁导致的阻塞常见的解决手段把隔离级别降到读已提交RC。RC 级别下没有间隙锁同时配合 binlog 用 row 格式这是很多互联网大厂的标配优化 SQL 走唯一索引等值查询避免范围被放大拆分大事务减少持锁时间给 WHERE 条件涉及的列建合适的索引锁的粒度越小越精准我有一个血的教训曾经有一张日志表因为一个统计查询走了全表扫描在 RR 级别下把整张表的所有间隙都锁住了导致所有 INSERT 全部阻塞接口雪崩。那次之后我对所有涉及范围查询的 SQL 都强制检查执行计划。5.4 MVCC 和间隙锁的分工很多人搞不清 MVCC 和间隙锁的关系觉得既然 MVCC 能解决读写冲突为什么还需要加锁锁我的理解是这样的MVCC 解决的是读不阻塞写、写不阻塞读的问题靠的是快照版本间隙锁解决的是写和写、以及当前读和写之间的冲突问题靠的是对索引区间加锁。在一个可重复读的事务里普通 SELECT 走 MVCC 快照读历史版本自然看不到别的事务插入的新行幻读被绕过去了。但 UPDATE、DELETE、SELECT ... FOR UPDATE走当前读必须看到最新数据这时候就触发临键锁用锁住间隙的方式阻止新记录插入从根上杜绝了幻读。可以说间隙锁是在加锁读路径上为 MVCC 兜底的机制。理解了这层关系再去看到网上关于RR 怎么解决幻读的讨论就不会被绕晕了。6. 常见锁问题排查与实战经验6.1 如何判断当前 SQL 加了什么锁判断一个 SQL 加了什么锁最直接的方法是查看performance_schema下的锁数据。MySQL 8.0 里可以这样查-- 查看当前持锁和等待锁的所有锁信息 SELECT * FROM performance_schema.data_locks\G; -- 查看锁等待关系 SELECT * FROM performance_schema.data_lock_waits\G;data_locks表里有几个关键字段需要关注ENGINE_TRANSACTION_ID事务 IDLOCK_TYPETABLE表锁或 RECORD记录锁LOCK_MODES、X 表示读写锁加上 GAP 表示间隙锁加上 NEXT-KEY 表示临键锁LOCK_STATUSGRANTED 表示已持有WAITING 表示在等待LOCK_DATA被锁的索引值或范围举个例子如果某个事务的锁记录是LOCK_MODE X, REC_NOT_GAP说明它持有一个排他的记录锁不涉及间隙如果是LOCK_MODE X, GAP说明是间隙锁如果是LOCK_MODE X且没有REC_NOT_GAP标记一般是临键锁。6.2 死锁成因、日志解读和解决死锁的经典场景是两个事务持有对方需要的锁互相等待谁也动不了。比如-- 事务 A BEGIN; UPDATE account SET balance balance - 100 WHERE id 1; UPDATE account SET balance balance 100 WHERE id 2; COMMIT; -- 事务 B BEGIN; UPDATE account SET balance balance - 100 WHERE id 2; UPDATE account SET balance balance 100 WHERE id 1; COMMIT;如果 A 先锁了 id1B 先锁了 id2然后 A 等 id2B 等 id1死锁就产生了。InnoDB 检测到死锁后会回滚其中一个事务通常是 undo 量较小的那个并抛出Deadlock found when trying to get lock错误。死锁日志里最核心的部分是LATEST DETECTED DEADLOCK里面会列出两个事务各自持有的锁和等待的锁。排查死锁时我建议画一个简单的等待图确认每条链路锁的是哪些资源然后从代码层面调整加锁顺序。解决死锁的常见手段所有事务按相同的顺序访问资源比如都先操作 id 小的记录再操作 id 大的缩小事务范围减少持锁时间和持锁数量合理利用索引避免全表扫描导致锁数量暴增对热点行更新考虑串行化比如在应用层做队列设置innodb_lock_wait_timeout作为兜底防止事务无限等待这里说一下我的经验死锁不可避免关键是要有重试机制。应用层捕获到死锁异常后稍作等待再重试整个事务。重试的次数建议控制在 3 次以内因为死锁多说明系统设计本身有瓶颈重试再多也解决不了根因。6.3 锁等待排查实战如果线上突然出现大量 SQL 执行超时第一件事就是要判断是不是锁等待导致的。-- 1. 查看当前是否有事务长时间未提交 SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id FROM information_schema.innodb_trx; -- 2. 查看当前运行的 SQL SHOW PROCESSLIST; -- 3. 查看锁等待链路MySQL 8.0 SELECT * FROM sys.innodb_lock_waits;sys.innodb_lock_waits这个视图非常实用它直接告诉你谁在等谁、等了多久、持有锁的线程是哪个。拿到线程 ID 后可以判断这个事务是否可以安全地 kill 掉。有个细节要注意SHOW PROCESSLIST里看到的Waiting for table metadata lock和Waiting for row lock原因完全不同。前者是 DDL 和 DML 在争 MDL 锁后者是行锁冲突。别一看到锁等待就急着 kill先分清锁的类型。6.4 锁机制相关的面试高频题梳理最后整理几个锁机制面试题都是这些年我面试别人或者被面试时反复遇到的MySQL 的锁有哪些分类上面已经讲了按粒度、模式、策略、实现机制四个维度分回答时最好把表格画出来。行锁有哪些实现方式记录锁、间隙锁、临键锁结合具体 SQL 举例说明。RR 隔离级别下如何解决幻读答案就是间隙锁 临键锁机制注意要说明 MVCC 快照读和当前读的区别。什么情况下行锁会升级成表锁关键点是WHERE 条件未命中索引导致全表扫描所有记录都被加锁还有显式 LOCK TABLES、MDL 锁变更表结构等情况。悲观锁和乐观锁在 MySQL 里分别怎么实现悲观锁用SELECT ... FOR UPDATE乐观锁用版本号或条件更新。死锁是什么怎么避免答清楚成因循环等待、日志怎么读、解决思路控制加锁顺序、缩短事务、重试机制。间隙锁和记录锁的区别记录锁锁已有记录间隙锁锁记录之间的空隙临键锁是两者组合。什么是当前读和快照读当前读锁定读最新版本快照读读历史版本不阻塞是 MVCC 的核心。为什么推荐 RC 隔离级别因为 RC 没有间隙锁和临键锁并发度高配合 row 格式 binlog 数据一致性也可以保障是很多大厂的生产标配。如何查看 MySQL 的锁等待信息information_schema.innodb_trx、performance_schema.data_locks、sys.innodb_lock_waits。6.5 一个实战案例库存扣减的完整设计用一个库存扣减的场景把本文的内容串起来。假设商品表结构CREATE TABLE product ( id BIGINT PRIMARY KEY AUTO_INCREMENT, sku VARCHAR(64) NOT NULL, stock INT NOT NULL DEFAULT 0, version INT NOT NULL DEFAULT 0, UNIQUE KEY uk_sku (sku) ) ENGINEInnoDB;方案一悲观锁BEGIN; SELECT stock FROM product WHERE sku A001 FOR UPDATE; -- 业务判断 stock 是否大于 0 UPDATE product SET stock stock - 1 WHERE sku A001; COMMIT;这个方案的优点是绝对不会超卖缺点是并发量高时排队严重。适合后台系统、只允许少量并发修改的场景。方案二乐观锁-- 第一次查询 SELECT stock, version FROM product WHERE sku A001; -- 更新时校验版本号 UPDATE product SET stock stock - 1, version version 1 WHERE sku A001 AND version ?; -- 影响行数为 0 时重试这个方案并发度较高但是重试逻辑要注意处理不能让失败的请求无限循环。方案三原子更新条件更新UPDATE product SET stock stock - 1 WHERE sku A001 AND stock 0;这个方案其实是我最推荐的一种一条 SQL 解决并发扣减既没有显式的锁等待也不需要版本号完全靠行锁保证一致性。stock 0这个条件相当于业务层的乐观校验。它唯一的弱点是如果扣减条件很复杂比如同时校验多个字段SQL 会变得很长可读性下降。无论选哪种方案都要记住一个原则把事务放到最小范围保持短小。我曾经见过有人在事务里调外部 API一个事务跑 20 多秒锁住了几十行数据最后整个系统的 UPDATE 操作全部排在它后面。锁本身不可怕可怕的是持锁时间太长。关于 MySQL 锁机制我最后再分享一点体会读多少篇文档都不如在线上环境实际排查一次锁等待来记得牢。第一次遇到Waiting for row lock的时候我连information_schema里有哪些表都不知道。现在回头看锁机制的核心脉络其实很清楚——选对引擎建好索引控制事务大小加锁顺序统一该用悲观锁别犹豫能走条件更新就别搞复杂版本号。把这些基本功做扎实了绝大部分锁问题都能在变成线上事故之前被你拦下来。
