第15章 事务与并发控制75086.ppt

1、第15章 事务与并发控制,本章导学,事务的概念 ACID属性 创建事务 启动事务 提交事务 回滚事务 并发控制 SQL Server中的锁,select * from 银行账户表update 银行账号表set 存款余额=存款余额-80000 where 账号=杨百万update 银行账号表set 存款余额=存款余额+80000 where 账号=邱发财select * from 银行账户表,事务的概念,事务的概念,一次成功的网上交易需要系统提供多方支持，数据库相关的操作构成一个事务。1. 买家所购商品库存减少2. 卖家交易记录增加3. 买家交易记录增加4. 付款所涉及买方、卖方和相关金融机构系

2、统数据更新5. 物流信息处理,事务的概念,事务是一个由Transact-SQL组成的作为单个逻辑工作单元执行的一系列操作 。 这个单元块中的语句是一个整体，不能分割，要么都执行要么都不执行，绝不能只完成部分操作，而还有一些操作没有完成。 事务中任何一个语句执行时出错，系统都会返回到事务开始前的状态。,ACID属性,事务必须满足以下四个属性 原子性（Atomicity） 一致性（Consistency） 隔离性（Isolation) 持久性（Durability）,ACID属性,原子性（Atomicity） 事务必须是原子工作单元，对于数据的操作，要么全都执行，要么全都不执行。 例 子 将一名学

3、生从1班转到2班，需要执行以下操作： 将该学生信息从1班删除 1班人数减1 将该学生信息添加到2班 2班人数加1,ACID属性,一致性（Consistency） 完成事务时，必须保证所有数据都的一致性。 例 子 将一名学生从1班转到2班，定义如下事务： 将该学生信息从1班删除 1班人数减1 将该学生信息添加到2班,ACID属性,隔离性（Isolation) 由并发事务所做的数据修改必须与任何其它并发所做的同一数据的修改隔离。 事务查看的数据所处状态，要么是一个并发事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看中间状态的数据。 例如 事务1：教师修改学生的成绩 事务2：学生查

4、看自己的成绩 事务2执行查看的是事务1执行前的或执行后的状态。,ACID属性,持久性（Durability） 事务对于数据库系统的影响是永久性的 事务对数据所做的的修改是永久的，即使该修改会导致致命的系统故障。,创建事务-基本语法,启动事务 BEGIN TRAN | TRANSACTION transaction_name | tran_name_variable WITH MARK description 提交事务 COMMIT TRAN | TRANSACTION transaction_name | tran_name_variable ,创建事务-基本语法,回滚事务ROLLBACK T

5、RAN | TRANSACTION transaction_name | tran_name_variable|savepoint_name | savepoint_variable 注 意 回滚某事务必须在它的提交语句之前才有效 ROLLBACK TRAN语句回滚到最外层事务的起点,创建事务-基本语法,设置保存点SAVE TRAN | TRANSACTION savepoint_name | savepoint_variable ,创建事务-基本语法,常用系统变量 TRANCOUNT BEGIN TRAN：TRANCOUNT加1 ROLLBACK TRAN | WORK：TRANCOUNT还

6、原为0 ROLLBACK TRAN savepoint_name：不影响TRANCOUNT COMMIT TRAN | WORK：TRANCOUNT减1,创建事务-实例分析,任务一：提交事务 提出问题 学号为“S036”的学生艾家羽完成了编号为“C010”的日语课程的学习，考试取得成绩为88。因此，该生已修总学分也相应增加。编写SQL语句，完成该生的信息修改，并防止在数据修改过程中发生意外状况导致数据不一致。,创建事务-实例分析,任务一：提交事务 分析问题 编写UPDATE语句更新Register表中的成绩。 编写UPDATE语句更新该生在Student表中的学分信息。 将以上两个操作纳入一个

7、事务中，作为一个逻辑单元来执行。,创建事务-实例分析,任务一：提交事务 问题求解 BEGIN TRANSACTION StuCreditTran -开始事务UPDATE Register SET score=88WHERE stuNo=S036 AND classNo=C010DECLARE credit intSELECT credit=classCredit FROM Classes WHERE classNo=C010UPDATE StudentSET stuTotalCredit=stuTotalCredit+creditWHERE stuNo=S036 COMMIT TRANSACT

8、ION StuCreditTran -提交事务,创建事务-实例分析,任务二：回滚事务 提出问题 编号为C005的课程操作系统已接近选课上限，但还有少数空缺，在接下来的选课过程中又有5位同学选择了该课程，所以必须判断是否超过该课程能容纳的最大学生数，如果已经超过，则选课无效，如果并未超过，则选课是有效的 。,创建事务-实例分析,任务二：回滚事务 分析问题 编写INSERT语句新增学生选课信息。 更新操作系统课程的已选课人数。 判断已选课人数是否已经超过上限，如果超过则撤销所有操作，如果并未超过则提交已完成的修改操作。,创建事务-实例分析,任务二：回滚事务 问题求解 BEGIN TRANSACTI

9、ON StuClassTranINSERT INTO Register VALUES(S009,C005,NULL)INSERT INTO Register VALUES(S016,C005,NULL)INSERT INTO Register VALUES(S022,C005,NULL)INSERT INTO Register VALUES(S028,C005,NULL)INSERT INTO Register VALUES(S029,C005,NULL)UPDATE ClassesSET classExistNum=classExistNum+5WHERE classNo=C005,创建事务

10、-实例分析,任务二：回滚事务 问题求解IF (SELECT classLimitNum-classExistNum FROM ClassesWHERE classNo=C005)0BEGINROLLBACK TRANSACTION StuClassTranPRINT 选课人数超过上限,事务已回滚ENDELSEBEGIN COMMIT TRANSACTION StuClassTranPRINT 选课人数未超过上限,事务已提交END,创建事务-实例分析,任务三：回滚部分事务 提出问题 为了让BigCollege数据库系统更完善，管理员希望在任务二中能添加部分学生的选课记录，直到达到选课人数上限，之

11、后所有的选课记录将被拒绝加入。,创建事务-实例分析,任务三：回滚部分事务 分析问题 为达到任务所提出的目的，需要在事务中设置保存点。 在实际操作中，因为无法事先预知某课程可容纳上限人数和现有人数的空缺，所以可能成功插入的选课记录数可能是15中的任何一个数值，所以为每条记录设置保存点，根据课程的空缺人数来判断事务是否提交，或是回滚到某一保存点。,创建事务-实例分析,任务三：回滚部分事务 问题求解,思考,假定学号为S001的学生已修学分为150，现在她又选择了建筑艺术和项目管理两门课程，请编写事务，完成该生的选课，并判断学分是否超过其专业要求学分，若超过则撤销选课。,并发控制,概念 并发控制指的是

12、当多个用户同时对数据库中相同数据进行更改时，系统实现的一种控制机制，让一个用户所作的更改不会影响到其他用户的更改操作，避免数据出现混乱。,并发控制,类型 悲观并发控制 在事务执行过程中锁定其所占用的资源，阻止其他事务同时访问这些资源。 乐观并发控制 乐观并发控制假定多个用户之间不存在争用资源的情况，事务在执行时也就不对资源进行锁定。,并发控制,并发性问题 丢失更新 当两个或多个事务同时更新某一行数据时，彼此都不知道有另一个事务的存在，这样必然会有更新后的数据被丢失。,并发控制,并发性问题 脏读 当一个事务正在进行更新操作，数据可能出现一些中间状态，这时其他事务读取了该数据，并分发给其他用户，那

13、么就发生了脏读。,并发控制,并发性问题不可重复读 当一个事务在更新表中某一行时，另一个事务多次读取该行，会发生数据不一致的情况。,并发控制,并发性问题 幻读 当一个事务对表进行插入或删除行操作时，另一个事务在读取该表，这时就会发生事务第一次读取的数据中的某行已经不存在源数据表中，或者源数据表中的某行不存在于读取的数据表中的情况，这就发生了幻读现象。,锁机制锁作为一种安全机制，用以封锁正被一个事务修改的数据，防止其他用户访问到“不一致”的数据。 锁机制能够控制多个用户的并发操作，可以防止用户读取正在由其他用户更改的数据或者多个用户同时修改同一数据，从而确保事务完整性和数据库一致性。,SQL Se

14、rver中的锁,SQL Server中的锁,锁粒度 SQL SERVER实现了多粒度上锁，支持事务以不同级别对不同资源上锁 锁粒度与并发性的矛盾,SQL Server中的锁,锁模式 共享（Shared）锁 同一资源可以同时被多个共享锁锁定 被共享锁锁定的资源能被其它事务读取，但不能被修改 通常在读取资源时使用共享锁,SQL Server中的锁,锁模式 排他（Exclusive）锁 同一资源只能同时被一个排他锁锁定 被排他锁锁定的资源不能被其他事务读取和修改 通常在修改资源时使用排他锁,SQL Server中的锁,锁模式 更新（Update）锁 更新锁是为了避免出现以下死锁： 当多个事务拥有同一

15、资源的共享锁，并且都意图获得该资源的排他锁，从而导致的死锁 同一资源只能同时被一个更新锁锁定,SQL Server中的锁,锁模式 意向（Extent）锁 指示SQL Server准备要在层次结构较低的某个资源上获得一个共享或排他锁 意向锁包括意向共享、意向排他和意向排他共享锁 架构（Scheme）锁 在执行表的数据定义语言操作时，对资源使用架构修改锁 当编译查询时，对资源使用架构稳定性锁,死锁,在两个或多个事务中，如果每个事务锁定了其他事务试图锁定的资源，此时会造成这些事务的永久阻塞，从而出现死锁。 一般来说，对数据库的修改由一个事务组成，此事务读取记录，获取资源的共享锁，然后修改记录行，最后

16、将锁转换为排它锁。但是采用这种方法时，如果两个事务获得了资源上的共享锁，然后试图同时更新数据，并均要将锁转换为排它锁，那么此时两个事务都会等待对方释放共享锁，从而形成死锁。,本章小结,事务是一个由Transact-SQL组成的作为单个逻辑工作单元执行的一系列操作，也就是说，事务中的操作要么全部执行，要么全部不执行。 一个事务必须具备原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）四个属性。 BEGIN TRANSACTION标记事务的开始。 若事务执行成功执行COMMIT TRANSACTION提交事务。 若事务执行失

17、败执行ROLLBACK TRANSACTION回滚事务。 并发控制指的是当多个用户同时对数据库中相同数据进行更改时，系统实现的一种控制机制，让一个用户所作的更改不会影响到其他用户的更改操作，避免数据出现混乱。,本章小结,并发控制根据其理论依据被分为悲观并发控制和乐观并发控制。 如果在关系数据库管理系统中并未进行并发控制，则将导致更新丢失、脏读、不可重复读和幻读的问题。 SQL SERVER实现了多粒度上锁，支持事务以不同级别对不同资源上锁。 SQL Server支持共享锁、更新锁、排他锁、意向锁和架构锁等模式，解决并发事务间的资源冲突。 在两个或多个事务中，如果每个事务锁定了其他事务试图锁定的资源，此时会造成这些事务的永久阻塞，从而出现死锁。,