《MySQL数据库管理与开发（慕课版 第2版）》教案 第15章 事务与锁机制

MySQL数据库管理与开发（慕课版第2版）

第15章

事务与锁机制PAGE2PAGE3MySQL数据库管理与开发（慕课版第2版）教学设计课程名称：授课年级：授课学期：教师姓名：年月课题名称第15章事务与锁机制计划课时2课时教学引入在数据库并发操作中，事务与锁机制是保障数据一致性和完整性的核心技术。无论是银行转账中资金的准确划转，还是电商平台并发下单时的库存控制，都依赖事务确保操作的原子性，依赖锁机制解决并发冲突。本章将系统讲解事务的ACID特性、提交与回滚操作，以及表级锁、行级锁的应用与死锁避免方法，帮助学生掌握高并发场景下的数据安全管理技术。教学目标使学生理解事务的概念及核心特性（原子性、一致性、隔离性、持久性，即ACID）。使学生掌握事务的操作方法：关闭自动提交、显式提交（COMMIT）、回滚（ROLLBACK）及回退点（SAVEPOINT）的使用。使学生掌握在存储过程中创建事务的方法，实现异常时的自动回滚。使学生理解锁机制的基本概念：读锁（共享锁）、写锁（排他锁）及锁粒度（表级锁、行级锁）。使学生掌握MyISAM表的表级锁设置（LOCKTABLES...READ/WRITE）及解锁（UNLOCKTABLES）方法。使学生掌握InnoDB表的行级锁设置（SELECT...LOCKINSHAREMODE/FORUPDATE）及事务中锁的生命周期。使学生了解死锁的概念、产生原因及避免方法，学会利用AI工具生成事务与锁的实例代码。教学重点事务的ACID特性及核心作用（保障数据一致性）事务的基本操作：关闭自动提交（SETAUTOCOMMIT=0）、提交（COMMIT）、回滚（ROLLBACK）及回退点（SAVEPOINT）存储过程中事务的创建（STARTTRANSACTION+异常处理）表级锁：MyISAM表的读锁（READ）与写锁（WRITE）设置及解锁行级锁：InnoDB表的共享锁（LOCKINSHAREMODE）与排他锁（FORUPDATE）应用死锁的概念及避免方法教学难点事务隔离性的理解（并发场景下事务间的相互影响）回退点的作用范围及部分回滚的实现逻辑MyISAM表级锁与InnoDB行级锁的适用场景差异行级锁在事务中的生命周期（提交/回滚后释放）死锁的产生机制及实际场景中的避免策略教学方式课堂教学以PPT讲授为主，结合MySQL命令行实操演示（实时运行文档案例代码），采用“概念解析→案例演示→冲突模拟→实操验证”四步教学法，辅以分组练习（基于文档“上机指导”内容）教学过程第一课时（事务机制）一、复习巩固回顾上一章MySQL性能优化的核心知识（索引优化、查询分析），提问：“当多个用户同时操作数据库时，如何确保转账等操作要么全部成功，要么全部失败？”引出事务的必要性，强事务是保障数据一致性的基础。二、导入新课在银行转账场景中，若A账户向B账户转账500元，A账户扣款成功但B账户加款失败，会导致数据不一致。事务可解决这一问题：将两个操作绑定为一个整体，失败则全部回滚。本课时将学习事务的概念、特性及操作方法，掌握数据一致性保障技术。三、新课讲解知识点1-事务的概念与ACID特性●概念：事务是一组相互依赖的操作单元集合，要么全部执行成功，要么全部失败回滚，确保数据完整性。●ACID特性：1.原子性（Atomicity）：事务是不可分割的整体，操作要么全成，要么全滚；2.一致性（Consistency）：事务执行后数据逻辑一致（如转账后总额不变）；3.隔离性（Isolation）：并发事务间相互隔离，不干扰；4.持久性（Durability）：事务提交后数据永久保存，不受系统故障影响。●案例：银行转账失败场景：A向B转账300元，A扣款失败但B加款成功，导致总额异常，凸显事务必要性。知识点2-关闭MySQL自动提交MySQL默认自动提交（每条SQL独立成事务），需关闭以支持多操作事务：●查看自动提交状态：SHOWVARIABLESLIKE'AUTOCOMMIT';（默认ON）；●显式关闭：SETAUTOCOMMIT=0;（当前会话有效，需手动提交/回滚）；●隐式关闭：STARTTRANSACTION;（开启事务，不改变AUTOCOMMIT值）。●注意：自动提交关闭后，所有操作需COMMIT才生效，ROLLBACK则撤销所有未提交操作。知识点3-事务的提交与回滚●提交事务：COMMIT;——将未提交操作永久写入数据库；●回滚事务：ROLLBACK;——撤销所有未提交操作，恢复到事务开始前状态。●案例（例15-2）：转账失败回滚1.关闭自动提交：SETAUTOCOMMIT=0;2.执行错误转账：CALLproc_transfer(1,2,300);（A余额不足）3.回滚操作：ROLLBACK;——B账户新增金额被撤销。●隐式提交：执行CREATE/ALTER/DROP等DDL命令、LOCKTABLES等操作时，会自动提交当前事务。知识点4-回退点与存储过程中的事务●回退点（SAVEPOINT）：在事务中设置部分回滚标记，语法：SAVEPOINT回退点名;--设置回退点ROLLBACKTOSAVEPOINT回退点名;--回滚到指定点【例15-4】：插入账户后设置回退点，转账失败仅回滚转账操作，保留插入记录。●存储过程中创建事务：结合异常处理实现自动回滚，语法：DELIMITER//CREATEPROCEDURE存储过程名(参数)BEGINDECLAREEXITHANDLERFORSQLEXCEPTIONROLLBACK;--异常回滚STARTTRANSACTION;--开启事务--事务操作（UPDATE/INSERT等）COMMIT;--成功提交END//DELIMITER;【例15-3】：转账存储过程prog_tran_account，异常时自动回滚，确保数据一致。四、归纳总结回顾事务的ACID特性核心作用，梳理事务操作流程：关闭自动提交→开启事务→执行操作→成功提交/失败回滚。强调回退点的部分回滚功能，及存储过程中异常处理与事务结合的必要性。通过课堂练习（创建带回退点的事务）检验掌握情况。五、布置作业●关闭自动提交，手动执行“账户A向B转账200元”的两条UPDATE语句，成功后提交，失败则回滚。●创建存储过程proc_safe_transfer，实现转账功能，要求余额不足时自动回滚并提示错误。●为上述存储过程添加回退点，实现“插入日志→转账”两步操作，转账失败仅回滚转账操作。第二课时（锁机制与死锁）一、复习巩固检查作业中事务的创建与回滚情况，重点分析存储过程中异常处理的逻辑。提问：“当多个事务同时操作同一数据时，如何避免冲突导致的数据不一致？”引出锁机制的必要性。二、导入新课在并发场景中，若两个用户同时修改同一商品库存，可能导致库存超卖或数据错误。锁机制通过控制数据访问权限解决此类冲突：读锁允许共享读取，写锁禁止并发修改。本课时将学习表级锁、行级锁的设置方法及死锁避免策略。三、新课讲解知识点1-锁机制基础●锁的类型：1.读锁（共享锁，ReadLock）：多个事务可同时读取，互不阻塞；2.写锁（排他锁，WriteLock）：阻止其他事务读写，确保修改独占性。（兼容规则：读锁与读锁兼容，读锁与写锁、写锁与写锁均不兼容）●锁粒度：●表级锁：锁定整张表，开销小但并发低（适合MyISAM引擎查询场景）；●行级锁：锁定单行记录，开销大但并发高（适合InnoDB引擎更新场景）。●锁策略：平衡锁开销与并发性能，MySQL各存储引擎自主实现锁策略。知识点2-MyISAM表的表级锁MyISAM引擎仅支持表级锁，通过LOCKTABLES设置：●读锁设置：LOCKTABLE表名READ;——当前事务可读，其他事务可读但不可写；【例15-5】：锁定tb_user表为读锁后，插入数据提示“被读锁阻止”。●写锁设置：LOCKTABLE表名WRITE;——当前事务可读写，其他事务读写全阻塞；【例15-6】：锁定tb_user表为写锁后，新会话查询被阻塞，直至解锁。●解锁：UNLOCKTABLES;——释放所有表锁，允许其他事务访问。●注意：写锁优先级高于读锁，可能插入读锁队列前方。知识点3-InnoDB表的行级锁InnoDB支持行级锁，通过SQL语句显式设置或更新操作隐式添加：●共享锁（读锁）：SELECT...LOCKINSHAREMODE;——允许其他事务读，阻止写；●排他锁（写锁）：SELECT...FORUPDATE;——阻止其他事务读写；●隐式写锁：INSERT/UPDATE/DELETE自动为操作行加排他锁。●锁的生命周期：行级锁在事务内生效，COMMIT或ROLLBACK后释放。【例15-7】：事务1加排他锁后，事务2查询同一行被阻塞，事务1提交后事务2才返回结果。知识点4-死锁的概念与避免●死锁：两个或多个事务相互等待对方释放锁，导致无限期阻塞（如事务1锁行A等行B，事务2锁行B等行A）。●MySQL处理：InnoDB自动检测死锁，撤销其中一个事务以解除阻塞。●避免方法：1.按固定顺序访问资源（如统一按ID升序更新）；2.缩短事务生命周期，减少锁持有时间；3.避免长事务中持有锁等待其他操作。知识点5-AI辅助实例生成AI可生成事务与锁的实例代码，如输入“创建带事务的库存更新存储过程”，生成含异常处理和回退点的代码；输入“MyISAM表读锁设置”，生成LOCKTABLES与解锁示例。四、归纳总结梳理锁机制核心：表级锁（MyISAM，LOCKTABLES）适合查询，行级锁（InnoDB，FORUPDATE）适合并发更新