数据库原理与MySQL应用-6 触发器与事务处理

第6章 触发器与事务处理6.1触发器数据库原理与应用26.1.1 概念触发器（TRIGGER）是一种特殊的存储过程。 为什么要使用触发器？
① 加入了新的水果供应商，在供应商表中添加一条该供应商相关的记录，供应商的总数就必须同时改变。 ② 供应商退出时，在供应
商表中删除该供应商的记录，同时也希望能删除该供应商提供的水果记录。当对表进行INSERT、UPDATE、DELETE操作时就会激活
相应的触发器并执行。触发器经常用于加强数据的完整性约束和业务规则等。触发器的作用（1）安全性。对用户操作数据库的权限进行控制。比如
，基于时间限制用户的操作，例如不允许下班后和节假日修改数据库数据（2）审计。可以跟踪用户对数据库的操作，把用户对数据库的更改写入到
审计表。（3）实现非标准的数据库完整性规则 触发器可以对数据库相关的表进行更新操作。 触发器可以产生比检查
约束更为复杂的限制。 触发器能够回退那些破坏相关完整性的操作，取消试图进行数据更改的事务。 触发器可以自动
计算数据值。6.1.2 创建触发器CREATE TRIGGER 触发器名 BEFORE | AFTER INSERT
| DELETE | UPDATE ON 表名 FOR EACH ROW 触发的SQL语句【例6-1】创建触发
器del_tri触发器，触发器将记录哪些用户删除了fruits表中的数据，以及删除的时间和进行的操作类型。首先创建merch_lo
g的日志信息表，用于存储用户对表的操作。CREATE TABLE merch_log( who VARCH
AR(30), oper_date DATE, oper VARCHAR(20));其次在fruits表上创建D
ELETE类型触发器，实现向merch_log表添加操作的用户名、日期及操作类型。CREATE TRIGGER del_tr
i AFTER DELETE ON fruits FOR EACH ROW INSERT INTO merch_l
og(who,oper_date,oper) VALUES(USER(),SYSDATE(),''DELETE'');最后测试触
发器是否正常运行，在fruits表中删除f_id为a1的记录；并查询日志信息表merch_log。DELETE FROM frui
ts WHERE f_id=''a1'';SELECT FROM merch_log;触发器如何取得激活触发器操作的旧值
和新值（1）旧值。在字段名前加上“OLD.”限定词（2）新值。在字段名前加上“NEW.”限定词 INSERT触发器，只能使用N
EW.列名，因为不涉及旧值行。 DELETE触发器，只能使用OLD.列名，因为不涉及新值行。 UPDATE触发器，可以使用
OLD.列名引用更新前某一行的旧值，使用NEW.列名引用更新后行的新值。【例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-3】查询触发器tr_up的信息。USE information_schema;SELECT FROM trigge
rs WHERE trigger_name=''tr_up''; 通过数据库information_schema中的系统表trigg
ers，查询指定触发器的定义、状态和语法信息等。6.1.4 删除触发器【例6-4】删除触发器tr_up。DROP TRIGGER
tr_up; DROP TRIGGER 触发器名;6.2事务数据库原理与应用13update bank set sal = sa
l - 1000 where name = ''张三'';update bank set sale = sale + 1000 whe
re name = ''李四'';如何在银行表（bank）中实现客户（name）张三给李四转账1000元存款（sal）的操作？upda
te bank set sal = sal - 1000 where name = ''张三'';update bank set sa
l = sal +1000 where name = ''李四'';下面的SQL语句执行后的结果？张三账户减少了1000元，但李四账户
却没有加钱。是否有一种方法使得一个业务对应的SQL语句要么都执行，要么里面有一句没有执行，就全部不执行？ 事务通常包含一系
列INSERT、DELETE、UPDATE等更新操作语句，这些更新操作是一个不可分割的逻辑工作单元。 每个事务的处理必须要
满足ACID的4个特性，即原子性（A）、一致性（C）、隔离性（I）和持久性（D）。6.2.1 事务的概述1. 原子性（Atomi
city） 原子性意味着每个事务都必须作为一个不可分割的单元，事务中包含的所有操作要么全做，要么全不做。6.2.2 事务的A
CID特性如何实现事务的原子性呢？ 使用DBMS的事务日志文件，把那些未成功执行的事务中已执行的操作对数据产生的影响“抹掉
”。 事务日志文件记录了每个事务对数据库所作变更的“旧值”和“新值”，当一个事务不能完成时，将这些变更了的“新值”恢复到它的“
旧值”（即抹掉了该变更）。 一致性是指事务在完成时，必须使所有的数据从一种一致性状态变更为另外一种一致性状态，确保数据的完整性
。如银行转账事务： update bank set sal = sal - 1000 where name = ''张三'';
update bank set sal = sal +1000 where name = ''李四''; 转账事务必须保证
两个账户的总钱数不变（这就是一种一致性的限制），转账前总数是多少，转账后的总还是多少。2. 一致性（Consistency）
事务的隔离性可以防止多个事务并发执行时，由于它们的操作命令交叉执行而导致的数据不一致状态。 发生过的事件： 要求：一个事务的
执行不能被其他事务干扰。3. 隔离性（Isolation） 事务完成之后，所做的修改对数据的影响是永久的，即使出现系统故障，数
据仍可以恢复。4. 持久性（Durability） InnoDB存储引擎引入了与事务处理相关的REDO（重做）日志和UNDO（
撤消）日志。 当每条SQL语句进行数据更新操作时，将写入REDO日志文件，在MySQL崩溃恢复时会重新执行REDO日志中的记录
。 REDO日志对应磁盘上的ib_logfileN文件 UNDO日志主要用于事务异常时的数据回滚。 磁盘上不存在单独
的UNDO日志文件，所有的UNDO日志均存放在表空间对应的.ibd数据文件中。1．一个事务执行过程中，其正在访问的数据被其他事务所
修改，导致处理结果不正确，这是由于违背了事务的 A）原子性 B）一致性 C）隔离性
D）持久性2．“一旦事务成功提交，其对数据库的更新操作将永久有效，即使数据库发生故障”，这一性质是指事务的 A）原子性
B）一致性 C）隔离性 D）持久性练习：CD（1）自动提交事务模式。 每条单独
的语句都是一个事务，是MySQL默认的事务管理模式。 （2）显式事务模式。 由用户定义事务的启动和结束。6.2.
3 MySQL事务控制语句1. 事务模式 （3）隐性事务模式。 在当前事务完成提交或回滚后，新事务自动启动。 修
改提交方式：SET AUTOCOMMIT = 0|1;① SET AUTOCOMMIT=1是默认的，为自动提交事务模式。② SE
T AUTOCOMMIT=0，设置之后的所有事务都需要通过明确的命令进行提交和回滚。START TRANSACTION;
或 BEGIN WO
RK;【说明】 在存储过程中只能使用START TRANSACTION语句来开启一个事务，因为MySQL数据库分析器会自动
将BEGIN识别为BEGIN…END语句。2. 开始事务COMMIT [WORK] [AND [NO] CHAIN] [
[NO] RELEASE];【说明】① 提交事务的最简单形式，只需要给出COMMIT命令。② AND CHAIN子句会在当前事务
结束时，立刻启动一个新事务，并且新事务与刚结束的事务有相同的隔离等级。③ RELEASE子句在终止了当前事务后，会让服务器断开与当
前客户端的连接。④ NO关键字可以抑制CHAIN或RELEASE完成。3. 提交事务ROLLBACK [WORK] [AND
[NO] CHAIN] [[NO] RELEASE]; 回滚会结束用户的事务，并撤消正在进行的所有未提交的修改（即BEG
IN WORK或START TRANSACTIO后的所有修改）。4. 回滚事务【例6-5】假设银行存在两个借记卡账户（accoun
t）''李三''与''王五''，要求这两个借记卡账户不能用于透支，即两个账户的余额（balance）不能小于0。创建存储过程tran_pr
oc()，实现两个账户的转账业务。 创建保存点：SAVEPOINT 保存点名称;5. 设置保存点 回滚事
务到保存点：ROLLBACK [WORK] TO SAVEPOINT 保存点名称;【例6-6】创建save_p1_proc
存储过程，仅仅撤消第二条insert语句，但提交了第一条insert语句。【例6-6】创建save_p2_proc存储过程，先撤消
第二条insert语句，然后撤消了所有的insert语句。6.3并 发 控 制数据库原理与应用34事务并发执行：DBMS同时执行多
个事务对同一数据的操作（并发操作），为此，DBMS需要对各事务中的操作顺序进行安排，以达到同时运行多个事务的目的。6.3.1 理
解什么是并发控制在单处理机系统中，事务的并发执行实际上是这些并发事务轮流交叉进行的，这种并发方式称为交叉并发方式。在多处理机系统中
，每个处理机可以运行一个事务，多个处理机可以同时运行多个事务，实现事务真正的并发运行，这种并发执行方式称为同时并发方式。为什么出现
一票两卖？分析： 售票处A读车票数据库余票数为x; 售票处B读车票数据库余票数为x; 售票处A售出一张火车票，更新数据库中余
票数为x-1； 售票处B售出一张火车票，更新数据库中余票数为x-1； 本卖出2张票，但数据库只减了1张票。原因： 两个售票
过程（事务）交叉进行，发生了相互干扰。并发执行的事务，可能会同时读写数据库中同一数据的情况，如果不加以控制，可能会引起读写数据的冲
突，对数据库的一致性会造成破坏。事务对数据库中数据可以进行哪些操作？ 读操作和写操作读和写，哪个可能会导致数据不正确？读不会破坏
数据，但写可能导致数据不正确。事务并发执行可能引发的问题？读-读读-写写-读 写-写 保持数据一致性不可重复读读脏数据丢失更新
6.3.2 并发执行可能引起的问题1. 丢失更新又称为覆盖未提交的数据 。原因：由于两个(或多个)事务对同一数据并发地写入引起，
称为写—写冲突。结果：与串行地执行两个(或多个)事务的结果不一致。2、不可重复读 又称为读值不可复现。 原因：该问题因读—写冲突引
起。结果：第二次读的值与前次读的值不同。 幻影读（phantom red）也属于不可重复读的问题。幻影读 与不可重复的区别是
：不可重复读的操作对象是数据，而幻影读的操作对象是表中的记录。3、读脏数据又称为读未提交的数据 。原因：由于后一事务读了前一个事务
写了但尚未提交的数据引起，称为写—读冲突。结果：读到有可能要回退的更新数据。 事务并发操作引发问题的解决方法方法一：设置事务隔离级
别 方法二：封锁6.3.3 事务隔离级别隔离级别定义了一个事务与其他事务的隔离程度。并发事务发生的4种异常情况
丢失更新 读脏数据 不可重复读 幻影读。（1）read uncommitted（
未提交读）用户可以对数据执行未提交读；在事务结束前可以更改数据集内的数值，行也可以出现在数据集中或从数据集消失。它是4个级别中限制
最小的级别。（2）read committed（提交读）此隔离级别不允许用户读一些未提交的数据，因此不会出现读脏数据的情况，但数据
可以在事务结束前被修改，从而产生不可重复读或幻影数据。（3）repeatable read（重复读）此隔离级别保证在一个事务中重复
读到的数据会保持同样的值，而不会出现读脏数据、不可重复读的问题。但允许其他用户将新的幻影行插入数据集，且幻影行包括在当前事务的后续
读取中。（4）serializable（串行读）此隔离级别是4种隔离级别中限制最大的级别，称为可串行读，不允许其它用户在事务完成之
前更新数据集或将行插入数据集内。事务的4种隔离级别1．MySQL隔离级别的设置6.3.4 MySQL事务隔离级别设置SET [
GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL SERIALIZABLE |
REPEATABLE READ |READ COMMITED | READ UNCOMMITED;GLOBAL，定义
的隔离级别适用于所有的SQL用户。SESSION，定义的隔离级别只适用于当前运行的会话和连接。MySQL默认的事务隔离级别是REP
EATABLE READ。系统变量@@TRANSACTION_ISOLATION存储了事务的隔离级别。2. READ UNCOMM
ITED隔离级别 所有事务都可以看到其他未提交事务的执行结果。该隔离级别很少用于实际应用。【例6-7】脏读现象示例。 （1）打
开MySQL客户机AUSE test;SET SESSION TRANSACTION ISOLATION LEVEL READ U
NCOMMITTED;SELECT @@transaction_isolation;START TRANSACTION;SELEC
T 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; ##
未提交事务 （3）打开MySQL客户机ASELECT FROM account; （4）关闭MySQL客户机A与MySQL客户
机B，由于两个客户机的事务都没有提交，所以，account表中的数据没有变化，''李三''账户的余额仍然是200。3. READ CO
MMITED隔离级别 一个事务只能看见已提交事务所做的改变。避免脏读现象，但可能出现不可重复读和幻影读。【例6-8】不可重复读现
象示例。 （1）打开MySQL客户机AUSE test;SET SESSION TRANSACTION ISOLATION LE
VEL READ COMMITTED;SELECT @@transaction_isolation;START TRANSACTI
ON;SELECT FROM account; （2）打开MySQL客户机BSET SESSION TRANSACTION I
SOLATION LEVEL READ COMMITTED;START TRANSACTION;UPDATE account SE
T balance=balance+1000 WHERE account_no=1;COMMIT; （3）打开MySQL客户机AS
ELECT FROM account; MySQL客户机A在同一个事务中两次执行“SELECT FROM accou
nt;”的结果不相同，造成不可重复读现象。4. REPEATABLE READ隔离级别 是MySQL的默认事务隔离级别，它确保在
同一事务内相同的查询语句的执行结果一致。避免脏读及不可重复读的现象，但可能出现幻影读现象。【例6-9】幻影读现象示例。 （1）打
开MySQL客户机AUSE test;SET SESSION TRANSACTION ISOLATION LEVEL REPEAT
ABLE READ;SELECT @@transaction_isolation;START TRANSACTION;SELECT
FROM account; （2）打开MySQL客户机BSET SESSION TRANSACTION ISOLATION
LEVEL REPEATABLE READ;START TRANSACTION;INSERT INTO account VALUE
S(10,''赵六'',3000);COMMIT;SELECT FROM account; （3）打开MySQL客户机ASELEC
T FROM account; 查询结果显示account表中不存在account_no=10的账户信息。 （4）由于MySQ
L客户机A检测到account表中不存在account_no=10的账户信息，在MySQL客户机A继续执行下面INSERT语句。I
NSERT INTO account VALUES(10,''赵六'',3000); 运行结果显示account表中确实存在acc
ount_no=10的账户信息，但由于REPEATABLE READ（可重复读）隔离级别使用了“障眼法”，使得MySQL客户机A无
法查询到account_no=10的账户信息，这种现象称为幻影读现象。5. SERIALIZABLE隔离级别 是最高的隔离级别，
它通过强制事务排序，使之不可能相互冲突。【例6-10】避免幻影读现象示例。 （1）打开MySQL客户机AUSE test;SET
SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;SELECT @@transa
ction_isolation;START TRANSACTION;SELECT FROM account; （2）打开MyS
QL客户机BSET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;START
TRANSACTION;INSERT INTO account VALUES(20,''马七'',5000);SELECT FRO
M account; 由于发生了锁等待超时引发的错误异常，事务被回滚，所以account_no=20的账户信息并没有添加到a
ccount表中。 对于大部分应用来说，READ COMMITTED是最合适的隔离级别。 如果所处的数据库中具有大量的并发
事务，并且对事务的处理和响应速度要求较高，则使用READ COMMITTED隔离级别比较合适。 如果所连接的数据库用户比较少，多
个事务并发地访问同一资源的概率比较小，并且用户的事务可能会执行很长一段时间，在这种情况下使用REPEATABLE READ或SER
IALIZABLE隔离级别较合适6.4封锁机制数据库原理与应用57一个锁实质上就是允许（或阻止）一个事务对一个数据对象的存取特权。
一个事务对一个对象加锁的结果是将其它事务“封锁”在该对象之外，特别是防止了其他事务对该对象的更改，而加锁的事务则可以执行它所希望的
处理并维持该对象的正确状态。6.4.1 锁（1）排它锁（X锁、写锁）事务更新数据前必须先加上X锁；数据对象加上X锁，其它事务对该对
象即不能加S锁也不能加X锁事务对数据加X锁后，对锁定数据即能读取也能修改。（2）共享锁（S锁、读锁） 事务读取数据前必须先加上S锁
； 数据对象加上S锁后，其它事务只能对该对象加S锁不能加X锁事务对数据加S锁后，对锁定数据只能读取。1．锁的类型（3）意向锁意向锁
分为意向共享锁（IS）和意向排他锁（IX）两类。意向锁表示一个事务有意向在某些数据上加共享锁或者排他锁。2．锁的相容矩阵封锁对象的
大小称为封锁粒度。 封锁的对象可以是字段、记录、表等逻辑单元；也可以是页（数据页或索引页）、块等物理单元。3．锁的粒度封锁粒度越小
，系统中能够被封锁的对象就越多，但封锁机构复杂，系统开销也就越大。封锁粒度越大，系统中能够被封锁的对象就越少，并发度越小，封锁机构
简单，相应系统开销也就越小。实际应用中，选择封锁粒度应同时考虑封锁开销和并发度两个因素，对系统开销与并发度进行权衡，以求得最优的效
果。需要处理大量元组的用户事务可以以关系为封锁单元；对于一个处理少量元组的用户事务，可以以元组为封锁单位 并发操作带来的问题？
丢失更新 读“脏”数据 不可重复读 封锁协议一级封锁协议二级封锁协议三级封锁协议封锁协议：在运用X锁和S锁对数据对象加锁时
，还需要约定一些规则，如：何时申请X锁或S锁、持锁时间、何时释放等，这些规则称为封锁协议。6.4.2 封锁协议写-写操作导致“丢
失更新”问题如何加锁？写操作前加X锁。1．一级封锁协议不同级别的封锁协议和一致性保证√√写-读操作导致“读脏数据”问题如何加锁？写
操作前加X锁读操作前加S锁数据对象加了X锁，还能再加S锁？不能2．二级封锁协议不同级别的封锁协议和一致性保证√√√√√√原因：S锁
操作结束即被释放读-写操作导致“不可重复读”问题如何加锁？写操作前加X锁读操作前加S锁不能数据对象加了S锁，还能再加X锁？3．三级
封锁协议不同级别的封锁协议和一致性保证√√√√√√√√√√√出现这种T1在等待T2，T2又在等待T1的局面，致使T1和T2两个事务
永远不能结束，形成“死锁”。1、死锁6.4.3 “死锁”问题在应用中，如果不同的程序会并发存取多个表，应尽量约定以相同的顺序来访问
表。2、避免死锁的常用方法在程序以批量方式处理数据的时候，如果事先对数据排序，保证每个线程按固定的顺序来处理记录。在事务中，如果要
更新记录，应该直接申请足够级别的排他锁，而不应先申请共享锁，更新时再申请排他锁。在REPEATABLE READ隔离级别下，如果两
个线程同时对相同条件记录加排他锁，在没有符合该条件记录情况下，两个线程都会加锁成功。程序发现记录尚不存在，就会试图插入一条新记录，
如果两个线程都这么做就会出现死锁。这种情况下，将隔离级别改为READ COMMITTED，就可以避免问题。当隔离级别为READ C
OMMITTED时，如果两个线程都先执行SELECT…FOR UPDATE，判断是否存在符合条件的记录，如果没有，就插入记录。此时
，只有一个线程能插入成功，另一个线程会出现锁等待，当第1个线程提交后，第2个线程会因主键值重复而出错，虽然这个线程出错了，却会获得
一个排他锁，这时如果有第3个线程又来申请排他锁，也会出现死锁。对于这种情况，可以直接做插入操作，然后再捕获主键值重复的异常情况，或
者在遇到主键值重复错误时，总是执行ROLLBACK释放获得的排他锁。6.5MySQL的并发控制数据库原理与设计73LOCK TAB
LES 表名 READ|WRITE [,表名 READ|WRITE,…] ;READ施加表级读锁，WRITE施加表级写锁。6.5
.1 表级锁1、加表级锁:2、解锁:UNLOCK TABLES;【例6-14】表级锁示例。 （1）打开MySQL客户机AUS
E test;LOCK TABLES account READ;SELECT FROM account;INSERT INTO
account VALUES(''100'',''王小一'',5000); （2）打开MySQL客户机BUSE test;LOCK T
ABLES account READ;SELECT FROM account;UNLOCK TABLES;LOCK TABLE
S account WRITE; （3）打开MySQL客户机AUNLOCK TABLES;LOCK TABLES account
WRITE;INSERT INTO account VALUES(20,''马七'',5000);SELECT FROM acc
ount; （4）打开MySQL客户机BUNLOCK TABLES;SELECT FROM 表名 WHERE 条件
LOCK IN SHARE MODE;6.5.2 行级锁1、在查询语句中，为符合查询条件的记录施加共享锁2、在查询语句中，为符合查询条件的记录施加排他锁SELECT FROM 表名 WHERE 条件 FOR UPDATE;3、在更新（INSERT、UPDATE、DELETE）语句中，MySQL将会对符合条件的记录自动施加隐式排他锁。【例6-15】行级锁示例。 （1）打开MySQL客户机AUSE test;START TRANSACTION;SELECT FROM account WHERE account_no=20 FOR UPDATE; （2）打开MySQL客户机BUSE test;START TRANSACTION;SELECT FROM account WHERE account_no=20 FOR UPDATE; （3）打开MySQL客户机A,为account表解锁。COMMIT;SELECT FROM account WHERE account_no=20 FOR UPDATE; （4）打开MySQL客户机B，因为MySQL客户机A释放了account表的行级锁，MySQL客户机B被“唤醒”，得以继续执行。COMMIT;意向共享锁（IS），事务在给一个数据行加共享锁之前必须先取得该表的IS锁。6.5.3 表的意向锁意向排他锁（IX），事务在给一个数据行加排他锁之前必须先取得该表的IX锁。意向锁是InnoDB自动加的，不需要用户干预。对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁；对于普通SELECT语句，InnoDB不会加任何锁。Thank You !