MySQL核心机制：事务、锁与存储引擎

前面四篇文章你的 SQL 已经能建表、查询、优化——但这一切都假设只有一个用户在操作数据库。真实的生产环境里几百上千个请求同时读写同一张表才是常态。如果两个用户同时抢最后一门课的选课名额MySQL 怎么保证不会超卖如果一个人转账到一半系统崩了钱会不会丢本文深入事务、锁和 MVCC讲清楚 MySQL 是怎么在并发环境下保证数据正确的。事务是什么事务Transaction是一组要么全部成功、要么全部失败的操作单元。最经典的例子是转账-- 张三转 100 元给李四UPDATEaccountsSETbalancebalance-100WHEREname张三;UPDATEaccountsSETbalancebalance100WHEREname李四;如果第一条执行完后系统崩溃了张三的 100 元扣掉了但李四没收到——钱凭空消失了。有了事务这两条 UPDATE 被包装成一个原子操作STARTTRANSACTION;UPDATEaccountsSETbalancebalance-100WHEREname张三;UPDATEaccountsSETbalancebalance100WHEREname李四;COMMIT;COMMIT之前所有的修改对其他连接不可见、随时可以撤销。COMMIT之后修改永久生效。-- 如果发现搞错了在 COMMIT 之前随时可以反悔STARTTRANSACTION;DELETEFROMstudentsWHEREid1;-- 等等删错了ROLLBACK;-- 撤销一切回到 START TRANSACTION 之前的状态一个简单的原则当一个业务操作需要修改多行数据、而只改一半是不可接受的状态时把它包在一个事务里。这个原则适用于绝大多数写操作——扣库存生成订单、从A账户转出向B账户转入、插入主表插入关联表。ACID 四大特性事务的可靠性建立在四个特性上简称 ACID。特性含义反例原子性 (Atomicity)要么全做要么全不做扣了钱没加上一致性 (Consistency)事务前后数据都满足所有约束转账后总额不对、余额变成负数隔离性 (Isolation)并发事务之间互不干扰A 读到 B 未提交的修改脏读持久性 (Durability)提交后的数据不会丢机房断电后数据没了其中**隔离性Isolation**是日常开发中最容易出问题、也是最需要理解的部分。下面重点展开。并发带来的三个问题当多个事务同时操作同一份数据时如果没有任何隔离措施会出现三种经典问题。我用学生选课场景来说明。脏读一个事务读到了另一个事务尚未提交的修改。时间线 事务A修改分数 事务B读分数 -------------------------------------------------------- T1 UPDATE enrollments SET score 95 WHERE student_id 1 AND course_id 1; T2 SELECT score FROM enrollments WHERE student_id 1 AND course_id 1; → 读到 95 T3 ROLLBACK; ← 撤销了事务 B 读到了 95 分但这个值从来没有真正存在过——事务 A 回滚了分数还是原来的。如果 B 基于这个 95 分做了后续操作比如奖励 90 分以上的学生就会造成逻辑错误。不可重复读在同一个事务内两次读取同一行数据结果不一样。时间线 事务A查询两次 事务B修改并提交 -------------------------------------------------------- T1 SELECT score FROM enrollments WHERE student_id 1 AND course_id 1; → 结果是 88 T2 UPDATE enrollments SET score 95 WHERE student_id 1 AND course_id 1; COMMIT; T3 SELECT score FROM enrollments WHERE student_id 1 AND course_id 1; → 结果是 95 ← 和第一次不一样了事务 A 内部两次同样的查询返回了不同的值。如果你的业务逻辑依赖读到的值在事务期间不变比如生成报表时同一行的数据前后对不上这就是问题。幻读在同一个事务内两次查询同一个范围的数据第二次多出来了一些行。时间线 事务A统计选课人数 事务B插入新选课记录 -------------------------------------------------------------- T1 SELECT COUNT(*) FROM enrollments WHERE course_id 1; → 4 人选了高等数学 T2 INSERT INTO enrollments (student_id, course_id) VALUES (8, 1); COMMIT; T3 SELECT COUNT(*) FROM enrollments WHERE course_id 1; → 5 人选了高等数学 ← 多了一行不可重复读是同一行数据变了幻读是多出来了一些行。它们的区别在于不可重复读针对的是已存在的行被修改幻读针对的是新插入或删除的行。四种隔离级别MySQL 提供四种隔离级别每种级别解决了不同的问题。注意下面的表格是MySQL InnoDB 引擎的实际行为。隔离级别脏读不可重复读幻读实现机制READ UNCOMMITTED可能可能可能不加锁直接读最新值READ COMMITTED不可能可能可能每次读都生成新的 ReadViewREPEATABLE READ (默认)不可能不可能不可能(部分)事务开始时生成一个 ReadView 间隙锁SERIALIZABLE不可能不可能不可能所有 SELECT 自动加共享锁MySQL 默认的REPEATABLE READ(RR) 通过MVCC 间隙锁的组合在 InnoDB 引擎下实际上解决了幻读。其他数据库如 PostgreSQL的 RR 级别可能只解决不可重复读而仍有幻读问题——这是 MySQL InnoDB 的一个优势。查看和设置隔离级别-- 查看当前隔离级别SELECTtransaction_isolation;-- 设置全局隔离级别影响所有新连接SETGLOBALtransaction_isolationREPEATABLE-READ;-- 设置当前会话的隔离级别SETSESSIONtransaction_isolationREAD-COMMITTED;选哪个级别SERIALIZABLE最安全但并发性能最差。所有读操作都加锁大量请求会排队。只用于对数据一致性要求极端的金融核心系统。REPEATABLE READMySQL 默认兼顾安全性和性能。绝大多数业务场景的默认选择。READ COMMITTEDPostgreSQL 和 Oracle 的默认级别。如果系统以简单 CRUD 为主、不太需要事务内多次读的一致性这个级别够用并发性能比 RR 更好。READ UNCOMMITTED基本只用于允许脏读的统计类场景比如近似估算用户数生产环境极少使用。MVCCMySQL 是怎么实现隔离性的前面讲了不可重复读被 RR 解决了但怎么解决的答案是 MVCC——多版本并发控制。核心思想MVCC 不给数据加锁而是给每个事务一份数据的快照。就像你有一个 Git 仓库每个事务看到的是仓库在某个时间点的状态别人后来的修改不会影响你的视图直到你提交。InnoDB 的实现依赖于两个隐藏列和一套版本链1. 两个隐藏列每一行数据实际存储时InnoDB 会在行数据后面附加两个隐藏列DB_TRX_ID6 字节最后一次修改这行的事务 IDDB_ROLL_PTR7 字节指向 undo log 中上一个版本的指针2. Undo Log回滚日志每次修改一行数据时InnoDB 会把修改前的旧版本记录到 undo log 中。所有 undo log 通过DB_ROLL_PTR形成一条版本链当前版本trx_id105 → undo log 版本1trx_id103 → undo log 版本2trx_id101 ↑ ↑ ↑ DB_ROLL_PTR ────────────→ DB_ROLL_PTR ────────────→ DB_ROLL_PTR → NULL3. ReadView读视图当事务执行一条 SELECT 时InnoDB 会创建一个 ReadView包含以下信息当前活跃的未提交的事务 ID 列表最小活跃事务 ID下一个要分配的事务 IDInnoDB 根据 ReadView 从版本链中找到对本事务可见的版本——规则是如果一行的DB_TRX_ID等于本事务 ID自己改的可见如果小于最小活跃事务 ID在本事务开始前就已提交可见如果在活跃事务列表中未提交不可见。RR 和 RC 的 ReadView 策略差异REPEATABLE READREAD COMMITTEDReadView 生成时机事务第一次读取时生成整个事务期间复用每次读取时都生成新的 ReadView效果整个事务看到同一个快照——不可重复读被消除每次读都能看到最新已提交的数据——不可重复读仍有可能这就是为什么 RR 能解决不可重复读——因为它的 ReadView 只创建一次同一事务内无论读多少次看到的都是同一个快照。trx_id 在本事务开始前已提交trx_id 是活跃事务事务开始分配 trx_id 105第一次 SELECT创建 ReadView判断版本可见性根据 DB_TRX_ID ReadView可见返回该版本不可见沿 DB_ROLL_PTR 找上一个版本RR 级别ReadView 保留后续 SELECT 复用同一视图同一事务内永远看到相同数据一个具体例子来感受 RR 的行为。打开两个 MySQL 会话并排操作会话 ASETSESSIONtransaction_isolationREPEATABLE-READ;STARTTRANSACTION;SELECTscoreFROMenrollmentsWHEREstudent_id1ANDcourse_id1;-- 结果88.0会话 B在会话 A 的事务期间执行UPDATEenrollmentsSETscore95.0WHEREstudent_id1ANDcourse_id1;COMMIT;-- 修改已提交回到会话 ASELECTscoreFROMenrollmentsWHEREstudent_id1ANDcourse_id1;-- 结果仍然是 88.0 —— RR 快照挡住了其他事务的提交COMMIT;锁机制MVCC 解决了读-写并发事务读的时候不阻塞别人写但当两个事务同时写同一行数据时必须有锁来协调。行锁 vs 表锁行锁只锁住被操作的那几行其他行可以并行操作。InnoDB 的默认行为并发性能好。表锁锁住整张表其他事务不能读写。MyISAM 的默认行为并发性能差。InnoDB 的行锁是加在索引上的不是加在物理行上的。如果一条 UPDATE 的 WHERE 条件没有走索引InnoDB 会退化为表锁。-- name 列有索引 → 行锁只锁张三那一行UPDATEstudentsSETage21WHEREname张三;-- name 列没有索引 → 表锁锁住整个 students 表UPDATEstudentsSETage21WHEREname张三;当你写UPDATE ... WHERE或DELETE ... WHERE时确保 WHERE 条件走了索引。不索引的列做更新条件触发的不只是慢查询——而是表级锁整个表的写操作都会被阻塞。这个坑线上环境的故障率极高。共享锁与排他锁锁类型SQL 写法兼容性共享锁 (S Lock)SELECT ... LOCK IN SHARE MODE(MySQL 8.0 前) /SELECT ... FOR SHARE与共享锁兼容与排他锁冲突排他锁 (X Lock)SELECT ... FOR UPDATE/INSERT/UPDATE/DELETE与任何其他锁都冲突排他锁的意思是我要改这行在此期间谁也不许动STARTTRANSACTION;-- 锁定张三的高等数学成绩准备修改SELECT*FROMenrollmentsWHEREstudent_id1ANDcourse_id1FORUPDATE;-- 此时其他事务不能对这行加任何锁直到 COMMITUPDATEenrollmentsSETscore95.0WHEREstudent_id1ANDcourse_id1;COMMIT;间隙锁为什么 MySQL 的 RR 能防幻读间隙锁锁住的不是实际存在的行而是行与行之间的间隙。在REPEATABLE READ隔离级别下当你执行一条范围查询比如WHERE student_id BETWEEN 1 AND 10时InnoDB 不仅锁住存在的行还锁住这些行之间的间隙——防止其他事务在这个间隙中插入新行从而避免了幻读。数据行: student_id1 [间隙] student_id2 [间隙] student_id4 [间隙] student_id7 SELECT ... WHERE student_id BETWEEN 1 AND 7 FOR UPDATE; 锁住: [行1] [间隙1-2] [行2] [间隙2-4] [行4] [间隙4-7] [行7]间隙锁的代价是降低了并发插入的性能——因为插入可能被间隙锁阻塞。如果你确定自己的业务场景不会出现幻读问题可以将隔离级别降到 READ COMMITTED 开启binlog_formatROW来禁用间隙锁。死锁死锁是怎么发生的两个事务各自持有对方需要的锁互相等待形成死循环时间线 事务A 事务B -------------------------------------------------------------- T1 START TRANSACTION; START TRANSACTION; T2 UPDATE enrollments UPDATE enrollments SET score 95 SET score 85 WHERE student_id 1 WHERE student_id 2 AND course_id 1; AND course_id 1; -- 锁住了(1,1)这行 -- 锁住了(2,1)这行 T3 UPDATE enrollments UPDATE enrollments SET score 90 SET score 80 WHERE student_id 2 WHERE student_id 1 AND course_id 1; AND course_id 1; -- 等待 B 释放(2,1)的锁... -- 等待 A 释放(1,1)的锁... ↑ 死锁 ↑ 死锁MySQL 怎么处理死锁InnoDB 会自动检测死锁通过等待图 wait-for graph并选择回滚代价最小的事务通常是修改行数最少的那个让它失败并释放锁。被回滚的事务收到错误ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction排查死锁-- 查看最近一次死锁的详细信息SHOWENGINEINNODBSTATUS\G在输出的LATEST DETECTED DEADLOCK部分你可以看到是哪两个事务、在哪两条 SQL 上发生了死锁、各自持有什么锁、等待什么锁。预防死锁的四个习惯1. 固定加锁顺序。所有事务以相同的顺序访问表和数据行。比如约定先操作 students 再操作 enrollments先锁 student_id1 再锁 student_id2。2. 事务尽量短。事务里只放必须一起执行的操作不要把发邮件、调第三方 API 这种外部操作放进事务。一个打开几分钟的事务持有锁不放是制造死锁的最佳配方。3. 批量操作分批次提交。不要在一个事务里DELETE或UPDATE几十万行数据——锁住的间隙会非常大极易与其他事务发生冲突。-- 不好的做法DELETEFROMlogsWHEREcreated_at2020-01-01;-- 好的做法每次只删 1000 行分批提交DELETEFROMlogsWHEREcreated_at2020-01-01LIMIT1000;-- 重复执行直到删完每次 COMMIT 释放锁4. 在 RR 隔离级别下注意间隙锁的范围。范围查询BETWEEN、、会在 RR 下触发间隙锁。如果业务场景允许往 RC 降级或用唯一索引做精确查询。存储引擎MySQL 支持多种存储引擎每种引擎有不同的数据存储方式、锁策略和功能特性。查看自己的表用的什么引擎SHOWTABLESTATUSWHERENamestudents;-- 或SHOWCREATETABLEstudents;InnoDB绝对主流MySQL 5.5 之后 InnoDB 就是默认引擎现在占了绝大多数使用场景。它的核心优势支持事务ACID支持行级锁——高并发写入性能好支持外键——保证引用完整性MVCC——读写不互相阻塞崩溃恢复——通过 redo log 保证持久性MyISAM老古董MySQL 5.5 之前的默认引擎现在只存在于遗留系统中。它不支持事务、不支持行锁只有表锁、不支持外键。它的唯一优势是——在纯读的场景下表锁开销比行锁小读速度可能略快。但在今天这个 SSD 高并发的时代这个优势微乎其微。如果你接手了一个老项目发现有的表用的是 MyISAM找个机会迁移到 InnoDB。一行 SQL 的事ALTER TABLE xxx ENGINEInnoDB;。除了全文索引有明显差异外其他方面的收益远超迁移成本。其他引擎速览引擎特点适用场景Memory数据全在内存重启丢失临时表、缓存Archive高压缩比只支持 INSERT 和 SELECT归档日志CSV数据存为 CSV 文件数据交换日常开发中其他引擎知道存在就行InnoDB 是唯一的默认答案。实战模拟并发选课回到学生选课系统。假设数据结构这门课course_id4有人数限制当前 7 个学生里有 5 人选了。如果同时两个人想选且只剩最后一个名额会发生什么开两个 MySQL 会话并排操作会话 ASETSESSIONtransaction_isolationREPEATABLE-READ;STARTTRANSACTION;-- 查当前选课人数SELECTCOUNT(*)FROMenrollmentsWHEREcourse_id4;-- 结果5-- 人数没满插入选课记录INSERTINTOenrollments(student_id,course_id)VALUES(6,4);-- 周八选了数据结构-- 现在有 6 人选了还剩 1 个名额会话 B在会话 A 提交之前执行STARTTRANSACTION;-- 查当前选课人数RR 下看到的是快照还是 5SELECTCOUNT(*)FROMenrollmentsWHEREcourse_id4;-- 结果5快照-- 人数没满也插入选课记录INSERTINTOenrollments(student_id,course_id)VALUES(7,4);-- 吴九选了数据结构-- 也成功了——RR 的快照让两个事务都以为还有名额回到会话 ACOMMIT;会话 BCOMMIT;两个事务都提交了选课人数变成了 7——而课程容量是 6。单纯依赖SELECT COUNT(*)然后判断再插入在并发场景下会超卖。解决方案用 SELECT … FOR UPDATESTARTTRANSACTION;-- 锁定 course_id4 的所有选课行并锁定间隙阻止其他事务插入新选课记录SELECTCOUNT(*)FROMenrollmentsWHEREcourse_id4FORUPDATE;-- 结果5-- 人数没满插入INSERTINTOenrollments(student_id,course_id)VALUES(6,4);COMMIT;当会话 A 执行SELECT ... FOR UPDATE时它锁住了 course_id4 的现有行以及之间的间隙。会话 B 在试图做同样的SELECT ... FOR UPDATE时会被阻塞直到 A 提交。这就保证了计数的正确性。注意这个方案的并发瓶颈很明显——同一门课的选课操作被串行化了。在真正高并发的选课系统里通常会引入 Redis 做缓存计数、用消息队列异步处理减少数据库锁竞争。这个优化不展开——留给你后面探索。总结事务和锁是 MySQL 在并发环境下保障数据正确性的核心机制事务 一组不可分割的操作COMMIT提交、ROLLBACK回滚ACID中隔离性最需要深入理解——脏读、不可重复读、幻读三种并发问题的定义和区别MySQL 默认 REPEATABLE READ通过 MVCCReadView undo log 版本链实现快照读通过间隙锁防止幻读行锁加在索引上没走索引的 UPDATE/DELETE 会退化为表锁死锁由 InnoDB 自动检测并回滚代价最小的事务预防靠固定加锁顺序、短事务、分批提交InnoDB 是唯一选择MyISAM 不支持事务和行锁应迁移SELECT ... FOR UPDATE是先查后改并发场景的标准解决方案但它会降低并发度高并发系统需要更复杂的策略下一篇文章是本系列的最后一篇——从 SQL 终端切换到 Python 代码用 SQLAlchemy ORM 把学生选课系统改写成完整的 FastAPI 项目并对比 Java/Go/JS 三个生态的 ORM 方案。