崔巍数据库实验：MySQL事务隔离与锁机制实战指南

简介本资源是面向高校数据库课程学习者的配套实验材料聚焦SQL实践与数据库核心原理巩固适用于课后练习、课程设计及期末复习场景。压缩包共5个文件全部为.sql脚本文件总大小仅5KB轻量简洁涵盖基础建表与数据插入、多表JOIN查询、GROUP BY聚合统计、事务控制BEGIN/COMMIT/ROLLBACK及简单备份逻辑等典型实验任务便于直接导入MySQL或SQL Server环境运行验证。已有220人下载学习适合作为崔巍《数据库》教材的实操延伸——每个SQL文件对应一个递进式实验模块结构清晰、语句规范附带必要注释可帮助初学者快速建立SQL语感理解范式设计、ACID事务与基础性能优化逻辑切实提升动手能力与问题调试经验。1. 这不是“抄答案”而是用崔巍《数据库课后实验》打通从SQL语法到事务隔离的真实能力断层你手头那本崔巍编著的《数据库课后实验》封面可能已经卷边页脚贴着便利贴但真正卡住你的从来不是第3章“创建学生表”的CREATE语句——而是第7次提交作业后老师批注“事务并发执行结果不符合预期请检查隔离级别设置”。这不是教材写得不好是绝大多数数据库教学把“事务”讲成名词解释把“锁机制”画成抽象流程图却没人告诉你在MySQL 8.0默认REPEATABLE READ下UPDATE语句加的是行锁还是间隙锁为什么SELECT ... FOR UPDATE在唯一索引和非唯一索引上行为完全不同这本书的实验设计恰恰反其道而行它用12个递进式实验从单表CRUD到多用户银行转账逼你亲手触发幻读、观察锁等待超时日志、对比READ COMMITTED与SERIALIZABLE下同一段代码的执行轨迹。适合正在备考软考中级数据库系统工程师、准备校招笔试中“事务一致性”高频题、或刚接手遗留系统发现“库存扣减偶尔为负”的后端开发者——它不教你怎么背ACID它让你在SHOW ENGINE INNODB STATUS\G输出里亲眼看见那行*** (1) WAITING FOR THIS LOCK TO BE GRANTED:。2. 用崔巍实验手册搭建可复现的本地验证环境MySQL 8.0 官方示例库 精确到秒的事务时间戳崔巍教材实验依赖真实数据库引擎行为而非模拟器或简化版SQL解析器。这意味着你必须在本地部署一个能复现教材中所有锁现象、隔离级别差异、回滚段行为的环境。常见误区是直接用Docker拉最新MySQL镜像——但教材配套实验脚本如ex7_bank_transfer.sql明确要求innodb_lock_wait_timeout5且autocommit0而官方镜像默认值会掩盖关键现象。2.1 下载并初始化崔巍配套数据库脚本含修正版建表语句教材附录提供的SQL脚本存在两处关键缺失student表未定义主键导致后续实验无法触发行锁bank_account表缺少balance字段的CHECK约束教材第9实验要求余额非负。需手动补全-- 创建修正版bank_account表教材原脚本漏掉CHECK CREATE TABLE bank_account ( account_id INT PRIMARY KEY, balance DECIMAL(10,2) NOT NULL CHECK (balance 0), last_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ); -- 插入初始数据教材P42要求两个账户各1000元 INSERT INTO bank_account VALUES (1, 1000.00), (2, 1000.00);提示务必使用DECIMAL(10,2)而非FLOAT否则第11实验“小数精度丢失”将无法复现教材描述的截断现象。教材中所有金额字段均采用定点数这是金融类实验的硬性前提。2.2 配置MySQL 8.0核心参数以匹配教材实验条件崔巍实验对事务行为的观测高度依赖InnoDB底层参数。以下配置必须写入my.cnfLinux路径/etc/mysql/my.cnfWindows路径C:\ProgramData\MySQL\MySQL Server 8.0\my.ini[mysqld] # 教材实验7-12要求显式控制事务关闭自动提交 autocommit 0 # 锁等待超时设为5秒教材P67明确要求 innodb_lock_wait_timeout 5 # 关键启用锁监控教材P72要求查看锁信息 innodb_status_output ON innodb_status_output_locks ON # 隔离级别设为REPEATABLE READ教材默认也是MySQL 8.0默认值 transaction_isolation REPEATABLE-READ # 启用binlog用于实验10的主从同步验证教材P89要求 log_bin mysql-bin binlog_format ROW重启MySQL服务后执行SELECT autocommit, transaction_isolation;确认返回0和REPEATABLE-READ。若返回1说明配置未生效——常见原因是配置文件路径错误或MySQL未读取该文件可通过mysqld --verbose --help | grep Default options确认实际加载路径。2.3 验证环境是否满足教材实验要求的三个黄金指标检查项执行命令期望结果不符后果锁监控是否开启SHOW VARIABLES LIKE innodb_status_output%;innodb_status_outputONinnodb_status_output_locksON无法执行教材P72“观察锁等待状态”实验事务隔离级别SELECT transaction_isolation;REPEATABLE-READ实验8“不可重复读现象”将无法触发自动提交关闭SELECT autocommit;0所有需手动COMMIT/ROLLBACK的实验如实验7银行转账将自动提交失去并发控制意义血泪经验某次调试实验9时发现SELECT * FROM bank_account WHERE account_id1 FOR UPDATE;不阻塞其他会话最终排查是account_id字段未设为主键——InnoDB在非主键字段上加锁会升级为表锁而教材明确要求“在主键上加锁观察行锁效果”。务必用SHOW CREATE TABLE bank_account;确认主键存在。3. 从教材实验7切入用三步法拆解银行转账中的事务隔离陷阱崔巍教材实验7P65要求实现“账户A向B转账100元”看似简单却是检验你是否真正理解事务边界的试金石。教材给出的参考SQL存在一个隐蔽陷阱它未考虑SELECT ... FOR UPDATE的锁范围差异。我们用三步法还原真实场景。3.1 第一步构造并发冲突场景教材P66要求的“同时操作”启动两个MySQL客户端Client A和Client B分别执行-- Client A发起转账 START TRANSACTION; SELECT balance FROM bank_account WHERE account_id 1; -- 查A余额 -- 此处故意暂停等待Client B执行 -- 实际操作按CtrlZ挂起进程或在另一终端执行Client B-- Client B同时查询A账户 START TRANSACTION; SELECT balance FROM bank_account WHERE account_id 1; -- 查A余额 -- 此时Client A尚未UPDATEClient B应能立即读到1000.00关键逻辑教材此处考察READ COMMITTED与REPEATABLE READ的区别。在REPEATABLE READ下Client B的SELECT会读取事务开始时的快照即1000.00而Client A的UPDATE会加行锁但Client B的SELECT不加锁故不阻塞——这正是教材P67强调“非阻塞读”的依据。3.2 第二步触发锁等待并捕获InnoDB状态教材P72核心技能Client A继续执行-- Client A UPDATE bank_account SET balance balance - 100 WHERE account_id 1; -- 此时A账户余额变为900但事务未提交行锁持续持有此时Client B尝试更新同一行-- Client B UPDATE bank_account SET balance balance 100 WHERE account_id 1; -- 将被阻塞等待Client A释放锁等待5秒后因innodb_lock_wait_timeout5Client B报错ERROR 1205 (40001): Deadlock found when trying to get lock; try restarting transaction。此时立即在Client A执行-- Client A在超时前 SHOW ENGINE INNODB STATUS\G在输出中定位TRANSACTIONS部分你会看到类似---TRANSACTION 4218, ACTIVE 12 sec mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s) MySQL thread id 8, OS thread handle 140234567890123, query id 123 localhost root updating UPDATE bank_account SET balance balance - 100 WHERE account_id 1 ------- TRX HAS BEEN WAITING 12 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 28 page no 3 n bits 72 index PRIMARY of table test.bank_account trx id 4218 lock_mode X locks rec but not gap waiting参数说明lock_mode X locks rec but not gap表明这是对主键记录的排他行锁X锁而非间隙锁gap lock。教材P73指出“当WHERE条件命中唯一索引时InnoDB只锁匹配记录”这正是你亲手验证的结论。3.3 第三步用时间戳证明事务可见性边界教材P68“不可重复读”验证Client A提交后Client B再次查询-- Client A COMMIT; -- Client B此时仍在事务中 SELECT balance FROM bank_account WHERE account_id 1; -- 仍显示1000.00快照读 SELECT * FROM bank_account WHERE account_id 1 LOCK IN SHARE MODE; -- 加锁读返回900.00玄学点破教材P68说“REPEATABLE READ下不会出现不可重复读”但LOCK IN SHARE MODE强制当前读current read绕过MVCC快照——这正是教材要求你对比两种读方式的深意。不亲手执行永远分不清“快照读”和“当前读”。4. 崔巍实验中必须避开的5个致命坑从锁表到隐式提交的血泪清单教材实验看似步骤清晰但每个编号背后都埋着生产环境级的坑。以下是我在带学生复现时累计记录的5个高频翻车点按“现象→原因→解决”结构整理4.1 现象实验4“插入学生记录”时INSERT INTO student VALUES (1,张三);报错Duplicate entry 1 for key PRIMARY但SELECT * FROM student;返回空原因教材P35要求先TRUNCATE TABLE student;但TRUNCATE是DDL语句在MySQL中会隐式提交当前事务。若你在事务中执行TRUNCATE随后INSERT将处于新事务而TRUNCATE前的INSERT未提交导致数据看似消失。解决严格按教材顺序——所有DDL操作CREATE TABLE、TRUNCATE必须在START TRANSACTION之前执行DML操作INSERT、UPDATE必须在事务内。4.2 现象实验8“修改课程学分”中UPDATE course SET credit5 WHERE course_name数据库原理;执行后SELECT credit FROM course WHERE course_name数据库原理;仍返回旧值原因course_name字段未建索引WHERE条件导致全表扫描InnoDB升级为表锁。此时其他会话的SELECT被阻塞你以为没更新成功实则是锁等待。解决执行CREATE INDEX idx_course_name ON course(course_name);后再运行UPDATE。教材P52虽未明说但实验8的并发验证依赖索引优化的行锁。4.3 现象实验10“主从同步”中从库SHOW SLAVE STATUS\G显示Seconds_Behind_Master: NULL且Slave_SQL_Running: No原因教材P89要求CHANGE MASTER TO时指定MASTER_LOG_FILE和MASTER_LOG_POS但新手常复制SHOW MASTER STATUS输出的File和Position值却忽略从库IO线程已追平主库——此时MASTER_LOG_POS应为Exec_Master_Log_Pos值而非Position。解决在从库执行STOP SLAVE; CHANGE MASTER TO MASTER_LOG_FILEmysql-bin.000001, MASTER_LOG_POS154; START SLAVE;其中154取自SHOW SLAVE STATUS\G的Exec_Master_Log_Pos。4.4 现象实验11“小数精度计算”中UPDATE bank_account SET balance balance * 0.99 WHERE account_id1;后余额变为990.0000000000001而非990.00原因balance字段定义为DECIMAL(10,2)但balance * 0.99运算中0.99被MySQL视为DOUBLE导致中间结果精度丢失。解决强制类型转换UPDATE bank_account SET balance ROUND(balance * 0.99, 2) WHERE account_id1;。教材P95强调“金融计算必须显式ROUND”。4.5 现象实验12“存储过程转账”中CALL transfer_money(1,2,100);执行后SELECT * FROM bank_account;显示A账户扣减成功但B账户未增加原因存储过程中未声明DETERMINISTIC或READS SQL DATAMySQL 8.0默认拒绝创建教材P102未提及此安全限制。解决创建存储过程时添加特性声明CREATE DEFINERrootlocalhost PROCEDURE transfer_money(IN from_id INT, IN to_id INT, IN amount DECIMAL(10,2)) READS SQL DATA BEGIN -- 过程体 END注意教材所有存储过程实验均需添加READS SQL DATA否则CALL将报错This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration。5. 把崔巍实验变成你的数据库肌肉记忆用“三屏对照法”固化事务直觉做完12个实验不等于掌握数据库。我带过的学员里80%在实验报告里写出完美SQL却在面试时答不出“为什么RR级别下UPDATE会加间隙锁”。问题出在教材实验是离散动作而真实世界需要连续直觉。我的解决方案是“三屏对照法”——用三个终端窗口同步运行把抽象概念钉进神经回路。5.1 屏幕1实时锁状态监控教材P72的进阶用法在第一个终端持续执行# 每2秒刷新一次InnoDB状态聚焦锁信息 watch -n 2 mysql -uroot -pyourpwd -e SHOW ENGINE INNODB STATUS\G | grep -A 10 TRANSACTIONS | grep -E (lock|wait|TRX)当你在屏幕2执行UPDATE时屏幕1立刻滚动出锁类型、等待时间、事务ID——这比翻教材P73的静态表格管用10倍。重点观察lock_mode X locks rec but not gap行锁与lock_mode X locks gap before rec间隙锁的切换时机教材实验6的“范围查询加锁”就靠这个捕捉。5.2 屏幕2事务时间轴可视化教材P68的动态验证在第二个终端执行-- 开启通用查询日志教材未提但极有用 SET GLOBAL general_log ON; SET GLOBAL log_output TABLE; -- 然后执行你的转账事务 START TRANSACTION; SELECT balance FROM bank_account WHERE account_id 1; UPDATE bank_account SET balance balance - 100 WHERE account_id 1; -- 不提交去屏幕3看日志在第三个终端查日志SELECT event_time, argument FROM mysql.general_log WHERE argument LIKE %bank_account% ORDER BY event_time DESC LIMIT 10;你会看到精确到微秒的SQL执行序列比如2023-10-05 14:22:33.123456 SELECT balance FROM bank_account WHERE account_id 1 2023-10-05 14:22:33.123789 UPDATE bank_account SET balance balance - 100 WHERE account_id 1后悔药时刻当Client B报死锁时立刻查general_log你会发现Client B的UPDATE时间戳晚于Client A的UPDATE——这就是教材P75“死锁是竞争资源的时序问题”的铁证。没有时间戳你永远在猜谁先谁后。5.3 屏幕3隔离级别沙盒教材P66的暴力验证建一个专用测试库用脚本快速切换隔离级别# save_as_ri.sh #!/bin/bash LEVEL$1 mysql -uroot -pyourpwd -e SET SESSION TRANSACTION ISOLATION LEVEL $LEVEL; echo Isolation level set to $LEVEL然后一键切换chmod x save_as_ri.sh ./save_as_ri.sh READ-COMMITTED # 执行同一段转账SQL观察Client B的SELECT结果变化 ./save_as_ri.sh SERIALIZABLE # 再执行观察锁等待是否升级为锁表教材P66说“不同隔离级别表现不同”但没告诉你怎么快速验证。这个脚本让你3秒内完成教材要求的全部对比实验比手动输SET TRANSACTION ISOLATION LEVEL高效10倍。我坚持用这三屏法带了6届学生最顽固的“事务玄学论者”也在第三周放弃背诵ACID定义转而盯着屏幕1的锁日志说“哦原来幻读就是间隙锁没锁住的地方被插入了新记录”。数据库不是背出来的是眼睛看出来的手指敲出来的时间戳印证出来的。希望帮到你。本文还有配套的精品资源点击获取