MySQL数据库教程（第2版）-课件 第9章事务

第9章事务学习目标了解事务的概念及基本特性。掌握事务控制语句。了解事务并发会引起的问题。了解事务隔离级别与锁的关系。9.1事务概述在MySQL中，事务完全依赖存储引擎实现，但并不是所有存储引擎都支持事务。InnoDB是支持事务的存储引擎之一。9.1.1事务的概念事务是一个不可分割的程序执行单元，由一条或多条数据操作命令语句组成。这些数据操作命令语句要么全部执行，要么全部不执行。只要有一条数据操作命令语句执行失败，整个事务都将回滚，所有被影响的数据都将恢复到事务开始前的状态。与事务相关的数据操作命令语句包括INSERT、UPDATE和DELETE。在MySQL中，通过日志文件来记录数据库的所有变化，并为事务回滚提供依据。。

事务举例以银行转账业务为例，要将1000元资金从A账户转到B账户，在账户余额充足的条件下，需要从A账户中减去转出的1000元，然后向B账户中增加转入的1000元。该事务主要包括以下3个步骤。①查询A账户的余额是否足够。②从A账户减去1000元。③向B账户增加1000元。在银行办理转账业务时，如果A账户中的资金已经转出（步骤②执行成功），但B账户由于发生故障未转入资金（步骤③执行失败），或者A账户中的资金由于网络故障未转出（步骤②执行失败），而B账户却已经完成了资金转入（步骤③执行成功），会给个人和银行带来很大的经济损失。采用事务处理机制后，一旦在转账过程中发生意外（步骤②或步骤③执行失败），整个转账业务将全部回滚，使A账户和B账户都恢复到转账前的状态，从而避免造成经济损失。9.1.2事务的基本特性——ACID1．原子性（Atomicity）原子性是指事务中的所有数据操作命令语句要么全部执行，要么全部不执行。以银行转账业务为例，一旦在转账过程中发生意外，相关账户能够回滚到转账前的状态。2．一致性（Consistency）一致性是指事务必须保证数据的状态保持一致。以银行转账业务为例，在转账前和转账后，A账户和B账户的资金总额不会发生改变。3．隔离性（Isolation）隔离性是指多个并发事务可以独立执行，彼此不会产生影响。例如，对于任意两个并发执行的事务1和事务2，在事务1看来，事务2要么在自己开始之前已经结束，要么在自己结束后才开始。隔离性使得每个事务都感觉不到有其他事务在并发执行。4．持久性（Durability）持久性是指事务执行完成后，对数据的更新是永久的，即便系统发生故障也不会丢失。例如，提交一个事务后，计算机“瘫痪”或者数据库因故障受到破坏，重启计算机后，该事务的执行结果依然存在。9.2单个事务控制1．开始事务STARTTRANSACTION|BEGINWORK;在MySQL中，事务是不允许嵌套的。在一个事务开始后，如果使用START TRANSACTION或BEGINWORK语句开始新事务，则系统会自动提交并结束当前的事务。9.2单个事务控制2．执行数据操作命令语句【例9-1】开始一个事务，使用每次插入一条记录的方式向课程表course中插入两条记录，分别是(10600150,软件工程,2.5)和(10600710,数据结构,4.0)。①开始事务。STARTTRANSACTION;②插入第一条记录。INSERTINTOcourseVALUES('10600150','软件工程',2.5);③插入第二条记录。INSERTINTOcourseVALUES('10600710','数据结构',4.0);④查看记录是否插入成功。SELECT*FROMcourseWHEREcrsno='10600150'ORcrsno='10600710';只有当前用户可以看到新记录插入成功。mysql>SELECT*FROMcourseWHEREcrsno='10600150'ORcrsno='10600710';+------------+-----------+--------+|crsno|crsname|credit|+------------+-----------+--------+|10600150|软件工程|2.5||10600710|数据结构

|4.0|+------------+-----------+--------+2rowsinset(0.00sec)9.2单个事务控制3．结束事务提交事务或回滚事务都标志着一个事务的结束。（1）提交事务COMMIT;（2）回滚事务ROLLBACK;9.2单个事务控制【例9-2】开始一个事务，使用每次插入一条记录的方式向课程表course中插入两条记录，分别是(10600151,软件工程A,2.5)和(10600711,数据结构A,4.0)，最后提交事务。STARTTRANSACTION;INSERTINTOcourseVALUES('10600151','软件工程A',2.5);INSERTINTOcourseVALUES('10600711','数据结构A',4.0);COMMIT;当前事务提交后，其他用户就可以使用SELECT语句查询到插入的记录。mysql>SELECT*FROMcourseWHEREcrsno='10600151'ORcrsno='10600711';+------------+-----------+---------+|crsno|crsname|credit|+------------+-----------+---------+|10600151|软件工程A|2.5||10600711|数据结构A|4.0|+------------+-----------+---------+2rowsinset(0.00sec)9.2单个事务控制【例9-3】开始一个事务，使用每次插入一条记录的方式向课程表course中插入两条记录，分别是(10600152,软件工程B,2.5)和(10600712,数据结构B,4.0)，最后回滚事务。

STARTTRANSACTION;INSERTINTOcourseVALUES('10600152','软件工程B',2.5);INSERTINTOcourseVALUES('10600712','数据结构B',4.0);ROLLBACK;当前事务回滚后，使用SELECT语句进行查询，可以看到新记录不存在，说明数据库已恢复到事务开始前的状态。mysql>SELECT*FROMcourseWHEREcrsno='10600152'ORcrsno='10600712';Emptyset(0.01sec)9.2单个事务控制除了可以回滚整个事务，还可以使事务回滚到某个点，但前提是之前使用SAVEPOINT语句设置了保存点。【例9-4】开始一个事务，向课程表course中插入一条记录(10600153,软件工程C,2.5)后设置一个保存点sp1，再插入另一条记录(10600713,数据结构C,4.0)，最后将事务回滚到保存点sp1并提交。

STARTTRANSACTION;INSERTINTOcourseVALUES('10600153','软件工程C',2.5);SAVEPOINTsp1;INSERTINTOcourseVALUES('10600713','数据结构C',4.0);ROLLBACKTOSAVEPOINTsp1;COMMIT;当前事务回滚到保存点sp1并提交。使用SELECT语句进行查询，可以看到实际上只插入了一条记录，保存点sp1后的INSERT语句被回滚了mysql>SELECT*FROMcourseWHEREcrsno='10600153'ORcrsno='10600713';+------------+-----------+--------+|crsno|crsname|credit|+------------+-----------+--------+|10600153|软件工程C|2.5|+------------+-----------+--------+1rowinset(0.00sec)9.3事务并发控制当多个用户同时访问同一个数据库对象时，在一个用户更新数据的过程中，其他用户可能也要更新数据。因此，为了保证数据的一致性，需要对事务的并发操作进行控制。9.3.1事务并发会引起的问题1．丢失更新（lostupdate）分为两类：一类是回滚丢失更新，另一类是覆盖丢失更新。回滚丢失更新是指后一个事务回滚时覆盖了前一个事务提交的数据更新所造成的数据丢失。9.3.1事务并发会引起的问题1．丢失更新（lostupdate）覆盖丢失更新是指后一个事务提交的数据更新覆盖了前一个事务提交的数据更新所造成的数据丢失。9.3.1事务并发会引起的问题2．脏读（dirtyread）脏读是指一个事务读取了另一个事务未提交的数据。通常将未提交且随后又被回滚的数据称为脏数据。。9.3.1事务并发会引起的问题3．不可重复读（non-repeatableread）不可重复读是指同一个事务前后两次读取的数据不同。9.3.1事务并发会引起的问题4．幻读（phantomread）幻读是指同一个事务前后两次使用相同的查询语句得到的执行结果的记录数不同。9.3.2事务隔离级别为了解决事务并发引起的丢失更新、脏读、不可重复读和幻读问题，MySQL提供了4种事务隔离级别。1．读未提交（ReadUncommitted）提供事务之间最小限度的隔离，所有事务都可以看到其他未提交事务的执行结果。该级别不能彻底解决丢失更新、脏读、不可重复读和幻读问题，在实际中很少应用。。2．读已提交（ReadCommitted）满足事务隔离性的定义，多个并发事务独立执行，一个事务只能看到已提交事务所做的数据更新。该级别可以解决回滚丢失更新和脏读问题，但不能彻底解决不可重复读、覆盖丢失更新和幻读问题。3．可重复读（RepeatableRead）MySQL默认的事务隔离级别，可以确保同一个事务内相同的查询语句得到的执行结果一致。该级别可以解决丢失更新、脏读和不可重复读问题，但不能彻底解决幻读问题。4．序列化（Serializable）提供严格的事务隔离，事务只能一个接着一个地执行，不能并发执行。这样就能保证事务之间最大限度的隔离。该级别可以解决丢失更新、脏读、不可重复读和幻读问题。9.3.2事务隔离级别低级别的事务隔离可以提高事务的并发执行性能，但不能彻底解决丢失更新、脏读、不可重复读和幻读等并发问题；高级别的事务隔离可以有效地解决并发问题，但会降低事务的并发执行性能。9.3.3锁机制锁机制是实现事务并发控制的主要方法和重要手段。在MySQL中，不同的事务隔离级别是通过不同的锁机制来实现的。1．锁的类型按照锁的共享策略，可将锁划分为共享锁和排他锁两种：（1）共享锁：也称S锁或读锁（ReadLock）。一个事务对数据添加共享锁后，其他事务只能读取这些数据，不能更新，以避免不可重复读问题。（2）排他锁：也称X锁或写锁（WriteLock）。一个事务对数据添加排他锁后，只有该事务可以操作这些数据，其他事务既不能读，也不能更新，以避免脏读问题。1．锁的类型按照数据操作的粒度，可将锁划分为表级锁、行级锁和页级锁3种类型：（1）表级锁：锁定整张表，其他事务访问同一张表时将受到限制。（2）行级锁：只锁定所使用的一条或多条记录，当其他事务访问同一张表时，只有被锁定的记录不能访问，其他的记录可以正常访问。。（3）页级锁：介于表级锁和行级锁之间，是MySQL中一种独特的锁机制，它锁定表中相邻的一组记录作为页来实现事务并发控制。2．事务隔离级别与锁的关系2．隔离级别与锁的关系（1）在读未提交隔离级别下，在读取数据时不加锁；在更新数据的过程中，对需要更新的数据加行级（或页级）共享锁，使其他事务不能更新这些数据，但可以读取数据，直到事务结束。因此，其他事务可以读取到未提交的数据，不能避免脏读问题。（2）在读已提交隔离级别下，在读取数据时加行级（或页级）共享锁，读取结束就释放；在更新数据的过程中，对需要更新的数据加行级（或页级）排他锁，直到事务结束。这样可有效防止其他事务读取到未提交的数据。但由于读取结束后就立即释放了行级（或页级）共享锁，因此可能会出现从同一事务中读取到的数据前后不一致的情况，不能避免不可重复读问题。（3）在可重复读隔离级别下，在读取数据时加行级（或页级）共享锁，直到事务结束；在更新数据的过程中，对需要更新的数据加行级（或页级）排他锁，直到事务结束。这样不会出现从同一事务中读取到的数据前后不一致的情况。但由于锁仅限制已有数据的读取和更新，不限制新数据的插入，因此不能避免幻读问题。（4）在序列化隔离级别下，在读取数据时加表级共享锁，直到事务结束；在更新数据时加表级排他锁，直到事务结束。因此其他事务不能读写该表中的任何数据，可以避免由事务并发操作引起的全部问题。9.4课堂案例：学生成绩管理数据库的事务控制1．新增课程事务控制编写存储过程，使用每次插入一条记录的方式将两条记录(10500260,国际金融学,2.5)和(10400350,自动控制原理,4.0)插入课程表course，其中有一条记录已经存在。（1）不使用事务控制的存储过程。（2）使用事务控制的存储过程。9.4课堂案例：学生成绩管理数据库的事务控制1．新开课程事务控制（1）不使用事务控制的存储过程。DELIMITER$$CREATEPROCEDUREsp_insert_course()BEGININSERTINTOcourseVALUES('10500260','国际金融学',2.5);INSERTINTOcourseVALUES('10400350','自动控制原理',4.0);END$$DELIMITER;由于课程编号为10400350的课程已经存在，插入第二条记录时违反了crsno字段的主键约束，导致出现错误。最终只有第一条记录插入成功。mysql>CALLsp_insert_course();ERROR1062(23000):Duplicateentry'10400350'forkey'course.PRIMARY'9.4课堂案例：学生成绩管理数据库的事务控制1．新开课程事务控制（2）使用事务控制的存储过程。DELIMITER$$CREATEPROCEDUREsp_insert_course()BEGINDECLAREhasErrorINTDEFAULT0; --声明SQL异常错误标志变量DECLARECONTINUEHANDLERFORSQLEXCEPTIONSEThasError=1; --声明SQL异常错误处理程序STARTTRANSACTION; --开始事务INSERTINTOcourseVALUES('10500260','国际金融学',2.5); --插入记录INSERTINTOcourseVALUES('10400350','自动控制原理',4.0);IFhasError=1THEN --发生异常，则回滚ROLLBACK;SELECT'失败'AS插入结果;ELSE --未发生异常，则提交COMMIT;SELECT'成功'AS插入结果;ENDIF;END$$DELIMITER;执行结果两条INSERT语句都回滚了，新课程插入失败。mysql>CALLsp_insert_course();+----------+|插入结果|+----------+|失败

|+----------+9.4课堂案例：学生成绩管理数据库的事务控制2．班级选修课程事务控制在存储过程中定义事务，根据给定的班级名称和课程编号实现为该班级全体学生选修课程。为了保证数据的一致性，如果班级全体学生都没有选修过该课程，或者已经选修过该课程但未获得学分（成绩<60），则需要在选修成绩表score中为班级的每一名学生增加选修该课程的记录。只有全体学生都选修该课程成功，才提交，否则回滚。9.4课堂案例：学生成绩管理数据库的事务控制2．班级选修课程事务控制DELIMITER$$CREATEPROCEDUREsp_elect_crs(INp_classnoVARCHAR(20),INp_crsnochar(8))BEGINDECLAREv_stunoCHAR(11); --声明存储学号的变量DECLAREcntINT; --声明存储记录数量的变量DECLAREdoneINTDEFAULT0; --声明游标结束标志变量DECLAREhasErrorINTDEFAULT0; --声明SQL异常错误标志变量DECLAREstulistCURSORFORSELECTstunoFROMstudentWHEREclassno=p_classno; --声明游标DECLARECONTINUEHANDLERFORSQLEXCEPTIONSEThasError=1;

DECLARECONTINUEHANDLERFORNOTFOUNDSETdone=1;

OPENstulist; --打开游标，获得该班级所有学生的学号STARTTRANSACTION; --开始事务DELIMITER$$

9.4课堂案例：学生成绩管理数据库的事务控制2．班级选修课程事务控制REPEATFETCHstulistINTOv_stuno; --提取一名学生的学号IFdone=0THEN --提取成功则查询该学生选修该课程并且获得学分的情况SELECTCOUNT(*)INTOcntFROMscoreWHEREstuno=v_stunoandcrsno=p_crsnoandgrade>=60;IFcnt=0THEN --学生没选修过或者已经选修过但未获得学分，选修课程 INSERTINTOscore(stuno,crsno)VALUES(v_stuno,p_crsno);

ELSE --学生已经选修过该课程并获得学分SETdone=1; --设置done值以结束循环SEThasError=1; --设置选修课程发生异常ENDIF;ENDIF;UNTILdone=1 --done值为1则结束循环ENDREPEAT;9.4课堂案例：学生成绩管理数据库的事务控制2．班级选修课程事务控制CLOSEstulist; --关闭游标IFhasError=1THEN --选修课程发生异常，则回滚ROLLBACK;SELECT'失败'AS选修课程结果;ELSE --选修课程未发生异常，则提交COMMIT;SELECT'成功'AS选修课程结果;ENDIF;END$$DELIMITER;9.4课堂案例：学生成绩管理数据库的事务控制2．班级选修课程事务控制在存储过程中定义事务，根据给定的班级名称和课程编号实现为该班级全体学生选修课程。为了保证数据的一致性，如果班级全体学生都没有选修过该课程，或者已经选修过该课程但未获得学分（成绩<60），则需要在选修成绩表score中为班级的每一名学生增加选修该课程的记录。只有全体学生都选修该课程成功，才提交，否则回滚。（1）模拟异常情况。完成为电气2401班全体学生选修课程编号为10600140的课程。其中电气2401班李华已经选修过该课程并获得了学分。mysql>CALLsp_elect_crs('电气2401','10600140');+--------------+|选修课程结果|+--------------+|失败|+--------------+9.4课堂案例：学生成绩管理数据库的事务控制2．