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 执行成功