《6 触发器与事务处理ppt课件数据库原理与应用 .pptx》由会员分享，可在线阅读，更多相关《6 触发器与事务处理ppt课件数据库原理与应用 .pptx（83页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、数据库原理与应用数据库原理与应用6.1触发器数据库原理与应用数据库原理与应用2数据库原理与应用数据库原理与应用6.1.1 6.1.1 概念概念触发器（触发器（TRIGGER）是一种）是一种特殊的存储过程特殊的存储过程。为为什么要使用触什么要使用触发发器？器？加入了新的水果供加入了新的水果供应应商，在供商，在供应应商表中添加一条商表中添加一条该该供供应应商相商相关的关的记录记录，供，供应应商的商的总总数就必数就必须须同同时时改改变变。供供应应商退出商退出时时，在供，在供应应商表中商表中删删除除该该供供应应商的商的记录记录，同，同时时也也希望能希望能删删除除该该供供应应商提供的水果商提供的水果记录
2、记录。当对表进行当对表进行INSERT、UPDATE、DELETE操作时就会激活相应操作时就会激活相应的触发器并执行。的触发器并执行。触发器经常用于加强数据的完整性约束和业务规则等。触发器经常用于加强数据的完整性约束和业务规则等。数据库原理与应用数据库原理与应用触发器的作用触发器的作用（1）安全性）安全性。对用户操作数据库的权限进行控制。比如，基于时。对用户操作数据库的权限进行控制。比如，基于时间限制用户的操作，例如不允许下班后和节假日修改数据库数据间限制用户的操作，例如不允许下班后和节假日修改数据库数据（2）审计审计。可以跟踪用可以跟踪用户对户对数据数据库库的操作，把用的操作，把用户对户对数
3、据数据库库的更的更改写入到改写入到审计审计表。表。（3）实现实现非非标标准的数据准的数据库库完整性完整性规则规则 触触发发器可以器可以对对数据数据库库相关的表相关的表进进行更新操作。行更新操作。触触发发器可以器可以产产生比生比检查约检查约束更束更为为复复杂杂的限制的限制。触发器能够回退那些破坏相关完整性的操作，取消试图进行数触发器能够回退那些破坏相关完整性的操作，取消试图进行数据更改的事务。据更改的事务。触发器可以自动计算数据值。触发器可以自动计算数据值。数据库原理与应用数据库原理与应用6.1.2 6.1.2 创建触发器创建触发器CREATE TRIGGER 触发器名触发器名 BEFORE|A
4、FTER INSERT|DELETE|UPDATE ON 表名表名 FOR EACH ROW 触发的触发的SQL语句语句数据库原理与应用数据库原理与应用【例6-1】创建触发器del_tri触发器，触发器将记录哪些用户删除了fruits表中的数据，以及删除的时间和进行的操作类型。首先创建merch_log的日志信息表，用于存储用户对表的操作。CREATE TABLE merch_log(who VARCHAR(30),oper_date DATE,oper VARCHAR(20);数据库原理与应用数据库原理与应用其次在fruits表上创建DELETE类型触发器，实现向merch_log表添加操作
5、的用户名、日期及操作类型。CREATE TRIGGER del_tri AFTER DELETE ON fruits FOR EACH ROW INSERT INTO merch_log(who,oper_date,oper)VALUES(USER(),SYSDATE(),DELETE);数据库原理与应用数据库原理与应用最后测试触发器是否正常运行，在fruits表中删除f_id为a1的记录；并查询日志信息表merch_log。DELETE FROM fruits WHERE f_id=a1;SELECT *FROM merch_log;数据库原理与应用数据库原理与应用触发器如何取得激活触发器操
6、作的旧值和新值触发器如何取得激活触发器操作的旧值和新值（1）旧值。在字段名前加上旧值。在字段名前加上“OLD.”限定词限定词（2）新值。新值。在字段名前加上在字段名前加上“NEW.”限定限定词词 INSERT触触发发器，只能使用器，只能使用NEW.列名，因列名，因为为不涉及旧不涉及旧值值行。行。DELETE触触发发器，只能使用器，只能使用OLD.列名，因列名，因为为不涉及新不涉及新值值行。行。UPDATE触触发发器，可以使用器，可以使用OLD.列名引用更新前某一行的旧列名引用更新前某一行的旧值值，使用使用NEW.列名引用更新后行的新列名引用更新后行的新值值。数据库原理与应用数据库原理与应用【例
7、6-2】本例题实现级联更新。在修改suppliers表中的s_id之后（AFTER）级联地、自动地修改fruits表中该供应商的s_id。CREATE TRIGGER tr_up AFTER UPDATE ON suppliers FOR EACH ROW UPDATE fruits SET s_id=NEW.s_id WHERE s_id=OLD.s_id;UPDATE suppliers SET s_id=110 WHERE s_id=101;SELECT*FROM fruits WHERE s_id=110;数据库原理与应用数据库原理与应用6.1.3 6.1.3 查看触发器查看触发器【例
8、6-3】查询触发器tr_up的信息。USE information_schema;SELECT*FROM triggers WHERE trigger_name=tr_up;通过数据库information_schema中的系统表triggers，查询指定触发器的定义、状态和语法信息等。数据库原理与应用数据库原理与应用6.1.4 6.1.4 删除触发器删除触发器【例6-4】删除触发器tr_up。DROP TRIGGER tr_up;DROP TRIGGER 触发器名;6.2事务数据库原理与应用数据库原理与应用13数据库原理与应用数据库原理与应用update bank set sal=sal-1
9、000 where name=张张三三;update bank set sale=sale+1000 where name=李四李四;如何在如何在银行银行表（表（bank）中中实现实现客户客户（name）张三张三给给李四李四转账转账1000元存款元存款（sal）的操作？）的操作？update bank set sal=sal-1000 where name=张张三三;update bank set sal=sal+1000 where name=李四李四;下面的下面的SQL语句执行后的结果？语句执行后的结果？张三张三账户减账户减少了少了10001000元元，但李四但李四账户账户却没有加钱却没有
10、加钱。是否有一种方法是否有一种方法使得使得一个业务对应的一个业务对应的SQL语句要么都执语句要么都执行，要么里面有一句没有执行，就全部不执行？行，要么里面有一句没有执行，就全部不执行？数据库原理与应用数据库原理与应用 事务通常包含一系列事务通常包含一系列INSERT、DELETE、UPDATE等更新操作等更新操作语句，这些更新操作是一个语句，这些更新操作是一个不可分割不可分割的逻辑工作单元。的逻辑工作单元。每个事务的处理必须要满足每个事务的处理必须要满足ACID的的4个特性，即原子性（个特性，即原子性（A）、）、一致性（一致性（C）、隔离性（）、隔离性（I）和持久性（）和持久性（D）。）。6.
11、2.1 6.2.1 事务的概述事务的概述数据库原理与应用数据库原理与应用1.1.原子性（原子性（AtomicityAtomicity）原子性意味着每个事务都必须作为一个不可分割的单元，事务原子性意味着每个事务都必须作为一个不可分割的单元，事务中包含的所有操作要么全做，要么全不做。中包含的所有操作要么全做，要么全不做。6.2.2 6.2.2 事务的事务的ACIDACID特性特性如何实现事务的原子性呢？如何实现事务的原子性呢？使用使用DBMS的事务日志文件，把那些未成功执行的事务中已执的事务日志文件，把那些未成功执行的事务中已执行的操作对数据产生的影响行的操作对数据产生的影响“抹掉抹掉”。事事务日
12、志文件日志文件记录了每个事了每个事务对数据数据库所作所作变更的更的“旧旧值”和和“新新值”，当一个事，当一个事务不能完成不能完成时，将，将这些些变更了的更了的“新新值”恢复到它的恢复到它的“旧旧值”（即抹掉了（即抹掉了该变更）。更）。数据库原理与应用数据库原理与应用 一致性是指事务在完成时，必须使所有的数据从一种一致性状一致性是指事务在完成时，必须使所有的数据从一种一致性状态变更为另外一种一致性状态，确保数据的完整性。态变更为另外一种一致性状态，确保数据的完整性。如银行转账事务：如银行转账事务：update bank set sal=sal-1000 where name=张张三三;updat
13、e bank set sal=sal+1000 where name=李四李四;转账事事务必必须保保证两个两个账户的的总钱数不数不变（这就是一种一致性的限制），就是一种一致性的限制），转账前前总数是多少，数是多少，转账后的后的总还是多少。是多少。2.2.一致性（一致性（ConsistencyConsistency）数据库原理与应用数据库原理与应用 事务的事务的隔离性可以防止多个事务隔离性可以防止多个事务并发执行并发执行时，由于它们的操时，由于它们的操作命令作命令交叉交叉执行而导致的数据不一致状态。执行而导致的数据不一致状态。发生发生过的过的事件：事件：要求：要求：一个事务的执行不能被其他事务干
14、扰。一个事务的执行不能被其他事务干扰。3.3.隔离性（隔离性（IsolationIsolation）数据库原理与应用数据库原理与应用 事务完成之后，所做的修改对数据的影响是永久的，即使出现事务完成之后，所做的修改对数据的影响是永久的，即使出现系统故障，数据仍可以恢复。系统故障，数据仍可以恢复。4.4.持久性（持久性（DurabilityDurability）InnoDB存储引擎引入了与事务处理相关的存储引擎引入了与事务处理相关的REDO（重做）日志（重做）日志和和UNDO（撤消）日志。（撤消）日志。当每条当每条SQL语句进行数据更新操作语句进行数据更新操作时，将写入时，将写入REDO日志文件，
15、日志文件，在在MySQL崩溃恢复时会重新执行崩溃恢复时会重新执行REDO日志中的记录。日志中的记录。REDO日志对应磁盘上的日志对应磁盘上的ib_logfileN文件文件 UNDO日志主要用于事务异常时的数据回滚日志主要用于事务异常时的数据回滚。磁盘上不存在单独的磁盘上不存在单独的UNDO日志文件，所有的日志文件，所有的UNDO日志均存放日志均存放在表空间对应的在表空间对应的.ibd数据文件中。数据文件中。数据库原理与应用数据库原理与应用1一个事务执行过程中，其正在访问的数据被其他事务所修一个事务执行过程中，其正在访问的数据被其他事务所修改，导致处理结果不正确，这是由于违背了事务的改，导致处理
16、结果不正确，这是由于违背了事务的 A）原子性）原子性 B）一致性）一致性 C）隔离性）隔离性 D）持久性）持久性2“一旦事务成功提交，其对数据库的更新操作将永久有效，一旦事务成功提交，其对数据库的更新操作将永久有效，即使数据库发生故障即使数据库发生故障”，这一性质是指事务的，这一性质是指事务的 A）原子性）原子性 B）一致性）一致性 C）隔离性）隔离性 D）持久性）持久性练习：练习：CD数据库原理与应用数据库原理与应用（1）自动提交事务模式自动提交事务模式。每条单独的语句都是一个事务，是每条单独的语句都是一个事务，是MySQL默认的事务管理模默认的事务管理模式。式。（2）显式事务模式显式事务模
17、式。由由用户定义事务的启动和结束。用户定义事务的启动和结束。6.2.3 MySQL6.2.3 MySQL事务控制语句事务控制语句1.1.事务模式事务模式（3）隐性事务模式隐性事务模式。在当前事务完成提交或回滚后，新事务自动启动。在当前事务完成提交或回滚后，新事务自动启动。数据库原理与应用数据库原理与应用 修改提交方式修改提交方式：SET AUTOCOMMIT=0|1;SET AUTOCOMMIT=1是默认的，为自动提交事务模式。是默认的，为自动提交事务模式。SET AUTOCOMMIT=0，设置之后的所有事务都需要通过明，设置之后的所有事务都需要通过明确的命令进行提交和回滚。确的命令进行提交和
18、回滚。数据库原理与应用数据库原理与应用START TRANSACTION;或或 BEGIN WORK;【说明】【说明】在存储过程中只能使用在存储过程中只能使用START TRANSACTION语句来开启一语句来开启一个事务，因为个事务，因为MySQL数据库分析器会自动将数据库分析器会自动将BEGIN识别为识别为BEGINEND语句。语句。2.2.开始事务开始事务数据库原理与应用数据库原理与应用COMMIT WORK AND NO CHAIN NO RELEASE;【说明】【说明】提交事务的最简单形式，只需要给出提交事务的最简单形式，只需要给出COMMIT命令命令。AND CHAIN子句会在当前
19、事务结束时，立刻启动一个新事务，子句会在当前事务结束时，立刻启动一个新事务，并且新事务与刚结束的事务有相同的隔离等级。并且新事务与刚结束的事务有相同的隔离等级。RELEASE子句在终止了当前事务后，会让服务器断开与当前子句在终止了当前事务后，会让服务器断开与当前客户端的连接。客户端的连接。NO关键字可以抑制关键字可以抑制CHAIN或或RELEASE完成。完成。3.3.提交事务提交事务数据库原理与应用数据库原理与应用ROLLBACK WORK AND NO CHAIN NO RELEASE;回滚会结束用户的事务，并撤消正在进行的所有未提交的修改回滚会结束用户的事务，并撤消正在进行的所有未提交的修
20、改（即（即BEGIN WORK或或START TRANSACTIO后的所有修改）。后的所有修改）。4.4.回滚事务回滚事务数据库原理与应用数据库原理与应用【例6-5】假设银行存在两个借记卡账户（account）李三与王五，要求这两个借记卡账户不能用于透支，即两个账户的余额（balance）不能小于0。创建存储过程tran_proc()，实现两个账户的转账业务。数据库原理与应用数据库原理与应用数据库原理与应用数据库原理与应用 创建保存点创建保存点：SAVEPOINT 保存点名称保存点名称;5.5.设置保存点设置保存点 回滚事务到保存点回滚事务到保存点：ROLLBACK WORK TO SAVEP
21、OINT 保存点名称保存点名称;数据库原理与应用数据库原理与应用【例6-6】创建save_p1_proc存储过程，仅仅撤消第二条insert语句，但提交了第一条insert语句。数据库原理与应用数据库原理与应用数据库原理与应用数据库原理与应用【例6-6】创建save_p2_proc存储过程，先撤消第二条insert语句，然后撤消了所有的insert语句。数据库原理与应用数据库原理与应用数据库原理与应用数据库原理与应用6.3并 发 控 制数据库原理与应用数据库原理与应用34数据库原理与应用数据库原理与应用事务并发执行：事务并发执行：DBMS同时执行多个事务对同一数据的操作同时执行多个事务对同一数
22、据的操作（并发操作），为此，（并发操作），为此，DBMS需要对各事务中的操作顺序进行需要对各事务中的操作顺序进行安排，以达到同时运行多个事务的目的。安排，以达到同时运行多个事务的目的。6.3.1 6.3.1 理解什么是并发控制理解什么是并发控制在单处理机系统中，事务的并发执行实际上是这些并发事务轮在单处理机系统中，事务的并发执行实际上是这些并发事务轮流交叉进行的，这种并发方式称为交叉并发方式。流交叉进行的，这种并发方式称为交叉并发方式。在多处理机系统中，每个处理机可以运行一个事务，多个处理在多处理机系统中，每个处理机可以运行一个事务，多个处理机可以同时运行多个事务，实现事务真正的并发运行，这种
23、并机可以同时运行多个事务，实现事务真正的并发运行，这种并发执行方式称为同时并发方式。发执行方式称为同时并发方式。数据库原理与应用数据库原理与应用为什么出现一票两卖为什么出现一票两卖？分析：分析：售票售票处A读车票数据票数据库余票数余票数为x;x;售票售票处B读车票数据票数据库余票数余票数为x;x;售票售票处A售出一售出一张火火车票，更新数票，更新数据据库中余票数中余票数为x-1x-1；售票售票处B售出一售出一张火火车票，更新数票，更新数据据库中余票数中余票数为x-1x-1；本本卖出出2张票，但数据票，但数据库只减了只减了1张票票。原因：原因：两个售票两个售票过程（事程（事务）交叉交叉进行，行，
24、发生了相互生了相互干干扰。并发执行的事务，可能会同时读写数据库中同一数据的情况，并发执行的事务，可能会同时读写数据库中同一数据的情况，如果不加以控制，可能会引起读写数据的冲突，对数据库的一如果不加以控制，可能会引起读写数据的冲突，对数据库的一致性会造成破坏。致性会造成破坏。数据库原理与应用数据库原理与应用事务对数据库中数据可以进行哪些操作？事务对数据库中数据可以进行哪些操作？读操作和读操作和写操作写操作读和写，哪个可能会导致数据不正确？读和写，哪个可能会导致数据不正确？读不会破坏数据，但写可能导致数据不正确。读不会破坏数据，但写可能导致数据不正确。事务并发执行可能引发的问题？事务并发执行可能引
25、发的问题？读读-读读读读-写写写写-读读 写写-写写 保持数据保持数据一致性一致性不可重复不可重复读读脏数据数据丢失更新失更新6.3.2 6.3.2 并发执行可能引起的问题并发执行可能引起的问题数据库原理与应用数据库原理与应用1.1.丢失更新丢失更新又称为又称为覆盖未提交的数据覆盖未提交的数据。原因原因：由于两个由于两个(或多个或多个)事务对同一数据并发地写入引起，事务对同一数据并发地写入引起，称为称为写写写写冲突。冲突。结果结果：与串行地执行两个：与串行地执行两个(或多个或多个)事务的结果不一致。事务的结果不一致。数据库原理与应用数据库原理与应用2 2、不可重复读不可重复读 又称为又称为读值
26、不可复现读值不可复现。原因：原因：该问题因该问题因读读写写冲突引起。冲突引起。结果：结果：第二次读的值与前次读的值不同。第二次读的值与前次读的值不同。数据库原理与应用数据库原理与应用 幻影读（幻影读（phantom redphantom red）也属于不可重复读的问题。）也属于不可重复读的问题。幻影读幻影读 与不可重复的区别是：不可重复读的操作对象是数据，而与不可重复的区别是：不可重复读的操作对象是数据，而幻影读的操作对象是幻影读的操作对象是表中的记录表中的记录。数据库原理与应用数据库原理与应用3 3、读脏数据读脏数据又称为又称为读未提交的数据读未提交的数据 。原因：原因：由于后一事务读了前一
27、个事务写了但尚未提交的数据由于后一事务读了前一个事务写了但尚未提交的数据引起，称为引起，称为写写读读冲突。冲突。结果：结果：读到有可能要回退的更新数据。读到有可能要回退的更新数据。数据库原理与应用数据库原理与应用事务并发操作引发问题的解决方法事务并发操作引发问题的解决方法方法一方法一：设置事务隔离级别：设置事务隔离级别 方法二方法二：封锁：封锁6.3.3 6.3.3 事务隔离级别事务隔离级别隔离隔离级别级别定定义义了一个事了一个事务务与其他事与其他事务务的隔离程度。的隔离程度。并发事务发生的并发事务发生的4种异常情况种异常情况 丢失更新丢失更新 读脏数据读脏数据 不可重复读不可重复读 幻影读。
28、幻影读。数据库原理与应用数据库原理与应用（1 1）read uncommittedread uncommitted（未提交读）（未提交读）用户可以对数据执行未提交读；在事务结束前可以更改用户可以对数据执行未提交读；在事务结束前可以更改数据集内的数值，行也可以出现在数据集中或从数据集数据集内的数值，行也可以出现在数据集中或从数据集消失。它是消失。它是4 4个级别中限制个级别中限制最小最小的的级别级别。（2 2）read committedread committed（提交读）（提交读）此隔离级别不允许用户读一些未提交的数据，因此此隔离级别不允许用户读一些未提交的数据，因此不会不会出现读脏数据出现
29、读脏数据的情况，但数据可以在事务结束前被修改，的情况，但数据可以在事务结束前被修改，从而从而产生不可重复读或幻影数据产生不可重复读或幻影数据。数据库原理与应用数据库原理与应用（3 3）repeatable readrepeatable read（重复读）（重复读）此隔离级别保证在一个事务中重复读到的数据会保持同样此隔离级别保证在一个事务中重复读到的数据会保持同样的值，而的值，而不会出现读脏数据、不可重复读不会出现读脏数据、不可重复读的问题。但允许的问题。但允许其他用户将新的其他用户将新的幻影幻影行插入数据集，且幻影行包括在当前行插入数据集，且幻影行包括在当前事务的后续读取中。事务的后续读取中。
30、（4 4）serializableserializable（串行读）（串行读）此隔离级别是此隔离级别是4 4种隔离级别中限制种隔离级别中限制最大最大的的级别级别，称为可，称为可串行读，不允许其它用户在事务完成之前更新数据集或串行读，不允许其它用户在事务完成之前更新数据集或将行插入数据集内。将行插入数据集内。数据库原理与应用数据库原理与应用事事务务的的4种隔离种隔离级别级别隔离级别隔离级别丢失更新丢失更新 读脏数据读脏数据 不可重复读不可重复读 幻影读幻影读未提交读未提交读（read uncommitted）是是是是是是是是提交读提交读（read committed）否否否否是是是是可重复读可重
31、复读（repeatable read）否否否否否否是是可串行读可串行读（serializable）否否否否否否否否数据库原理与应用数据库原理与应用1 1MySQLMySQL隔离级别的设置隔离级别的设置6.3.4 MySQL6.3.4 MySQL事务隔离级别设置事务隔离级别设置SET GLOBAL|SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE|REPEATABLE READ|READ COMMITED|READ UNCOMMITED;GLOBAL，定，定义义的隔离的隔离级别级别适用于所有的适用于所有的SQL用用户户。SESSION，定，定义义的
32、隔离的隔离级别级别只适用于当前运行的会只适用于当前运行的会话话和和连连接。接。MySQL默默认认的事的事务务隔离隔离级别级别是是REPEATABLE READ。系系统变统变量量TRANSACTION_ISOLATION存存储储了事了事务务的隔离的隔离级别级别。数据库原理与应用数据库原理与应用2.READ UNCOMMITED2.READ UNCOMMITED隔离级别隔离级别 所有事所有事务务都可以看到其他未提交事都可以看到其他未提交事务务的的执执行行结结果。果。该该隔离隔离级别级别很很少用于少用于实际应实际应用用。【例6-7】脏读现象示例。（1）打开MySQL客户机AUSE test;SET
33、SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;SELECT transaction_isolation;START TRANSACTION;SELECT*FROM account;数据库原理与应用数据库原理与应用（2）打开MySQL客户机BUSE test;SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;START TRANSACTION;UPDATE account SET balance=balance+1000 WHERE account_no=1;#未提交事务（
34、3）打开MySQL客户机ASELECT*FROM account;（4）关闭MySQL客户机A与MySQL客户机B，由于两个客户机的事务都没有提交，所以，account表中的数据没有变化，李三账户的余额仍然是200。数据库原理与应用数据库原理与应用3.READ COMMITED3.READ COMMITED隔离级别隔离级别 一个事一个事务务只能看只能看见见已提交事已提交事务务所做的改所做的改变变。避免。避免脏读现脏读现象象，但可，但可能出现不可重复读和幻影读。能出现不可重复读和幻影读。【例6-8】不可重复读现象示例。（1）打开MySQL客户机AUSE test;SET SESSION TRAN
35、SACTION ISOLATION LEVEL READ COMMITTED;SELECT transaction_isolation;START TRANSACTION;SELECT*FROM account;数据库原理与应用数据库原理与应用（2）打开MySQL客户机BSET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;START TRANSACTION;UPDATE account SET balance=balance+1000 WHERE account_no=1;COMMIT;（3）打开MySQL客户机ASELECT*FROM
36、 account;MySQL客户机A在同一个事务中两次执行“SELECT*FROM account;”的结果不相同，造成不可重复读现象。数据库原理与应用数据库原理与应用4.REPEATABLE READ4.REPEATABLE READ隔离级别隔离级别 是是MySQL的默的默认认事事务务隔离隔离级别级别，它确保在同一事，它确保在同一事务务内相同的内相同的查询查询语语句的句的执执行行结结果一致。避免果一致。避免脏读脏读及不可重复及不可重复读读的的现现象，但可能出象，但可能出现现幻影幻影读现读现象。象。【例6-9】幻影读现象示例。（1）打开MySQL客户机AUSE test;SET SESSION
37、 TRANSACTION ISOLATION LEVEL REPEATABLE READ;SELECT transaction_isolation;START TRANSACTION;SELECT*FROM account;数据库原理与应用数据库原理与应用（2）打开MySQL客户机BSET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;START TRANSACTION;INSERT INTO account VALUES(10,赵六,3000);COMMIT;SELECT*FROM account;（3）打开MySQL客户机ASELE
38、CT*FROM account;查询结果显示account表中不存在account_no=10的账户信息。数据库原理与应用数据库原理与应用（4）由于MySQL客户机A检测到account表中不存在account_no=10的账户信息，在MySQL客户机A继续执行下面INSERT语句。INSERT INTO account VALUES(10,赵六,3000);运行结果显示account表中确实存在account_no=10的账户信息，但由于REPEATABLE READ（可重复读）隔离级别使用了“障眼法”，使得MySQL客户机A无法查询到account_no=10的账户信息，这种现象称为幻影读
39、现象。数据库原理与应用数据库原理与应用5.SERIALIZABLE5.SERIALIZABLE隔离级别隔离级别 是最高的隔离是最高的隔离级别级别，它通，它通过过强强制事制事务务排序，使之不可能相互冲突。排序，使之不可能相互冲突。【例6-10】避免幻影读现象示例。（1）打开MySQL客户机AUSE test;SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;SELECT transaction_isolation;START TRANSACTION;SELECT*FROM account;数据库原理与应用数据库原理与应用（2）打开MySQL
40、客户机BSET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;START TRANSACTION;INSERT INTO account VALUES(20,马七,5000);SELECT*FROM account;由于发生了锁等待超时引发的错误异常，事务被回滚，所以account_no=20的账户信息并没有添加到account表中。数据库原理与应用数据库原理与应用 对于大部分应用来说，READ COMMITTED是最合适的隔离级别。如果所处的数据库中具有大量的并发事务，并且对事务的处理和响应速度要求较高，则使用READ COMMITTED隔
41、离级别比较合适。如果所连接的数据库用户比较少，多个事务并发地访问同一资源的概率比较小，并且用户的事务可能会执行很长一段时间，在这种情况下使用REPEATABLE READ或SERIALIZABLE隔离级别较合适6.4封锁机制数据库原理与应用数据库原理与应用57数据库原理与应用数据库原理与应用一个锁实质上就是允许（或阻止）一个事务对一个数据对象一个锁实质上就是允许（或阻止）一个事务对一个数据对象的存取特权。的存取特权。一个事一个事务对务对一个一个对对象加象加锁锁的的结结果是将其它事果是将其它事务务“封封锁锁”在在该对该对象之外，特象之外，特别别是防止了其他事是防止了其他事务对该对务对该对象的更改
42、，而加象的更改，而加锁锁的的事事务则务则可以可以执执行它所希望的行它所希望的处处理并理并维维持持该对该对象的正确状象的正确状态态。6.4.1 6.4.1 锁锁数据库原理与应用数据库原理与应用（1）排它排它锁（锁（X X锁、写锁）锁、写锁）事务事务更新更新数据前必须先加上数据前必须先加上X锁；锁；数据对象加上数据对象加上X锁，其它事务对该对象即锁，其它事务对该对象即不能加不能加S锁也不能加锁也不能加X锁锁事务对数据加事务对数据加X锁后，对锁定数据即能读取也能修改。锁后，对锁定数据即能读取也能修改。（2）共享共享锁（锁（S S锁、锁、读读锁）锁）事务事务读取读取数据前必须先加上数据前必须先加上S锁
43、；锁；数据对象加上数据对象加上S锁后，其它事务锁后，其它事务只能只能对该对象对该对象加加S锁锁不能加不能加X锁锁事务对数据加事务对数据加S锁后，对锁定数据只能读取。锁后，对锁定数据只能读取。1 1锁的类型锁的类型（3）意向意向锁锁意向锁分为意向共享锁（意向锁分为意向共享锁（IS）和意向排他锁（）和意向排他锁（IX）两类。）两类。意向锁表示一个事务有意向在某些数据上加共享锁或者排他锁。意向锁表示一个事务有意向在某些数据上加共享锁或者排他锁。数据库原理与应用数据库原理与应用2 2锁的相容矩阵锁的相容矩阵数据库原理与应用数据库原理与应用封封锁对锁对象的大小称象的大小称为为封封锁锁粒度粒度。封封锁锁的
44、的对对象可以是字段、象可以是字段、记录记录、表等、表等逻辑单逻辑单元；也可以是元；也可以是页页（数据（数据页页或索引或索引页页）、）、块块等物理等物理单单元。元。3 3锁的粒度锁的粒度封锁粒度越小，系统中能够被封锁的对象就越多，但封锁机构复封锁粒度越小，系统中能够被封锁的对象就越多，但封锁机构复杂，系统开销也就越大。杂，系统开销也就越大。封锁粒度越大，系统中能够被封锁的对象就越少，并发度越小，封锁粒度越大，系统中能够被封锁的对象就越少，并发度越小，封锁机构简单，相应系统开销也就越小。封锁机构简单，相应系统开销也就越小。实际应用中，选择封锁粒度应同时考虑封锁实际应用中，选择封锁粒度应同时考虑封锁
45、开销开销和并发度两个因和并发度两个因素，对系统开销与并发度进行权衡，以求得最优的效果。素，对系统开销与并发度进行权衡，以求得最优的效果。需要处理大量元组的用户事务可以以关系为封锁单元需要处理大量元组的用户事务可以以关系为封锁单元；对于一个；对于一个处理少量元组的用户事务，可以以元组为封锁单位处理少量元组的用户事务，可以以元组为封锁单位数据库原理与应用数据库原理与应用 并发操作带来的问题？并发操作带来的问题？丢失更新丢失更新 读读“脏脏”数据数据 不可重复读不可重复读 封锁协议封锁协议一级封一级封锁协议锁协议二级封二级封锁协议锁协议三级封三级封锁协议锁协议封锁协议封锁协议：在运用：在运用X锁和锁
46、和S锁对数据对象锁对数据对象加锁加锁时，还需要约定一时，还需要约定一些规则，如：何时申请些规则，如：何时申请X锁或锁或S锁、持锁时间、何时释放等，这锁、持锁时间、何时释放等，这些些规则规则称为封锁协议。称为封锁协议。6.4.2 6.4.2 封锁协议封锁协议数据库原理与应用数据库原理与应用事务事务T1事务事务T2R(A)：5W(A)：6AR(A)：7？W(A)：7A事务事务T1事务事务T2Xlock(A)R(A)：5W(A)：6AR(A)：6CommitUnlock(A)Xlock(A)等待等待等待等待等待等待等待等待Xlock(A)R(A)W(A)：7A写写-写写操作导致操作导致“丢失更新丢失
47、更新”问题问题如何加锁？如何加锁？写操作前加写操作前加X锁。锁。1 1一级封锁协议一级封锁协议数据库原理与应用数据库原理与应用不同级别的封锁协议和一致性保证不同级别的封锁协议和一致性保证封封锁协议X锁S锁一致性保一致性保证事务结束释放操作结束释放事务结束释放防止丢失更新防止读“脏”数据防止不可重复读一级封锁协议一级封锁协议 二级封锁协议三级封锁协议数据库原理与应用数据库原理与应用事务事务T1事务事务T2R(A)：5W(A)：6AROLLBACKA的值恢复为的值恢复为5R(A)：6读的读的6为脏数据为脏数据写写-读读操作导致操作导致“读脏数据读脏数据”问题问题如何加锁？如何加锁？写操作前加写操作
48、前加X锁锁读操作前加读操作前加S锁锁数据对象加了数据对象加了X X锁，锁，还能再加还能再加S S锁锁？不能不能事务事务T1事务事务T2Xlock(A)R(A)：5W(A)：6AROLLBACKUnlock(A)Slock(A)等待等待等待等待Slock(A)R(A)：5commitUlock(A)2 2二级封锁协议二级封锁协议数据库原理与应用数据库原理与应用不同级别的封锁协议和一致性保证不同级别的封锁协议和一致性保证封封锁协议X锁S锁一致性保一致性保证事务结束释放操作结束释放事务结束释放防止丢失更新防止读“脏”数据防止不可重复读一级封锁协议 二级封锁协二级封锁协议三级封锁协议数据库原理与应用数
49、据库原理与应用事务事务T1事务事务T2Xlock(A)R(A)：5W(A)：6AROLLBACKUnlock(A)Xlock(A)W(A):7AUlock(A)Slock(A)等待等待等待等待Slock(A)R(A)：5commitUlock(A)Slock(A)R(A):7 (与前面读到的与前面读到的5不同，不同，发生不可重复读发生不可重复读)原因：S锁操作结束即被释放数据库原理与应用数据库原理与应用事务事务T1事务事务T2R(A)：5R(A)：6？W(A)：6A读读-写写操作导致操作导致“不可重复读不可重复读”问题问题如何加锁？如何加锁？写操作前加写操作前加X锁锁读操作前加读操作前加S锁锁
50、不能不能数据对象加了数据对象加了S S锁，锁，还能再加还能再加X X锁锁？事务事务T1事务事务T2Slock(A)R(A)：5R(A)：5CommitUnlock(A)Xlock(A)等待等待等待等待等待等待Xlock(A)W(A)：6ACommitUnlock(A)3 3三级封锁协议三级封锁协议数据库原理与应用数据库原理与应用不同级别的封锁协议和一致性保证不同级别的封锁协议和一致性保证封封锁协议X锁S锁一致性保一致性保证事务结束释放操作结束释放事务结束释放防止丢失更新防止读“脏”数据防止不可重复读一级封锁协议 二级封锁协议三级封锁协议三级封锁协议数据库原理与应用数据库原理与应用出现这种出现这