《MySQL数据库基础与实例教程 工业和信息化普通高等教育“十二五”规划教材 教学课件 ppt 作者 孔祥盛 MySQL数据库基础与实例教程第9章》由会员分享，可在线阅读，更多相关《MySQL数据库基础与实例教程 工业和信息化普通高等教育“十二五”规划教材 教学课件 ppt 作者 孔祥盛 MySQL数据库基础与实例教程第9章（72页珍藏版）》请在金锄头文库上搜索。
1、,孔祥盛,MySQL数据库基础与实例教程 之 事务机制和锁机制,1,2,3,本章探讨了数据库中事务与锁机制的必要性，讲解了如何在数据库中使用事务与锁机制实现数据库的一致性以及并发性，并结合“选课系统”讲解事务与锁机制在该系统中的应用。,4,锁机制,事务机制,事务的ACID特性,事务与锁机制注意事项,内容一览,事务通常包含一系列更新操作，这些更新操作是一个不可分割的逻辑工作单元。如果事务成功执行，那么该事务中所有的更新操作都会成功执行、并将执行结果提交到数据库文件中，成为数据库永久的组成部分。如果事务中某条更新操作执行失败，那么事务中的所有操作均被撤销。,9.1 事务机制,1,2,3,简言之：事
2、务中的更新操作要么都执行，要么都不执行，这个特征叫做事务的原子性。,关闭MySQL自动提交,事务的必要性,回滚,4,5,提交,事务,9.1 事务机制,6,7,简言之：事务中的更新操作要么都执行，要么都不执行，这个特征叫做事务的原子性。,保存点,“选课系统”中的事务,9.1 事务机制,对于银行系统而言，转账业务是银行最基本的、且最常用的业务，有必要将转账业务封装成存储过程，调用该存储过程后即可实现两个银行账户间的转账。 任务布置1：上机操作，完成本书场景描述1的任务要求，理解事务的必要性。,9.1.1 事务机制的必要性,关闭自动提交的方法有两种：一种是显示地关闭自动提交，一种是隐式地关闭自动提交
3、。 方法一：显示地关闭自动提交 使用MySQL命令“set autocommit=0;”，可以显示地关闭MySQL自动提交。,9.1.2 关闭MySQL自动提交,方法二：隐式地关闭自动提交 使用MySQL命令“start transaction;”可以隐式地关闭自动提交。隐式地关闭自动提交，不会修改系统会话变量autocommitte的值。 推荐使用方法二。,9.1.2 关闭MySQL自动提交,关闭MySQL自动提交后，数据库开发人员可以根据需要回滚（也叫撤销）更新操作。 任务布置2：上机操作，完成本书场景描述2以及场景描述3的任务要求，理解回滚的作用。,9.1.3 回滚,MySQL自动提交一
4、旦关闭，数据库开发人员需要“提交”更新语句，才能将更新结果提交到数据库文件中，成为数据库永久的组成部分。自动提交关闭后，MySQL的提交方式分为显示地提交与隐式地提交。,9.1.4 提交,显示地提交：MySQL自动提交关闭后，使用MySQL命令“commit;”可以显示地提交更新语句。 隐式地提交：MySQL自动提交关闭后，使用下面的MySQL语句，可以隐式地提交更新语句。,9.1.4 提交,begin、set autocommit=1、 start transaction、rename table、 truncate table等语句； 数据定义（create、alter、drop）语句，例
5、如create database、create table、 create index、create function、 create procedure、alter table、 alter function、alter procedure、 drop database、drop table、 drop function、drop index、 drop procedure等语句；,9.1.4 提交,权限管理和账户管理语句： 例如grant、revoke、set password、 create user、drop user、rename user等语句）； 锁语句（lock tables、
6、unlock tables）。,9.1.4 提交,任务布置3：上机操作，完成本书场景描述2以及场景描述4的任务要求，理解提交的作用。,9.1.4 提交,使用MySQL命令“start transaction;”可以开启一个事务，该命令开启事务的同时，会隐式地关闭MySQL自动提交。 使用commit命令可以提交事务中的更新语句；使用rollback命令可以回滚事务中的更新语句。,9.1.5 事务,任务布置4：上机操作，完成本书场景描述5的任务要求，理解事务的作用。,9.1.5 事务,保存点（也称为检查点）可以实现事务的“部分”提交或者“部分”撤销。 使用MySQL命令“savepoint 保存
7、点名;”可以在事务中设置一个保存点，使用MySQL命令“rollback to savepoint 保存点名;”可以将事务回滚到保存点状态。,9.1.6 保存点,任务布置5：上机操作，完成本书场景描述6的任务要求，理解保存点的作用。,9.1.6 保存点,说明：“rollback to savepoint B”仅仅是让数据库回到事务中的某个“一致性状态B”，而“一致性状态B”仅仅是一个“临时状态”，该“临时状态”并没有将更新回滚，也没有将更新提交。事务回滚必须借助于rollback（而不是“rollback to savepoint B”），事务的提交需借助于commit。,9.1.6 保存点,
8、使用MySQL命令“release savepoint 保存点名;”可以删除一个事务的保存点。 如果该保存点不存在，该命令将出现错误信息：ERROR 1305 (42000): SAVEPOINT does not exist。如果当前的事务中存在两个相同名字的保存点，旧保存点将被自动丢弃。,9.1.6 保存点,“选课系统”中，最为复杂的业务逻辑莫过于“学生选课”以及“学生调课”功能的实现，之前的章节已经编写了choose_proc()存储过程实现了学生的选课功能。本章将借用事务的概念编写调课存储过程replace_course_proc()，实现“选课系统”的调课功能。,9.1.7 “选课系
9、统”中的事务,任务布置6：上机操作，完成本书场景描述7的任务要求。,9.1.7 “选课系统”中的事务,1,2,3,同一时刻，多个并发用户同时访问同一个数据时，仅仅通过事务机制，无法保证多用户同时访问同一数据的数据一致性，有必要引入锁机制实现MySQL的并发访问，锁机制是实现多用户并发访问的基石。,MySQL锁机制的基础知识,锁机制的必要性,MyISAM表的表级锁,4,5,InnoDB表的行级锁,“选课系统”中的行级锁,9.2 锁,6,7,8,同一时刻，多个并发用户同时访问同一个数据时，仅仅通过事务机制，无法保证多用户同时访问同一数据的数据一致性，有必要引入锁机制实现MySQL的并发访问，锁机制
10、是实现多用户并发访问的基石。,InnoDB行级锁 与索引之间的关系,InnoDB表的意向锁,间隙锁与死锁,9,死锁与锁等待,9.2 锁,并发用户访问同一数据，锁机制可以避免数据不一致问题的发生。以场景描述2为例。,9.2.1 锁机制的必要性,1锁的粒度 锁的粒度是指锁的作用范围。 锁的粒度可以分为服务器级锁（server-level locking）和存储引擎级锁（storage-engine-level locking）。 MyISAM存储引擎支持表锁。 InnoDB存储引擎支持表锁以及行级锁。,9.2.2 MySQL锁机制的基础知识,2隐式锁与显式锁 MySQL锁分为隐式锁以及显式锁。 M
11、ySQL自动加锁称为隐式锁。 数据库开发人员手动加锁称为显式锁。,9.2.2 MySQL锁机制的基础知识,3锁的类型 锁的类型包括读锁（read lock）和写锁（write lock），其中读锁也称为共享锁，写锁也称为排他锁或者独占锁。,9.2.2 MySQL锁机制的基础知识,读锁允许其它MySQL客户机对数据同时“读”，但不允许其它MySQL客户机对数据任何“写” 。,9.2.2 MySQL锁机制的基础知识,写锁不允许其它MySQL客户机对数据同时读，也不允许其它MySQL客户机对数据同时写 。,9.2.2 MySQL锁机制的基础知识,4锁的钥匙 多个MySQL客户机并发访问同一个数据时，
12、如果MySQL客户机A对该数据成功地施加了锁，那么只有MySQL客户机A拥有这把锁的“钥匙”，也就是说：只有MySQL客户机A能够对该锁进行解锁操作。,9.2.2 MySQL锁机制的基础知识,5锁的生命周期 锁的生命周期是指在同一个MySQL服务器连接内，对数据加锁到解锁之间的时间间隔。,9.2.2 MySQL锁机制的基础知识,任何针对MyISAM表的查询操作或者更新操作，都会隐式地施加表级锁，隐式锁的生命周期非常短暂，且不受数据库开发人员的控制。,9.2.3 MyISAM表的表级锁,有时需要延长表级锁的生命周期，MySQL为数据库开发人员提供了显示地施加表级锁以及显示地解锁的MySQL命令。
13、,9.2.3 MyISAM表的表级锁,9.2.3 MyISAM表的表级锁,注意事项： read与write选项的功能在于施加表级读锁还是表级写锁。 MySQL客户机A使用lock tables命令可以同时为多个表施加表级锁（包括读锁或者写锁），并且加锁期间，MySQL客户机A不能对“没有锁定的表”进行更新及查询操作，否则将抛出“表未被锁定”的错误信息。,9.2.3 MyISAM表的表级锁,如果需要为同一个表同时施加读锁与写锁，需要为该表起两个别名，以区分读锁与写锁。,9.2.3 MyISAM表的表级锁,read local与read选项之间的区别在于：如果MySQL客户机A使用read选项为某
14、个MyISAM表施加读锁，加锁期间，MySQL客户机A以及MySQL客户机B都不能对该表进行插入操作。如果MySQL客户机A使用read local选项为某个MyISAM表施加读锁，加锁期间，MySQL客户机B可以对该表进行插入操作，前提是新记录必须插入到表的末尾。,9.2.3 MyISAM表的表级锁,InnoDB提供了两种类型的行级锁，分别是共享锁（S）以及排他锁（X），其中共享锁也叫读锁，排他锁也叫写锁。 在查询（select）语句或者更新（insert、update以及delete）语句中，为受影响的记录施加行级锁的方法也非常简单。,9.2.4 InnoDB表的行级锁,方法1在查询（se
15、lect）语句中，为符合查询条件的记录施加共享锁，语法格式如下所示。 select * from 表 where 条件语句 lock in share mode; 方法2在查询（select）语句中，为符合查询条件的记录施加排他锁，语法格式如下所示。 select * from 表 where 条件语句 for update;,9.2.4 InnoDB表的行级锁,方法3在更新（insert、update以及delete）语句中，InnoDB存储引擎将符合更新条件的记录自动施加排他锁（隐式锁）。 即：InnoDB存储引擎自动地为更新语句影响的记录施加隐式排他锁。,9.2.4 InnoDB表的行级
16、锁,任务布置8：完成本书场景描述9：通过事务延长行级锁的生命周期的任务要求。,9.2.4 InnoDB表的行级锁,任务布置9：完成本书场景描述10的任务要求。,9.2.5 “选课系统”中的行级锁,考虑如下场景：MySQL客户机A获得了某个InnoDB表中若干条记录的行级锁，此时MySQL客户机B出于某种原因需要向该表显式地施加表级锁（使用lock tables命令即可），MySQL客户机B为了获得该表的表级锁，需要逐行检测表中的行级锁是否与表级锁兼容，而这种检测需要耗费大量的服务器资源。,9.2.6 InnoDB表的意向锁,试想：如果MySQL客户机A获得该表若干条记录的行级锁之前，MySQL客户机A直接向该表施加一个“表级锁”（这个表级锁是隐式的，也叫意向锁），MySQL客户机B仅仅需要检测自己的表级锁与该意向锁是否兼容，无需逐行检测该表是否存在行级锁，就会节省不少服务器资源。,9.2.6 InnoDB表的意向锁,MySQL提供了两种意向锁：意向共享锁（IS）和意向排它锁（IX）。,9.2.6 InnoDB表的意向
