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