MySQL并发控制：MVCC与锁机制原理及死锁排查实战

1. 并发控制的两条路线MVCC和锁到底在解决什么问题先说个我早年踩过的坑。某天线上接口突然大量超时排查了一圈发现罪魁祸首是一条跑了二十多秒的UPDATE语句它把一张千万级大表的某一列做了批量更新。表面上看起来只是慢但真正致命的是——这条更新没走索引导致它把整张表锁住了。期间所有针对这张表的SELECT、UPDATE、DELETE全部排队等待业务直接雪崩。那次事故之后我深刻意识到一件事数据库的事务并发控制本质上就是在做“多个事务同时访问同一份数据时如何保证数据不错乱还能保持性能”的平衡术。而锁和MVCC就是实现这套平衡的两根支柱。很多人面试被问到锁张口能背出共享锁、排他锁、表锁、行锁这些名词但真到了线上排查死锁、优化并发场景的时候往往一脸茫然。因为名词好记背后的设计逻辑和实际运作机制才是真正要命的地方。1.1 没有并发控制的世界会怎样想象一个最简单的场景一个银行转账系统账户A余额是1000元事务T1要扣款100事务T2同时要扣款200。如果没有任何控制两个事务都先读到余额1000然后各自算出新余额900和800最后谁后写入谁就覆盖对方的结果——这就是典型的“丢失更新”。更麻烦的是如果事务T1读到T2还没提交的中间数据而T2回滚了T1就拿了不存在的数据去计算这就是“脏读”。数据库解决这些问题的核心手段就是两套互补机制MVCC负责处理“读和写”之间的并发让读操作不阻塞写、写操作不阻塞读锁负责处理“写和写”之间的并发保证同一时间只有一个事务能真正改动某条数据。两者分工不同却配合得天衣无缝。1.2 MVCC多版本复制读写互不伤害MVCC全称Multi-Version Concurrency Control中文叫多版本并发控制。它的核心思路很有意思不是让读操作等待写操作释放锁而是给每一行数据维护多个历史版本。当某个事务要修改一行数据时它并不会直接覆盖原来的值而是生成一个新版本正在并发执行的其他读事务如果起步早、还没提交就继续读旧版本完全不受影响。用咱们生活里的场景类比就像一本书改了新版但图书馆里借书的人手里拿的还是旧版——旧版不会因为新版出版就消失大家各看各的版本互不干扰。在InnoDB引擎里MVCC的实现依赖三个隐藏字段DB_TRX_ID记录最后修改这行数据的事务IDDB_ROLL_PTR指向undo log里的旧版本链DB_ROW_ID是行的隐式主键。读事务根据自身的事务ID和当前活跃事务列表去判断该看到哪个版本这套逻辑在源码里叫ReadView——每个读事务建立时都会生成一个视图记录此刻哪些事务还在活跃、哪些已提交。底层机制明白了再看隔离级别就会清楚很多。1.3 锁写写互斥的保底手段MVCC把读写并发的问题解决了大半但“写写并发”依然是硬骨头。两个事务同时更新同一行数据不能各自为政必须排他。这就是锁登场的场景——通过粒度不同的锁和不同的锁定模式把并发控制变成一套精密的调度系统。话说回来锁不是MySQL独有的发明几乎所有关系型数据库都有只是实现细节略有差异。我们日常讨论最多的MySQL InnoDB引擎支持表级锁和行级锁配合共享锁、排他锁、意向锁这几种模式覆盖了绝大多数并发场景。1.4 隔离级别怎么把这套东西串起来数据库的READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ、SERIALIZABLE四种隔离级别本质上就是对MVCC可见性规则和锁使用力度的不同配置。READ UNCOMMITTED完全不管能读到未提交数据性能最高但几乎不用于生产。READ COMMITTED每次读都生成新的ReadView只能看到已提交数据解决脏读。REPEATABLE READ事务内第一次读时生成ReadView后续读复用保证同一事务多次读结果一致解决不可重复读。这是MySQL的默认级别InnoDB还额外用间隙锁解决了幻读。SERIALIZABLE直接把读也升级为加锁读彻底串行化最安全但并发能力最差。理解了这条主线后面所有锁的细节都能串起来了。2. 从表锁到行锁粒度选择的代价与收益2.1 锁粒度是怎么演进的早期数据库引擎包括MyISAM清一色用表级锁。表锁的好处是简单——锁整张表数据结构开销小不会出现死锁复杂度爆炸的问题但坏处也明显一张表的任何写操作都会阻塞其它所有读写操作。放到并发量高的业务里一张热点表基本就是性能瓶颈。后来InnoDB引入了行级锁把锁的粒度从“整张表”缩小到“具体的某一行”或某个范围。这意味着事务A锁住第1行时事务B仍然可以操作第2行并发度大幅提升。但代价也来了锁的元数据变多加锁判断变复杂更重要的是多个事务各自锁定不同行然后彼此等待对方释放就会形成死锁。这也是为什么InnoDB需要专门提供死锁检测机制的原因。2.2 InnoDB行锁的真实结构InnoDB的行锁不是简单锁“一行数据”而是锁在索引记录上。具体来说锁定的是索引项——如果有聚簇索引主键索引就锁主键索引上的记录如果没有可用索引InnoDB会退化到锁全表实际上是锁所有记录。这个细节是很多性能事故的根源。另外InnoDB行锁有三种锁定算法Record Lock精确锁定单条索引记录也就是“行锁”的最基本形态。Gap Lock间隙锁锁定一个范围但不包括记录本身用来防止幻读。Next-Key Lock记录锁和间隙锁的结合体锁定范围包括记录本身及前面的间隙。在RR隔离级别下配合范围查询时InnoDB默认用这种锁。用一个简单例子说明表t有id为1、5、9三条记录如果你执行SELECT * FROM t WHERE id BETWEEN 1 AND 9 FOR UPDATEInnoDB不仅会锁住1、5、9这三行还会锁住(1,5)和(5,9)之间的空隙阻止其他事务插入id3、id7这类数据从而保证此时查询结果集的稳定。2.3 一个没有索引的UPDATE为什么会让全表“卡死”回到我开头讲的线上事故。那条UPDATE语句的WHERE条件是普通非索引列InnoDB为了保证更新正确性必须扫描全表找到所有匹配的行。行锁的特性决定了扫描过程中每一行只要被检查到就会加锁。虽然理论上符合条件的行只有一部分但扫描过程会把全表所有记录的锁都加上——本质上和表锁没区别了而且行锁的内存开销比表锁还高性能反而更差。这就是为什么DBA天天强调更新、删除操作的WHERE条件一定要走索引。索引能帮数据库快速定位到目标行锁的数量就能控制在极小的范围内。提示判断一条写语句锁范围最直接的方法是用EXPLAIN看执行计划里的key字段。如果显示NULL说明没走索引这条语句在并发环境下极可能引发锁表需要立刻优化。3. 共享锁和排他锁读读不互斥读写要分清3.1 两种锁的定义与兼容矩阵锁的“模式”和“粒度”是两个维度。粒度说的是锁的范围表还是行模式说的是锁的访问权限。InnoDB最基本的两种锁模式是共享锁S Lock, Shared Lock也叫读锁。多个事务可以同时持有同一资源的共享锁谁都能读谁也不能写。排他锁X Lock, Exclusive Lock也叫写锁。一旦某个事务持有排他锁其它事务既不能加共享锁也不能加排他锁直到锁释放。它们的兼容关系可以用一张小表说清楚锁模式共享锁(S)排他锁(X)共享锁(S)兼容冲突排他锁(X)冲突冲突简单记忆共享锁之间互不打架排他锁和谁都不兼容。这个设计逻辑很直观——多个事务同时读一行数据没有风险但只要有写操作介入就必须独占。3.2 什么时候走共享锁什么时候走排他锁SELECT默认不加锁走的是MVCC快照读性能最好。但有些场景必须加锁读如果业务要求“先查出来再基于查到的结果去更新”并且必须保证这个过程中数据没有被其他人改过那就需要SELECT ... FOR UPDATE加排他锁。典型场景是库存扣减先查询剩余库存判断是否足够再执行扣减。如果不加锁两个事务同时查到库存为5各自都认为自己可以扣减最终库存变成负数。如果业务只需要保证“读取期间数据不被修改”但允许其它事务并发读那可以用SELECT ... LOCK IN SHARE MODE加共享锁。比如导出报表时希望看到某一刻的一致快照同时又不阻塞其它所有读操作。SELECT ... LOCK IN SHARE MODE和FOR UPDATE是行锁层面最常见的显式用法。不过坦白讲实际生产中用LOCK IN SHARE MODE的场景不多因为MVCC在大多数情况下已经提供了足够的一致性保证过度加锁反而拖垮并发。3.3 加锁SQL的实操写法与注意事项在MySQL里显式加锁的写法如下-- 加共享锁 SELECT * FROM account WHERE id 100 LOCK IN SHARE MODE; -- 加排他锁 SELECT * FROM account WHERE id 100 FOR UPDATE;FOR UPDATE必须放在事务里用配合COMMIT或ROLLBACK释放锁。我见过很多新手在未开启事务的情况下执行FOR UPDATE结果锁瞬间释放完全没有保护效果。MySQL默认autocommit1每一条语句都是独立事务显式开启事务需要执行START TRANSACTION或BEGIN。注意FOR UPDATE锁定的是“查出来的记录”如果查询条件里没有索引同样会退化成锁全表。所以加锁读的WHERE条件也必须走索引。另一个容易忽略的点FOR UPDATE只对SELECT生效。如果把普通UPDATE写成UPDATE account SET balance balance - 100 WHERE id 100这本身就是排他锁操作不需要额外加锁。行锁是自动获取的不需要显式声明但要注意此时这条更新如果条件没走索引同样会升级为全表锁。4. 意向锁数据库里最容易被忽略的“隐形调度者”4.1 为什么需要意向锁很多人第一次听到“意向锁”都会觉得莫名其妙——数据库里已经有共享锁和排他锁了怎么又来两种意向锁的全名是Intention Lock它本身不直接锁数据而是一种“预告”机制。想象一个场景事务T1要给某一行加排他锁事务T2要给整张表加排他锁。如果数据库没有意向锁InnoDB判断T2能否加表锁时必须逐行检查这张表里有没有行锁——这对千万级大表来说是不可接受的性能灾难。有了意向锁之后事务T1在给行加X锁之前必须先给表加IX锁意向排他锁事务T2想加表级X锁时只需要检查表上有没有意向锁就能快速判断是否有行锁冲突不用再遍历所有行。这个过程就像小区大门的管理员每个业主出门前先在门卫室登记“我要出门”门卫想确认小区里还有没有人没回来时看一眼登记表就行不必挨家挨户敲门。4.2 意向锁的兼容规则这么记就对了InnoDB的意向锁也分两种IS锁Intention Shared Lock事务准备给某行加共享锁先在表级别打上“我可能要读某些行”的标记。IX锁Intention Exclusive Lock事务准备给某行加排他锁先在表级别打上“我可能要写某些行”的标记。意向锁和普通表锁的兼容关系如下锁类型ISIXS(表)X(表)IS兼容兼容兼容冲突IX兼容兼容冲突冲突S(表)兼容冲突兼容冲突X(表)冲突冲突冲突冲突核心规律就两条意向锁之间永远互相兼容因为它们只是“预告”不真锁数据。表级排他锁和所有意向锁冲突表级共享锁和IX锁冲突。一句话记忆意向锁之间不互斥但任何人都别想越过意向锁直接锁整张表。比如某个事务已经对某行加了IX锁另一个事务想直接给整张表加X锁不行——因为表里已经有事务在写某些行了你锁全表等于把别人踢出去必须等对方提交。4.3 从information_schema看锁的真实面貌纸上谈兵不如眼见为实。MySQL提供了information_schema库下的几张表可以直观看到当前锁的状态。最常用的是这三张表名作用INNODB_TRX查看当前所有运行中的事务及其状态INNODB_LOCKS查看当前持有的锁不同版本字段有差异高版本叫performance_schema.data_locksINNODB_LOCK_WAITS查看锁等待关系即谁在等谁的锁排查锁问题时先看INNODB_TRX确认事务是否存活再看INNODB_LOCK_WAITS找到阻塞链最后通过INNODB_LOCKS定位到具体锁类型。这个排查套路我在后文的问题排查章节还会展开讲。我自己在排查线上死锁时常用的一个组合SQL是这样-- 查看当前所有事务 SELECT * FROM information_schema.INNODB_TRX\G -- 查看锁等待关系 SELECT * FROM information_schema.INNODB_LOCK_WAITS\G配合SHOW ENGINE INNODB STATUS\G可以拿到最近一次死锁的详细信息里面会明确展示两个事务各自的SQL语句和持有的锁是排查死锁的第一手资料。5. MVCC和锁在事务中的协同运作5.1 快照读与当前读MVCC和锁不是两套割裂的机制而是同一套并发控制体系下的分工。要理解它们如何协同先要分清两种读的概念快照读Snapshot Read普通SELECT不加任何锁走MVCC读历史版本。在RR级别下事务内第一次快照读建立ReadView后续复用在RC级别下每次读都新建ReadView。当前读Current ReadSELECT ... FOR UPDATE、SELECT ... LOCK IN SHARE MODE、以及UPDATE、DELETE语句执行前的数据读取。当前读必须读取最新已提交版本并对读到的记录加锁防止其他事务同时修改。快照读和当前读的差异决定了事务里不同的SQL语句能看到的数据版本可能不同。最经典的问题是一个RR级别的事务先快照读一行数据然后另一个事务更新并提交了这行数据原本的事务再执行SELECT ... FOR UPDATE读到的还是旧数据但加锁的范围已经变了——这在特殊场景下会引发意料之外的行为。5.2 一条UPDATE语句的完整加锁过程来看一条最简单的更新语句分析它到底干了什么START TRANSACTION; UPDATE account SET balance balance - 100 WHERE id 100;执行过程大致是这几步当前事务获取排他锁检查id100这行是否已有其他事务持锁如果有进入等待队列。获取该行的行级排他锁X锁同时给表加上意向排他锁IX锁。如果id100这行不存在在RR级别下对不存在的间隙加Gap Lock阻止其他事务插入id100这条记录。修改数据生成新版本把旧版本写入undo log用于回滚和MVCC快照读。事务提交时释放该行上的X锁和表上的IX锁。这个流程展示了MVCC和锁的协作MVCC负责让同时进行的其他读事务还能读到旧版本数据不会因为这次更新而阻塞而锁负责阻止其他写事务同时修改这一行。5.3 RR隔离级别下的间隙锁与幻读REPEATABLE READ不能完全杜绝幻读InnoDB为了兼顾性能和安全用了Next-Key Lock来解决这个问题——它把记录锁和间隙锁合在一起。假设表t的id索引上有1、5、9三条记录事务A执行SELECT * FROM t WHERE id BETWEEN 5 AND 9 FOR UPDATE;此时不仅5和9两行被锁(5,9)这个天然空隙也被锁住了。事务B如果尝试插入id7的数据会被锁阻塞。这就是间隙锁的核心价值锁定一个范围不允许范围被插入新数据。但间隙锁有个容易让人困惑的点它锁的是“间隙”而不是“记录”所以两个事务可以同时持有重叠的间隙锁——因为它们都没有锁定具体的行互相并不冲突。只有真正插入数据时才会互相阻塞。这种设计大大降低了死锁概率但也留下了一个诡异的坑两个事务都持有重叠的间隙锁然后各自尝试插入数据彼此等待对方释放形成死锁。间隙锁的性能影响同样不能忽视。它意味着范围查询会阻塞范围内所有插入操作所以即便只是SELECT ... FOR UPDATE一个范围也会让该范围内的数据插入全部串行化。高并发写入场景下遇到这种锁冲突先从SQL层面找问题往往比调数据库参数更有效。6. 线上锁问题排查实录与解决技巧6.1 经典死锁场景复盘先说一个我实际处理过的死锁案例。某订单系统有两个事务事务A先更新订单表再更新库存表。事务B先更新库存表再更新订单表。正常执行时各走各的路但如果两个事务交叉执行就可能出现A锁住订单行、等待库存行B锁住库存行、等待订单行。两边都在等对方释放数据库检测到死锁后会主动回滚其中一个事务通常是undo log更小的事务另一个继续执行。排查死锁最直接的方法是看SHOW ENGINE INNODB STATUS的死锁信息区块。它会打印出LATEST DETECTED DEADLOCK里面包含两个事务各自的持有锁列表、等待锁列表以及相关SQL。通过这个信息可以反推出事务的加锁顺序然后从代码层面统一两个事务的加锁顺序死锁就能消除。6.2 锁等待超时的排查三板斧线上最怕的不是死锁而是锁等待——一个事务迟迟不提交把其他所有事务全堵住最终报Lock wait timeout exceeded错误。MySQL默认innodb_lock_wait_timeout是50秒超过就报错回滚。遇到这类问题我的排查思路固定在三个步骤第一步看INNODB_TRX表找出运行时间特别长、状态为RUNNING的事务。很多锁等待的根源是某个事务开启了但没有提交比如代码里漏了COMMIT或者事务里面做了耗时的外部接口调用。第二步看INNODB_LOCK_WAITS表找出谁阻塞了谁。这个表会明确展示BLOCKING_TRX_ID和REQUESTING_TRX_ID顺着链条找到源头事务。第三步结合业务日志确认源头事务为什么没提交。如果是代码逻辑问题修复代码如果是长事务想办法拆短事务。提示排查阶段优先用SELECT * FROM information_schema.INNODB_TRX\G看trx_started字段判断事务启动时间。如果事务已经跑了十几分钟还没提交基本可以锁定问题源头了。6.3 减少锁冲突的实践建议结合这些年处理过的各种锁问题我总结了几个亲测有效的实践原则事务务必短小精悍。把事务内不需要的查询、外部接口调用、消息发送全部移出事务范围。锁的持有时间越短冲突概率指数级下降。写操作务必走索引。这是最容易被忽略却影响最大的一条。没有索引的UPDATE/DELETE会让行锁退化成表锁并发度瞬间归零。固定加锁顺序。如果多个事务要更新多张表或多行数据约定一个固定的顺序比如按主键从小到大。这样可以避免循环等待直接消灭一大类死锁。不要用SELECT ... FOR UPDATE做复杂业务。能用MVCC快照读解决的问题就不要加锁。比如库存扣减可以用UPDATE ... SET stock stock - #{num} WHERE id ? AND stock #{num}这种原子条件更新完全不需要先SELECT再UPDATE的两步走。定期检查慢查询日志。锁冲突往往和慢SQL相伴而生。一条跑了几秒的UPDATE会把范围锁挂很久它释放之前所有相关操作都要排队。我个人在实际排查中还养成了一个习惯每次遇到锁相关故障都会把当时的死锁日志和INNODB_TRX快照存档事后复盘时建一个“锁问题案例库”。次数多了以后很多看似诡异的锁现象对照历史案例很快就能找到方向。这个习惯比记多少理论都管用建议大家也试试——处理数据库锁问题实战案例积累越多排查速度就越快。