MySQL数据库应用技术与实战电子教案 单元8 MySQL事务与锁

单元8MySQL事务与锁课程名称：MYSQL数据库应用课程类别：必修适用专业：计算机技术类相关专业总学时：64学时（其中理论31学时，实践33学时）总学分：4.0学分本章学时：8学时（其中理论4学时，实践4学时）学情分析：学生已完成单元七的学习，掌握了存储过程的创建、调用和管理，能够用存储过程封装多步骤的业务逻辑。但学生在编写存储过程时还没有考虑“多步骤操作失败后如何整体撤销”的问题。例如积分下单功能需要“扣减积分+创建订单+更新库存”三个步骤，如果第二步失败而第一步已经执行，数据就会不一致。本单元将学习事务与锁机制——事务保证一组操作要么全部成功要么全部回滚，锁机制解决多用户并发操作时的数据冲突问题。事务和锁是数据库并发控制的核心概念，比较抽象，学生不易直观感知，需要借助两个客户端窗口对比演示，让学生亲眼看到事务回滚和并发冲突的效果。材料清单《MYSQL数据库应用技术与实战（微课版）》教材。配套PPT。代码及SQL脚本。引导性提问。探究性问题。拓展性问题。教学目标与基本要求教学目标先掌握事务的概念、ACID特性以及事务的开启、提交和回滚方法，然后了解MySQL的锁分类和InnoDB锁算法，最后理解锁带来的并发问题并掌握事务隔离级别的概念和设置方法。基本要求掌握事务的开启、提交和回滚操作掌握在存储过程中使用事务的方法掌握表级锁的添加和解除方法掌握行级锁（SELECT...FORUPDATE）的使用方法掌握事务隔离级别的设置方法问题引导性提问引导性提问需要教师根据教材内容和学生实际水平，提出问题，启发引导学生思考解决问题，帮助学生理解掌握知识，提升专业实践能力。在银行转账业务中，“从A账户扣款”和“向B账户加款”是两个步骤，如果扣款成功但加款失败，会造成什么后果？如何保证这两个步骤要么都成功要么都失败？什么是事务的ACID特性？每个特性分别保证了什么？多个用户同时操作同一条数据时，可能会产生哪些问题？数据库是如何避免这些问题的？探究性问题探究性问题需要教师深入钻研教材的基础上精心设计，在提问的角度或者在引导性提问的基础上，从重点、难点问题切入，进行插入式提问。或者是对引导式提问中尚未涉及但在课文中又是重要的问题加以设问。MySQL中自动提交（AUTOCOMMIT）默认是开启还是关闭的？它对事务操作有什么影响？回退点（SAVEPOINT）的作用是什么？它与直接回滚整个事务有什么区别？共享锁和排他锁有什么区别？为什么说行级锁比表级锁的并发能力更强？脏读、不可重复读、幻读分别是什么？它们之间有什么区别？拓展性问题拓展性问题需要教师深刻理解教材的意义、学生的学习动态后，根据学生学习层次，提出切实可行的关乎实际的可操作问题。亦可以提供拓展资料供学生研习探讨，完成拓展性问题。为什么InnoDB存储引擎支持事务而MyISAM不支持？在选择存储引擎时应该考虑哪些因素？在实际项目中，如何根据业务场景选择合适的隔离级别？隔离级别越高越好吗？数据库的锁机制和事务隔离级别之间有什么关系？它们是如何共同保证数据一致性的？主要知识点、重点与难点主要知识点事务的概念与ACID特性自动事务提交的查看与关闭事务的开启（STARTTRANSACTION）、提交（COMMIT）与回滚（ROLLBACK）回退点（SAVEPOINT）的设置在存储过程中使用事务锁机制概述（表级锁/行级锁）锁分类（共享锁/排他锁/意向锁）InnoDB锁算法（记录锁/间隙锁/Next-Key锁）锁带来的问题（脏读/不可重复读/幻读/丢失更新）事务的四种隔离级别及其设置重点事务的概念与ACID特性事务的开启、提交与回滚操作回退点（SAVEPOINT）的设置在存储过程中使用事务表级锁与行级锁的区别事务隔离级别的概念与设置难点ACID特性中隔离性和持久性的理解存储过程中事务的正确使用脏读、不可重复读、幻读的区别事务隔离级别与并发问题的对应关系教学过程设计第一次课（4课时：理论2学时+实践2学时）理论教学（2课时，80分钟）第1课时事务的概念与ACID特性（40分钟）1.课堂导入（5分钟）以银行转账为例提问：“A账户向B账户转账100元，数据库需要执行‘A账户扣款100元’和‘B账户加款100元’两条UPDATE语句。如果第一条执行成功、第二条执行失败，会出现什么情况？钱少了100元去了哪里？”引导学生认识到多步骤操作必须作为一个整体执行，引出本课主题——事务。2.事务的概念（8分钟）讲解事务的概念：事务（Transaction）是数据库操作的最小工作单元，是一组要么全部执行成功、要么全部执行失败的SQL语句集合。（1）事务典型场景：转账、下单、积分变更等涉及多步骤数据修改的业务。（2）举例：积分下单功能包含“扣减用户积分”“创建订单”“更新商品库存”三个步骤，三个步骤必须作为一个事务整体执行。（3）说明：只有InnoDB存储引擎支持事务，MyISAM等存储引擎不支持事务。MySQL8.0中默认存储引擎为InnoDB。3.事务的ACID特性（12分钟）讲解事务的四个核心特性（ACID）：（1）原子性（Atomicity）：事务中的操作要么全部成功，要么全部失败回滚，不存在部分成功的情况。实现机制：通过回滚日志（undolog）记录修改前的数据，失败时根据日志回滚。（2）一致性（Consistency）：事务执行前后，数据库的完整性约束不被破坏，数据始终保持一致状态。例如转账前后，两个账户的余额总和不变。（3）隔离性（Isolation）：多个事务并发执行时，一个事务的执行不能被其他事务干扰，各事务之间相互隔离。（4）持久性（Durability）：事务一旦提交，对数据的修改就是永久性的，即使系统故障也不会丢失。实现机制：通过重做日志（redolog）保证。通过转账案例逐一对应讲解四个特性：转账的原子性（两笔更新一起成功或失败）、一致性（总金额不变）、隔离性（两个转账互不干扰）、持久性（提交后余额永久改变）。4.自动事务提交的查看与关闭（10分钟）讲解MySQL的自动提交机制：（1）查看自动提交状态：SELECT@@AUTOCOMMIT;返回1表示开启自动提交，返回0表示关闭。（2）自动提交的含义：开启时（默认），每条SQL语句执行后立即自动提交，无法回滚；关闭时，需要手动COMMIT提交或ROLLBACK回滚。（3）关闭自动提交：SETAUTOCOMMIT=0;或SET@@AUTOCOMMIT=0;关闭后，执行的DML语句不会立即生效，必须执行COMMIT才真正提交。（4）强调：自动提交只影响DML语句（INSERT/UPDATE/DELETE），不影响DDL语句（CREATE/DROP等，DDL自动提交且无法回滚）。演示查看和修改自动提交状态，并演示关闭自动提交后执行UPDATE再ROLLBACK的效果，让学生初步感知事务回滚。5.课堂小结（5分钟）总结本课核心知识：事务是最小工作单元，具有原子性、一致性、隔离性、持久性四大特性；InnoDB支持事务而MyISAM不支持；MySQL默认开启自动提交，可用SELECT@@AUTOCOMMIT查看、SETAUTOCOMMIT=0关闭。下节课学习事务的开启、提交、回滚和回退点。第2课时事务的提交、回滚与回退点（40分钟）1.复习导入（5分钟）回顾ACID特性和自动提交。提问：“关闭自动提交后，如何手动控制事务的提交和回滚？如果事务中的某一步出错，能不能只撤销这一步而不是整个事务？”引出本课主题——事务的开启、提交、回滚与回退点。2.事务的开启、提交与回滚（12分钟）讲解事务操作的三条核心语句：（1）开启事务：STARTTRANSACTION;或BEGIN;开启后，此后的DML语句处于同一事务中，不会立即提交。（2）提交事务：COMMIT;将事务中的所有修改永久保存到数据库。（3）回滚事务：ROLLBACK;撤销事务中所有未提交的修改，数据恢复到事务开始前的状态。演示完整流程：STARTTRANSACTION;执行UPDATE修改数据；SELECT验证修改；ROLLBACK;再次SELECT验证数据恢复原状。再演示COMMIT提交后数据永久保存。强调：只有未提交的事务才能回滚；COMMIT或ROLLBACK之后，事务结束。3.回退点（SAVEPOINT）的设置（8分钟）讲解回退点的概念：回退点（SAVEPOINT）是事务中设置的标记点，可以只回滚到该标记点，而不必撤销整个事务。（1）设置回退点：SAVEPOINT回退点名;（2）回滚到回退点：ROLLBACKTO回退点名;撤销回退点之后执行的修改，保留回退点之前的修改。（3）示例：STARTTRANSACTION;执行第一条UPDATE;SAVEPOINTsp1;执行第二条UPDATE;ROLLBACKTOsp1;此时第一条修改保留，第二条修改被撤销。演示回退点的使用，强调：回滚到回退点后事务并未结束，仍然可以继续执行或最终COMMIT/ROLLBACK。4.在存储过程中使用事务（10分钟）讲解在存储过程中使用事务的方法：在BEGIN...END内使用STARTTRANSACTION、COMMIT、ROLLBACK控制事务。（1）示例：创建积分下单存储过程——DELIMITER$$CREATEPROCEDUREpro_order(INu_idINT,INpoints_costINT)BEGINDECLAREEXITHANDLERFORSQLEXCEPTIONBEGINROLLBACK;END;STARTTRANSACTION;UPDATEuserinfoSETpoints=points-points_costWHEREid=u_id;INSERTINTOorderinfo(userId,pointsCost)VALUES(u_id,points_cost);COMMIT;END$$DELIMITER;（2）讲解：事务内先扣减积分再创建订单，任一步出错都回滚；使用DECLAREEXITHANDLERFORSQLEXCEPTION捕获异常并回滚，保证原子性。（3）强调：存储过程内使用事务可以保证多步骤业务逻辑的整体性，这是实际项目中最常用的做法。5.任务书8.1讲解与课堂小结（5分钟）任务书8.1要求完成教务系统积分管理模块——在存储过程中使用事务实现积分下单功能，并测试事务提交与回滚。总结本课核心知识：STARTTRANSACTION开启事务、COMMIT提交、ROLLBACK回滚；SAVEPOINT设置回退点，ROLLBACKTO回退到标记点；存储过程中配合异常处理使用事务可以保证业务原子性。实践教学（2课时，80分钟）1.任务导入（5分钟）明确本节课的实践任务：完成任务书8.1——在存储过程中使用事务实现积分下单功能，并测试事务提交与回滚。本节课将在两个客户端窗口（Navicat查询窗口）中对比操作，直观观察事务和锁的效果。2.事务操作演示（15分钟）在Navicat查询窗口演示手动事务操作：（1）SELECT@@AUTOCOMMIT;查看自动提交状态。（2）STARTTRANSACTION;UPDATEuserinfoSETpoints=points-100WHEREid=1;在当前窗口SELECT验证积分已扣减。（3）在另一个查询窗口SELECT该用户积分，发现仍是原值（未提交，其他会话看不到），让学生直观感受事务隔离。（4）回到第一个窗口ROLLBACK;再SELECT验证积分恢复原值，演示回滚效果。再演示COMMIT提交的效果，强调：未提交的修改对其他会话不可见，这是事务隔离性的直观体现。3.存储过程实现积分下单功能（30分钟）讲解并演示创建积分下单存储过程：DELIMITER$$CREATEPROCEDUREpro_order(INu_idINT,INpoints_costINT)BEGINDECLAREEXITHANDLERFORSQLEXCEPTIONBEGINROLLBACK;END;STARTTRANSACTION;UPDATEuserinfoSETpoints=points-points_costWHEREid=u_id;INSERTINTOorderinfo(userId,pointsCost)VALUES(u_id,points_cost);COMMIT;END$$DELIMITER;演示正常调用：CALLpro_order(1,100);验证积分扣减和订单插入都成功。演示异常情况：故意传入不存在的用户id或超量积分，观察存储过程是否整体回滚，验证事务的原子性。学生自主完成任务书8.1的存储过程创建和调用，巡回指导，重点关注：事务语句在存储过程中的位置、异常处理语句的写法、COMMIT/ROLLBACK的配对。要求截图保存执行结果。4.测试事务提交与回滚（20分钟）安排学生完成事务提交与回滚的专项测试：（1）关闭自动提交：SETAUTOCOMMIT=0;执行UPDATE后不提交，在另一个窗口验证数据未变，执行ROLLBACK观察回滚效果。（2）开启事务后设置回退点：STARTTRANSACTION;更新数据；SAVEPOINTsp1;再更新数据；ROLLBACKTOsp1;验证第一处修改保留、第二处修改被撤销。（3）恢复自动提交：SETAUTOCOMMIT=1;巡回指导，重点关注：回退点之前和之后的修改范围区分、自动提交状态的恢复。要求截图保存测试结果。5.课堂小结（10分钟）总结本节课实践内容：手动事务的开启、提交、回滚操作；两个窗口对比观察未提交数据对其他会话不可见；存储过程中使用事务和异常处理保证积分下单的原子性；回退点的设置与局部回滚。强调：事务是保证数据一致性的基础工具，实际项目中多步骤数据修改必须放在事务中。预告下节课学习锁机制和事务隔离级别。第二次课（4课时：理论2学时+实践2学时）理论教学（2课时，80分钟）第3课时锁机制与InnoDB锁算法（40分钟）1.课堂导入（5分钟）提问：“多个用户同时登录教务系统，同时修改同一条学生记录，会发生什么？两个事务同时给同一个用户增加积分，积分会不会算错？”引出本课主题——锁机制。数据库通过锁来协调多个事务对共享数据的并发访问，保证数据的一致性。2.锁机制概述（10分钟）讲解锁的概念：锁是数据库用来控制多个并发事务对共享资源访问的机制，防止多个事务同时修改同一数据造成数据不一致。（1）表级锁：锁定整张表。加锁简单、开销小、冲突概率高、并发能力低。示例：LOCKTABLES表名READ/WRITE;解除锁定使用UNLOCKTABLES;（2）行级锁：只锁定被操作的行。加锁复杂、开销大、冲突概率低、并发能力高。InnoDB存储引擎支持行级锁，MyISAM只支持表级锁。（3）对比：表级锁适合读多写少的场景，行级锁适合高并发写入场景。演示表级锁的添加和解除：LOCKTABLESstuinfoWRITE;其他会话无法操作该表；UNLOCKTABLES;解除锁定。3.锁的分类（10分钟）讲解锁按类型（模式）的分类：（1）共享锁（SharedLock，S锁）：又称读锁。多个事务可以同时加共享锁读取同一数据，但加共享锁期间其他事务不能修改该数据。SELECT...LOCKINSHAREMODE为查询行加共享锁。（2）排他锁（ExclusiveLock，X锁）：又称写锁。加排他锁期间，其他事务不能加任何锁访问该数据。SELECT...FORUPDATE为查询行加排他锁；INSERT/UPDATE/DELETE语句会自动加排他锁。（3）意向锁（IntentionLock）：InnoDB在加行级锁之前自动为表加意向锁（意向共享锁IS、意向排他锁IX），用于快速判断表中是否有行被锁定，提高锁冲突检测效率。强调：共享锁与共享锁兼容，共享锁与排他锁互斥，排他锁与排他锁互斥。4.InnoDB锁算法（10分钟）讲解InnoDB存储引擎的三种行锁算法：（1）记录锁（RecordLock）：只锁定索引记录本身，即单条记录。（2）间隙锁（GapLock）：锁定索引记录之间的间隙（范围），防止其他事务在间隙中插入新记录，解决幻读问题。（3）Next-Key锁：记录锁与间隙锁的组合，既锁定记录本身，也锁定记录前的间隙。InnoDB默认使用Next-Key锁。通过图示说明记录锁、间隙锁、Next-Key锁的锁定范围，强调：间隙锁和Next-Key锁主要用于防止幻读，只在可重复读及以上隔离级别下生效。5.课堂小结（5分钟）总结本课核心知识：锁控制并发访问，分为表级锁和行级锁；锁按模式分为共享锁（S）、排他锁（X）和意向锁；InnoDB行锁算法包括记录锁、间隙锁、Next-Key锁。下节课学习锁带来的并发问题和事务隔离级别。第4课时并发问题与事务隔离级别（40分钟）1.复习导入（5分钟）回顾锁的分类和InnoDB锁算法。提问：“两个事务并发执行时，如果没有隔离措施，会出现哪些数据异常？数据库提供了几种隔离级别？隔离级别越高越好吗？”引出本课主题——锁带来的问题和事务隔离级别。2.锁带来的问题（15分钟）讲解并发事务可能产生的四类问题：（1）脏读：一个事务读到了另一个事务未提交的数据。例如事务A修改了积分但未提交，事务B读到了修改后的积分，之后事务A回滚，事务B读到的就是脏数据。（2）不可重复读：一个事务内两次读取同一数据，结果不一致。例如事务A先读到积分100，事务B提交修改为200，事务A再次读到200，两次读取结果不同（针对同一行记录的更新）。（3）幻读：一个事务内两次查询同一范围的数据，结果集不一致。例如事务A查询积分大于100的用户有10个，事务B插入了新的符合条件的记录并提交，事务A再查询发现变成11个（针对新增记录）。（4）丢失更新：两个事务同时修改同一数据，后提交的事务覆盖先提交的事务的修改，导致先提交的更新丢失。逐一举例讲解四类问题的产生场景，重点区分脏读（读未提交）、不可重复读（读已提交但值变化）、幻读（结果集数量变化）三者的不同。3.事务的四种隔离级别（12分钟）讲解MySQL支持的四种事务隔离级别（由低到高）：（1）READUNCOMMITTED（读未提交）：允许读取未提交的数据，存在脏读、不可重复读、幻读问题，隔离性最差。（2）READCOMMITTED（读已提交）：只能读取已提交的数据，解决脏读，但仍存在不可重复读和幻读。Oracle默认隔离级别。（3）REPEATABLEREAD（可重复读）：保证事务内多次读取同一数据结果一致，解决不可重复读，理论上仍可能幻读，但InnoDB通过间隙锁解决了幻读。MySQL默认隔离级别。（4）SERIALIZABLE（串行化）：最高隔离级别，事务串行执行，完全解决脏读、不可重复读、幻读，但并发性能最低。用表格对比四种隔离级别与三类问题的对应关系，强调：MySQL默认是REPEATABLEREAD，隔离级别越高数据越安全但并发性能越低，实际项目需权衡。4.隔离级别的设置（5分钟）讲解事务隔离级别的查看与设置：（1）查看当前隔离级别：SELECT@@TX_ISOLATION;或SELECT@@GLOBAL.TX_ISOLATION;（2）设置会话隔离级别：SETSESSIONTRANSACTIONISOLATIONLEVELREPEATABLEREAD;只对当前会话生效。（3）设置全局隔离级别：SETGLOBALTRANSACTIONISOLATIONLEVELREADCOMMITTED;对所有新会话生效。演示查看和设置隔离级别的操作，强调：SESSION只影响当前会话，GLOBAL影响所有新连接。5.课堂小结（3分钟）总结本课核心知识：并发事务会产生脏读、不可重复读、幻读、丢失更新四类问题；四种隔离级别由低到高为READUNCOMMITTED、READCOMMITTED、REPEATABLEREAD、SERIALIZABLE，分别解决不同程度的问题；MySQL默认可重复读，可用SETTRANSACTIONISOLATIONLEVEL设置。实践教学（2课时，80分钟）1.任务导入（5分钟）明确本节课的实践任务：完成任务书8.2（使用行级锁实现积分赠予功能）和任务书8.3（使用SERIALIZABLE隔离级别实现银行安全转账），并完成单元八综合练习。本节课将在两个查询窗口中进行并发操作对比。2.行级锁实现积分赠予功能（25分钟）讲解任务书8.2的实现思路：用户A向用户B赠予积分，需要同时修改两个用户的积分，为保证并发安全，使用SELECT...FORUPDATE对涉及的行加排他锁。演示操作（窗口1）：STARTTRANSACTION;SELECTpointsFROMuserinfoWHEREid=1FORUPDATE;修改A的积分；再SELECT...FORUPDATE锁定B的行并修改积分；COMMIT;在窗口2尝试：STARTTRANSACTION;SELECTpointsFROMuserinfoWHEREid=1FORUPDATE;观察窗口2被阻塞（等待窗口1释放锁），让学生直观感受行级锁的互斥效果；窗口1提交后窗口2继续执行。学生自主完成任务书8.2，巡回指导，重点关注：FORUPDATE的语法