MySQL 基础教程 - 第九章：事务与锁机制
MySQL 基础教程 - 第九章：事务与锁机制
摘要：在多人并发访问的数据库系统中，如何保证数据不会“打架”？本章将深入探讨 MySQL 的核心特性——事务 (Transaction)。我们将抛弃简单的“转账”玩具模型，基于一个完整的电商订单支付系统，构建包含用户、账户、订单、库存的完整表结构（含外键约束）。在此基础上，深度剖析事务的 ACID 特性，复现脏读、幻读等并发事故，并实战演示 MySQL 5.7 默认的 RR 隔离级别是如何通过MVCC和锁机制解决这些问题的。
9.1 全景环境准备：电商支付系统
为了演示真实的事务场景，我们需要构建一个相互关联的业务系统。这包括：用户表、资金账户表、商品库存表、订单表。
请务必执行以下 SQL 脚本，确保实验环境的一致性。
-- 1. 初始化数据库CREATEDATABASEIFNOTEXISTSshop_bizCHARSET=utf8;USEshop_biz;-- 2. 清理旧数据 (如果存在)-- 注意删除顺序：先删子表 (有外键依赖的)，再删父表DROPTABLEIFEXISTSorders;DROPTABLEIFEXISTSaccounts;DROPTABLEIFEXISTSinventory;DROPTABLEIFEXISTSproducts;-- 假设 products 是库存的父表，这里简化合二为一DROPTABLEIFEXISTSusers;-- 3. 创建用户表 (Users) - 父表CREATETABLEusers(user_idINTPRIMARYKEYAUTO_INCREMENTCOMMENT'用户ID',usernameVARCHAR(50)NOTNULLCOMMENT'用户名',statusTINYINTDEFAULT1COMMENT'状态: 1-正常, 0-冻结')CHARSET=utf8ENGINE=InnoDBCOMMENT='用户表';-- 4. 创建资金账户表 (Accounts) - 子表 (1:1 关联用户)CREATETABLEaccounts(account_idINTPRIMARYKEYAUTO_INCREMENTCOMMENT'账户ID',user_idINTNOTNULLUNIQUECOMMENT'用户ID (外键)',balanceDECIMAL(10,2)NOTNULLDEFAULT0.00COMMENT'余额',versionINTNOTNULLDEFAULT0COMMENT'乐观锁版本号',CONSTRAINTfk_accounts_userFOREIGNKEY(user_id)REFERENCESusers(user_id))CHARSET=utf8ENGINE=InnoDBCOMMENT='资金账户表';-- 5. 创建商品库存表 (Inventory)CREATETABLEinventory(product_idINTPRIMARYKEYAUTO_INCREMENTCOMMENT'商品ID',product_nameVARCHAR(100)NOTNULLCOMMENT'商品名称',stockINTNOTNULLDEFAULT0COMMENT'库存数量')CHARSET=utf8ENGINE=InnoDBCOMMENT='商品库存表';-- 6. 创建订单表 (Orders) - 子表 (关联用户)CREATETABLEorders(order_idINTPRIMARYKEYAUTO_INCREMENTCOMMENT'订单ID',user_idINTNOTNULLCOMMENT'用户ID (外键)',product_idINTNOTNULLCOMMENT'商品ID',quantityINTNOTNULLDEFAULT1COMMENT'购买数量',total_amountDECIMAL(10,2)NOTNULLCOMMENT'订单金额',order_statusTINYINTNOTNULLDEFAULT0COMMENT'状态: 0-待支付, 1-已支付, 2-已取消',create_timeDATETIMEDEFAULTCURRENT_TIMESTAMPCOMMENT'创建时间',CONSTRAINTfk_orders_userFOREIGNKEY(user_id)REFERENCESusers(user_id))CHARSET=utf8ENGINE=InnoDBCOMMENT='订单表';-- 7. 初始化测试数据-- 用户INSERTINTOusers(username)VALUES('Alice'),('Bob'),('Charlie');-- 账户 (Alice 有 1000元, Bob 有 500元)INSERTINTOaccounts(user_id,balance)VALUES(1,1000.00),(2,500.00),(3,0.00);-- 库存 (iPhone 15 有 10 台)INSERTINTOinventory(product_name,stock)VALUES('iPhone 15',10);-- 验证数据SELECT*FROMusers;SELECT*FROMaccounts;SELECT*FROMinventory;
四个表的信息也是全的。
9.2 事务 (Transaction) 基础
9.2.1 什么是事务？
事务是一组 SQL 操作的集合，它们被视为一个不可分割的工作单元。
真实业务场景：Alice 购买一台 iPhone 15 (价格 100 元)
这涉及三个核心操作：
- 扣减库存：
inventory表 stock - 1 - 创建订单：
orders表 insert 一条记录 - 扣减余额：
accounts表 balance - 100
如果第 1、2 步成功，但第 3 步余额不足导致失败，如果没有事务，Alice 就白拿了一个手机，系统库存也对不上了。
9.2.2 ACID 四大特性详解
- 原子性 (Atomicity)：
- 定义：操作要么全做，要么全不做。
- 实现：靠Undo Log。如果事务执行一半失败了，MySQL 利用 Undo Log 把数据恢复到原来的样子（回滚）。
- 一致性 (Consistency)：
- 定义：事务前后，数据库的完整性约束（如外键、余额不为负）不被破坏。
- 实现：靠代码逻辑 + 数据库约束（如外键）+ 原子性/隔离性共同保证。
- 隔离性 (Isolation)：
- 定义：并发事务之间互不干扰。
- 实现：靠锁 (Locks)和MVCC (多版本并发控制)。
- 持久性 (Durability)：
- 定义：一旦提交，数据永久保存。
- 实现：靠Redo Log。即使断电，重启后也能通过 Redo Log 重放恢复数据。
9.2.3 事务控制实战
场景：模拟 Alice 购买手机的完整事务流程。
-- 1. 开启事务STARTTRANSACTION;-- 2. 扣减库存 (假设 product_id=1)UPDATEinventorySETstock=stock-1WHEREproduct_id=1;-- 3. 创建订单INSERTINTOorders(user_id,product_id,quantity,total_amount)VALUES(1,1,1,100.00);-- 4. 扣减余额UPDATEaccountsSETbalance=balance-100WHEREuser_id=1;-- 5. 模拟意外：手动回滚 (ROLLBACK) 看看效果-- 此时你可以新开一个查询窗口 SELECT 查看，会发现数据根本没变ROLLBACK;-- 6. 再次执行并提交 (COMMIT)STARTTRANSACTION;UPDATEinventorySETstock=stock-1WHEREproduct_id=1;INSERTINTOorders(user_id,product_id,quantity,total_amount)VALUES(1,1,1,100.00);UPDATEaccountsSETbalance=balance-100WHEREuser_id=1;COMMIT;-- 此时数据才真正生效9.3 事务隔离级别与并发问题
当多个用户同时抢购时，会发生什么？我们需要开启两个数据库连接（Session A 和 Session B）来模拟。
9.3.1 隔离级别一览
MySQL 5.7 支持 4 种隔离级别。
| 隔离级别 | 脏读 | 不可重复读 | 幻读 | 性能 |
|---|---|---|---|---|
| READ UNCOMMITTED(读未提交) | ✅ | ✅ | ✅ | 极高 (极不安全) |
| READ COMMITTED(读已提交) | ❌ | ✅ | ✅ | 高 (Oracle默认) |
| REPEATABLE READ(可重复读) | ❌ | ❌ | ❌ (大部分解决) | 中 (MySQL默认) |
| SERIALIZABLE(串行化) | ❌ | ❌ | ❌ | 低 (排队执行) |
9.3.2 脏读 (Dirty Read) 演示
前提：将 Session A 的隔离级别设置为“读未提交”。
-- Session A 设置SETSESSIONTRANSACTIONISOLATIONLEVELREADUNCOMMITTED;| Session A (Alice) | Session B (Bob) | 说明 |
|---|---|---|
START TRANSACTION; | START TRANSACTION; | |
UPDATE accounts SET balance = balance - 100 WHERE user_id = 2; | Bob 扣款 100，但未提交 | |
SELECT * FROM accounts WHERE user_id = 2;(结果: 400) | A 读到了 Bob 未提交的数据！ | |
ROLLBACK; | Bob 后悔了，回滚了操作 | |
UPDATE ... | A 以为 Bob 只有 400 块，基于此做了错误决策 |
9.3.3 可重复读 (Repeatable Read) 实战
这是 MySQL 的默认级别，也是我们最常用的。
恢复默认设置：
SETSESSIONTRANSACTIONISOLATIONLEVELREPEATABLEREAD;演示步骤：
| Session A (Alice) | Session B (Bob) | 说明 |
|---|---|---|
START TRANSACTION; | START TRANSACTION; | |
SELECT stock FROM inventory WHERE product_id = 1;(结果: 9) | A 看到库存是 9 | |
UPDATE inventory SET stock = 5 WHERE product_id = 1;COMMIT; | B 把库存改成了 5 并提交了！ | |
SELECT stock FROM inventory WHERE product_id = 1;(结果: 9) | A 看到的依然是 9！ (MVCC 发挥作用，保证视图一致性) | |
COMMIT; | A 提交事务 | |
SELECT stock FROM inventory WHERE product_id = 1;(结果: 5) | A 重新查询，看到了最新值 |
9.4 锁机制 (Lock) 深度解析
MVCC 解决了“读-写”冲突（读快照，写最新），但“写-写”冲突必须靠锁。
9.4.1 行锁 vs 表锁
InnoDB 的行锁是加在索引上的。
- 行锁 (Record Lock)：
-- user_id 是主键索引，只锁 id=1 这一行UPDATEaccountsSETbalance=balance-1WHEREuser_id=1; - 表锁 (Table Lock)：
-- 假设 balance 字段没有索引-- 这会锁住整张 accounts 表！其他人连 user_id=2 都改不了！UPDATEaccountsSETbalance=balance-1WHEREbalance=1000;⚠️ 警告：生产环境更新数据，务必确保
WHERE条件走了索引，否则会造成灾难性的锁表。
9.4.2 悲观锁实战：余额扣减
在高并发下防止余额扣成负数。
STARTTRANSACTION;-- 1. 显式加锁 (X锁)-- 这行 SQL 会让当前事务持有这行记录的排他锁，其他事务必须等待SELECTbalanceFROMaccountsWHEREuser_id=1FORUPDATE;-- 2. 检查余额 (应用层逻辑)-- if balance < 100: rollback-- 3. 执行扣款UPDATEaccountsSETbalance=balance-100WHEREuser_id=1;COMMIT;9.4.3 乐观锁实战：CAS 机制
不加锁，利用version字段解决冲突。
-- 1. 查询当前版本和余额SELECTbalance,versionFROMaccountsWHEREuser_id=1;-- 假设查出来 version = 0-- 2. 尝试更新-- 核心：WHERE 条件里加上 version = 0UPDATEaccountsSETbalance=balance-100,version=version+1WHEREuser_id=1ANDversion=0;-- 3. 检查受影响行数-- 如果为 1：更新成功-- 如果为 0：说明在第1步和第2步之间，有人修改了数据(version变了)，需要重试流程9.5 死锁 (Deadlock) 复现
场景：Alice 转账给 Bob，同时 Bob 转账给 Alice。
| Session A (Alice -> Bob) | Session B (Bob -> Alice) |
|---|---|
START TRANSACTION; | START TRANSACTION; |
UPDATE accounts SET balance=balance-10 WHERE user_id=1;(持有 id=1 的锁) | |
UPDATE accounts SET balance=balance-10 WHERE user_id=2;(持有 id=2 的锁) | |
UPDATE accounts SET balance=balance+10 WHERE user_id=2;(等待 id=2 的锁) | |
UPDATE accounts SET balance=balance+10 WHERE user_id=1;(等待 id=1 的锁) | |
| 死锁！MySQL 自动回滚 A | B 执行成功 |
9.6 总结
- 完整性：事务通过 ACID 保证了复杂业务（如支付下单）的数据完整性。
- 隔离性：理解 RR 级别和 MVCC，知道为什么“读不到别人已提交的数据”。
- 锁的艺术：
- 更新必走索引（避开表锁）。
- 顺序加锁（避开死锁）。
- 读多写少用乐观锁，写多读少用悲观锁。
下一章，我们将进入 DCL（用户管理），学习如何为不同的开发人员分配不同的数据库权限。