简介西北工业大学《数据库原理》课程实验报告五围绕数据库基本操作与安全管理展开适用于正在学习数据库原理、需要实验参考的本科生。报告完整覆盖了视图重命名、带参数存储过程、加密存储过程以及多种触发器的创建与验证包括插入、删除、更新触发器对数据一致性的维护并涉及自动更新统计表的联动机制能够帮助读者深入理解T-SQL语法和数据库对象的管理方法。压缩包内为单个doc文档大小约231KB内含具体实验步骤、SQL语句及运行验证截图可以按步骤复现实验或借鉴撰写实验报告。已有712人学习浏览适合数据库初学者及需要系统练习存储过程与触发器开发的人群参考。整体结构清晰步骤与注释完整所附存储过程及触发器样例均可直接运行方便边学边练。1. 数据库实验报告5这节实验不以增删改查为主考察的是数据一致性的边界数据库课程实验前几节一般是建库、单表查询、多表连接和视图索引做到第五次实验报告时话题往往已经推进到数据库并发控制和完整性约束。事务提交、回滚、脏读、不可重复读、死锁这些词在《西北工业大学数据库实验报告5.doc》这类文档里要求的不再是背定义而是在两个会话里用 SQL 亲手把现象复现出来。很多人把这节实验当成“把课堂上的语句再写一遍”实际它考察的是另一个维度当两个连接同时操作同一条记录数据库的锁和隔离级别到底拦下了什么、又放过了什么。下面这条线正好能作为一份数据库实验报告5的完整展开。2. 事务隔离与锁把实验5背后的概念先放在一张表里2.1 从 ACID 说起实验报告该解释到什么程度实验报告5如果涉及事务第一页逃不开 ACID。许多学生把原子性、一致性、隔离性、持久性四行字抄进报告就算完但数据库实验5的答辩通常会追问一句原子性和持久性靠什么机制保证答案是日志。redo log 负责崩溃恢复时把已提交事务重放undo log 负责回滚时把旧值恢复InnoDB 里这两个日志配合 change buffer、doublewrite buffer 一起工作才是完整的持久性和原子性链条。一致性不是数据库单独保证的它由应用层约束、外键、CHECK 约束和触发器共同兜底。实验报告里如果出现“数据库保证一致性”这种结论严谨程度是不够的。更准确的说法是原子性和隔离性为一致性创造条件但业务规则最终由约束来执行。把这句话写进实验报告比空谈 ACID 更站得住。2.2 隔离级别和异常现象报告里最好带一张对照表隔离级别是实验5最容易出彩的部分也是最能体现“动手验证”价值的地方。SQL 标准定义了四个级别不同数据库实现程度不同MySQL 的默认隔离级别是 REPEATABLE READOracle 和达梦默认是 READ COMMITTED这个差异经常被实验者忽略导致在两个不同数据库上跑出完全不同的现象。隔离级别脏读不可重复读幻读加锁实现READ UNCOMMITTED可能可能可能读不加锁READ COMMITTED避免可能可能行级锁读已提交快照REPEATABLE READ避免避免可能InnoDB 用间隙锁解决事务内快照一致SERIALIZABLE避免避免避免所有读加锁表格里的“可能”和“避免”是实验报告里必须亲手验证的。认真做实验5的人会发现InnoDB 的 REPEATABLE READ 下普通的快照读已经解决了幻读只有当前读带 FOR UPDATE 或 LOCK IN SHARE MODE配合间隙锁才能彻底锁住范围。实验报告中如果能把“标准定义”和“InnoDB 实际行为”区分开这个实验基本就是高分。2.3 锁的类型读锁、写锁、间隙锁分别在挡住什么事务隔离级别的底层是锁和 MVCC。读锁共享锁之间不互斥读锁和写锁排他锁互斥写锁之间也互斥这是数据库并发控制的基本盘。实验5里最常见的错误是把“行锁”理解成“锁住一行”实际上 InnoDB 的行锁锁的是索引记录不是行本身。如果更新条件没有走索引InnoDB 会升级为锁住所有扫描到的记录表现为锁表。间隙锁是 REPEATABLE READ 隔离级别下 InnoDB 特有的机制。两个事务同时往一个范围的间隙里插入数据时间隙锁会让后到的插入阻塞从而解决幻读。但这也有反作用间隙锁扩大了锁范围死锁概率随之上升。实验报告里如果能把死锁发生的原因定位到“两个事务以不同顺序请求同一组索引记录”就已经超出课程要求直接到了生产环境排查的思路。3. 把实验环境搭起来表结构、事务脚本和完整性约束3.1 建表语句实验5里最容易被忽略的约束设计很多实验指导书的建表语句只留主键和外键其他约束一律省略。但实验5考察完整性约束时表结构本身就需要精心设计。常见做法是模拟一个教务或订单场景这里以订单和库存两个表为基线它们之间的外键、非空、默认值、CHECK 约束都要体现出来。CREATE TABLE product ( product_id INT PRIMARY KEY AUTO_INCREMENT COMMENT 商品ID, product_name VARCHAR(64) NOT NULL COMMENT 商品名称, stock_qty INT NOT NULL DEFAULT 0 COMMENT 库存数量不允许负库存, price DECIMAL(10,2) NOT NULL COMMENT 单价, CONSTRAINT ck_product_stock CHECK (stock_qty 0) ) ENGINEInnoDB COMMENT商品表; CREATE TABLE order_detail ( order_id INT NOT NULL COMMENT 订单号, product_id INT NOT NULL, quantity INT NOT NULL COMMENT 购买数量, order_time DATETIME DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (order_id, product_id), CONSTRAINT fk_order_product FOREIGN KEY (product_id) REFERENCES product(product_id) ON UPDATE CASCADE ON DELETE RESTRICT ) ENGINEInnoDB COMMENT订单明细表;这段 DDL 的逻辑说明NOT NULL 用 product_name 和 quantity 上确保业务核心字段不出现空值CHECK (stock_qty 0) 把负库存挡在数据库入口任何应用层绕过校验的负数写入都会直接报错外键的 ON UPDATE CASCADE 配合 ON DELETE RESTRICT则保证商品主键变动时订单明细跟着更新但商品删除若还有订单引用会被拒绝。参数说明ENGINEInnoDB 是必须的MyISAM 不支持外键约束和事务实验5跑事务的环节在 MyISAM 下完全不成立。3.2 第一个事务脚本提交、回滚、保存点怎么演示事务实验的最小复现是同一事务里先扣库存再写订单明细中间故意制造失败观察回滚。下面的脚本在两个会话里都需要执行为了区分建议在命令行或 Navicat 中分别开两个查询窗口。-- 会话A开启事务扣减库存然后暂停等待输入 START TRANSACTION; UPDATE product SET stock_qty stock_qty - 1 WHERE product_id 1; SELECT * FROM product WHERE product_id 1; -- 此时先不提交切到会话B执行查询观察B能否读到stock_qty99 -- 继续会话A插入订单明细然后回滚 INSERT INTO order_detail(order_id, product_id, quantity) VALUES (1001, 1, 1); ROLLBACK; -- 回滚后验证会话B重新查询看到stock_qty仍为100且order_detail无1001记录脚本逻辑说明UPDATE 之后的 SELECT 能读到修改后的值是因为当前会话读自己未提交的修改这是数据库的事务内可见性规则不叫脏读脏读要发生在另一个事务。ROLLBACK 之后UPDATE 和 INSERT 的效果一起撤销两个表回到事务前状态用户能观察到原子性如何体现在“库存在订单也写库存不在订单跟着不写”上。命令参数说明如果希望只撤销部分操作用 SAVEPOINT。在 INSERT 之后设置保存点再执行一条会失败的 SQL最后 ROLLBACK TO SAVEPOINT这样订单明细可以保留库存扣减仍然生效。这种写法在报告里可以解释为“部分回滚用于长事务中保留已完成步骤”。3.3 完整性约束触发实验违反约束时数据库的动作实验5另一类必写内容是让数据库主动拒绝非法数据。把 PRODUCT 表里的商品库存改成 -5看 CHECK 约束如何报错再删除一条已经被 order_detail 引用的 product_id看 RESTRICT 行为。这两步在实验报告里都要写清楚“执行语句 报错信息 违背哪种约束”。-- 违反CHECK约束期望报错 UPDATE product SET stock_qty -5 WHERE product_id 1; -- 期望看到类似错误: Check constraint ck_product_stock is violated -- 违反外键约束期望被拒绝 DELETE FROM product WHERE product_id 1; -- 期望看到类似错误: Cannot delete or update a parent row: a foreign key constraint fails这里的价值在于让实验者理解应用层可以做校验但数据库约束是最后一道关卡。实验报告里常犯的错是只贴报错截图不写约束名。正确做法是把约束名写进报告如 ck_product_stock、fk_order_product并能解释约束名如何帮助快速定位是哪个字段、哪条规则出了问题。4. 并发实验脏读、不可重复读与死锁的复现和排查4.1 不同隔离级别下的脏读只在 READ UNCOMMITTED 下能看见并发实验是实验5的重头戏需要两个会话配合一个写一个读。复现脏读的条件是两个事务都处于 READ UNCOMMITTED这样会话B能读到会话A未提交的修改。-- 会话A和会话B都先设置隔离级别 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -- 会话A更新库存不提交 START TRANSACTION; UPDATE product SET stock_qty stock_qty - 1 WHERE product_id 1; -- 会话B在未提交的情况下查询 SELECT stock_qty FROM product WHERE product_id 1; -- 结果为49假设原来50读到的是会话A的未提交修改产生脏读 -- 会话A回滚 ROLLBACK; -- 会话B再次查询结果恢复为50证明之前读到的是脏数据实验逻辑说明脏读的本质是读到临时中间状态这个状态可能因回滚而消失。报告中要在查询结果旁边标注“此时会话A未提交”和“回滚后数值恢复”才能证明观察到了脏读而不是简单地把两个查询结果贴在一起。如果默认隔离级别为 REPEATABLE READ上面的步骤不会出现脏读B 读到的是事务开始前的那一版数据这就是 MVCC 快照读的体现。4.2 不可重复读同一事务内两次查询结果不一致不可重复读的复现比脏读多一步前提两个会话都要在 READ COMMITTED 或更低级别。会话B在一个事务内先查一次会话A修改并提交会话B再查第二次前后不一致就产生了。它的意义在于事务B第一次读取过这条记录后第二次读取时它已经变成另一个值如果业务基于这一行做两次运算就会拿旧值和新值分别计算结果不稳定。-- 会话B设置为READ COMMITTED开启事务并第一次查询 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; START TRANSACTION; SELECT stock_qty FROM product WHERE product_id 1; -- 读到50 -- 会话A更新并提交 UPDATE product SET stock_qty 49 WHERE product_id 1; COMMIT; -- 会话B同一事务内第二次查询 SELECT stock_qty FROM product WHERE product_id 1; -- 读到49两次不一致 COMMIT;这里在 REPEATABLE READ 下重复相同步骤会话B两次都读到 50因为第一次 SELECT 产生的快照被整个事务复用。实验5报告中建议把同一段实验在 READ COMMITTED 和 REPEATABLE READ 下各跑一遍两张结果对比才算把隔离级别的差异讲透。注意快照读靠 MVCC不阻塞其他事务的写操作这是它和加锁读的本质区别。4.3 死锁制造两个事务按相反顺序更新同一组记录死锁的经典复现是事务A先更新 product 表的 1 号记录再更新 2 号记录事务B正好相反先更新 2 号再更新 1 号。两边各持一把锁等待对方的锁僵持几秒后 InnoDB 会自动检测并回滚一个事务另一个事务继续执行。-- 会话A START TRANSACTION; UPDATE product SET price price 1 WHERE product_id 1; -- 此时持有product_id1的排他锁等待product_id2的锁 -- 会话B START TRANSACTION; UPDATE product SET price price 1 WHERE product_id 2; -- 此时持有product_id2的排他锁等待product_id1的锁 -- 返回会话A继续执行 UPDATE product SET price price 1 WHERE product_id 2; -- 阻塞等待会话B释放 -- 返回会话B继续执行 UPDATE product SET price price 1 WHERE product_id 1; -- 触发死锁检测其中一个会话报错并回滚实验报告需要记录死锁报错文本例如“Deadlock found when trying to get lock; try restarting transaction”并写明会话 B 被回滚、会话 A 成功提交最后两个商品的价格都只加了 1 而不是各加 2 或加 3说明死锁回滚保证了数据没有出现叠加错误。生产环境排查死锁时用 SHOW ENGINE INNODB STATUS 查看 LATEST DETECTED DEADLOCK 段能读到两个事务各自执行了哪条语句、等待了哪把锁这也是实验5可以延伸掌握的排查手段。5. 实验报告的最后一步把现象变成可复现的证据5.1 验证隔离级别的自证手段写实验报告时不能只贴“我执行了 SET TRANSACTION ISOLATION LEVEL”。一种可靠的验证方法是利用 information_schema 查询事务和锁信息证明当前会话处于什么隔离级别、事务里持有多少把锁。-- 查看当前会话的隔离级别 SELECT transaction_isolation; -- 查看正在运行的事务和锁等待关系 SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id FROM information_schema.innodb_trx; -- 查看锁等待发生时的阻塞源头 SELECT * FROM sys.innodb_lock_waits\G这三条 SQL 的用途分别是第一条记录会话的隔离级别配合实验现象一起放进报告避免只写结论没有环境参数第二条确认事务是在 RUNNING 还是 LOCK WAIT 状态尤其是死锁实验前可以确认两个事务都已开启第三条查看锁等待的来源表、来源线程和阻塞线程这是排查死锁的完整证据链。使用时要说明版本差异MySQL 5.7 用 tx_isolation8.0 后才换成 transaction_isolation达梦数据库的对应视图则是 v$trx 和相关动态视图。5.2 报告里要写全的三类参数一份有说服力的实验5报告最少包含三类参数数据库品牌和版本、隔离级别、事务操作顺序。版本对实验结果影响很大例如 MySQL 8.0 的 REPEATABLE READ 行为和 5.7 基本一致但锁信息和系统视图名称变了达梦的默认隔离级别是 READ COMMITTED复现不可重复读时不需要额外设置直接跑 4.2 节的脚本即可。操作顺序用 A、B 会话分别列出每条 SQL 前面标注“A1、A2、B1、B2”这样的步骤号比对结果时只按行号引用避免大段文字描述造成混乱。5.3 收尾时留一个快照验证脚本最后一个技巧是给报告附一段验证脚本把实验后的数据状态和预期结果写成对照关系product 表最终库存应为多少、order_detail 里是否包含某条记录。这样做的好处是让评审者快速确认事务是全部提交还是部分回滚。如果实验环境允许定时器还可以在死锁实验后间隔 10 秒再查一次两张表的状态证明死锁回滚后数据回到一致起点。实验5的全部意义就在于用数据变化证明机制而不是用文字说明机制。本文还有配套的精品资源点击获取