news 2026/4/16 15:04:19

MySQL 基础教程 - 第九章：事务与锁机制

作者头像

张小明

前端开发工程师

1.2k 24
文章封面图
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 元)
这涉及三个核心操作：

  1. 扣减库存：inventory表 stock - 1
  2. 创建订单：orders表 insert 一条记录
  3. 扣减余额：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 自动回滚 AB 执行成功

9.6 总结

  1. 完整性：事务通过 ACID 保证了复杂业务（如支付下单）的数据完整性。
  2. 隔离性：理解 RR 级别和 MVCC，知道为什么“读不到别人已提交的数据”。
  3. 锁的艺术：
    • 更新必走索引（避开表锁）。
    • 顺序加锁（避开死锁）。
    • 读多写少用乐观锁，写多读少用悲观锁。

下一章，我们将进入 DCL（用户管理），学习如何为不同的开发人员分配不同的数据库权限。

版权声明: 本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若内容造成侵权/违法违规/事实不符，请联系邮箱：809451989@qq.com进行投诉反馈，一经查实，立即删除！
网站建设 2026/4/15 7:54:37

第六章：高级查询技术 (DQL) —— 驾驭复杂数据的艺术

核心摘要&#xff1a; 如果说基础查询是“捡起地上的苹果”&#xff0c;那么高级查询就是“设计一套自动化收割系统”。 本章是 SQL 学习的分水岭。我们将深入研究聚合统计的底层差异&#xff08;COUNT(*) 到底慢不慢&#xff1f;&#xff09;、分组陷阱&#xff08;ONLY_FULL_…

作者头像 李华
网站建设 2026/4/15 10:00:20

结构体（Java 类）实战题解笔记（持续更新）

前言 Java也可以有结构体吗&#xff1f; 在 Java 中并没有直接的「结构体」概念&#xff0c;但可以通过自定义类&#xff08;class&#xff09; 实现结构体的核心功能——封装一组具有关联关系的数据。本笔记通过实战题目&#xff0c;讲解如何用自定义类存储复杂数据、处理业务…

作者头像 李华
网站建设 2026/4/16 13:06:10

【Linux系统】进程间通信：基于匿名管道实现进程池

1. 进程池介绍 ​ 1.1 核心定义 进程池&#xff08;Process Pool&#xff09; 是一种预创建复用式的进程管理技术&#xff0c;其本质是操作系统中预分配的进程资源容器。它包含两大核心组件&#xff1a; 资源进程&#xff1a;池中预先创建的空闲进程&#xff0c;随时待命执…

作者头像 李华
网站建设 2026/4/16 10:42:38

第 8 篇：适配器模式 (Adapter) —— 换芯片不换代码

专栏导读：适配器模式就像我们出国的“电源转换插头”。你（业务层）需要的是标准的 220V 两孔插座，而墙上（硬件层）提供的是美标、英标、欧标各种奇形怪状的插孔。适配器负责在中间做一次“翻译”，让你根本不需要关心墙后面是核电还是水电。 1. 场景还原 (The Pain) 假设你…

作者头像 李华
网站建设 2026/4/16 7:44:12

Python中 .whl 后缀文件的全称

你想了解Python中.whl后缀文件的全称&#xff0c;以及文件名各部分的含义&#xff0c;对吧&#xff1f; 首先先纠正一个小偏差&#xff0c;.whl的全称不是“啥”&#xff0c;而是Wheel&#xff08;字面意思是“轮子”&#xff09;&#xff0c;它是Python的一种预编译软件包格式…

作者头像 李华
网站建设 2026/4/16 7:43:47

Type-C 领夹麦的核心痛点与 PD 协议解决方案

领夹麦作为直播、录音场景的核心设备&#xff0c;长期面临三大技术瓶颈&#xff1a;传统单接口无法同时实现 “音频传输 快充供电”&#xff0c;导致直播中途断电&#xff1b;充电电流干扰音频信号&#xff0c;产生底噪&#xff1b;设备兼容性差&#xff0c;难以适配多品牌手机…

作者头像 李华