简介南京邮电大学数据库系统实验报告二以DBMS的数据库保护为主题围绕MySQL环境下的安全控制与并发控制展开适合正在学习数据库原理、需要完成同类实验或理解事务与锁机制的学生参考。报告完整记录了实验目的、原理、操作步骤与验证过程涵盖用户U1/U2创建与权限分配、GRANT/REVOKE授权回收、事务COMMIT/ROLLBACK、X锁/S锁并发场景等关键内容并附有实验小结与常见问题处理。资源共1个文件为标准doc格式文档压缩包大小约1.3MB结构清晰可直接阅读或按需修改。目前已有152人学习下载对于巩固ACID概念、掌握MySQL访问控制及并发控制实践具有不错的参考价值。1. 数据库保护实验到底在考什么如果你拿到的任务是“数据库系统实验报告二DBMS的数据库保护”先别急着打开客户端建表。这个实验真正要验证的不是 SQL 写得有多花哨而是四类机制事务的原子性和隔离性怎么体现系统崩溃后已提交的数据靠什么恢复非法数据为什么进不了表没有权限的人为什么动不了表。数据库保护是 DBMS 自己在做的事你的任务是把它们“逼出来”并留下证据而不是替数据库实现一套保护代码。这个实验最常见的情况是用图形化工具开两个查询窗口一个改了数据没提交另一个窗口怎么查都是旧值于是截图写“看到了隔离”。这其实只摸到了 MVCC 的边。真正要区分的是读已提交、可重复读、串行化在锁上的差异以及 redo 日志和 undo 日志各自负责什么。这篇文章按“建场景 → 跑命令 → 看证据”的顺序把一份能扛住追问的实验过程拆给你代码以 MySQL 8.0 的 InnoDB 引擎为例小版本有差异的地方我会单独标出。适合三类人正在做数据库课程实验报告二的学生、想补并发控制实操经验的开发以及要给学生讲清楚保护机制的教学助理。读完你至少能回答三个问题锁到底锁住了什么崩溃后谁负责恢复为什么说 CHECK 约束在旧版本里可能是个摆设。2. 建库建表、事务脚本与权限账号把实验材料一次备齐数据库保护实验要从并发控制和崩溃恢复开始但不要直接从并发开始。先把库表、事务脚本、权限账号准备好否则后面验证锁、验证回滚时表结构或账号问题会干扰所有结论。2.1 实验环境选型为什么用 MySQL 8.0 InnoDB常见做法是用 MySQL 8.0 做这个实验引擎指定 InnoDB。原因不是 MySQL 最先进而是它的默认存储引擎正好把数据库保护需要的几件工具集齐了支持事务、支持行级锁、支持 redo/undo 日志还提供 information_schema 和 performance_schema 两张系统库能让你直接观察到锁和事务状态。Access 和 SQLite 也能跑 SQL但默认锁粒度要么是文件级要么是库级很难演示行锁竞争SQLite 默认每个写事务独占整个数据库两个连接交替写基本产生不了你想要的锁等待实验现象不明显。选型时还要确认两点。第一确认版本是 8.0 而不是 5.7因为后面查锁等待我会用 performance_schema.data_locks5.7 没有这张表字段对不上。第二把默认存储引擎和隔离级别打出来作为实验报告的“环境参数”。mysql -uroot -p SELECT VERSION(); SELECT transaction_isolation; SELECT autocommit; SHOW VARIABLES LIKE default_storage_engine;逻辑说明VERSION() 确认大版本transaction_isolation 显示默认隔离级别InnoDB 默认是 REPEATABLE-READautocommit 为 1 表示每条语句自动提交做事务实验前必须清楚这一点。参数说明autocommit1 时如果你手动 START TRANSACTION仍然可以自行 COMMIT 或 ROLLBACK但一旦忘记提交后续语句可能被自动提交干扰实验结论建议实验全程把 autocommit 显式设为 0或者严格控制在单个会话里手动提交。2.2 建库建表账户表、库存表、订单表的最小结构我用三张表就够覆盖后面的实验账户表做转账事务库存表做防超卖事务订单表挂外键做完整性验证。字段不引入复杂业务保持最小可复现。CREATE DATABASE IF NOT EXISTS db_protect DEFAULT CHARACTER SET utf8mb4; USE db_protect; CREATE TABLE account ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(32) NOT NULL, balance DECIMAL(10,2) NOT NULL DEFAULT 0, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINEInnoDB; CREATE TABLE stock ( id INT PRIMARY KEY, goods_name VARCHAR(64) NOT NULL, qty INT NOT NULL ) ENGINEInnoDB; CREATE TABLE orders ( id INT PRIMARY KEY AUTO_INCREMENT, user_id INT NOT NULL, qty INT NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES account(id) ) ENGINEInnoDB; INSERT INTO account(name, balance) VALUES (alice, 1000.00), (bob, 1000.00); INSERT INTO stock(id, goods_name, qty) VALUES (1, notebook, 10);逻辑说明balance 用 DECIMAL(10,2) 而不是 FLOAT是为了避免二进制浮点误差在回滚演示里你不想因为精度问题被追问。外键 fk_orders_user 是必须的后面做完整性实验时插入一条不存在的 user_id 会被拒绝这就是 DBMS 保护数据一致性的直接证据。所有表都显式指定 ENGINEInnoDB如果建表时没写默认引擎一旦不是 InnoDB事务和行锁全部失效这是最隐蔽的坑之一。参数说明DECIMAL(10,2) 表示最多 10 位数字小数占 2 位适合账户余额外键列的数据类型必须和父表主键完全一致否则建表直接报错。如果实验报告要求体现实体完整性、参照完整性在建表 SQL 里保留 CONSTRAINT 命名报告里才方便描述。2.3 两组演示事务转账与防超卖数据库保护实验的高发区是并发写冲突。我准备两组最小事务一组转账一组带条件的扣减库存。-- 事务S1转账两个账户的余额要一起变化 START TRANSACTION; UPDATE account SET balance balance - 100.00 WHERE id 1; UPDATE account SET balance balance 100.00 WHERE id 2; COMMIT; -- 事务S2防超卖扣减前先判断库存足够 START TRANSACTION; UPDATE stock SET qty qty - 1 WHERE id 1 AND qty 0; COMMIT;逻辑说明事务 S1 里有两条 UPDATE中间故意留出“提交点”方便实验中去掉 COMMIT 观察未提交状态对其它连接的影响事务 S2 的 WHERE 条件带着 qty 0这是 SQL 层面的业务自保护后面对比 DBMS 锁保护时能讲清楚两者差异。实际做并发实验前先开两个 mysql 命令行会话分别执行 SELECT session.transaction_isolation; 确认隔离级别一致。这里有个关键习惯不要用图形化工具的多个查询窗口代替命令行会话。图形工具会自动复用或隐藏连接你很难确定哪条查询跑在哪个连接上查锁信息时也对应不上 trx_mysql_thread_id。用两个显式终端会话每条命令的效果都是可控的。2.4 完整性与权限的最小闭环CHECK、触发器与 GRANT/REVOKE数据库保护的另一半是完整性与权限建议在准备阶段就把约束和账号建好后面并发与恢复实验不会受干扰。先验证 CHECK 约束ALTER TABLE account ADD CONSTRAINT chk_balance_positive CHECK (balance 0); INSERT INTO account(name, balance) VALUES (carol, -5.00);第二条 INSERT 会触发 CHECK 约束报错数据被拒绝写入。注意 MySQL 的 CHECK 约束从 8.0.16 才开始真正强制生效如果你用的版本更早这条语句会被解析但不检查这时需要靠触发器兜底。触发器写法如下CREATE TRIGGER trg_account_balance_before_insert BEFORE INSERT ON account FOR EACH ROW BEGIN IF NEW.balance 0 THEN SIGNAL SQLSTATE 45000 SET MESSAGE_TEXT balance cannot be negative; END IF; END;逻辑说明SIGNAL SQLSTATE 45000 是自定义异常专用状态码会终止 INSERT 并抛出指定错误。触发器里不能返回结果集所以要么 SIGNAL 报错要么 SET NEW.balance 0 做修正。权限部分创建两个账号一个只读一个可写并验证 REVOKE 生效CREATE USER app_rolocalhost IDENTIFIED BY ro_pass; CREATE USER app_rwlocalhost IDENTIFIED BY rw_pass; GRANT SELECT ON db_protect.* TO app_rolocalhost; GRANT SELECT, INSERT, UPDATE, DELETE ON db_protect.* TO app_rwlocalhost; SHOW GRANTS FOR app_rolocalhost; REVOKE UPDATE ON db_protect.account FROM app_rwlocalhost;逻辑说明SHOW GRANTS 的输出是授权事实报告里贴完整输出比贴一句“我授权了”更有说服力。REVOKE 是即时生效的不需要重启服务。真正验证时必须切换到 app_ro 账号登录执行 UPDATE系统会报权限不足不要用 root 验证root 是超级用户REVOKE 对 root 无效。3. 并发控制隔离级别、行锁与死锁可视化数据库保护最核心的一块是并发控制。事务并发时会遇到脏读、不可重复读、幻读、丢失更新DBMS 用锁定和多版本控制来挡。这一章用三个实验把机制逐层逼出来每个实验都要能留下系统表证据。3.1 四种隔离级别在 MySQL 里的表现差异先查默认隔离级别然后准备两个会话。会话 A 开启事务查询余额但不提交会话 B 修改同一行并提交回到会话 A 再查一次观察两次结果是否一致。下面代码以 READ COMMITTED 为例-- 会话A SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; START TRANSACTION; SELECT balance FROM account WHERE id 1; -- 此时不要在会话A提交 -- 会话B SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; UPDATE account SET balance balance - 200.00 WHERE id 1; COMMIT; -- 回到会话A再次查询 SELECT balance FROM account WHERE id 1;逻辑说明在 READ COMMITTED 下会话 A 第二次查询会看到会话 B 提交后的新值这就是不可重复读。把这个实验在 REPEATABLE-READ 下重做一遍会话 A 两次查询结果一致因为 InnoDB 的快照读在同一事务内使用的是同一份一致性视图。参数说明SET SESSION 只影响当前连接不影响其它会话和全局配置所以两个会话可以各自设置不同隔离级别做对比实验。四种隔离级别的现象差异整理成表格放报告里很直观隔离级别脏读不可重复读幻读InnoDB 锁策略READ UNCOMMITTED可能可能可能读不加锁写加锁READ COMMITTED不可能可能可能每次读生成新快照REPEATABLE READ不可能不可能基本不可能事务内快照一致配合间隙锁SERIALIZABLE不可能不可能不可能读也加共享锁和 ANSI 标准里 RR 可能幻读不同InnoDB 的 REPEATABLE-READ 通过 next-key lock 解决了大部分幻读问题。你在报告里写“MySQL 的 RR 通过间隙锁避免了幻读”比只写一句“RR 会幻读”更能体现你真的跑过实验。3.2 行锁与表锁用 information_schema 现场看锁等待隔离级别是结果锁是手段。现在让两个会话对同一行更新制造锁等待。会话 A 开启事务更新 id1 但不提交会话 B 对同一行执行更新会被阻塞。然后在第三个会话查询锁信息-- 会话A START TRANSACTION; UPDATE account SET balance balance - 1.00 WHERE id 1; -- 不提交 -- 会话B START TRANSACTION; UPDATE account SET balance balance 50.00 WHERE id 1; -- 这条会一直等待 -- 会话C查看事务和锁 SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id FROM information_schema.INNODB_TRX; SELECT ENGINE_TRANSACTION_ID, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS FROM performance_schema.data_locks\G逻辑说明第一段查询能看到两个活跃事务其中一个 trx_state 是 LOCK WAIT另一个是 RUNNING第二段查询能看到锁对象是 account 表的某一行LOCK_TYPE 是 RECORDLOCK_MODE 是 X这就是行级排他锁存在的直接证据。如果你把 WHERE 条件换成没有索引的 name 字段会发现锁的范围变成全表锁记录数暴涨这正好引出后面避坑章里的索引问题。参数说明INNODB_TRX 里的 trx_mysql_thread_id 对应 SHOW PROCESSLIST 里的 Id某个会话卡住时可以用它精确 KILLdata_locks 里的 LOCK_MODE 对排他锁显示 X共享锁显示 S间隙锁会额外显示 GAP。报告里截取包含 X 和 LOCK WAIT 的输出比任何描述都硬。3.3 死锁模拟两个会话互相等锁怎么收场死锁是并发控制里最值得写进报告的部分构造方式不复杂。会话 A 锁住 id1会话 B 锁住 id2然后双方再去更新对方持有的行-- 会话A START TRANSACTION; UPDATE account SET balance balance - 1.00 WHERE id 1; UPDATE account SET balance balance 1.00 WHERE id 2; -- 第二句等待会话B释放id2 -- 会话B START TRANSACTION; UPDATE account SET balance balance - 1.00 WHERE id 2; UPDATE account SET balance balance 1.00 WHERE id 1; -- 死锁被InnoDB检测到其中一个事务被回滚执行后其中一个会话会收到 “Deadlock found when trying to get lock; try restarting transaction” 的错误对应的 UPDATE 被回滚另一个事务正常继续。这里要讲清楚 InnoDB 的死锁处理策略它并不等锁超时而是靠死锁检测发现循环等待选代价较小的事务回滚然后抛出错误号 1213。实验报告里截下错误信息再补充说明“被回滚的事务需要应用层重试”这是 DBA 和开发配合的边界。参数 innodb_deadlock_detect 默认 ON实验里不要关闭。如果关掉它系统只能靠 innodb_lock_wait_timeout 兜底默认 50 秒两个会话会卡到超时才结束体验很差且不能说明死锁检测机制。4. 故障恢复redo/undo、崩溃模拟与备份恢复并发控制管的是“同时跑”恢复机制管的是“跑一半崩了”。这章不需要你写日志而是让 DBMS 用日志把数据救回来。落点是你怎么在报告里证明 redo 和 undo 各自干了什么活。4.1 redo 和 undo 在实验里怎么讲才不空洞很多报告写“InnoDB 通过 redo 日志保证已提交事务不丢失通过 undo 日志回滚未提交事务”这句话没错但太干。你必须有物理层面的支撑。先看数据目录下的日志文件和落盘策略SHOW VARIABLES LIKE innodb_flush_log_at_trx_commit; SHOW VARIABLES LIKE innodb_log_file_size; SHOW VARIABLES LIKE innodb_undo_tablespaces; SHOW VARIABLES LIKE log_error;逻辑说明innodb_flush_log_at_trx_commit 默认是 1表示每次事务提交都把 redo 刷到磁盘所以只要有提交重启就不丢数据log_error 指向错误日志文件后面验证崩溃恢复要到这里找证据。概念上redo 是物理日志记录“数据页被改成什么样”事务提交时刷盘undo 是逻辑日志记录“怎么改回去”用于回滚和快照读。实验报告里画一个简单时序就够事务执行 → 写 undo → 写 redo → 提交 → 刷数据页崩溃后先回滚未提交事务再前滚已提交事务。注意顺序不要写反。4.2 模拟崩溃kill 会话看回滚重启服务看恢复模拟崩溃按安全性从高到低排序kill 一个未提交的会话最安全能观察回滚重启 MySQL 服务次之能观察 redo 对已提交数据的恢复不建议在课程实验里 kill -9 模拟断电一旦恢复链路受影响整个数据目录可能损坏。第一步验证 undo。会话 A 开启事务更新余额但不提交拿到它的线程 ID 后在另一个会话里 kill-- 会话A START TRANSACTION; UPDATE account SET balance balance - 500.00 WHERE id 1; -- 会话B SHOW PROCESSLIST; -- 找到会话A的线程ID后执行 KILL 123;杀完再查询 id1 的余额没提交的 500 元扣减被回滚数据回到事务开始前。这个现象对应 undo 的作用连接终止时事务未提交InnoDB 用 undo 回滚。第二步验证 redo。会话 A 执行事务并正常 COMMIT然后重启 MySQL 服务START TRANSACTION; UPDATE account SET balance balance 200.00 WHERE id 2; COMMIT; FLUSH LOGS;重启后再查询 id2已提交的 200 元更新还在。虽然实验场景里数据页可能早就落盘了但机制上如果数据页还没来得及落盘重启时 InnoDB 会扫描 redo 日志把已提交事务重做一遍。重启后去错误日志里搜索 recovery 或 Starting crash recovery 之类的记录不同版本措辞略有不同把那段日志截进报告就是 redo 恢复过程的现场证据。4.3 手动做一次备份恢复验证逻辑备份的作用数据库保护不能只靠崩溃恢复还要有管理员主动备份的习惯。课程实验里用 mysqldump 做逻辑备份就够了它导出的是 SQL 语句恢复时会重新执行建表和插入。先备份再删表再恢复mysqldump --single-transaction -uroot -p db_protect /tmp/db_protect.sql mysql -uroot -p -e DROP TABLE db_protect.account; mysql -uroot -p db_protect /tmp/db_protect.sql逻辑说明--single-transaction 参数利用 InnoDB 的 MVCC 生成一致性快照导出过程中不阻塞事务适合课程实验恢复时 mysqldump 生成的 SQL 会把表结构和数据重新建立起来。参数说明如果不加 --single-transactionmysqldump 默认会锁表导出虽然这里表很小无所谓但报告中体现你了解这个参数会显得更专业。备份恢复实验做完你的报告里就有了三层数据保护并发控制防同时写乱、日志恢复防崩溃丢数据、备份恢复防误删误改。这三层正好对应事务的隔离性、持久性和管理员兜底手段。5. 避坑数据库保护实验里最容易翻车的 5 个现场这一章写的都是我见过或踩过的真实问题每条按“现象 → 原因 → 解决”展开。照着实验顺序做遇到问题直接对号入座。5.1 现象事务里 UPDATE 没生效外面一查还是旧值第一个会话执行了 UPDATE没提交去另一个会话里查数据还是老样子于是以为 UPDATE 语句执行失败。原因InnoDB 的 MVCC 让其它连接读的是快照未提交事务的修改对其它会话不可见这是隔离性的正常表现不是 SQL 写错。解决回到执行 UPDATE 的会话执行 COMMIT再重新查询。如果想演示脏读需要显式把隔离级别改成 READ UNCOMMITTED不要拿默认配置硬套结论。5.2 现象两个会话同时 UPDATE 同一行后提交的覆盖先提交的两个连接都对同一行 UPDATE而且都成功后提交的事务把先提交的覆盖了看起来没有锁竞争。原因这是典型的丢失更新DBMS 的默认隔离级别并不会阻止“先读旧值、再写新值”的逻辑覆盖因为你没有对读操作加锁。解决把第二步改成 SELECT ... FOR UPDATE 锁定该行START TRANSACTION; SELECT balance FROM account WHERE id 1 FOR UPDATE; UPDATE account SET balance balance - 100.00 WHERE id 1; COMMIT;另一个事务再执行同一条 SELECT ... FOR UPDATE 就会被阻塞等当前事务提交后才能继续。这样才算把并发控制的演示从隔离级别推进到加锁层面。5.3 现象第二个会话的 UPDATE 不阻塞怀疑行锁没生效按文档做两个会话 UPDATE 同一行结果第二个会话秒回完全看不到锁等待。原因最常见的是 WHERE 条件没走索引。InnoDB 的行锁基于索引如果 WHERE 字段没有索引它会把全表记录都锁上锁信息分散且表现接近表锁另一个可能是第二个会话做的是快照读读操作本身不阻塞写操作。解决用 SHOW INDEX FROM account; 确认索引用 EXPLAIN SELECT * FROM account WHERE namealice; 看是否走索引。实验里建议都用主键 id 做条件因为主键天然有索引行锁效果最干净。另外两个连接必须都处于事务中单独一条自动提交的 UPDATE 执行完就释放锁自然看不到等待。5.4 现象模拟崩溃后数据没丢实验报告没法写“恢复”重启数据库后发现数据一条没少感觉实验没做出来因为没有看到“恢复过程”。原因这恰恰是 redo 日志的功劳数据没丢证明可靠性生效但恢复过程的证据在错误日志里不在表数据里。解决重启后立刻按 log_error 变量找到错误日志搜索 recovery 或 Starting crash recovery 关键词把日志片段截进报告。如果重启前执行大量 INSERT 并提交再快速重启错误日志里能看到恢复阶段扫描 redo 的记录。不要为了制造数据丢失去调低 innodb_flush_log_at_trx_commit实验目的是证明机制有效不是制造事故。5.5 现象权限实验里 root 什么都干得成没有说服力用 root 执行 GRANT 和 REVOKE然后继续用 root 验证权限发现 REVOKE 根本拦不住 root报告没法写。原因root 是超级用户MySQL 对超级用户不做权限回收限制另一个坑是账号的主机部分app_rolocalhost 只能从本机 socket 登录如果从 127.0.0.1 连接可能匹配到不同账号规则。解决验证时务必退出 root用普通账号重新登录mysql -uapp_ro -h127.0.0.1 -p UPDATE account SET balance 0 WHERE id 1;执行后能看到权限拒绝错误。把 CREATE USER、GRANT、SHOW GRANTS、被拒错误这四段输出都贴进报告权限链路就完整了。6. 报告与进阶验证把数据库保护证据链补完整6.1 截图之外的证据链用系统表导出锁和事务状态老师批改实验报告时最经不起追问的不是结论而是“你凭什么说这是行锁”。所以在锁等待发生的那一刻把系统表查询结果原样导出比事后补截图有说服力得多SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id FROM information_schema.INNODB_TRX; SELECT ENGINE_TRANSACTION_ID, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS FROM performance_schema.data_locks;在锁等待发生时跑这两条输出里能看到一个事务 LOCK WAIT另一个 RUNNING锁对象指向 account 表主键索引。建议实验过程中用 tee 把命令和输出一起记录到文件或者把终端内容完整复制到报告附录避免事后凭记忆补。6.2 一个值得写进思考题的进阶实验隔离级别组合矩阵如果想把报告从“完成”做到“有深度”可以做隔离级别组合矩阵两个会话分别设置四种隔离级别按“读读、读写、写读、写写”四种组合各跑一次记录是否阻塞、是否读到重复数据最后整理成一张 4×4 矩阵。结论用三句话收住InnoDB 的 MVCC 让读不阻塞写写与写必然互斥串行化下读也加共享锁。这个实验能直接回答“为什么默认隔离级别是 REPEATABLE-READ 却还能保持较高并发”。我自己的习惯是先让实验“出错”再证明机制“兜住”。一份实验报告如果从头到尾只有成功截图多半是只看结论倒推出来的把锁等待、权限拒绝、回滚这些“失败现场”保留下来反而更可信。之前有一次我只写了“重启后数据恢复”几个字被追问“恢复过程中谁是主角”当场说不清 redo 和 undo 的分工后来补了错误日志和事务状态表才算过关。希望这篇笔记能帮你少踩同一个坑用证据链把数据库保护讲扎实希望帮到你。本文还有配套的精品资源点击获取