简介面向软件工程与数据库课程学习者的实验五配套材料围绕电商项目说明完成ER模式设计适合正在做概念建模、实体关系图绘制、实验报告整理与课程验收的学生参考。压缩包共20个文件约282KB主要由14个gif操作过程图、2个txt说明、2个doc实验文档以及1个cdm概念模型和1个htm页面组成体量轻便但覆盖需求理解、模型绘制与结果验证环节。已有911人学习。内容可对照电商业务中的注册、购物车、结算、订单、配送和会员账户等流程梳理实体、联系与属性参考ER图及文字说明完成数据库概念设计gif展示操作与结果cdm文件便于继续修改模型文档和说明帮助理解实验要求与表达方式适合课程实验参考和建模查漏补缺。1. 选课系统做过、图书管理也做过为什么一到实验五还是卡住数据库实验前四个大多是建表、插数据、写几条 JOIN交上去基本不会被打回。真正的分水岭在实验五题目要求写存储过程、建触发器还要在两个会话里复现脏读和不可重复读。某高校软件学院这套数据库实验五的配套材料就是围绕这一组任务整理的——脚本、测试数据和报告模板都在里面。它适合两类人正在赶实验五、被事务和触发器绕晕的学生以及需要一套可复用 SQL 模板的从业者。下面按任务拆解、总体设计、关键实现、避坑、验收的顺序讲一遍参数和锁的细节讲透照着改业务表名就能跑。2. 需求与选型实验五到底要交付什么实验五的难点不在语法而在任务边界模糊。指导书往往只写一句「实现转账功能并保证一致性」具体到「用存储过程还是应用层事务」「触发器里能不能写事务控制」「隔离级别调到哪一档才能复现异常」全靠自己判断。先把任务拆成可验收的块选型和实现才有落点。2.1 把任务清单拆成验收点数据库实验五一般由四块任务构成每块对应一个可验证的产出。拆解的时候不要按「知识点」拆要按「跑起来能看到什么结果」拆答辩时演示的就是这些结果。任务块具体要求交付物验收方式存储过程至少一个带输入输出参数的业务过程含异常回滚sp_*.sql传非法参数返回错误码账户余额不变触发器数据变更后自动写审计表或维护汇总值trg_*.sql手工 UPDATE 一行审计表出现一条对应记录事务与并发复现脏读/不可重复读/幻读中的至少两种双会话脚本两个终端同时执行结果可重复约束与索引业务规则写进 DDL慢查询加索引后执行计划改善schema.sql、explain.sqlSHOW CREATE TABLE能看到 CHECK/UNIQUEEXPLAIN 的 type 从 ALL 变 ref这张表的价值在于写报告的时候每一行都能对应到一节内容和一张截图。很多人报告写不厚不是没做实验是做了但没留下可截图的证据——事务回滚前和回滚后的余额对比、两个会话的输出顺序这些才是报告的骨架。拆完之后要检查一遍任务之间有没有依赖。触发器依赖表结构事务脚本依赖测试数据测试数据又依赖外键顺序。依赖链一旦搞反初始化脚本就会在第三节报外键错误后面全跑不下去。2.2 数据库与客户端怎么选材料通常固定了一种数据库跟着走就行如果允许自选MySQL 8.0 和 PostgreSQL 是两条主流路线差别集中在几个会直接影响实验五的点上。MySQL 8.0 的优势是资料多、装起来快。8.0.16 之后 CHECK 约束真正生效写业务规则不用再靠触发器硬撑窗口函数和 CTE 也齐了做统计类子任务不用绕。缺点是默认隔离级别是 REPEATABLE READ而且 InnoDB 在 RR 下用快照读直接跑「脏读复现」是看不到脏读的必须显式把会话隔离级别降到 READ UNCOMMITTED——这一点是实验五最常见的卡点后面避坑章会展开。PostgreSQL 的优势是类型严格、约束行为一致RETURNING子句能让存储过程少写一次 SELECT缺点是存储过程语法PL/pgSQL和 MySQL 的差异不小网上抄来的代码大概率跑不通改造成本比想象中高。客户端的取舍更实际图形化工具DBeaver、DataGrip 一类适合写脚本和看结果但做并发实验必须开两个独立连接。图形工具里开两个查询窗口很多时候共用同一个连接你在 A 窗口 START TRANSACTIONB 窗口的 UPDATE 会直接参与同一个事务永远等不出锁等待。我一般的做法是并发部分全部用命令行两个终端各起一个客户端# 终端 A显式指定隔离级别避免受全局配置影响 mysql -u student -p -D lab5 --init-commandSET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED # 终端 B默认隔离级别用来制造未提交的修改 mysql -u student -p -D lab5--init-command让每个新会话自动带上隔离级别设置省得每次手动敲一遍。参数-D lab5直接切到实验库避免误操作到别的库。这两个终端的输出要并排截图报告里才能说明「A 读到了 B 未提交的数据」。提示并发实验前先确认autocommit的值。命令行客户端默认是 1手动 START TRANSACTION 才进入显式事务图形工具里有的版本默认 0行为完全不同。2.3 数据模型与测试数据让并发问题能稳定复现模型不用复杂但必须能制造冲突。选课系统好看不好测——选课冲突多半是唯一索引拦截体现不出锁。转账模型最直接两个账户、一个流水表并发方向相反的两笔转账天然构成死锁候选场景。测试数据的关键是「初始值固定、可重复初始化」。每次实验前把库删掉重建比在脏数据上反复调试省时间。初始化脚本里要显式写死主键和余额不要依赖自增-- 初始化固定 id保证每次跑出来的锁等待顺序一致 DELETE FROM account_log; DELETE FROM account; INSERT INTO account (id, owner_name, balance) VALUES (1, alice, 1000.00), (2, bob, 1000.00), (3, carol, 500.00);DELETE而不是TRUNCATE是因为有外键引用时 TRUNCATE 会被拒绝。三条记录的余额特意设成不同值方便在并发实验里一眼看出哪条被改了。数据量控制在个位数锁等待和死锁的产生条件才好控制——数据一多执行计划变了加锁范围也跟着变复现就不稳定了。3. 总体设计三层脚本结构与四个模块的职责划分实验五的脚本如果全塞在一个.sql文件里改一处就得整体重跑调试成本极高。合理的做法是按「结构—数据—逻辑—验证」四层拆开每层可单独执行、单独回滚。这一章讲清楚分层怎么切、表结构怎么把业务规则沉淀进 DDL、事务边界画在哪里。3.1 脚本分层与执行顺序目录结构直接决定别人能不能接手你的实验。我习惯按下面这种切法文件名带序号执行顺序一眼可见lab5/ ├── 00_drop.sql # 删库重建保证每次从干净状态开始 ├── 01_schema.sql # 建表、约束、索引 ├── 02_seed.sql # 初始化测试数据 ├── 03_procedure.sql # 存储过程 ├── 04_trigger.sql # 触发器 ├── 05_concurrency.sql # 并发复现脚本分 A/B 两段 └── 06_explain.sql # 执行计划验证分层原则是「越靠前越稳定」。01_schema.sql定下来之后基本不动03、04会反复改所以逻辑层单独成文件改完只重跑这一个。05单独放是因为它必须在两个会话里分别执行混进主脚本会卡住整个执行流程。批量执行时用SOURCE而不是重定向mysql -u student -p -D lab5 EOF SOURCE 01_schema.sql; SOURCE 02_seed.sql; SOURCE 03_procedure.sql; EOFheredoc 里写SOURCE出错时客户端会打印具体是哪个文件第几行比管道重定向的报错信息清楚得多。00_drop.sql单独跑不要放进这个批处理——万一哪次脚本写错把库删了至少还有机会先备份。3.2 表结构与约束设计把业务规则写进 DDL约束不是装饰。实验五的评分点里「数据完整性」通常占一档能不能用 DDL 表达业务规则是区分度所在。余额不能为负、转账金额必须为正、同一账户不能重复开户这些都用约束表达不要留给应用层判断。CREATE TABLE account ( id BIGINT PRIMARY KEY AUTO_INCREMENT, owner_name VARCHAR(32) NOT NULL, balance DECIMAL(12,2) NOT NULL DEFAULT 0.00, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- 余额不允许为负这一条由数据库兜底存储过程里的判断只是提前失败 CONSTRAINT ck_account_balance CHECK (balance 0), CONSTRAINT uk_account_owner UNIQUE (owner_name) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; CREATE TABLE account_log ( id BIGINT PRIMARY KEY AUTO_INCREMENT, from_id BIGINT NULL, to_id BIGINT NULL, amount DECIMAL(12,2) NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT ck_log_amount CHECK (amount 0), CONSTRAINT fk_log_from FOREIGN KEY (from_id) REFERENCES account(id), CONSTRAINT fk_log_to FOREIGN KEY (to_id) REFERENCES account(id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;几个参数值得说明。DECIMAL(12,2)而不是DOUBLE金额用浮点会累积误差余额对不上是实验里最常见的翻车点。CHECK约束在 MySQL 8.0.16 之前是被解析后忽略的如果材料指定了更低版本这条约束等同于没写必须靠触发器补。外键故意允许from_id为 NULL是为了给「充值」「提现」这类单边流水留位置——这一点很多人一开始写成NOT NULL后面加功能时表结构就得改。索引跟着查询走不要一次性建满。账户表的owner_name有唯一约束天然带索引流水表按时间倒序查得多补一个CREATE INDEX idx_log_created ON account_log (created_at DESC);3.3 事务边界与隔离级别设计事务边界画在存储过程内部还是外部直接决定异常时能不能回滚干净。我的选择是单次业务操作的事务封装在存储过程里跨多个过程的组合逻辑交给调用方。理由是存储过程被 CALL 时如果内部没有显式事务每条语句自成事务中途失败会留下半截数据。隔离级别的选择要看实验要复现什么现象。MySQL 默认 REPEATABLE READ能防住脏读和不可重复读但幻读在某些场景下仍会出现快照读和当前读的差异。实验五要求复现异常就必须显式降级现象最低复现级别InnoDB 下的注意事项脏读READ UNCOMMITTED默认 RR 下看不到必须会话级设置不可重复读READ COMMITTEDRR 下两次读走同一快照看不出差异幻读READ COMMITTED需用范围查询 插入配合死锁任意级别两个方向相反的事务即可触发设置隔离级别用SET SESSION而不是SET GLOBAL后者需要更高权限而且在共享环境里会影响别人。会话级设置的生效范围只到当前连接断开实验做完自动恢复不留后遗症。3.4 四个模块的职责划分把脚本拆成四块以后职责边界要定清楚否则容易出现「触发器里也写事务控制」「存储过程里也建表」这种混杂写法。模块负责不负责出问题先看结构层表、约束、索引业务判断SHOW CREATE TABLE数据层初始化与回归数据逻辑分支外键顺序、自增起点逻辑层存储过程、触发器建表改表SHOW PROCEDURE STATUS验证层并发脚本、执行计划修改数据两个会话的输出顺序职责定清楚之后调试路径就短了余额为负说明约束没生效去结构层看版本触发器没写日志去逻辑层看触发时机并发等不出锁去验证层看隔离级别。不用满脚本翻。4. 关键实现存储过程、触发器与并发控制的落地写法设计讲完落到能跑的代码。这一章的四段脚本是实验五的主体每一段都会给出可直接改用的写法以及容易被忽略的参数细节。4.1 存储过程参数模式、异常处理与加锁顺序转账过程的难点不在 UPDATE 语句在「两个方向的并发转账不能互相等死」。解决办法是让所有事务按同一顺序加锁——先锁 id 小的账户。DELIMITER $$ CREATE PROCEDURE sp_transfer( IN p_from BIGINT, IN p_to BIGINT, IN p_amount DECIMAL(12,2), OUT p_code INT, OUT p_msg VARCHAR(128) ) BEGIN DECLARE v_first BIGINT; DECLARE v_second BIGINT; DECLARE v_bal DECIMAL(12,2); -- 任何 SQL 异常统一回滚并把错误码透出给调用方 DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; SET p_code -1; SET p_msg transfer rollback; END; SET p_code 0, p_msg ok; IF p_amount IS NULL OR p_amount 0 THEN SET p_code 1001, p_msg amount must be positive; ELSEIF p_from p_to THEN SET p_code 1002, p_msg from and to are the same account; ELSE -- 固定加锁顺序id 小的先锁消除交叉等待 IF p_from p_to THEN SET v_first p_from, v_second p_to; ELSE SET v_first p_to, v_second p_from; END IF; START TRANSACTION; SELECT balance INTO v_bal FROM account WHERE id v_first FOR UPDATE; SELECT balance INTO v_bal FROM account WHERE id v_second FOR UPDATE; SELECT balance INTO v_bal FROM account WHERE id p_from FOR UPDATE; IF v_bal p_amount THEN ROLLBACK; SET p_code 1003, p_msg insufficient balance; ELSE UPDATE account SET balance balance - p_amount WHERE id p_from; UPDATE account SET balance balance p_amount WHERE id p_to; INSERT INTO account_log (from_id, to_id, amount) VALUES (p_from, p_to, p_amount); COMMIT; END IF; END IF; END$$ DELIMITER ;这段有几个必须理解的点。DELIMITER $$只是客户端指令不发给服务器作用是把过程体内的分号保护起来忘了改就会出现「只创建了一半」的报错。OUT参数用错误码而不是抛异常是为了让调用方能用 SELECT 直接看到结果报告里好截图。EXIT HANDLER必须写在所有变量声明之后位置错了语法检查直接失败。按 id 升序加锁这一步是消除死锁的关键。如果不排序A 转账 1→2、B 转账 2→1两个事务分别持有对方要的行锁InnoDB 检测到环路后回滚其中一个。实验里能看到死锁本身是加分项但演示「正常转账」被回滚就不合适了所以我一般把排序锁作为默认写法死锁单独用一个不排序的过程去复现两不耽误。调用时用变量接输出参数CALL sp_transfer(1, 2, 200.00, code, msg); SELECT code, msg; SELECT * FROM account WHERE id IN (1,2);4.2 触发器时机选择与「不要在触发器里做重活」触发器的评分点通常落在「自动维护审计记录或汇总值」。写之前先确定两件事触发时机BEFORE 还是 AFTER、能否接受行级触发。BEFORE 适合做数据修正和校验——修改 NEW 里的值或者用 SIGNAL 抛错阻断操作。AFTER 适合做副作用比如写日志、刷汇总表。MySQL 只支持行级触发器批量更新 1000 行就触发 1000 次这一点在设计汇总逻辑时必须考虑。DELIMITER $$ CREATE TRIGGER trg_account_audit AFTER UPDATE ON account FOR EACH ROW BEGIN -- 只在余额真的变化时记录避免无关字段更新把审计表刷爆 IF NEW.balance OLD.balance THEN INSERT INTO account_audit (account_id, old_balance, new_balance, changed_at) VALUES (NEW.id, OLD.balance, NEW.balance, NOW()); END IF; END$$ DELIMITER ;OLD和NEW在 UPDATE 触发器里都可用INSERT 只有 NEWDELETE 只有 OLD写错会直接报语法错误。IF判断不能省——存储过程里的两次 UPDATE 都会触发流水插入那一步不影响余额但如果没有判断审计表会多出一倍记录报告里的条数对不上。触发器里绝对不要写COMMIT、ROLLBACK、START TRANSACTIONMySQL 会直接拒绝创建。这个限制不是随便定的触发器运行在父语句的事务上下文里自己提交会把外层事务切断。注意触发器是隐藏逻辑新人接手时最容易忽略。实验报告里要单独列一节说明有哪些触发器、各自在什么时机做什么这份说明在答辩时比代码本身更有说服力。4.3 并发场景复现两个会话的输出才是证据并发实验的做法是固定的A 会话开事务读B 会话改但不提交A 再读一次对比两次结果。关键是隔离级别要配对。-- 会话 A降到 READ UNCOMMITTED 才能看到未提交数据 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; START TRANSACTION; SELECT balance FROM account WHERE id 1; -- 第一次读1000.00 -- 会话 B修改但不提交 START TRANSACTION; UPDATE account SET balance balance - 300 WHERE id 1; -- 此处不要 COMMIT保持未提交状态 -- 回到会话 A再读一次 SELECT balance FROM account WHERE id 1; -- 读到 700.00脏读成立复现不可重复读时换个级别A 用 READ COMMITTED 开事务读一次B 提交一次修改A 再读两次结果不同。这个现象在 RR 下不会出现因为快照在事务开始时已经固定。锁等待的演示更直观A 对某行加FOR UPDATE后不提交B 去更新同一行B 会卡住直到超时。超时时间由innodb_lock_wait_timeout控制默认 50 秒演示时太长先调小SET SESSION innodb_lock_wait_timeout 5;死锁复现则用两个方向相反的转账日志里能看到Deadlock found when trying to get lock; try restarting transaction。这一条截图放进报告比任何文字描述都有分量——它证明你真的让 InnoDB 的检测机制跑起来了。4.4 索引与执行计划用数据说话别凭感觉加索引索引部分的验收方式是看执行计划变化。先造一条不带索引的慢查询跑 EXPLAIN 记录基线加索引后再跑一次做对比。-- 基线按时间范围查流水此时 created_at 上没有索引 EXPLAIN SELECT a.owner_name, l.amount, l.created_at FROM account_log l JOIN account a ON a.id l.from_id WHERE l.created_at 2024-01-01 AND l.created_at 2024-02-01;关注三列type、rows、Extra。typeALL表示全表扫描加索引后期望变成rangerows是估算扫描行数数字明显下降说明索引被用上了Extra里出现Using filesort说明排序没走索引出现Using index说明覆盖索引生效。-- 加索引后重跑对比 type 与 rows 的变化 CREATE INDEX idx_log_created ON account_log (created_at); EXPLAIN ANALYZE SELECT ...; -- 版本支持时用 ANALYZE能看到真实耗时EXPLAIN ANALYZE在 MySQL 8.0.18 之后可用它会真正执行语句并给出实际耗时比单纯的估算更可信。版本不支持就退回EXPLAIN报告里注明是估算值即可。有一点容易做反索引不是越多越好。每个二级索引都要在写入时维护实验里的插入操作本来就慢再加三四个索引初始化脚本能跑好几分钟。索引只建在 WHERE、JOIN、ORDER BY 实际用到的列上够用就行。5. 避坑与常见问题五个最容易翻车的点实验五的报错大多不是语法问题是运行环境和执行顺序问题。这一章按「现象 → 原因 → 解决」列五条都是我自己或同学踩过的。第一条触发器创建报错提示语句不合法。现象是CREATE TRIGGER一直失败报错位置指向过程体中间。原因是没用DELIMITER改分隔符客户端读到过程体里第一个分号就当作语句结束把半截 SQL 发给了服务器。解决办法是在创建前后成对写DELIMITER $$和DELIMITER ;中间的过程体内部正常用分号。改完记得把分隔符改回来否则后面所有语句都会等着一个不存在的$$。第二条并发实验复现不出脏读两次读结果一样。几乎都是隔离级别没生效。MySQL 默认 REPEATABLE READ快照读根本看不到别人未提交的数据更隐蔽的情况是在图形化工具里执行了SET SESSION但后续查询跑在另一个连接上设置没带过去。解决方式是用命令行开独立会话并在连接时就通过--init-command指定隔离级别再用SELECT transaction_isolation;确认一次当前值。第三条初始化脚本执行到一半报外键约束失败。原因是删除和插入的顺序与外键方向相反。有外键引用时必须先删子表再删父表插入反之。流水表引用账户表所以DELETE FROM account_log必须在DELETE FROM account之前。同理TRUNCATE在有外键时会直接报错用DELETE更稳。这类错误信息一般会指明是哪张表的哪个约束看到就按依赖顺序调。第四条存储过程里余额扣成负数CHECK 约束没拦住。两种可能。一是数据库版本低于 8.0.16CHECK被解析后忽略SHOW CREATE TABLE里能看到约束定义但实际不生效二是扣减语句写成了先查后改中间被别的事务插了一脚。前者的解决办法是把校验搬到存储过程里显式判断或者用BEFORE UPDATE触发器配合SIGNAL后者要用SELECT ... FOR UPDATE把读和改锁在同一次事务里不能分成两步。第五条脚本单独跑没问题批量执行就报错。多半是SOURCE的顺序或者字符集问题。文件开头没声明编码中文字段注释在某些客户端下会变成乱码进而导致建表失败。统一在每个文件顶部加SET NAMES utf8mb4;建表语句显式写DEFAULT CHARSETutf8mb4。另外批量执行时不要在同一个批次里既删库又建库DROP DATABASE之后当前连接指向的库就没了后面的语句全部落空。6. 验收与答辩用一张对照表把实验讲清楚验收环节最怕的不是做错是说不清做了什么。答辩时间通常只有五到八分钟老师不会让你现场写代码他看的是「你能不能证明这套东西跑得通」。我的做法是提前准备一张对照表每一行对应一个评分点行数控制在六到八行演示时按表走。评分点交付物现场验证命令合格线表结构与约束01_schema.sqlSHOW CREATE TABLE account;能看到 CHECK、UNIQUE、外键三项存储过程03_procedure.sqlCALL sp_transfer(1,2,200,c,m);返回码为 0余额正确变动异常回滚同上传负金额调用返回 1001两账户余额不变触发器04_trigger.sql手工 UPDATE 后查审计表新增一条记录旧值新值正确并发异常05_concurrency.sql双终端执行能复现至少两种异常执行计划06_explain.sqlEXPLAIN前后对比type 从 ALL 变为 range演示顺序有讲究。先跑00_drop.sql加01_schema.sql从零重建一遍这一步比任何解释都更能说明脚本可重复执行接着演示一次成功转账再演示一次余额不足的回滚两次结果并排放在屏幕上最后切到两个终端做并发把脏读的两次查询结果摆出来。整个流程控制在四分钟以内剩下的时间留给提问。提问环节高频出现的问题是「你这个死锁是怎么产生的」。如果只是背概念很容易被追问细节。稳妥的回答是把加锁顺序讲清楚两个事务分别锁了对方需要的行形成环路InnoDB 检测到后回滚代价较小的那个事务里的业务代码要能处理这种回滚并重试。能说到重试这一层说明真的理解而不是复述。还有一个容易被忽略的细节报告里的截图要带时间戳或会话标识。两个终端的截图如果看不出先后顺序说服力会打对折。我一般把命令行提示符改成带序号的形式A 会话和 B 会话一眼能区分。从那以后我每次交实验前都强制走一遍「删库重建 双会话复现」只要这两步能一次跑通其他问题基本不会在验收现场冒出来。材料里的脚本已经按这套分层整理好了对着自己的实验指导书改业务表名和参数就能跑。希望帮到你。本文还有配套的精品资源点击获取
简介面向软件工程与数据库课程学习者的实验五配套材料围绕电商项目说明完成ER模式设计适合正在做概念建模、实体关系图绘制、实验报告整理与课程验收的学生参考。压缩包共20个文件约282KB主要由14个gif操作过程图、2个txt说明、2个doc实验文档以及1个cdm概念模型和1个htm页面组成体量轻便但覆盖需求理解、模型绘制与结果验证环节。已有911人学习。内容可对照电商业务中的注册、购物车、结算、订单、配送和会员账户等流程梳理实体、联系与属性参考ER图及文字说明完成数据库概念设计gif展示操作与结果cdm文件便于继续修改模型文档和说明帮助理解实验要求与表达方式适合课程实验参考和建模查漏补缺。1. 选课系统做过、图书管理也做过为什么一到实验五还是卡住数据库实验前四个大多是建表、插数据、写几条 JOIN交上去基本不会被打回。真正的分水岭在实验五题目要求写存储过程、建触发器还要在两个会话里复现脏读和不可重复读。某高校软件学院这套数据库实验五的配套材料就是围绕这一组任务整理的——脚本、测试数据和报告模板都在里面。它适合两类人正在赶实验五、被事务和触发器绕晕的学生以及需要一套可复用 SQL 模板的从业者。下面按任务拆解、总体设计、关键实现、避坑、验收的顺序讲一遍参数和锁的细节讲透照着改业务表名就能跑。2. 需求与选型实验五到底要交付什么实验五的难点不在语法而在任务边界模糊。指导书往往只写一句「实现转账功能并保证一致性」具体到「用存储过程还是应用层事务」「触发器里能不能写事务控制」「隔离级别调到哪一档才能复现异常」全靠自己判断。先把任务拆成可验收的块选型和实现才有落点。2.1 把任务清单拆成验收点数据库实验五一般由四块任务构成每块对应一个可验证的产出。拆解的时候不要按「知识点」拆要按「跑起来能看到什么结果」拆答辩时演示的就是这些结果。任务块具体要求交付物验收方式存储过程至少一个带输入输出参数的业务过程含异常回滚sp_*.sql传非法参数返回错误码账户余额不变触发器数据变更后自动写审计表或维护汇总值trg_*.sql手工 UPDATE 一行审计表出现一条对应记录事务与并发复现脏读/不可重复读/幻读中的至少两种双会话脚本两个终端同时执行结果可重复约束与索引业务规则写进 DDL慢查询加索引后执行计划改善schema.sql、explain.sqlSHOW CREATE TABLE能看到 CHECK/UNIQUEEXPLAIN 的 type 从 ALL 变 ref这张表的价值在于写报告的时候每一行都能对应到一节内容和一张截图。很多人报告写不厚不是没做实验是做了但没留下可截图的证据——事务回滚前和回滚后的余额对比、两个会话的输出顺序这些才是报告的骨架。拆完之后要检查一遍任务之间有没有依赖。触发器依赖表结构事务脚本依赖测试数据测试数据又依赖外键顺序。依赖链一旦搞反初始化脚本就会在第三节报外键错误后面全跑不下去。2.2 数据库与客户端怎么选材料通常固定了一种数据库跟着走就行如果允许自选MySQL 8.0 和 PostgreSQL 是两条主流路线差别集中在几个会直接影响实验五的点上。MySQL 8.0 的优势是资料多、装起来快。8.0.16 之后 CHECK 约束真正生效写业务规则不用再靠触发器硬撑窗口函数和 CTE 也齐了做统计类子任务不用绕。缺点是默认隔离级别是 REPEATABLE READ而且 InnoDB 在 RR 下用快照读直接跑「脏读复现」是看不到脏读的必须显式把会话隔离级别降到 READ UNCOMMITTED——这一点是实验五最常见的卡点后面避坑章会展开。PostgreSQL 的优势是类型严格、约束行为一致RETURNING子句能让存储过程少写一次 SELECT缺点是存储过程语法PL/pgSQL和 MySQL 的差异不小网上抄来的代码大概率跑不通改造成本比想象中高。客户端的取舍更实际图形化工具DBeaver、DataGrip 一类适合写脚本和看结果但做并发实验必须开两个独立连接。图形工具里开两个查询窗口很多时候共用同一个连接你在 A 窗口 START TRANSACTIONB 窗口的 UPDATE 会直接参与同一个事务永远等不出锁等待。我一般的做法是并发部分全部用命令行两个终端各起一个客户端# 终端 A显式指定隔离级别避免受全局配置影响 mysql -u student -p -D lab5 --init-commandSET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED # 终端 B默认隔离级别用来制造未提交的修改 mysql -u student -p -D lab5--init-command让每个新会话自动带上隔离级别设置省得每次手动敲一遍。参数-D lab5直接切到实验库避免误操作到别的库。这两个终端的输出要并排截图报告里才能说明「A 读到了 B 未提交的数据」。提示并发实验前先确认autocommit的值。命令行客户端默认是 1手动 START TRANSACTION 才进入显式事务图形工具里有的版本默认 0行为完全不同。2.3 数据模型与测试数据让并发问题能稳定复现模型不用复杂但必须能制造冲突。选课系统好看不好测——选课冲突多半是唯一索引拦截体现不出锁。转账模型最直接两个账户、一个流水表并发方向相反的两笔转账天然构成死锁候选场景。测试数据的关键是「初始值固定、可重复初始化」。每次实验前把库删掉重建比在脏数据上反复调试省时间。初始化脚本里要显式写死主键和余额不要依赖自增-- 初始化固定 id保证每次跑出来的锁等待顺序一致 DELETE FROM account_log; DELETE FROM account; INSERT INTO account (id, owner_name, balance) VALUES (1, alice, 1000.00), (2, bob, 1000.00), (3, carol, 500.00);DELETE而不是TRUNCATE是因为有外键引用时 TRUNCATE 会被拒绝。三条记录的余额特意设成不同值方便在并发实验里一眼看出哪条被改了。数据量控制在个位数锁等待和死锁的产生条件才好控制——数据一多执行计划变了加锁范围也跟着变复现就不稳定了。3. 总体设计三层脚本结构与四个模块的职责划分实验五的脚本如果全塞在一个.sql文件里改一处就得整体重跑调试成本极高。合理的做法是按「结构—数据—逻辑—验证」四层拆开每层可单独执行、单独回滚。这一章讲清楚分层怎么切、表结构怎么把业务规则沉淀进 DDL、事务边界画在哪里。3.1 脚本分层与执行顺序目录结构直接决定别人能不能接手你的实验。我习惯按下面这种切法文件名带序号执行顺序一眼可见lab5/ ├── 00_drop.sql # 删库重建保证每次从干净状态开始 ├── 01_schema.sql # 建表、约束、索引 ├── 02_seed.sql # 初始化测试数据 ├── 03_procedure.sql # 存储过程 ├── 04_trigger.sql # 触发器 ├── 05_concurrency.sql # 并发复现脚本分 A/B 两段 └── 06_explain.sql # 执行计划验证分层原则是「越靠前越稳定」。01_schema.sql定下来之后基本不动03、04会反复改所以逻辑层单独成文件改完只重跑这一个。05单独放是因为它必须在两个会话里分别执行混进主脚本会卡住整个执行流程。批量执行时用SOURCE而不是重定向mysql -u student -p -D lab5 EOF SOURCE 01_schema.sql; SOURCE 02_seed.sql; SOURCE 03_procedure.sql; EOFheredoc 里写SOURCE出错时客户端会打印具体是哪个文件第几行比管道重定向的报错信息清楚得多。00_drop.sql单独跑不要放进这个批处理——万一哪次脚本写错把库删了至少还有机会先备份。3.2 表结构与约束设计把业务规则写进 DDL约束不是装饰。实验五的评分点里「数据完整性」通常占一档能不能用 DDL 表达业务规则是区分度所在。余额不能为负、转账金额必须为正、同一账户不能重复开户这些都用约束表达不要留给应用层判断。CREATE TABLE account ( id BIGINT PRIMARY KEY AUTO_INCREMENT, owner_name VARCHAR(32) NOT NULL, balance DECIMAL(12,2) NOT NULL DEFAULT 0.00, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- 余额不允许为负这一条由数据库兜底存储过程里的判断只是提前失败 CONSTRAINT ck_account_balance CHECK (balance 0), CONSTRAINT uk_account_owner UNIQUE (owner_name) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; CREATE TABLE account_log ( id BIGINT PRIMARY KEY AUTO_INCREMENT, from_id BIGINT NULL, to_id BIGINT NULL, amount DECIMAL(12,2) NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT ck_log_amount CHECK (amount 0), CONSTRAINT fk_log_from FOREIGN KEY (from_id) REFERENCES account(id), CONSTRAINT fk_log_to FOREIGN KEY (to_id) REFERENCES account(id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;几个参数值得说明。DECIMAL(12,2)而不是DOUBLE金额用浮点会累积误差余额对不上是实验里最常见的翻车点。CHECK约束在 MySQL 8.0.16 之前是被解析后忽略的如果材料指定了更低版本这条约束等同于没写必须靠触发器补。外键故意允许from_id为 NULL是为了给「充值」「提现」这类单边流水留位置——这一点很多人一开始写成NOT NULL后面加功能时表结构就得改。索引跟着查询走不要一次性建满。账户表的owner_name有唯一约束天然带索引流水表按时间倒序查得多补一个CREATE INDEX idx_log_created ON account_log (created_at DESC);3.3 事务边界与隔离级别设计事务边界画在存储过程内部还是外部直接决定异常时能不能回滚干净。我的选择是单次业务操作的事务封装在存储过程里跨多个过程的组合逻辑交给调用方。理由是存储过程被 CALL 时如果内部没有显式事务每条语句自成事务中途失败会留下半截数据。隔离级别的选择要看实验要复现什么现象。MySQL 默认 REPEATABLE READ能防住脏读和不可重复读但幻读在某些场景下仍会出现快照读和当前读的差异。实验五要求复现异常就必须显式降级现象最低复现级别InnoDB 下的注意事项脏读READ UNCOMMITTED默认 RR 下看不到必须会话级设置不可重复读READ COMMITTEDRR 下两次读走同一快照看不出差异幻读READ COMMITTED需用范围查询 插入配合死锁任意级别两个方向相反的事务即可触发设置隔离级别用SET SESSION而不是SET GLOBAL后者需要更高权限而且在共享环境里会影响别人。会话级设置的生效范围只到当前连接断开实验做完自动恢复不留后遗症。3.4 四个模块的职责划分把脚本拆成四块以后职责边界要定清楚否则容易出现「触发器里也写事务控制」「存储过程里也建表」这种混杂写法。模块负责不负责出问题先看结构层表、约束、索引业务判断SHOW CREATE TABLE数据层初始化与回归数据逻辑分支外键顺序、自增起点逻辑层存储过程、触发器建表改表SHOW PROCEDURE STATUS验证层并发脚本、执行计划修改数据两个会话的输出顺序职责定清楚之后调试路径就短了余额为负说明约束没生效去结构层看版本触发器没写日志去逻辑层看触发时机并发等不出锁去验证层看隔离级别。不用满脚本翻。4. 关键实现存储过程、触发器与并发控制的落地写法设计讲完落到能跑的代码。这一章的四段脚本是实验五的主体每一段都会给出可直接改用的写法以及容易被忽略的参数细节。4.1 存储过程参数模式、异常处理与加锁顺序转账过程的难点不在 UPDATE 语句在「两个方向的并发转账不能互相等死」。解决办法是让所有事务按同一顺序加锁——先锁 id 小的账户。DELIMITER $$ CREATE PROCEDURE sp_transfer( IN p_from BIGINT, IN p_to BIGINT, IN p_amount DECIMAL(12,2), OUT p_code INT, OUT p_msg VARCHAR(128) ) BEGIN DECLARE v_first BIGINT; DECLARE v_second BIGINT; DECLARE v_bal DECIMAL(12,2); -- 任何 SQL 异常统一回滚并把错误码透出给调用方 DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; SET p_code -1; SET p_msg transfer rollback; END; SET p_code 0, p_msg ok; IF p_amount IS NULL OR p_amount 0 THEN SET p_code 1001, p_msg amount must be positive; ELSEIF p_from p_to THEN SET p_code 1002, p_msg from and to are the same account; ELSE -- 固定加锁顺序id 小的先锁消除交叉等待 IF p_from p_to THEN SET v_first p_from, v_second p_to; ELSE SET v_first p_to, v_second p_from; END IF; START TRANSACTION; SELECT balance INTO v_bal FROM account WHERE id v_first FOR UPDATE; SELECT balance INTO v_bal FROM account WHERE id v_second FOR UPDATE; SELECT balance INTO v_bal FROM account WHERE id p_from FOR UPDATE; IF v_bal p_amount THEN ROLLBACK; SET p_code 1003, p_msg insufficient balance; ELSE UPDATE account SET balance balance - p_amount WHERE id p_from; UPDATE account SET balance balance p_amount WHERE id p_to; INSERT INTO account_log (from_id, to_id, amount) VALUES (p_from, p_to, p_amount); COMMIT; END IF; END IF; END$$ DELIMITER ;这段有几个必须理解的点。DELIMITER $$只是客户端指令不发给服务器作用是把过程体内的分号保护起来忘了改就会出现「只创建了一半」的报错。OUT参数用错误码而不是抛异常是为了让调用方能用 SELECT 直接看到结果报告里好截图。EXIT HANDLER必须写在所有变量声明之后位置错了语法检查直接失败。按 id 升序加锁这一步是消除死锁的关键。如果不排序A 转账 1→2、B 转账 2→1两个事务分别持有对方要的行锁InnoDB 检测到环路后回滚其中一个。实验里能看到死锁本身是加分项但演示「正常转账」被回滚就不合适了所以我一般把排序锁作为默认写法死锁单独用一个不排序的过程去复现两不耽误。调用时用变量接输出参数CALL sp_transfer(1, 2, 200.00, code, msg); SELECT code, msg; SELECT * FROM account WHERE id IN (1,2);4.2 触发器时机选择与「不要在触发器里做重活」触发器的评分点通常落在「自动维护审计记录或汇总值」。写之前先确定两件事触发时机BEFORE 还是 AFTER、能否接受行级触发。BEFORE 适合做数据修正和校验——修改 NEW 里的值或者用 SIGNAL 抛错阻断操作。AFTER 适合做副作用比如写日志、刷汇总表。MySQL 只支持行级触发器批量更新 1000 行就触发 1000 次这一点在设计汇总逻辑时必须考虑。DELIMITER $$ CREATE TRIGGER trg_account_audit AFTER UPDATE ON account FOR EACH ROW BEGIN -- 只在余额真的变化时记录避免无关字段更新把审计表刷爆 IF NEW.balance OLD.balance THEN INSERT INTO account_audit (account_id, old_balance, new_balance, changed_at) VALUES (NEW.id, OLD.balance, NEW.balance, NOW()); END IF; END$$ DELIMITER ;OLD和NEW在 UPDATE 触发器里都可用INSERT 只有 NEWDELETE 只有 OLD写错会直接报语法错误。IF判断不能省——存储过程里的两次 UPDATE 都会触发流水插入那一步不影响余额但如果没有判断审计表会多出一倍记录报告里的条数对不上。触发器里绝对不要写COMMIT、ROLLBACK、START TRANSACTIONMySQL 会直接拒绝创建。这个限制不是随便定的触发器运行在父语句的事务上下文里自己提交会把外层事务切断。注意触发器是隐藏逻辑新人接手时最容易忽略。实验报告里要单独列一节说明有哪些触发器、各自在什么时机做什么这份说明在答辩时比代码本身更有说服力。4.3 并发场景复现两个会话的输出才是证据并发实验的做法是固定的A 会话开事务读B 会话改但不提交A 再读一次对比两次结果。关键是隔离级别要配对。-- 会话 A降到 READ UNCOMMITTED 才能看到未提交数据 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; START TRANSACTION; SELECT balance FROM account WHERE id 1; -- 第一次读1000.00 -- 会话 B修改但不提交 START TRANSACTION; UPDATE account SET balance balance - 300 WHERE id 1; -- 此处不要 COMMIT保持未提交状态 -- 回到会话 A再读一次 SELECT balance FROM account WHERE id 1; -- 读到 700.00脏读成立复现不可重复读时换个级别A 用 READ COMMITTED 开事务读一次B 提交一次修改A 再读两次结果不同。这个现象在 RR 下不会出现因为快照在事务开始时已经固定。锁等待的演示更直观A 对某行加FOR UPDATE后不提交B 去更新同一行B 会卡住直到超时。超时时间由innodb_lock_wait_timeout控制默认 50 秒演示时太长先调小SET SESSION innodb_lock_wait_timeout 5;死锁复现则用两个方向相反的转账日志里能看到Deadlock found when trying to get lock; try restarting transaction。这一条截图放进报告比任何文字描述都有分量——它证明你真的让 InnoDB 的检测机制跑起来了。4.4 索引与执行计划用数据说话别凭感觉加索引索引部分的验收方式是看执行计划变化。先造一条不带索引的慢查询跑 EXPLAIN 记录基线加索引后再跑一次做对比。-- 基线按时间范围查流水此时 created_at 上没有索引 EXPLAIN SELECT a.owner_name, l.amount, l.created_at FROM account_log l JOIN account a ON a.id l.from_id WHERE l.created_at 2024-01-01 AND l.created_at 2024-02-01;关注三列type、rows、Extra。typeALL表示全表扫描加索引后期望变成rangerows是估算扫描行数数字明显下降说明索引被用上了Extra里出现Using filesort说明排序没走索引出现Using index说明覆盖索引生效。-- 加索引后重跑对比 type 与 rows 的变化 CREATE INDEX idx_log_created ON account_log (created_at); EXPLAIN ANALYZE SELECT ...; -- 版本支持时用 ANALYZE能看到真实耗时EXPLAIN ANALYZE在 MySQL 8.0.18 之后可用它会真正执行语句并给出实际耗时比单纯的估算更可信。版本不支持就退回EXPLAIN报告里注明是估算值即可。有一点容易做反索引不是越多越好。每个二级索引都要在写入时维护实验里的插入操作本来就慢再加三四个索引初始化脚本能跑好几分钟。索引只建在 WHERE、JOIN、ORDER BY 实际用到的列上够用就行。5. 避坑与常见问题五个最容易翻车的点实验五的报错大多不是语法问题是运行环境和执行顺序问题。这一章按「现象 → 原因 → 解决」列五条都是我自己或同学踩过的。第一条触发器创建报错提示语句不合法。现象是CREATE TRIGGER一直失败报错位置指向过程体中间。原因是没用DELIMITER改分隔符客户端读到过程体里第一个分号就当作语句结束把半截 SQL 发给了服务器。解决办法是在创建前后成对写DELIMITER $$和DELIMITER ;中间的过程体内部正常用分号。改完记得把分隔符改回来否则后面所有语句都会等着一个不存在的$$。第二条并发实验复现不出脏读两次读结果一样。几乎都是隔离级别没生效。MySQL 默认 REPEATABLE READ快照读根本看不到别人未提交的数据更隐蔽的情况是在图形化工具里执行了SET SESSION但后续查询跑在另一个连接上设置没带过去。解决方式是用命令行开独立会话并在连接时就通过--init-command指定隔离级别再用SELECT transaction_isolation;确认一次当前值。第三条初始化脚本执行到一半报外键约束失败。原因是删除和插入的顺序与外键方向相反。有外键引用时必须先删子表再删父表插入反之。流水表引用账户表所以DELETE FROM account_log必须在DELETE FROM account之前。同理TRUNCATE在有外键时会直接报错用DELETE更稳。这类错误信息一般会指明是哪张表的哪个约束看到就按依赖顺序调。第四条存储过程里余额扣成负数CHECK 约束没拦住。两种可能。一是数据库版本低于 8.0.16CHECK被解析后忽略SHOW CREATE TABLE里能看到约束定义但实际不生效二是扣减语句写成了先查后改中间被别的事务插了一脚。前者的解决办法是把校验搬到存储过程里显式判断或者用BEFORE UPDATE触发器配合SIGNAL后者要用SELECT ... FOR UPDATE把读和改锁在同一次事务里不能分成两步。第五条脚本单独跑没问题批量执行就报错。多半是SOURCE的顺序或者字符集问题。文件开头没声明编码中文字段注释在某些客户端下会变成乱码进而导致建表失败。统一在每个文件顶部加SET NAMES utf8mb4;建表语句显式写DEFAULT CHARSETutf8mb4。另外批量执行时不要在同一个批次里既删库又建库DROP DATABASE之后当前连接指向的库就没了后面的语句全部落空。6. 验收与答辩用一张对照表把实验讲清楚验收环节最怕的不是做错是说不清做了什么。答辩时间通常只有五到八分钟老师不会让你现场写代码他看的是「你能不能证明这套东西跑得通」。我的做法是提前准备一张对照表每一行对应一个评分点行数控制在六到八行演示时按表走。评分点交付物现场验证命令合格线表结构与约束01_schema.sqlSHOW CREATE TABLE account;能看到 CHECK、UNIQUE、外键三项存储过程03_procedure.sqlCALL sp_transfer(1,2,200,c,m);返回码为 0余额正确变动异常回滚同上传负金额调用返回 1001两账户余额不变触发器04_trigger.sql手工 UPDATE 后查审计表新增一条记录旧值新值正确并发异常05_concurrency.sql双终端执行能复现至少两种异常执行计划06_explain.sqlEXPLAIN前后对比type 从 ALL 变为 range演示顺序有讲究。先跑00_drop.sql加01_schema.sql从零重建一遍这一步比任何解释都更能说明脚本可重复执行接着演示一次成功转账再演示一次余额不足的回滚两次结果并排放在屏幕上最后切到两个终端做并发把脏读的两次查询结果摆出来。整个流程控制在四分钟以内剩下的时间留给提问。提问环节高频出现的问题是「你这个死锁是怎么产生的」。如果只是背概念很容易被追问细节。稳妥的回答是把加锁顺序讲清楚两个事务分别锁了对方需要的行形成环路InnoDB 检测到后回滚代价较小的那个事务里的业务代码要能处理这种回滚并重试。能说到重试这一层说明真的理解而不是复述。还有一个容易被忽略的细节报告里的截图要带时间戳或会话标识。两个终端的截图如果看不出先后顺序说服力会打对折。我一般把命令行提示符改成带序号的形式A 会话和 B 会话一眼能区分。从那以后我每次交实验前都强制走一遍「删库重建 双会话复现」只要这两步能一次跑通其他问题基本不会在验收现场冒出来。材料里的脚本已经按这套分层整理好了对着自己的实验指导书改业务表名和参数就能跑。希望帮到你。本文还有配套的精品资源点击获取
