数据库原理及应用（MySQL项目式版）- 课件 项目9 事务

项目9

事务项目目标了解事务的概念和事务的基本特性。掌握事务控制语句的使用。了解事务并发会引起的问题。了解事务隔离级别与锁的关系。任务9.1认识事务在MySQL中，事务完全依赖存储引擎实现，但并不是所有存储引擎都支持事务。InnoDB是支持事务的存储引擎之一。9.1.1事务的概念事务是一个不可分割的程序执行单元，由一条或多条数据操作命令语句组成。这些数据操作命令语句要么全部执行，要么全部不执行。只要有一条操作命令语句执行失败，整个事务都将撤销（回滚），所有被影响的数据都将恢复到事务开始前的状态。与事务相关的数据操作命令语句包括INSERT、UPDATE和DELETE语句。在MySQL中，通过日志文件来记录数据库的所有变化，为事务回滚提供依据。9.1.1事务的概念事务处理机制在程序编写过程中有非常重要的作用，可以使整个系统更加安全。以银行转账业务为例，该事务主要包括以下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要么在事务1开始之前已经结束，要么在事务1结束后才开始。隔离性使得每个事务都感觉不到有其他事务在并发执行。4．持久性（Durability）持久性是指事务完成后，对数据的修改是永久的，即便系统发生故障也不会丢失。例如，提交一个事务后，计算机“瘫痪”或者数据库因故障受到破坏，重启计算机后，该事务的执行结果依然存在。任务9.2单个事务控制1．开始事务STARTTRANSACTION|BEGINWORK;在MySQL中，事务是不允许嵌套的。在一个事务开始后，如果使用STARTTRANSACTION或BEGINWORK语句开始新事务，则系统会自动提交并结束当前的事务。任务9.2单个事务控制2．执行数据操作命令语句【例9-1】开始一个事务，使用每次插入一条记录的方式向读者表reader中插入两条记录（1101045110，1，孙豪达，男，外国语学院）和（2107240120，2，刘明星，女，数理学院）。①开始事务。STARTTRANSACTION;②插入第1条记录。INSERTINTOreaderVALUES('1101045110',1,'孙豪达','男','外国语学院');③插入第2条记录。INSERTINTOreaderVALUES('2107240120',2,'刘明星','女','数理学院');④使用SELECT语句查看记录是否插入成功。SELECT*FROMreaderWHEREreader_idIN('1101045110','2107240120');只有当前用户可以看到新记录插入成功+------------+---------+--------+--------+------------+|reader_id

|type_id|name

|gender|dept

|+------------+---------+--------+--------+------------+|1101045110|

1|孙豪达|男

|外国语学院||2107240120|

2|刘明星

|女

|数理学院

|+------------+---------+--------+--------+------------+任务9.2单个事务控制3．结束事务提交事务或回滚事务都标志着事务的结束。。（1）提交事务COMMIT;（2）回滚事务ROLLBACK;任务9.2单个事务控制【例9-2】开始一个事务，使用每次插入一条记录的方式向读者表reader中插入两条记录（1101045111，1，孙豪达1，男，外国语学院）和（2107240121，2，刘明星1，女，数理学院），最后提交事务。STARTTRANSACTION;INSERTINTOreaderVALUES('1101045111',1,'孙豪达1','男','外国语学院');INSERTINTOreaderVALUES('2107240121',2,'刘明星1','女','数理学院');COMMIT;当前事务提交后，其他用户就可以使用SELECT语句查询到插入的记录。任务9.2单个事务控制【例9-3】开始一个事务，使用每次插入一条记录的方式向读者表reader中插入两条记录（1101045112，1，孙豪达2，男，外国语学院）和（2107240122，2，刘明星2，女，数理学院），最后回滚事务。STARTTRANSACTION;INSERTINTOreaderVALUES('1101045112',1,'孙豪达2','男','外国语学院');INSERTINTOreaderVALUES('2107240122',2,'刘明星2','女','数理学院');ROLLBACK;当前事务回滚后，使用SELECT语句查询，可以看到新记录不存在，数据库恢复到事务开始前的状态。mysql>SELECT*FROMreaderWHEREreader_idIN('1101045112','2107240122');Emptyset(0.00sec)任务9.2单个事务控制【例9-4】开始一个事务，向读者表reader中插入一条记录（1101045113，1，孙豪达3，男，外国语学院）后设置一个保存点sp1，然后向读者表reader中插入另一条记录（2107240123，2，刘明星3，女，数理学院），最后将事务回滚到保存点sp1并提交。STARTTRANSACTION;INSERTINTOreaderVALUES('1101045113',1,'孙豪达3','男','外国语学院');SAVEPOINTsp1;INSERTINTOreaderVALUES('2107240123',2,'刘明星3','女','数理学院');ROLLBACKTOSAVEPOINTsp1;COMMIT;事务回滚到保存点sp1，使保存点sp1后的INSERT语句被回滚了。mysql>SELECT*FROMreaderWHEREreader_idIN('1101045113','2107240123');+------------+---------+---------+--------+------------+|reader_id|type_id|name

|gender|dept

|+------------+---------+---------+--------+------------+|1101045113|

1|孙豪达3|男

|外国语学院|+------------+---------+---------+--------+------------+任务9.3事务并发控制当多个用户同时访问同一个数据库对象时，在一个用户修改数据的过程中，其他用户可能也要修改数据。因此，为了保证数据的一致性，需要对事务的并发操作进行控制。9.3.1事务并发会引起的问题1．丢失更新（

LostUpdate）分为两类：一类是回滚丢失更新，另一类是覆盖丢失更新。回滚丢失更新是指后一个事务回滚时覆盖了前一个事务提交的数据更新所造成的数据丢失。9.3.1事务并发会引起的问题覆盖丢失更新是指后一个事务提交的数据更新覆盖了前一个事务提交的数据更新所造成的数据丢失。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中，不同的事务隔离级别是通过不同的锁机制来实现的，而且是由数据库自动完成的，不需要人为干预。9.3.3锁机制1．锁的类型按照锁的共享策略，可将锁划分为2种：（1）共享锁（SharedLock）：也称S锁或读锁（ReadLock）。一个事务对数据加共享锁后，其他事务只能读取这些数据，不能修改，可以避免不可重复读问题。（2）排他锁（ExclusiveLock）：也称X锁或写锁（WriteLock）。一个事务对数据加排他锁后，只有该事务可以操作这些数据，其他的事务既不能读，也不能修改，可以避免脏读问题。9.3.3锁机制1．锁的类型按照对数据操作的粒度，可将锁划分为3种：（1）表级锁：锁定整张表，其他事务访问同一张表时将受到限制。（2）行级锁：只锁定所使用的一条或多条记录，当其他事务访问同一张表时，只有被锁定的记录不能访问，其他的记录可以正常访问。（3）页级锁：介于表级锁和行级锁之间，是MySQL中独特的一种锁机制，它锁定表中相邻的一组记录作为页来实现事务并发控制。9.3.3锁机制2．隔离级别与锁的关系（1）在未提交读级别下，在读取数据时不加锁；在修改数据的过程中，对需要更新的数据加行级（或页级）共享锁，使其他事务不能修改这些数据，但是可以读取数据，直到事务结束。因此，其他事务可以读取到未提交的数据，不能避免脏读问题。（2）在提交读级别下，在读取数据时加行级（或页级）共享锁，读取结束就释放；在修改数据的过程中，对需要更新的数据加行级（或页级）排他锁，直到事务结束。这样可有效防止其他事务读取到未提交的数据。但由于读取结束后就立即释放了行级（或页级）共享锁，因此可能会出现从同一事务中读取到的数据前后不一致的情况，不能避免不可重复读问题。（3）在可重复读级别下，在读取数据时加行级（或页级）共享锁，直到事务结束；在修改数据的过程中，对需要更新的数据加行级（或页级）排他锁，直到事务结束。因此不会出现从同一事务中读取到的数据前后不一致的情况。但由于锁仅限制数据的读取和修改，不限制数据的插入和删除，因此不能避免幻读问题。（4）在序列化级别下，在读取数据时加表级共享锁，直到事务结束；在修改数据时加表级排他锁，直到事务结束。因此其他事务不能读写该表中的任何数据，可以避免由事务并发操作引起的问题。项目案例：图书馆借还书管理数据库的事务控制1．新增读者事务控制要求编写存储过程，使用每次插入一条记录的方式将两条记录（2107230116，2，马玉兰，女，数理学院）和（2107240101，2，李淑子，女，数理学院）插入读者表reader中。（1）不使用事务控制的存储过程。（2）使用事务控制的存储过程。项目案例：图书馆借还书管理数据库的事务控制（1）不使用事务控制的存储过程。DELIMITER$$CREATEPROCEDUREp_insert_reader()BEGININSERTINTOreaderVALUES('2107230116',2,'马玉兰','女','数理学院');INSERTINTOreaderVALUES('2107240101',2,'李淑子','女','数理学院');END$$DELIMITER;读者编号reader_id为2107240101的读者已经存在，插入第2条记录时违反了reader_id的主键约束，导致错误。最终只有第1条记录插入成功了。项目案例：图书馆借还书管理数据库的事务控制（2）使用事务控制的存储过程。DELIMITER$$DROPPROCEDUREIFEXISTSp_insert_reader;

/*删除同名的存储过程*/CREATEPROCEDUREp_insert_reader()BEGINDECLAREhasErrorINTDEFAULTFALSE;

/*声明错误标志变量hasError*/DECLARECONTINUEHANDLERFORSQLEXCEPTIONSEThasError=TRUE; /*声明错误处理程序*/STARTTRANSACTION;

/*开始事务*/INSERTINTOreaderVALUES('2107230116',2,'马玉兰','女','数理学院');INSERTINTOreaderVALUES('2107240101',2,'李淑子','女','数理学院');IFhasError=TRUETHEN

/*发生异常，则回滚*/ROLLBACK;SELECT"失败"AS插入结果;ELSE

/*未发生异常，则提交*/COMMIT;SELECT"成功"AS插入结果;ENDIF;END$$DELIMITER;执行结果两条INSERT语句都回滚了，新记录插入失败，与期望的结果一致。项目案例：图书馆借还书管理数据库的事务控制2．借阅图书事务控制要求在存储过程中定义事务，根据给定的reader_id和馆藏编号stock_id实现图书借阅，借出时间填写当前时间。为了保证数据的一致性，如果该读者已借阅的图书数量没有达到所属读者类型的最大借阅数量且该图书的状态status值为0（在馆），不仅要在借还书表borrow中插入一条借阅记录，还要在馆藏表stock中修改该图书的status值为1（借出）。只有在borrow表中插入借阅记录和在stock表中修改该图书状态的操作都成功，才提交，否则回滚，输出借阅是否成功的提示信息。项目案例：图书馆借还书管理数据库的事务控制DELIMITER$$CREATEPROCEDUREp_borrow(INvrdidVARCHAR(10),INvstidCHAR(8))BEGINDECLAREhasErrorINTDEFAULTFALSE; /*错误标志变量hasError*/DECLAREvtype,vnum,vmax,vupdateTINYINT;

/*声明变量*/DECLARECONTINUEHANDLERFORSQLEXCEPTIONSEThasError=TRUE;

/*错误处理程序*/SELECTCOUNT(borrow_id)INTOvnumFROMborrow

/*查询已借未还图书数量*/WHEREreader_id=vrdidANDreturntimeISNULL;SELECTtype_idINTOvtypeFROMreaderWHEREreader_id=vrdid; /*类型编号*/SELECTmaxborrowINTOvmaxFROMreadertypeWHEREtype_id=vtype;

/*最大借阅数量*/IFvnum<vmaxTHENSTARTTRANSACTION; /*开始事务*/INSERTINTOborrowVALUES(0,vrdid,vstid,NOW(),NULL);UPDATEstockSETstatus=1WHEREstock_id=vstidANDstatus=0;SELECTROW_COUNT()INTOvupdate;

/*查询修改图书状态数量*/IFhasErrorORvupdate=0THENROLLBACK;SELECT"失败"AS借阅结果;ELSECOMMIT;SELECT"成功"AS借阅结果;ENDIF;ELSESELECT"借阅数量已经达到最大"AS借阅结果;ENDIF;END$$DELIMITER;项目案例：图书馆借还书管理数据库的事务控制2．借阅图书事务控制