news 2026/5/23 16:05:43

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/5/9 6:18:17

【Linux】操作系统的认识

设计OS的目的 对下&#xff0c;与硬件交互&#xff0c;管理所有的硬件资源。对上&#xff0c;为用户提供良好&#xff08;稳定&#xff0c;高效&#xff0c;安全&#xff09;的执行环境。 但是这里的用户是指程序员&#xff0c;而不是普通人、用户&#xff0c;因为普通用户使…

作者头像 李华
网站建设 2026/5/13 20:43:00

Java SpringBoot+Vue3+MyBatis 经方药食两用服务平台系统源码｜前后端分离+MySQL数据库

系统架构设计### 摘要 随着中医药文化的普及和健康意识的增强&#xff0c;经方药食两用服务逐渐成为人们关注的焦点。传统的中医药服务模式存在信息分散、查询不便、个性化推荐不足等问题&#xff0c;亟需通过信息化手段提升服务效率和质量。本系统旨在构建一个基于现代技术的经…

作者头像 李华
网站建设 2026/5/20 23:01:54

Spring Boot与微服务核心技术面试实战解析

Spring Boot与微服务核心技术面试实战解析 场景设定&#xff1a; 谢飞机是一名初入职场的Java程序员&#xff0c;正在参加某互联网大厂的面试&#xff0c;面试官是一位严肃且专业的技术专家。 第一轮&#xff1a;Spring Boot基础与项目构建 **面试官&#xff08;严肃脸&…

作者头像 李华
网站建设 2026/5/11 4:35:34

2601,写个kde语法高亮文件

提示:如果想写一个高亮语法文件,XMLCompletion插件可能会很有帮助. 这里概述了KDE4中高亮定义XML格式.基于如下,它将描述主要组成部分及其含义和用法. Kate高亮定义文件的主要部分 在language.dtd文件中也就是DTD,保存正式定义,应该在你的系统目录安装,即$KDEDIR/share/apps/ka…

作者头像 李华
网站建设 2026/5/19 9:49:36

2601C++,复制超文本格式

超文本的剪切板格式 超文本有自己的剪切板格式(叫超文本格式(CF_HTML),可用来向其他应用助手(如Excel,Word或其他办公应用复制)提供数据. CF_HTML是包含说明,环境和该环境中的片段完全基于文本的格式.生成要发送到剪切板的数据时,必须包含数据说明,以指示剪切板版本及环境和片…

作者头像 李华
网站建设 2026/5/18 12:07:54

OTG数据充电交互讲解

随着科技的飞速发展&#xff0c;智能移动设备已成为我们生活中不可或缺的一部分。而在这些设备的连接与数据传输中&#xff0c;Type-C接口以其高效、便捷的特性逐渐占据了主导地位。OTG&#xff08;On-The-Go&#xff09;技术则进一步扩展了Type-C接口的功能&#xff0c;使得设…

作者头像 李华